كل الأنشطة

الرئيسية
البرامج والتطبيقات
الإنتاجية وسير العمل
مايكروسوفت أوفيس
اكسل
صناعة الدوال الخاصة والتعامل مع الأحداث في مايكروسوفت إكسل VBA

صناعة الدوال الخاصة والتعامل مع الأحداث في مايكروسوفت إكسل VBA

13 سبتمبر 2021

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

تعرفنا سابقًا على آلية كتابة الإجراءات باستخدام فيجوال بيسك في درس مدخل إلى البرمجة باستخدام VBA في مايكروسوفت إكسل، ووجدنا أن الإجراء من النوع sub-routine يُنفذ أمرًا أو مجموعةً معينةً من الأوامر، وأن الإجراء من النوع Function يُضيف دوال على برنامج مايكروسوفت إكسل للاستخدام الاعتيادي كما لو أنها من ضمن دوال البرنامج الأساسية.

صناعة الدوال

توجد طريقتان لكتابة الإجراء هما:

إجراء مع وسطاء (معطيات)

وتكون الشيفرة كما يلي:

Function name(arguments) as type
    Code
End Function

حيث إن:

name: اسم للدالة التي نصنعها، ويُفضل أن يكون الاسم مُعبرًا عن عمل الدالة.
arguments: الوسطاء التي ستعمل الدالة مع محتوياتها.
type: نوع المُخرَج للدالة التي نصنعها.
Code: مجموعة الشيفرات التي ستُنفذها الدالة.

مثال

لدينا البيانات التالية، ونريد صناعة دالة خاصة تعمل على دمج الاسم الأول مع الأخير للحصول على الاسم الكامل.

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

Function FullName(FirstName As String, LastName As String) As String
FullName = FirstName & " " & LastName
End Function

عند العودة إلى برنامج مايكروسوفت إكسل سنجد أن الدالة أُضيفت إلى الدوال الأساسية فيه، وتعمل مثل باقي الدوال الاعتيادية.

اقتباس

يمكننا وضع وسيط في الدالة قيمته إما من خلية مرجعية مثل المثال السابق، أو ربما لا نضع له قيمة أبدًا، وعندها يجب أن نضع قبله الكلمة Optional التي تدل على أن هذا الوسيط اختياري، أي يمكن ألّا نُسند له قيمةً.

مثال

لدينا البيانات التالية، ونريد صناعة دالة خاصة تحسب الراتب بعد الزيادة، وذلك وفقًا لتقييم كل موظف.

حيث يتقاضى الموظف زيادةً قدرها 0.2 عندما يكون تقييمه أكثر من 7، وزيادةً قدرها 0.1 عندما يكون تقييمه أكثر من 5 وأقل من 7، وزيادةً أخرى قدرها 0.08 عندما يكون أكثر من 1، وزيادةً قدرها 0.04 إذا لم نُسند خانة التقييم للدالة، وعليه يكون الإجراء كالتالي:

Function NewSalary(Salary As Currency, Optional Rate As Integer) As Currency
 If Rate = 0 Then
NewSalary = Salary + (Salary * 0.04)
ElseIf Rate >= 7 Then
NewSalary = Salary + (Salary * 0.2)
 ElseIf Rate >= 5 Then
NewSalary = Salary + (Salary * 0.1)
ElseIf Rate >= 1 Then
NewSalary = Salary + (Salary * 0.08)
End If
End Function

بتنفيذ الدالة على البيانات السابقة نجد التالي:

إجراء بدون وسطاء (معطيات)

وتكتب الشيفرة كما يلي:

Function name() as type
    Code
End Function

حيث إن:

name: اسم للدالة التي نصنعها، ويُفضل أن يكون الاسم مُعبرًا عن عمل الدالة.
type: نوع المُخرَج للدالة التي نصنعها.
Code: مجموعة الشيفرات التي ستُنفذها الدالة.

مثال

لنَعُد إلى المثال السابق، حيث نريد إضافة عمود يحتوي أرقامًا تعريفيةً عشوائيةً للموظف، لذلك نكتب الإجراء التالي:

Function RandCode() As Long
RandCode = Rnd() * 10000
End Function

عند تنفيذ الدالة على البيانات السابقة فسنحصل على النتيجة التالية:

التعامل مع الأحداث

تعلمنا في المقالات السابقة كيفية كتابة إجراءات لتنفيذ مجموعة من الأوامر، وذلك بطريقتين: الأولى عندما نطلب منه تنفيذ الإجراء يدويًا، والثانية عن طريق إضافة أزرار تُنفذ الإجراء عند الضغط عليها، لكن في بعض الأحيان نحتاج أن تُنفَّذ هذه الإجراءات آليًا عند وقوع حدث معين، مثل فتح ملف، أو ظهور رسالة ترحيب عند فتح ملف إكسل، أو عند الطباعة، أو عند تعبئة خلية، وهذه الإجراءات تسمى أحداثًا، وهي موجودة في كل لغات البرمجة، ونستطيع برمجة برنامج الإكسل لتنفيذها عند وقوع الحدث.

