24 أمثلة مفيدة من Excel Macro للمبتدئين من VBA (جاهز للاستخدام)

يمكن أن يؤدي استخدام وحدات ماكرو Excel إلى تسريع عملك وتوفير الكثير من الوقت.

تتمثل إحدى طرق الحصول على رمز VBA في تسجيل ماكرو والحصول على الكود الذي ينشئه.ومع ذلك ، غالبًا ما يكون رمز مسجل الماكرو مليئًا بالشفرة التي ليست ضرورية حقًا.مسجل الماكرو أيضا له بعض القيود.

لذلك من المفيد امتلاك مجموعة مفيدة من وحدات ماكرو VBA التي يمكنك الاحتفاظ بها في جيبك الخلفي واستخدامها عند الحاجة.

على الرغم من أن الأمر قد يستغرق بعض الوقت لترميز ماكرو Excel VBA في البداية ، فبمجرد الانتهاء من ذلك ، يمكنك استخدامه كمرجع واستخدامه في المرة التالية التي تحتاجها فيها.

في هذه المقالة الضخمة ، سأدرج بعض أمثلة ماكرو Excel المفيدة التي أحتاجها كثيرًا وأحتفظ بها في قبو الخاص.

سأستمر في تحديث هذا البرنامج التعليمي بمزيد من أمثلة الماكرو.يرجى ترك تعليق إذا كنت تعتقد أن شيئًا ما يجب أن يكون على القائمة.

يمكنك وضع إشارة مرجعية على هذه الصفحة للرجوع إليها في المستقبل.

الآن ، قبل الدخول في أمثلة الماكرو وإعطائك رمز VBA ، اسمح لي أولاً أن أوضح لك كيفية استخدام رمز المثال.

استخدام الكود من مثال ماكرو Excel

فيما يلي الخطوات التي يجب اتباعها لاستخدام الرمز من أي من الأمثلة:

• افتح المصنف الذي تريد استخدام الماكرو فيه.
• اضغط باستمرار على مفتاح ALT واضغط على F11.هذا سوف يفتحمحرر VB.
• انقر بزر الماوس الأيمن فوق أي عنصر في Project Explorer.
• انتقل إلى إدراج -> وحدات.
• انسخ الكود والصقه في نافذة كود الوحدة النمطية.

إذا قال المثال إنك بحاجة إلى لصق الرمز في نافذة التعليمات البرمجية لورقة العمل ، فانقر نقرًا مزدوجًا فوق كائن ورقة العمل وقم بنسخ الرمز ولصقه في نافذة التعليمات البرمجية.

بعد إدخال الرمز في المصنف ، تحتاج إلى حفظه بملحق .XLSM أو .XLS.

كيفية تشغيل الماكرو

بعد نسخ الكود في محرر VB ، تكون خطوات تشغيل الماكرو كما يلي:

• انتقل إلى علامة التبويب المطور.
• انقر فوق ماكرو.

• في مربع الحوار "وحدات الماكرو" ، حدد الماكرو المراد تشغيله.
• انقر فوق الزر "تشغيل".

إذا لم تتمكن من العثور على علامة تبويب المطور في الشريط ، فاقرأ هذا البرنامج التعليمي لمعرفة كيفية الحصول عليها.

إذا تم لصق الكود في نافذة كود ورقة العمل ، فلا داعي للقلق بشأن تشغيل الكود.يتم تشغيله تلقائيًا عند حدوث الإجراء المحدد.

الآن ، دعنا ندخل في أمثلة ماكرو مفيدة يمكن أن تساعدك على أتمتة عملك وتوفير الوقت.

