برنامج Excel VBA التعليمي - كيفية كتابة التعليمات البرمجية في جدول بيانات باستخدام Visual Basic

المقدمة

هذا برنامج تعليمي حول كتابة التعليمات البرمجية في جداول بيانات Excel باستخدام Visual Basic for Applications (VBA).

يعد Excel أحد أشهر منتجات Microsoft. في عام 2016 ، قال الرئيس التنفيذي لشركة Microsoft "فكر في عالم بدون Excel. هذا مستحيل بالنسبة لي." حسنًا ، ربما لا يستطيع العالم التفكير بدون Excel.

  • في عام 1996 ، كان هناك أكثر من 30 مليون مستخدم لبرنامج Microsoft Excel (مصدر).
  • اليوم ، هناك ما يقدر بنحو 750 مليون مستخدم لبرنامج Microsoft Excel. وهذا يزيد قليلاً عن عدد سكان أوروبا ويزيد 25 ضعفًا عن عدد المستخدمين في عام 1996.

نحن عائلة واحدة كبيرة سعيدة!

في هذا البرنامج التعليمي ، ستتعرف على VBA وكيفية كتابة التعليمات البرمجية في جدول بيانات Excel باستخدام Visual Basic.

المتطلبات الأساسية

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

  • الإلمام الأساسي بالمتوسط ​​ببرنامج Microsoft Excel
  • إذا كنت ترغب في المتابعة مع أمثلة VBA في هذه المقالة ، فستحتاج إلى الوصول إلى Microsoft Excel ، ويفضل أن يكون أحدث إصدار (2019) ولكن Excel 2016 و Excel 2013 سيعملان بشكل جيد.
  • الرغبة في تجربة أشياء جديدة

أهداف التعلم

خلال هذه المقالة سوف تتعلم:

  1. ما هو VBA
  2. لماذا تستخدم VBA
  3. كيفية الإعداد في Excel لكتابة VBA
  4. كيفية حل بعض مشاكل العالم الحقيقي باستخدام VBA

مفاهيم مهمة

فيما يلي بعض المفاهيم المهمة التي يجب أن تكون على دراية بها لفهم هذا البرنامج التعليمي بشكل كامل.

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

إذا لم يكن لديك أي خبرة في البرمجة الموجهة للكائنات وهذا مفهوم جديد تمامًا ، فاخذ ثانية لتترك ذلك يغرق!

الإجراءات : الإجراء عبارة عن جزء من كود VBA ، مكتوب في محرر Visual Basic ، ينجز مهمة. في بعض الأحيان ، يُشار إلى هذا أيضًا باسم ماكرو (المزيد في وحدات الماكرو أدناه). هناك نوعان من الإجراءات:

  • الإجراءات الفرعية: مجموعة من عبارات VBA تقوم بتنفيذ إجراء واحد أو أكثر
  • الدالات: مجموعة من عبارات VBA تقوم بتنفيذ إجراء واحد أو أكثر وتقوم بإرجاع قيمة واحدة أو أكثر

ملاحظة: يمكن أن يكون لديك وظائف تعمل داخل الإجراءات الفرعية. سترى لاحقًا.

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

يرجى أن تضع في اعتبارك أنه في بعض الأحيان قد يكون من الأسهل والأسرع تسجيل ماكرو بدلاً من كتابة رمز يدوي لإجراء VBA.

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

ما هو VBA؟

Visual Basic for Applications هي لغة برمجة طورتها شركة Microsoft. يتم تجميع كل برنامج في مجموعة Microsoft Office مع لغة VBA دون أي تكلفة إضافية. يسمح VBA لمستخدمي Microsoft Office بإنشاء برامج صغيرة تعمل ضمن برامج Microsoft Office.

فكر في VBA مثل فرن البيتزا داخل المطعم. التفوق هو المطعم. يأتي المطبخ مع الأجهزة التجارية القياسية ، مثل الثلاجات الكبيرة والمواقد وأفران أولية العادية - هذه كلها ميزات قياسية في Excel.

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

يم.

لماذا استخدام VBA في Excel؟

لأن بيتزا الحطب هي الأفضل!

لكن بجدية.

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

أحيانًا تكون تلك الأيام حتمية. ولكن إذا كنت تقضي 8-10 ساعات يوميًا في Excel للقيام بمهام متكررة ، أو تكرار الكثير من نفس العمليات ، أو محاولة التنظيف بعد مستخدمين آخرين للملف ، أو حتى تحديث ملفات أخرى بعد إجراء التغييرات على ملف Excel ، قد يكون إجراء VBA هو الحل المناسب لك.