الصيغة العامة لكتابة الأحداث

Sub Event_Name (variables)
 Our code 
End sub

نجد أن الصيغة العامة للحدث هي نفسها للإجراء الروتيني، ولكن يجب تسميتها بأسماء محددة تدل على حدث معين، أي أن الاسم هنا ليس اختياريًا، بل يكون مُعرّفًا عن طريق لغة البرمجة، وتوجد بعض القوانين لكتابة الأحداث وهي:

تُكتب الأحداث في محرر الفيجوال بيسك ضمن الأوراق أو ضمن الملف، وليس ضمن model؛ لأن الحدث هو خاص بورقة العمل أو الملف، وبالنقر مرتين على اسم الصفحة أو اسم الملف يفتح المحرر الخاص بالصفحة أو الملف.

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

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

سنتعرف على بعض الأمثلة التي تشرح آلية عمل الأحداث.

مثال 1

نريد إنشاء حدث يُظهر رسالة ترحيب عند فتح صفحة معينة، لذا فأول ما نفعله البحث عن الحدث الذي يُنفذ عند فتح الصفحة، وهو مُعرّف بالاسم SheetActivate، وهذا الحدث يعمل على مستوى الملف، ويكون الإجراء كالتالي:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
 If Sh.Name = "Sheet2" Then
 MsgBox "hello"
 End If
End Sub

حيث إن المتغير sh هو متغير مُعرف في الحدث، يُعبر عن الصفحات كمتغيرات.

مثال 2

نريد إظهار نفس الرسالة السابقة عند فتح ملف الإكسل. Workbook_Open: هو الحدث الذي يُنفّذ عند فتح ملف الإكسل، ولذلك نكتب الإجراء التالي:

Private Sub Workbook_Open()
MsgBox "welcome back"
End Sub

مثال 3

نريد كتابة إجراء يُضيف الصفحات الجديدة في نهاية صف الصفحات، ولتنفيذ ذلك علينا تقسيم المراحل لعمل الإجراء ومعرفة الأوامر الضرورية لفعل ذلك:

Workbook_NewSheet: هو الحدث الذي يُنفذ عند إضافة صفحة عمل جديدة.
يجب في البداية أن نعرف عدد الصفحات في الملف، لذا لنُعرّف المتغير sheet_count، ونُخزن فيه قيمة الأمر الذي يحصي عدد الصفحات، وهو الأمر sheet.count.
نكتب الأمر المسؤول عن تحريك الصفحة الجديدة لآخر الصف، وهو الأمر التالي:

object.move after:=sheets("عدد الصفات في الملف")

حيث يُعبّر object هنا عن الصفحة الجديدة، ويُصبح الإجراء كالتالي:

Private Sub Workbook_NewSheet(ByVal Sh As Object)
Dim sheet_count As Integer
sheet_count = Sheet.Count
Sh.Move after:=Sheets("sheet_count")
End Sub

مثال 4

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

Cells.Interior.ColorIndex = xlNone

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

EntireRow.Interior.Color = RGB(14, 148, 148)

ونفس الحالة بالنسبة لإعطاء خلايا العمود لونًا واحدًا، ولكن نُسبق التعليمتين بالأمر Target الذي يُعبر هنا عن مجال الصفحة، ومن هنا يصبح الإجراء بالشكل التالي:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Cells.Interior.ColorIndex = xlNone
Target.EntireRow.Interior.Color = RGB(14, 148, 148)
Target.EntireColumn.Interior.Color = RGB(14, 148, 148)
End Sub

أهم الأحداث المرتبطة بملف العمل

الأحداث المرتبطة بملف العمل كثيرة، ولكن سنذكر أهمها فيما يلي:

Workbook_BeforeSave: الحدث قبل إتمام حفظ الملف.
Workbook_BeforePrint: الحدث قبل البدء بالطباعة.
Workbook_AfterSave: الحدث بعد إتمام حفظ الملف.
Workbook_SheetActivate: الحدث عند تنشيط (فتح) الصفحة (ورقة عمل معينة).
Workbook_NewSheet: الحدث عند إضافة ورقة عمل جديدة.
Workbook_SheetChange: الحدث عند تعديل ورقة عمل.
Workbook_BeforeClose: الحدث قبل إغلاق ملف الإكسل.
Workbook_Open: الحدث عند فتح ملف الإكسل.

أهم الأحداث المرتبطة بورقة العمل

Worksheet_Activate: الحدث عند تنشيط الصفحة.
Worksheet_BeforeDelete: الحدث قبل تنفيذ حذف الصفحة.
Worksheet_Change: الحدث عند إجراء تغييرات على الصفحة.
Worksheet_Deactivate: الحدث عند إلغاء تنشيط الصفحة (الانتقال إلى صفحة أخرى).

خاتمة

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