ملاحظة: ستجد العديد من الفاصلات العليا (') متبوعة بسطر أو سطرين.هذه هي التعليقات التي يتم تجاهلها عند تشغيل الكود ووضعها كتعليقات ذاتية / للقارئ.

إذا وجدت أي أخطاء في المقالة أو الكود ، فيرجى إبلاغي بذلك.

مثال على ماكرو Excel

توضح هذه المقالة أمثلة الماكرو التالية:

إظهار كل أوراق العمل مرة واحدة

إذا كنت تعمل على مصنف به أوراق مخفية متعددة ، فأنت بحاجة إلى إظهار هذه الأوراق واحدة تلو الأخرى.قد يستغرق هذا بعض الوقت إذا كان هناك العديد من الأوراق المخفية.

هذا هو الكود لإظهار كل الأوراق في المصنف.

سيظهر هذا الرمز كافة الأوراق في المصنف Sub UnhideAllWoksheets () Dim ws As Worksheet لكل ws في ActiveWorkbook. Worksheets ws. Visible = xlSheetVisible Next ws End Sub

يستخدم الكود أعلاه حلقة VBA (لكل منهما) للتكرار خلال كل ورقة عمل في المصنف.ثم يغير الخاصية المرئية للورقة إلى مرئية.

فيما يلي برنامج تعليمي مفصل حول كيفية إظهار ورقة عمل في Excel باستخدام طرق مختلفة.

إخفاء جميع أوراق العمل باستثناء ورقة العمل النشطة

يمكنك استخدام رمز الماكرو هذا إذا كنت تعمل على تقرير أو لوحة معلومات وتريد إخفاء جميع الأوراق باستثناء الورقة التي تحتوي على التقرير / لوحة المعلومات.

سيخفي هذا الماكرو كل ورقة العمل باستثناء الورقة النشطة Sub HideAllExceptActiveSheet () Dim ws As Worksheet لكل ws في ThisWorkbook.Worksheets If ws.Name <> ActiveSheet.Name ثم ws.Visible = xlSheetHidden Next ws End Sub

قم بفرز ورقة العمل أبجديًا باستخدام VBA

يمكن أن يكون رمز الماكرو هذا مفيدًا إذا كان لديك مصنف به العديد من الأوراق وتريد فرز هذه الأوراق أبجديًا.يمكن أن يحدث هذا إذا كان لديك اسم الورقة على أنه السنة أو اسم الموظف أو اسم المنتج.

سيقوم هذا الرمز بفرز أوراق العمل أبجديًا Sub SortSheetsTabName () Application.ScreenUpdating = False Dim ShCount As Integer، i As Integer، j As Integer ShCount = Sheets.Count For i = 1 To ShCount - 1 For j = i + 1 To ShCount إذا كان Sheets (j). اسم <Sheets (i) .Name ثم Sheets (j). التحرك قبل: = Sheets (i) End If التالي j Next i Application.ScreenUpdating = True End Sub

حماية جميع أوراق العمل دفعة واحدة

إذا كان لديك الكثير من الأوراق في المصنف الخاص بك وتريد حماية كل الأوراق ، يمكنك استخدام رمز الماكرو هذا.

يسمح لك بتحديد كلمة مرور في الكود.ستحتاج إلى كلمة المرور هذه لإلغاء حماية الورقة.

سيحمي هذا الرمز جميع الأوراق دفعة واحدة Sub ProtectAllSheets () Dim ws As Worksheet Dim password As String password = "Test123" 'استبدل Test123 بكلمة المرور التي تريدها لكل ws في أوراق العمل ws.Protect password: = password Next ws End Sub

قم بإلغاء حماية جميع أوراق العمل مرة واحدة

إذا قمت بحماية بعض أوراق العمل الخاصة بك أو كلها ، فيمكنك إلغاء حمايتها بتعديل بسيط فقط على الكود المستخدم لحماية أوراق العمل.

سيحمي هذا الرمز جميع الأوراق دفعة واحدة Sub ProtectAllSheets () Dim ws As Worksheet Dim password As String password = "Test123" 'استبدل Test123 بكلمة المرور التي تريدها لكل ws في أوراق العمل ws.Unprotect password: = password Next ws End Sub

لاحظ أن كلمة المرور يجب أن تكون نفس كلمة المرور المستخدمة لقفل الورقة.إذا لم يكن كذلك ، فسترى خطأ.

إظهار كافة الصفوف والأعمدة

سيكشف رمز الماكرو هذا عن جميع الصفوف والأعمدة المخفية.

يمكن أن يكون هذا مفيدًا إذا كنت تأخذ الملف من شخص آخر وتريد التأكد من عدم وجود صفوف / أعمدة مخفية.

سيؤدي هذا الرمز إلى إظهار جميع الصفوف والأعمدة في ورقة عمل Sub UnhideRowsColumns () Columns.

قم بإلغاء دمج جميع الخلايا المدمجة

يعد دمج الخلايا في واحدة ممارسة شائعة.أثناء عمله ، لن تتمكن من فرز البيانات عند دمج الخلايا.

إذا كنت تستخدم ورقة عمل تحتوي على خلايا مدمجة ، فاستخدم الكود أدناه لإلغاء دمج جميع الخلايا المدمجة مرة واحدة.

سيؤدي هذا الرمز إلى إلغاء دمج جميع الخلايا المدمجة Sub UnmergeAllCells () ActiveSheet.Cells.UnMerge End Sub

لاحظ أنني أوصي باستخدام خيار "توسيط عبر التحديد" بدلاً من خيار "دمج وتوسيط".

حفظ المصنف مع الطابع الزمني في الاسم

هناك عدة مرات قد تحتاج فيها إلى إنشاء نسخة صالحة للعمل.هذه رائعة للمشاريع طويلة المدى التي تتعامل مع الملفات بمرور الوقت.

من الممارسات الجيدة حفظ الملف بطابع زمني.

سيسمح لك استخدام الطابع الزمني بالعودة إلى ملف لمعرفة التغييرات التي تم إجراؤها أو البيانات التي تم استخدامها.

هذا هو الرمز الذي يحفظ المصنف تلقائيًا في المجلد المحدد ويضيف طابعًا زمنيًا عند الحفظ.

'سيحفظ هذا الرمز الملف مع الطابع الزمني في اسمه Sub SaveWorkbookWithTimeStamp () Dim timestamp As String timestamp = Format (Date، "dd-mm-yyyy") & "_" & Format (Time، "hh-ss") ThisWorkbook.SaveAs "C: UsersUsernameDesktopWorkbookName" & الطابع الزمني End Sub

تحتاج إلى تحديد موقع المجلد واسم الملف.

في الكود أعلاه ، "C: UsersUsernameDesktop هو موقع المجلد الذي أستخدمه.تحتاج إلى تحديد موقع المجلد حيث تريد حفظ الملف.أيضًا ، استخدمت الاسم العام "WorkbookName" كبادئة اسم الملف.يمكنك تحديد محتوى متعلق بمشروعك أو شركتك.

احفظ كل ورقة عمل كملف PDF منفصل

إذا كنت تستخدم بيانات من سنوات أو أقسام أو منتجات مختلفة ، فقد تحتاج إلى حفظ أوراق عمل مختلفة كملفات PDF.

على الرغم من أن هذه العملية قد تستغرق وقتًا طويلاً إذا تم إجراؤها يدويًا ، إلا أن VBA تعمل على تسريع الأمور.

إليك رمز VBA الذي يحفظ كل ورقة عمل كملف PDF منفصل.

سيحفظ هذا الرمز كل ورقة عمل منفصلة بتنسيق PDF Sub SaveWorkshetAsPDF () Dim ws كورقة عمل لكل ws في أوراق العمل ws.ExportAsFixedFormat xlTypePDF ، "C: UsersSumitDesktopTest" & ws.Name & ".pdf" التالي ws End Sub

في الكود أعلاه ، قمت بتحديد عنوان موقع المجلد حيث أريد حفظ ملف PDF.أيضًا ، سيحصل كل ملف PDF على نفس اسم الورقة.سيتعين عليك تعديل موقع المجلد هذا (ما لم يكن اسمك أيضًا Sumit وقمت بحفظه في مجلد اختبار على سطح المكتب).

لاحظ أن هذا الرمز يعمل فقط لأوراق العمل (وليس لأوراق المخططات).

احفظ كل ورقة عمل كملف PDF منفصل

هذا هو الكود لحفظ المصنف بأكمله كملف PDF في المجلد المحدد.

سيحفظ هذا الرمز المصنف بأكمله بتنسيق PDF Sub SaveWorkshetAsPDF () ThisWorkbook.ExportAsFixedFormat xlTypePDF ، "C: UsersSumitDesktopTest" & ThisWorkbook.Name & ".pdf" End Sub

يجب عليك تغيير موقع المجلد لاستخدام هذا الرمز.

تحويل كل الصيغ إلى قيم

استخدم هذا الرمز عندما يكون لديك ورقة عمل بها الكثير من الصيغ وتريد تحويل تلك الصيغ إلى قيم.

سيحول هذا الرمز جميع الصيغ إلى قيم Sub ConvertToValues ​​() With ActiveSheet.UsedRange .Value = .Value End With End Sub

يتعرف هذا الرمز تلقائيًا على الخلية المستخدمة ويحولها إلى قيمة.

حماية / قفل الخلايا باستخدام الصيغ

عندما يكون لديك الكثير من العمليات الحسابية ولا تريد حذفها أو تغييرها عن طريق الخطأ ، فقد ترغب في قفل الخلايا باستخدام الصيغ.

إليك الكود الذي سيقفل جميع الخلايا بالصيغ ويترك جميع الخلايا الأخرى بدون قفل.

سيؤدي رمز الماكرو هذا إلى قفل جميع الخلايا ذات الصيغ Sub LockCellsWithFormulas () مع ActiveSheet .Unprotect .Cells.Locked = False .Cells.SpecialCells (xlCellTypeFormulas) .Locked = True .Protect AllowDeletingRows: = True End With End Sub

البرنامج التعليمي ذو الصلة: كيفية قفل الخلايا في Excel.

حماية جميع الأوراق في مصنف

استخدم الكود التالي لحماية كل الأوراق في مصنف مرة واحدة.

سيحمي هذا الرمز جميع الأوراق الموجودة في المصنف Sub ProtectAllSheets () Dim ws As ورقة عمل لكل ws في Worksheets ws. حماية Next ws End Sub

سيمر هذا الرمز عبر جميع الأوراق واحدة تلو الأخرى وحمايتها.

إذا كنت تريد إلغاء حماية جميع الأوراق ، فاستخدم ws.Unprotect بدلاً من ws.Protect في التعليمات البرمجية الخاصة بك.

أدخل صفًا بعد كل صف من التحديد

استخدم هذا الرمز عندما تريد إدراج صف فارغ بعد كل صف في النطاق المحدد.

ستدرج هذه الشفرة صفًا بعد كل صف في التحديد Sub InsertAlternateRows () Dim rng كـ Range Dim CountRow As Integer Dim i As Integer Set rng = Selection CountRow = rng.EntireRow.Count For i = 1 To CountRow ActiveCell.EntireRow. أدخل ActiveCell.Offset (2، 0). حدد Next i End Sub

مرة أخرى ، يمكنك تعديل هذا الرمز لإدراج عمود فارغ بعد كل عمود في النطاق المحدد.

إدراج التاريخ والطابع الزمني تلقائيًا في الخلايا المجاورة

الطوابع الزمنية هي ما تستخدمه عندما تريد تتبع النشاط.

على سبيل المثال ، قد ترغب في تعقب الأنشطة مثل وقت تكبد مصروفات معينة ، ووقت إنشاء فاتورة مبيعات ، ووقت إدخال البيانات في خلية ، ووقت آخر تحديث لتقرير ، وما إلى ذلك.

استخدم هذا الرمز لإدراج طابع التاريخ والوقت في الخلايا المجاورة عند كتابة محتوى موجود أو تحريره.

سيقوم هذا الرمز بإدراج طابع زمني في الخلية المجاورة Private Sub Worksheet_Change (ByVal Target As Range) عند خطأ GoTo Handler If Target.Column = 1 And Target.Value <> "" ثم Application.EnableEvents = False Target.Offset (0، 1) = Format (Now ()، "dd-mm-yyyy hh: mm: ss") Application.EnableEvents = True End If Handler: End Sub

لاحظ أنك بحاجة إلى إدراج هذا الرمز في نافذة رمز ورقة العمل (ليس في نافذة رمز الوحدة النمطية كما فعلنا في أمثلة ماكرو Excel الأخرى حتى الآن).للقيام بذلك ، في محرر VB ، انقر نقرًا مزدوجًا فوق اسم الورقة التي تريد هذه الوظيفة لها.ثم انسخ والصق هذا الرمز في نافذة التعليمات البرمجية لتلك الورقة.

يعمل هذا الرمز أيضًا عندما يتم إدخال البيانات في العمود A (لاحظ أن الرمز يحتوي على Target.Column = سطر واحد).يمكنك تغييره وفقًا لذلك.

قم بتمييز الصفوف البديلة في التحديد

يمكن أن يؤدي تمييز الخطوط البديلة إلى تحسين إمكانية قراءة بياناتك بشكل كبير.يمكن أن يكون هذا مفيدًا عندما تحتاج إلى طباعته وتصفح البيانات.

إليك رمز سيبرز على الفور صفوفًا بديلة في التحديد.

سيُبرز هذا الرمز صفوفًا بديلة في التحديد Sub HighlightAlternateRows () Dim Myrange as Range Dim Myrow As Range Set Myrange = Selection لكل Myrow في Myrange.Rows If Myrow.Row Mod 2 = 1 ثم Myrow.Interior.Color = vbCyan End إذا كان التالي Myrow End Sub

لاحظ أنني حددت اللون كـ vbCyan في الكود.يمكنك أيضًا تحديد ألوان أخرى (مثل vbRed و vbGreen و vbBlue).

قم بتمييز الخلايا التي بها أخطاء إملائية

لا يحتوي Excel على التدقيق الإملائي في Word أو PowerPoint.بينما يمكنك تشغيل التدقيق الإملائي بالضغط على F7 ، لا يوجد دليل مرئي للأخطاء الإملائية.

استخدم هذا الرمز لتمييز جميع الخلايا التي بها أخطاء إملائية على الفور.

سيُبرز هذا الرمز الخلايا التي تحتوي على كلمات بها أخطاء إملائية Sub HighlightMisspelledCells () Dim cl كنطاق لكل cl في ActiveSheet.UsedRange If Not Application.CheckSpelling (word: = cl.Text) ثم cl.Interior.Color = vbRed End إذا التالي cl End Sub

لاحظ أن الخلايا المميزة هي الخلايا التي تحتوي على نص يعتقد Excel أنه يحتوي على أخطاء إملائية.في كثير من الحالات ، يبرز أيضًا الأسماء أو مصطلحات العلامة التجارية التي لا يفهمها.

قم بتحديث كل الجداول المحورية في مصنف

إذا كان لديك عدة جداول محورية في المصنف ، فيمكنك استخدام هذا الرمز لتحديثها جميعًا مرة واحدة.

سيقوم هذا الكود بتحديث كل جدول Pivot في Workbook Sub RefreshAllPivotTables () Dim PT مثل PivotTable لكل PT في ActiveSheet.PivotTables PT.

يمكنك قراءة المزيد حول تحديث الجداول المحورية هنا.

قم بتغيير حالة الأحرف للخلايا المحددة إلى حالة الأحرف الكبيرة

بينما يحتوي Excel على صيغ لتغيير حالة الأحرف النصية ، فإنه يتيح لك القيام بذلك في مجموعة أخرى من الخلايا.

استخدم هذا الرمز لتغيير حالة الأحرف في النص المحدد على الفور.

سيؤدي هذا الرمز إلى تغيير التحديد إلى الحالة العلوية Sub ChangeCase () Dim Rng كنطاق لكل Rng في التحديد.

لاحظ أنه في هذه الحالة أستخدم UCase لضبط حالة النص على أحرف كبيرة.يمكنك استخدام LCase للأحرف الصغيرة.

قم بتمييز كل الخلايا بالتعليقات

استخدم الكود التالي لتمييز جميع الخلايا التي تحتوي على تعليقات.

سيُبرز هذا الرمز الخلايا التي تحتوي على تعليقات` Sub HighlightCellsWithComments () ActiveSheet.Cells.SpecialCells (xlCellTypeComments) .Interior.Color = vbBlue End Sub

في هذه الحالة ، أستخدم vbBlue لإعطاء الخلية لونًا أزرق.يمكنك تغييره إلى لون آخر إذا لزم الأمر.

قم بتمييز الخلايا الفارغة باستخدام VBA

بينما يمكنك استخدام التنسيق الشرطي أو استخدام مربع الحوار Go To Special لتمييز الخلايا الفارغة ، إذا كان عليك القيام بذلك كثيرًا ، فمن الأفضل استخدام ماكرو.

بمجرد الإنشاء ، يمكنك الحصول على هذا الماكرو في شريط أدوات الوصول السريع أو حفظه في مصنف الماكرو الشخصي.

هنا هو كود الماكرو VBA:

سيسلط هذا الرمز الضوء على جميع الخلايا الفارغة في مجموعة البيانات Sub HighlightBlankCells () Dim Dataset كمجموعة بيانات مجموعة النطاق = مجموعة بيانات التحديد. specialCells (xlCellTypeBlanks).

في هذا الرمز ، حددت الخلايا الفارغة المميزة باللون الأحمر.يمكنك اختيار ألوان أخرى مثل الأزرق ، الأصفر ، السماوي ، إلخ.

كيفية فرز البيانات حسب عمود واحد

يمكنك استخدام التعليمات البرمجية التالية لفرز البيانات حسب عمود محدد.

الترتيب الفرعي DataHeader () 
Range ("DataRange"). مفتاح الفرز 1: = Range ("A1") ، Order1: = xlAscending ، Header: = xlYes 
نهاية الفرعية

لاحظ أنني أنشأت نطاقًا مسمى باسم "DataRange" واستخدمته بدلاً من مرجع الخلية.

هناك أيضًا ثلاث معلمات رئيسية مستخدمة هنا:

• Key1 - هذا هو المفتاح الذي تريد فرز مجموعة البيانات بواسطته.في نموذج التعليمات البرمجية أعلاه ، سيتم فرز البيانات بناءً على القيم الموجودة في العمود A.
• Order1 - هنا تحتاج إلى تحديد ما إذا كنت تريد فرز البيانات بترتيب تصاعدي أو تنازلي.
• رأس - هنا تحتاج إلى تحديد ما إذا كانت بياناتك تحتوي على رأس أم لا.

اقرأ المزيد حول كيفية فرز البيانات في Excel باستخدام VBA.

كيفية فرز البيانات حسب عدة أعمدة

افترض أن لديك مجموعة بيانات تبدو كالتالي:

فيما يلي رمز لفرز البيانات بناءً على أعمدة متعددة:

SortMultipleColumns () مع ActiveSheet.Sort .SortFields.Add Key: = Range ("A1") ، الترتيب: = xlAscending .SortFields.Add Key: = Range ("B1") ، الترتيب: = xlAscending .SetRange Range ("A1 : C13 ") .Header = xlYes. قم بتطبيق End With End Sub

لاحظ أنه هنا قمت بتحديد الفرز في العمود A أولاً ثم في العمود B.

سيبدو الإخراج كما يلي:

كيفية الحصول على الجزء الرقمي فقط من سلسلة في Excel

إذا كنت تريد فقط استخراج الجزء الرقمي أو جزء النص من السلسلة ، فيمكنك إنشاء وظيفة مخصصة في VBA.

يمكنك بعد ذلك استخدام وظيفة VBA هذه في ورقة العمل (تمامًا مثل وظيفة Excel العادية) وستستخرج فقط الرقم أو جزء النص من السلسلة.

كما هو مبين أدناه:

إليك رمز VBA الذي سينشئ دالة تستخرج الجزء الرقمي من سلسلة:

سيقوم رمز VBA هذا بإنشاء دالة للحصول على الجزء الرقمي من سلسلة دالة GetNumeric (CellRef As String) Dim StringLength As Integer StringLength = Len (CellRef) For i = 1 To StringLength If IsNumeric (Mid (CellRef، i، 1) ) ثم النتيجة = النتيجة & منتصف (CellRef، i، 1) التالي i GetNumeric = نتيجة وظيفة النهاية

تحتاج إلى وضع الكود في وحدة نمطية وبعد ذلك يمكنك استخدام الوظيفة = GetNumeric في ورقة العمل.

ستستغرق هذه الوظيفة معلمة واحدة فقط ، مرجع الخلية للخلية التي يتم الحصول على الجزء الرقمي منها.

وبالمثل ، ستحصل الوظيفة أدناه فقط على جزء النص من سلسلة في Excel:

سيقوم رمز VBA هذا بإنشاء دالة للحصول على جزء النص من سلسلة دالة GetText (CellRef As String) Dim StringLength As Integer StringLength = Len (CellRef) For i = 1 To StringLength If Not (IsNumeric (Mid (CellRef، i، 1))) ثم النتيجة = النتيجة والمتوسطة (CellRef، i، 1) التالي i GetText = وظيفة نهاية النتيجة

إذن ، هذه بعض أكواد ماكرو Excel المفيدة التي يمكنك استخدامها في عملك اليومي لأتمتة المهام وزيادة إنتاجيتك.