يجب أن تفكر في استخدام VBA إذا كنت بحاجة إلى:

  • أتمتة المهام المتكررة
  • قم بإنشاء طرق سهلة للمستخدمين للتفاعل مع جداول البيانات الخاصة بك
  • معالجة كميات كبيرة من البيانات

الإعداد لكتابة VBA في Excel

علامة تبويب المطور

لكتابة VBA ، ستحتاج إلى إضافة علامة تبويب Developer إلى الشريط ، حتى ترى الشريط مثل هذا.

لإضافة علامة التبويب المطور إلى الشريط:

  1. في علامة التبويب ملف ، انتقل إلى خيارات> تخصيص الشريط.
  2. ضمن تخصيص الشريط وضمن علامات التبويب الرئيسية ، حدد خانة الاختيار المطور.

بعد إظهار علامة التبويب ، تظل علامة التبويب المطور مرئية ، إلا إذا قمت بإلغاء تحديد خانة الاختيار أو تضطر إلى إعادة تثبيت Excel. لمزيد من المعلومات ، راجع وثائق تعليمات Microsoft.

محرر VBA

انتقل إلى علامة تبويب المطور ، وانقر فوق الزر Visual Basic. ستظهر نافذة جديدة - هذا هو محرر Visual Basic. لأغراض هذا البرنامج التعليمي ، تحتاج فقط إلى التعرف على جزء Project Explorer وجزء Properties Properties.

أمثلة Excel VBA

أولاً ، دعنا ننشئ ملفًا لنا للتلاعب فيه.

  1. افتح ملف Excel جديدًا
  2. احفظه كمصنف ممكن بماكرو (. xlsm)
  3. حدد علامة التبويب المطور
  4. افتح محرر VBA

لنبدأ مع بعض الأمثلة السهلة لنجعلك تكتب التعليمات البرمجية في جدول بيانات باستخدام Visual Basic.

المثال 1: عرض رسالة عندما يفتح المستخدمون مصنف Excel

في محرر VBA ، حدد إدراج -> وحدة جديدة

اكتب هذا الرمز في نافذة الوحدة النمطية (لا تلصق!):

فتح تلقائي فرعي ()

MsgBox ("مرحبًا بك في مصنف XYZ.")

End Sub

احفظ المصنف وأغلقه وأعد فتحه. يجب أن يظهر هذا الحوار.

تا دا!

كيف تفعل ذلك؟

اعتمادًا على إلمامك بالبرمجة ، قد يكون لديك بعض التخمينات. إنه ليس معقدًا بشكل خاص ، ولكن هناك الكثير مما يحدث:

  • Sub (اختصار لـ "Subroutine): تذكر من البداية ،" مجموعة من عبارات VBA التي تنفذ إجراءً واحدًا أو أكثر. "
  • الفتح التلقائي: هذا هو الروتين الفرعي المحدد. يقوم تلقائيًا بتشغيل التعليمات البرمجية الخاصة بك عند فتح ملف Excel - وهذا هو الحدث الذي يقوم بتشغيل الإجراء. سيتم تشغيل Auto_Open فقط عند فتح المصنف يدويًا ؛ لن يتم تشغيله إذا تم فتح المصنف عبر رمز من مصنف آخر (Workbook_Open سيفعل ذلك ، تعرف على المزيد حول الفرق بين الاثنين).
  • بشكل افتراضي ، يكون الوصول إلى الروتين الفرعي عامًا. هذا يعني أن أي وحدة نمطية أخرى يمكنها استخدام هذا الروتين الفرعي. ستكون جميع الأمثلة في هذا البرنامج التعليمي عبارة عن إجراءات فرعية عامة. إذا لزم الأمر ، يمكنك إعلان الإجراءات الفرعية على أنها خاصة. قد يكون هذا مطلوبًا في بعض المواقف. تعرف على المزيد حول معدِّلات الوصول للروتين الفرعي.
  • msgBox: هذه وظيفة - مجموعة من عبارات VBA التي تنفذ إجراءً واحدًا أو أكثر وتقوم بإرجاع قيمة. القيمة التي تم إرجاعها هي الرسالة "مرحبًا بك في مصنف XYZ."

باختصار ، هذا روتين فرعي بسيط يحتوي على وظيفة.

متى يمكنني استخدام هذا؟

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

  • المشكلة: في معظم الأوقات عندما يصل المستخدمون إلى الملف ، فإنهم مرتبكون بشأن الغرض من هذا الملف (سبب وجوده) ، وكيف يتم تحديثه كثيرًا ، ومن يحافظ عليه ، وكيف يجب أن يتفاعلوا معه. لدى الموظفين الجدد دائمًا الكثير من الأسئلة ، وعليك الإجابة عن هذه الأسئلة مرارًا وتكرارًا.
  • الحل: قم بإنشاء رسالة مستخدم تحتوي على إجابة موجزة لكل من هذه الأسئلة التي يتم الإجابة عليها بشكل متكرر.

أمثلة من العالم الحقيقي

  • استخدم وظيفة MsgBox لعرض رسالة عندما يكون هناك أي حدث: يغلق المستخدم مصنف Excel ، يطبع المستخدم ، تتم إضافة ورقة جديدة إلى المصنف ، إلخ.
  • استخدم الدالة MsgBox لعرض رسالة عندما يحتاج المستخدم إلى تلبية شرط قبل إغلاق مصنف Excel
  • استخدم وظيفة InputBox للحصول على معلومات من المستخدم

المثال الثاني: السماح للمستخدم بتنفيذ إجراء آخر

في محرر VBA ، حدد إدراج -> وحدة جديدة

اكتب هذا الرمز في نافذة الوحدة النمطية (لا تلصق!):

استعلام تقرير مستخدم فرعي ()

تعتيم إدخال المستخدم على طول

إجابة خافتة كعدد صحيح

مدخلات المستخدم = vbYesNo

الإجابة = MsgBox ("هل تريد معالجة تقرير XYZ؟" ، إدخال المستخدم)

إذا كانت الإجابة = vbYes ثم ProcessReport

End Sub

تقرير العملية الفرعية ()

MsgBox ("شكرًا على معالجة تقرير XYZ.")

End Sub

احفظ وانتقل مرة أخرى إلى علامة تبويب Developer في Excel وحدد خيار "Button". انقر فوق خلية وقم بتعيين ماكرو UserReportQuery إلى الزر.

الآن انقر فوق الزر. يجب أن تظهر هذه الرسالة:

انقر فوق "نعم" أو اضغط على Enter.

مرة أخرى ، تادا!

يرجى ملاحظة أن الروتين الفرعي الثانوي ، ProcessReport ، يمكن أن يكون أي شيء . سأوضح المزيد من الاحتمالات في المثال رقم 3. لكن اولا...

كيف تفعل ذلك؟

يعتمد هذا المثال على المثال السابق ويحتوي على عدد قليل من العناصر الجديدة. دعنا ننتقل إلى الأشياء الجديدة:

  • Dim UserInput As Long: Dim اختصار لـ "البعد" ويسمح لك بتعريف أسماء المتغيرات. في هذه الحالة ، يكون UserInput هو اسم المتغير ويكون Long هو نوع البيانات. في اللغة الإنجليزية البسيطة ، يعني هذا السطر "هنا متغير يسمى" UserInput "، وهو نوع متغير طويل."
  • Dim Answer As Integer: يعلن متغيرًا آخر يسمى "Answer" بنوع بيانات صحيح. تعرف على المزيد حول أنواع البيانات هنا.
  • UserInput = vbYesNo: يعين قيمة للمتغير. في هذه الحالة ، vbYesNo ، التي تعرض أزرار نعم ولا. هناك العديد من أنواع الأزرار ، تعرف على المزيد هنا.
  • Answer = MsgBox ("معالجة تقرير XYZ؟" ، UserInput): تعيين قيمة المتغير الإجابة لتكون دالة MsgBox ومتغير UserInput. نعم ، متغير داخل متغير.
  • إذا كانت الإجابة = vbYes ثم ProcessReport: هذه عبارة "If statement" ، وهي عبارة شرطية ، تتيح لنا تحديد ما إذا كانت x صحيحة ، فافعل y. في هذه الحالة ، إذا حدد المستخدم "نعم" ، فقم بتنفيذ الإجراء الفرعي ProcessReport.

متى يمكنني استخدام هذا؟

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

على سبيل المثال ، ربما لديك ملف يتم استخدامه لإنشاء 3 تقارير أسبوعية مختلفة. تم تنسيق هذه التقارير بطرق مختلفة تمامًا.

  • المشكلة: في كل مرة يلزم إنشاء أحد هذه التقارير ، يفتح المستخدم الملف ويغير التنسيق والمخططات ؛ هكذا وهكذا دواليك. يتم تحرير هذا الملف على نطاق واسع 3 مرات على الأقل في الأسبوع ، ويستغرق الأمر 30 دقيقة على الأقل في كل مرة يتم تحريره.
  • الحل: إنشاء زر واحد لكل نوع تقرير ، والذي يقوم تلقائيًا بإعادة تهيئة المكونات الضرورية للتقارير وإنشاء المخططات اللازمة.

أمثلة من العالم الحقيقي

  • أنشئ مربع حوار للمستخدم ليقوم تلقائيًا بتعبئة معلومات معينة عبر أوراق متعددة
  • استخدم وظيفة InputBox للحصول على معلومات من المستخدم ، والتي يتم ملؤها بعد ذلك عبر أوراق متعددة

مثال # 3: أضف أرقامًا إلى نطاق باستخدام حلقة For-Next

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

في محرر VBA ، حدد إدراج -> وحدة جديدة

اكتب هذا الرمز في نافذة الوحدة النمطية (لا تلصق!):

مثال حلقة فرعية ()

خافت X كعدد صحيح

بالنسبة إلى X = 1 إلى 100

النطاق ("A" & X). القيمة = X

التالي X

End Sub

احفظ وانتقل مرة أخرى إلى علامة تبويب المطور في Excel وحدد زر وحدات الماكرو. قم بتشغيل الماكرو LoopExample.

يجب أن يحدث هذا:

إلخ ، حتى الصف 100.

كيف تفعل ذلك؟

  • Dim X As Integer: يعلن المتغير X كنوع بيانات لعدد صحيح.
  • بالنسبة إلى X = 1 إلى 100: هذه هي بداية حلقة For. ببساطة ، يخبر الحلقة أن تستمر في التكرار حتى X = 100. X هي العداد . ستستمر الحلقة في التنفيذ حتى X = 100 ، وستنفذ مرة أخيرة ، ثم تتوقف.
  • Range ("A" & X) .Value = X: هذا يحدد نطاق الحلقة وما يجب وضعه في هذا النطاق. نظرًا لأن X = 1 في البداية ، ستكون الخلية الأولى هي A1 ، وعند هذه النقطة ستضع الحلقة X في تلك الخلية.
  • التالي X: هذا يخبر الحلقة بالتشغيل مرة أخرى

متى يمكنني استخدام هذا؟

تعد حلقة For-Next واحدة من أقوى وظائف VBA ؛ هناك العديد من حالات الاستخدام المحتملة. هذا مثال أكثر تعقيدًا يتطلب طبقات متعددة من المنطق ، لكنه يوصل عالم الاحتمالات في حلقات For-Next.

ربما لديك قائمة بجميع المنتجات المباعة في مخبزك في العمود A ، ونوع المنتج في العمود B (كعك ، أو كعك ، أو فطائر) ، وتكلفة المكونات في العمود C ، ومتوسط ​​تكلفة السوق لكل نوع منتج في ورقة أخرى.

تحتاج إلى معرفة سعر التجزئة لكل منتج. أنت تعتقد أنه يجب أن تكون تكلفة المكونات بالإضافة إلى 20٪ ، ولكن أيضًا 1.2٪ أقل من متوسط ​​السوق إن أمكن. تسمح لك حلقة For-Next بإجراء هذا النوع من الحسابات.

أمثلة من العالم الحقيقي

  • استخدم حلقة مع عبارة if المتداخلة لإضافة قيم معينة إلى مصفوفة منفصلة فقط إذا كانت تفي بشروط معينة
  • قم بإجراء عمليات حسابية على كل قيمة في النطاق ، مثل حساب الرسوم الإضافية وإضافتها إلى القيمة
  • مرر كل حرف في سلسلة واستخرج كل الأرقام
  • حدد عددًا من القيم من المصفوفة بشكل عشوائي

استنتاج

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

  • ما هو VBA؟
  • كيف يمكنني الإعداد لبدء استخدام VBA في Excel؟
  • لماذا ومتى تستخدم VBA؟
  • ما هي بعض المشاكل التي يمكنني حلها باستخدام VBA؟

إذا كانت لديك فكرة عادلة عن كيفية الإجابة على هذه الأسئلة ، فهذا ناجح.

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

ترميز سعيد!

مصادر التعلم

  • برنامج Excel VBA للدمى ، جون والكنباخ
  • ابدأ مع VBA و Microsoft Documentation
  • تعلم VBA في Excel ، ليندا

قليلا عني

أنا كلوي تاكر ، فنانة ومطوّرة في بورتلاند ، أوريغون. بصفتي معلمًا سابقًا ، فأنا أبحث باستمرار عن تقاطع التعلم والتعليم ، أو التكنولوجيا والفن. تواصل معي على Twitter_chloetucker وتحقق من موقع الويب الخاص بي على chloe.dev.