كيف يمكنك جعل كود الـ VBA أكثر كفاءة

[محمد حجازي]

السلام عليكم ...

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

بعض هذه النصائح التي سوف أطرحها تتعلق بلغة الفيجوال بيسك بشكل عام وبعضها الآخر خاص بتطبيق الفيجوال على الاكسل .

عملية طرح المشاركات لن تعتمد على جدول زمني محدد ، وإنما سيتم طرح المشاركات تبعاً لتوفر الوقت لدي.

لقد اعتمدت في طرحي لهذه النصائح على مصدرين أساسيين:

- البرمجة التفاعلية باستخدام الفيجوال بيسك و الاكسل : كتاب ورقي للمهندس شريف فتحي الشافعي.

- مجموعة كتابات حصلت عليها من الويب للأخ المتميز تركي العسيري.

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

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

كيف يمكنني أن أحصل على أساسيات الـ VBA؟

بصورة عامة فإنك تحتاج من أجل الولوج إلى عالم الـ VBA إلى معرفتين أساسيتين:

1. معرفة مبادئ الفيجوال الأساسية :

- الأحداث وكيفية ربط السبب بالنتيجة (ربط الحدث بالإجراء)

- المتغيرات و الثوابت و أنواعها

- خصائص الكائنات

- أداة الشرط IF و الحلقات التكرارية

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

2. معرفة الأهداف في الاكسل وتسلسلها الهرمي.

في الروابط التالية دروس للأخ محمد طاهر عن لغة الـ VBA:

http://www.officena.net/Tips/Excel1-intr.htm

http://www.officena.net/Tips/Excel2-vbeditor.htm

http://www.officena.net/Tips/Excel3-Invoice.htm

http://www.officena.net/Tips/Excel4-Grades.htm

ومن خلال تصفحي السريع لهذه الدروس فإنني أعتقد بأنها تمثل خير بداية لمن يريد الدخول لعالم الـ VBA الممتع . أنصحك الآن بقراءة هذه الدروس لكي تتمكن من متابعة سلسلة النصائح التي سنوردها.

أنا لا أرى من ضرورة تدفعني لتعلم الـ VBA!

بالنسبة لي فأنا أرى أن الـ VBA تعني الإبداع و السرعة و المرونة ، وسوف يمنحك تعلم الـ VBA الفرصة في انتقالك من مجرد مستخدم عادي للتطبيق إلى مطور له ، وسوف تتمكن من القيام بمهام ما كنت لتحلم بتنفيذها بواسطة استخدام الأدوات التقليدية في الاكسل ، ولا تعجب إذا أخبرتك بأنني أختصر عملاً قد يستغرق عدة ساعات بكتابة كود لا يتجاوز السبعة أسطر . أظن أنك قد اقتنعت بتعلم الـ VBA. :d

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

[محمد حجازي]

1. التقليل بقدر الإمكان من مرات اختيار وتنشيط الهدف:

بدايتي وبداية أغلب مبرمجي الـ VBA كانت تعتمد على مسجل الماكرو ، و السبب الذي يبرر هذه البداية بسيط جداً ألا وهو جهل المبرمج المبتدأ بالخصائص و الأساليب المستخدمة في الفيجوال والخاصة بتطبيق الاكسل .

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

من أهم الأسباب التي تجعل الكود المسجل غير فعال (والتي تدفعنا لتعديله لاحقاً) هو تنشيط الأهداف بصورة غير مبررة ، حيث أنه يمكننا في أغلب الأحيان كتابة كود يتعامل مع الأهداف بدون الحاجة لتنشيط أي منها ، ولكن مادام هذا هو الأسلوب الأنسب لماذا لا يعتمده مسجل المايكرو في عمله؟.

إن مسجل المايكرو يقوم بمراقبة الحركات و الأفعال التي يقوم بها المستخدم ويعمل على تسجيلها بأمانة تامة ( يبدوا أن الأمانة الزائدة لمسجل المايكرو هي السبب في خلق هذه المشكلة :d )

لنقوم الآن بتسجيل مايكرو يقوم بمسح محتويات الخلية C5 الموجودة في الورقة Sheet2:

اضغط على الخيار Record New Macro الموجود في القائمة Tools (القائمة الفرعية Macro) لتبدأ عملية تسجيل المايكرو.

انتقل للورقة Sheet2 وانقر على الخلية C5 واضغط على الزر Delete ، ومن ثم أوقف تسجيل الماكرو من نفس الخيار السابق (لاحظ تغير اسمه إلى Stop Recording).

الآن اضغط على Alt+Enter لتفتح لك نافذة محرر الفيجوال ، افتح الموديل المنشأ لترى فيه الكود الذي تم تسجيله مؤخراً:

Sub Macro1()
    Sheets("Sheet2").Select
    Range("C5").Select
    Selection.ClearContents
End Sub

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

Sub Macro1()
  Sheets("Sheet2").Range("C5").ClearContents
End Sub

لاحظ الفرق (y) هناك سبب آخر يدفعنا لتعديل الكود المسجل ، وهو تسجيل الحركات الزائدة التي قام بها المستخدم دون قصد منه (لاحظ أن مسجل المايكرو يمارس رقابة صارمة على جميع حركاتك). لاحظ الكود التالي:

Sub Macro1()
  Sheets("Main").Select
  Application.CommandBars("Standard").Visible = False
End Sub

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

Sheets("Main").Select

أرجو أن أكون قد وفقت في إيصال المعلومة

[حسام نور]

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

خالص شكري وتقديري

[محمد حجازي]

2.استخدام متغيرات الهدف:

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

انظر للكود التالي:

Sub Report()
  MsgBox "المجموع " & Application.WorksheetFunction.Sum(Sheets(1).Range("A1:D5,E10,F5,G1:G4"))
  MsgBox "المعدل " & Application.WorksheetFunction.Average(Sheets(1).Range("A1:D5,E10,F5,G1:G4"))
  MsgBox "الحدالأعلى " & Application.WorksheetFunction.Max(Sheets(1).Range("A1:D5,E10,F5,G1:G4"))
  MsgBox "الحد الأدنى " & Application.WorksheetFunction.Min(Sheets(1).Range("A1:D5,E10,F5,G1:G4"))
  MsgBox "عدد الأرقام " & Application.WorksheetFunction.Count(Sheets(1).Range("A1:D5,E10,F5,G1:G4"))
End Sub

لاحظ كيف أننا أشرنا إلى الهدف التالي خمس مرات خلال الكود السابق:

Sheets(1).Range("A1:D5,E10,F5,G1:G4")

لنقوم الآن باستخدام متغير معين (المتغير MyRange مثلاً) للدلالة على الهدف السابق:

Sub Report1()
  Dim MyRange As Range
  Set MyRange = Sheets(1).Range("A1:D5,E10,F5,G1:G4")
  MsgBox "المجموع " & Application.WorksheetFunction.Sum(MyRange)
  MsgBox "المعدل " & Application.WorksheetFunction.Average(MyRange)
  MsgBox "الحدالأعلى " & Application.WorksheetFunction.Max(MyRange)
  MsgBox "الحد الأدنى " & Application.WorksheetFunction.Min(MyRange)
  MsgBox "عدد الأرقام " & Application.WorksheetFunction.Count(MyRange)
End Sub

لاحظ أننا لم نحتاج لكتابة الهدف سوى مرة واحدة خلال الكود (y)

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

[محمد حجازي]

3. استخدام الجملة With :

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

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

في الكود التالي سنقوم بإظهار كل من أشرطة الأدوات : القياسي و التنسيق و النموذج:

Sub ToolsList1()
   Application.CommandBars("Standard").Visible = True
   Application.CommandBars("Formatting").Visible = True
   Application.CommandBars("Forms").Visible = True
End Sub

يمكننا تعديل الكود السابق لتتم الإشارة إلى الهدف Application مرة واحدة فقط :

Sub ToolsList2()
  With Application
    .CommandBars("Standard").Visible = True
    .CommandBars("Formatting").Visible = True
    .CommandBars("Forms").Visible = True
  End With
End Sub

كما يمكنك أيضاً الاستعانة بمجموعة من جمل With المتداخلة :o ، لاحظ الكود التالي :

Sub FormatCells()
  With Sheets("Sheet1").Range("A1:D5")
    With .Font
      .ColorIndex = 3
      .Bold = True
      .Size = 12
    End With
    With .Borders()
      .LineStyle = xlDot
      .Weight = xlMedium
    End With
  End With
End Sub

وبهذا فإننا نلاحظ أن الدليل المرجعي المستخدم في الحلقة With الأولى (المجال A1:D5 الموجود في الورقة Sheet1) يمكن أن يكون دليلاً مرجعياً لمجموعات أخرى ( Borders، Font) و التي بدورها ستكون دليلاً مرجعياً لأساليب و خصائص مختلفة (ColorIndex ، LineStyle ، ... ، الخ)

بالتوفيق

[محمد حجازي]

4. التعامل مع الأهداف بصورة مجمعة كلما أمكن ذلك :

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

للأسلوب السابق مزايا عديدة ، من بينها:

- الاستغناء عن التكرار الغير مبرر في الكود وما ينتج عنه من توفير في حجم الكود وسهولة في العمل نتيجة عدم الحاجة لتكرير نفس العبارات أكثر من مرة في الكود الواحد.

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

و الآن سوف نناقش سوياً في الأسطر القادمة الطرق التي نستطيع من خلالها تطبيق هذه الطريقة على نوعين من الأهداف:

أولاً : التعامل مع الهدف Range:

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

Sub ClearMyCells()
  Worksheets("Sheet1").Range("A1:A5").ClearContents
  Worksheets("Sheet1").Range("C4").ClearContents
  Worksheets("Sheet1").Range("E22:E30").ClearContents
End Sub

الكود السابق يقوم بحذف محتويات الخلايا الموجودة في كل من المجالات A1:A5 وC4 و E22:E30. نلاحظ من الكود السابق كيف أننا قمنا باستدعاء الأهداف الثلاثة كل واحد بمفرده هذا فضلاً عن استدعاء الأسلوب ClearContents ثلاث مرات أيضاً !!! ، وفي هذه الحالة فإنني أعتقد أنك ستكون موالياً تماماً للكود التالي (المخصور :d ):

Sub ClearMyCells()
  Worksheets("Sheet1").Range("A1:A5,C4,E22:E30").ClearContents
End Sub

الطريقة السابقة لا تقتصر فقط على تعليمة Range وإنما يمكن استخدامها أيضاً مع تعليمة Cells أيضاً ، لا حظ الكود التالي :

Sub ClearMyCells()
  NumberRow = Application.InputBox(prompt:="أدخل رقم الصف", Title:="رقم الصف", Type:=1)
  Sheets("Sheet1").Cells(NumberRow, 1).ClearContents
  Sheets("Sheet1").Cells(NumberRow, 2).ClearContents
  Sheets("Sheet1").Cells(NumberRow, 3).ClearContents
End Sub

الكود السابق يقوم بمسح محتويات الخلايا الموجودة في الأعمدة A و B و C و الصف المدخل من قبل المستخدم. يمكننا دمج الأهداف الثلاثة السابقة عن طريق التناغم الذي يوفره لنا الاكسل بين التعليمتين Range و Cells ، لاحظ الكود:

Sub ClearMyCells()
  NumberRow = Application.InputBox(prompt:="أدخل رقم الصف", Title:="رقم الصف", Type:=1)
  Sheets("Sheet1").Range(Cells(NumberRow, 1), Cells(NumberRow, 3)).ClearContents
End Sub

هناك أيضاً التعليمة Union التي يمكنها ربط مجموعة من النطاقات بهدف Range واحد ، وميزة هذه التعليمة في أنها توفر لك ربط الأهداف وقت الحاجة إليها فقط ، وبهذا فإنه يمكنك التعامل مع الأهداف بصورة مستقلة عندما تشاء و التعامل معها بصورة مجتمعة عندما تشاء أيضاً. لاحظ الكود التالي:

Sub ClearMyCells()
  Set MyRange1 = Sheets("Sheet1").Range("A1:A5")
  Set MyRange2 = Sheets("Sheet1").Range("C1:C5")
  Union(MyRange1, MyRange2).ClearContents
  MyRange1.Font.Bold = True
End Sub

الكود السابق يقوم بمسح محتويات الخلايا الموجودة في المجالين A1:A5 و C1:C5 ومن ثم يقوم بتثقيل الخط في خلايا المجال A1:A5 فقط . ثانيا : التعامل مع الهدف Sheet : يمكننا دمج عدة أهداف من النوع Sheet باستخدام التعليمة Array . التعليمة Array تحول عدة بيانات مدخلة كنصوص تفصلها فاصلة إلى مصفوفة يكون ترتيب أول عنصر فيها هو صفر ، ولكن كيف يمكننا استخدام هذه التعليمة لدمج عدة أهداف من النوع Sheet؟ لاحظ أولاً الكود التالي:

Sub DeleteSheets()
  Sheets("Sheet2").Delete
  Sheets("Sheet3").Delete
  Sheets("Sheet4").Delete
End Sub

الكود السابق يقوم بحذف الأوراق Sheet2 ، Sheet3 ، Sheet4 الواحدة تلو الأخرى. - الآن يمكننا استخدام التعليمة Array لإنشاء المصفوفة (في سطر منفصل) ، ومن ثم يتم استخدام المصفوفة المنشئة داخل التعليمة Sheets ، لاحظ الكود التالي:

Sub DeleteSheets()
  MySheets = Array("Sheet2", "Sheet3", "Sheet4")
  Sheets(MySheets).Delete
End Sub

- أو يمكننا استخدام التعليمة Array بشكل مباشر داخل التعليمة Sheets ، لاحظ الكود التالي:

Sub DeleteSheets()
  Sheets(Array("Sheet2", "Sheet3", "Sheet4")).Delete
End Sub

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

Sub DeleteSheets()
  Sheets(Array(2, 3, 4)).Delete
End Sub

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

Sub DeleteSheets()
On Error GoTo OutOfRange
Dim ReadyToDel() As String
For Each MySheet In ActiveWorkbook.Worksheets
  If Not MySheet.Name = "Muhammad" And Not MySheet.Name = "Ahmad" Then
    ReDim Preserve ReadyToDel(UBound(ReadyToDel) + 1)
    ReadyToDel(UBound(ReadyToDel)) = MySheet.Name
  End If
Next MySheet
If UBound(ReadyToDel) > 0 Then Sheets(ReadyToDel).Delete
Exit Sub

OutOfRange:
If Err = 9 Then
  ReDim ReadyToDel(0)
  Resume Next
Else
  MsgBox Err.Description
End If
End Sub

الكود السابق يقوم بالبحث ضمن أوراق العمل الموجودة في المصنف النشط ويقوم بحذف جميع الأوراق الموجودة ما عدا الورقتين Muhammad و Ahmad ، وهو (الكود) يعتمد على مصفوفة ديناميكية من أجل تخزين مراجع الأوراق المطلوب حذفها.

أتمنى أن يكون شرحي واضحاً و وافياً

[محمد حجازي]

5. جعل الجملة With خارج الحلقات التكرارية :

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

Sub MyFunction1()
  Dim X As Byte
  For X = 1 To 10
    Sheets("Sheet1").Cells(X + 1, 2).Value = 2 * X + 6
    Sheets("Sheet1").Cells(X + 1, 3).Value = X ^ 2 - X + 7
    Sheets("Sheet1").Cells(X + 1, 4).Value = 23 * X - 20
    Sheets("Sheet1").Cells(X + 1, 5).Value = X ^ 3 + 55 * X ^ 2 + 6
    Sheets("Sheet1").Cells(X + 1, 6).Value = 2 * X
  Next X
End Sub

Sub MyFunction2()
  Dim X As Byte
  For X = 1 To 10
    With Sheets("Sheet1")
      .Cells(X + 1, 2).Value = 2 * X + 6
      .Cells(X + 1, 3).Value = X ^ 2 - X + 7
      .Cells(X + 1, 4).Value = 23 * X - 20
      .Cells(X + 1, 5).Value = X ^ 3 + 55 * X ^ 2 + 6
      .Cells(X + 1, 6).Value = 2 * X
    End With
  Next X
End Sub

Sub MyFunction3()
  Dim X As Byte
  With Sheets("Sheet1")
    For X = 1 To 10
      .Cells(X + 1, 2).Value = 2 * X + 6
      .Cells(X + 1, 3).Value = X ^ 2 - X + 7
      .Cells(X + 1, 4).Value = 23 * X - 20
      .Cells(X + 1, 5).Value = X ^ 3 + 55 * X ^ 2 + 6
      .Cells(X + 1, 6).Value = 2 * X
    Next X
  End With
End Sub

الأكواد الثلاثة السابقة تقوم بنفس العمل (حساب قيمة المتغير التابع y لعدد من المعادلات عند قيم محددة للمتغير المستقل X "من 1 إلى 10"). نلاحظ أنه تم استدعاء الهدف :

Sheets("Sheet1")

50 مرة في الكود الأول ، و 10 مرات في الكود الثاني ، ومرة واحدة في الكود الثالث . عملية الاستدعاء للهدف السابق تؤثر حتماً على سرعة الكود (الكود الثالث هو الأسرع يليه الثاني ثم الأول) ، لاحظ كيف أن عملية استدعاء الهدف السابق قد تقلصت بعد وضع الجملة With خارج الحلقة التكرارية For ... Next . (y) لا تقلق أخي إذا لم تشعر بتغير سرعة الكود وذلك لأن الفرق أقل من أي يدرك بسهولة ...... ولكن مهلاً أخي ، ستشعر بالفرق في حالة وجود عمليات كثيرة تجرى على بيانات كبيرة. هل انتهى الحديث ؟!!!!!! لا أعتقد ذلك : أثناء كتابتي للدرس تذكرت أحد الاستعمالات الغامضة للجملة With : إذا قررت (بعد أن ابيض شعرك) القيام بحفظ جدول الضرب و التخلص من هذه العقدة التي عانيت منها كثيراً :) . أول الخطوات ستكون إحضار الجدول ( ولكن من أين؟ :o ) . حسناً لديك عدة خيارات : استعارة دفتر ولدك الصغير ، طلب الجدول من المكتبة (من أجل ولدك طبعاً :d ) ، القيام بتصميم الجدول عن طريق الاكسل : أول ما سيخطر على بالك هو الكود التالي :

Sub MultiplicationTable1()
  Dim i As Byte, ii As Byte
  For i = 1 To 10
    For ii = 1 To 10
      Sheets("Sheet2").Cells(i, ii) = i * ii
    Next ii
  Next i
End Sub

ولكن ، و بما أن وقتك غالي عليك و تريد استثمار كل ثانية منه لحفظ جدول الضرب ، فإنك ستفضل حتماً التعديل التالي في الكود السابق :

Sub MultiplicationTable2()
  Dim i As Byte, ii As Byte
  With Sheets("Sheet2")
    For i = 1 To 10
      For ii = 1 To 10
        .Cells(i, ii) = i * ii
      Next ii
    Next i
  End With
End Sub

لاحظ أخي أن الهدف :

Sheets("Sheet2")

تم استدعائه 100 مرة في الكود الأول و مرة واحدة فقط في الكود الثاني. (y)

مرفق ملف يتضمن الأكواد الموجودة في هذه المشاركة :

بالتوفيق

WithAndLoops.zip

[العربي_الحر]

احسنت احسن الله اليك

[محمد حجازي]

6. طرق أخرى لتجنب الهدر الناتج عن إبقاء العديد من الجمل داخل الحلقات التكرارية :

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

قارن بين الكودين التاليين :

Sub Test1()
  Dim i As Integer
  With Sheets("Sheet1")
    For i = 2 To 100
      .Cells(i, 1).Value = .Cells(1, 1).Value * i
    Next i
  End With
End Sub

Sub Test2()
  Dim i As Integer
  Dim MyNumber As Double
  With Sheets("Sheet1")
    MyNumber = .Cells(1, 1).Value
    For i = 2 To 100
      .Cells(i, 1).Value = MyNumber * i
    Next i
  End With
End Sub

لاحظ أخي أننا في الكود الأول قمنا باستدعاء القيمة الموجودة داخل الخلية A1 باستخدام الجملة التالية :

.Cells(1, 1).Value

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

Sub Test3()
  With Sheets("Sheet2")
    For Each MyCell In .Range("B2:B11").Cells
      If MyCell.Value > 90000 Then
        .Cells(MyCell.Row, 3).Value = .Cells(2, 8).Value * 0.3
        .Cells(MyCell.Row, 4).Value = .Cells(3, 8).Value * 0.7
        .Cells(MyCell.Row, 5).Value = .Cells(4, 8).Value * 0.85
      ElseIf MyCell.Value > 60000 Then
        .Cells(MyCell.Row, 3).Value = .Cells(2, 8).Value * 0.25
        .Cells(MyCell.Row, 4).Value = .Cells(3, 8).Value * 0.4
        .Cells(MyCell.Row, 5).Value = .Cells(4, 8).Value * 0.65
      Else
        .Cells(MyCell.Row, 3).Value = .Cells(2, 8).Value * 0.2
        .Cells(MyCell.Row, 4).Value = .Cells(3, 8).Value * 0.3
        .Cells(MyCell.Row, 5).Value = .Cells(4, 8).Value * 0.5
      End If
    Next MyCell
  End With
End Sub

الكود الثاني (المعدل) :

Sub Test4()
  With Sheets("Sheet2")
    Group1 = Array(.Cells(2, 8).Value * 0.2, .Cells(3, 8).Value * 0.3, .Cells(4, 8).Value * 0.5)
    Group2 = Array(.Cells(2, 8).Value * 0.25, .Cells(3, 8).Value * 0.4, .Cells(4, 8).Value * 0.65)
    Group3 = Array(.Cells(2, 8).Value * 0.3, .Cells(3, 8).Value * 0.7, .Cells(4, 8).Value * 0.85)
    For Each MyCell In .Range("B2:B11").Cells
      If MyCell.Value > 90000 Then
        .Range(Cells(MyCell.Row, 3), Cells(MyCell.Row, 5)).Value = Group3
      ElseIf MyCell.Value > 60000 Then
        .Range(Cells(MyCell.Row, 3), Cells(MyCell.Row, 5)).Value = Group2
      Else
        .Range(Cells(MyCell.Row, 3), Cells(MyCell.Row, 5)).Value = Group1
      End If
    Next MyCell
  End With
End Sub

بالتوفيق

Loops.zip

[محمد حجازي]

7. اختصار التغييرات الغير ضرورية التي تم تسجيلها :

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

أولاً : عدم قدرة مسجل المايكرو على تمييز الخيارات التي تم تعديلها من داخل صناديق الحوار :

لنقم بتسجيل المايكرو التالي ، وذلك حتى نستطيع معاينة المشكلة بصورة واضحة :

اضغط على الخيار Record New Macro الموجود في القائمة Tools (القائمة الفرعية Macro) لتبدأ عملية تسجيل المايكرو.

حدد المجال B9:G17 . الآن انقر على الخيار Cells الموجود في القائمة Format لتظهر لك نافذة :

ضمن التبويب Font و تحت Font style اختر نوع الخط مثقل (Bold) و اضغط OK .

أوقف تسجيل الماكرو من نفس الخيار السابق (لاحظ تغير اسمه إلى Stop Recording).

الآن اضغط على Alt+Enter لتفتح لك نافذة محرر الفيجوال ، افتح الموديل المنشأ لترى فيه الكود الذي تم تسجيله مؤخراً:

Sub Macro1()
    Range("B9:G17").Select
    With Selection.Font
        .Name = "Arial"
        .FontStyle = "Bold"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
    End With
End Sub

الآن أعد شكل الخط الموجود في خلايا المجال B9:G17 إلى Regular أو Italic و شغل الكود السابق ... بالتأكد ستشعر بالنشوة و ذلك لأن كودك المسجل قد أدى المهمة التي حددتها له سابقاً و قام بتغير شكل الخط إلى Bold :( و لكن مهلاً يا أخي ... لا تتسرع بالحكم و ذلك خوفاً من أن تصيبك صدمة إذا ما قمت بتجريب الكود السابق عدد من المرات الإضافية. :p الآن قم بتغيير نوع الخط الموجود في المجال السابق B9:G17 إلى Andalus و شكله إلى Italic و قم بعدها بتجريب الكود الذي قمت بتسجيله سابقاً : سوف تصرخ قائلاً بأنك لم تحدد للمسجل (عند تسجيل الكود السابق) أن يغير نوع الخط إلى Arial :@ لكي تحدد ماهية المشكلة السابقة يجب أولاً التعرف على الأسلوب الذي يستخدمه مسجل المايكرو في توليد أكواده . كما قلنا سابقاً ، فإن مسجل المايكرو لا يقدر على تمييز الخيارات التي تم تعديلها من داخل صناديق الحوار ، كيف ؟ عندما قمت (في المثال السابق) بتغيير شكل الخط إلى Bold فإن مسجل المايكرو لم يستطيع و للأسف معرفة الخيار الذي قمت بتعديله ، ولتعويض عن هذا النقص قام المسجل (عندما قمت بالضغط على الزر OK) بإدراج قيم جميع الخيارات الموجودة في التبويب Font. مما لا شك فيه أن المشكلة السابقة تسبب قصورين خطيرين في الكود المنشأ : زيادة حجم الكود ، و عدم القدرة على تنفيذ مهام محددة و مفصلة . حل هذه المشكلة بسيط جداً ، و يتلخص في حذف جميع التغييرات الغير ضرورية من الكود و الإبقاء على الأسلوب المطلوب فقط ، لاحظ التعديل التالي للكود السابق :

Sub Macro1()
     Range("B9:G17").Font.FontStyle = "Bold"
End Sub

ثانياً : عدم قدرة مسجل المايكرو في كثير من الأحيان على توليد أكواد مختصرة : كما تعودنا ، لنقم بتسجيل مايكرو يتضمن المشكلة موضوع البحث : اضغط على الخيار Record New Macro الموجود في القائمة Tools (القائمة الفرعية Macro) لتبدأ عملية تسجيل المايكرو. حدد المجال B9:G17 . الآن انقر على الخيار Cells الموجود في القائمة Format لتظهر لك نافذة : ضمن التبويب Border قم بإحاطة جميع خلايا المجال بخط ذو سماكة و لون قياسيين ، و من ثم اضغط OK . أوقف تسجيل الماكرو من نفس الخيار السابق (لاحظ تغير اسمه إلى Stop Recording). الآن اضغط على Alt+Enter لتفتح لك نافذة محرر الفيجوال ، افتح الموديل المنشأ لترى فيه الكود الذي تم تسجيله مؤخراً:

Sub Macro2()
    Range("B9:G17").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
End Sub

لاحظ أخي أن الكود السابق يقوم بإدراج الخطوط في كل موضع بصورة مستقلة عن الموضع الآخر. يمكننا اختصار الكود السابق إلى ما يلي :

Sub Macro2()
    With Range("B9:G17").Borders()
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
End Sub

و لكن هل يمكننا اختصار الكود السابق بصورة أكثر ؟ يمكننا عمل ذلك فقط عندما تكون الخيارات المحدد ة من نوع Default ، كيف ؟ عندما تبحث داخل التعليمات البرمجية تجد أن الثوابت xlContinuous ، xlThin ، xlAutomatic الخاصة بالمعاملات السابقة LineStyle ، Weight ، ColorIndex هي من النوع Default ، أي أن الخط المستمر و العرض الرفيع و اللون التلقائي تعتبر من الخصائص الافتراضية في الأسلوب Borders ، و بما أن جميع الخصائص المستخدمة في الكود السابق تعتبر من و جهة النظر البرمجة خصائص افتراضية ، فيمكننا بذلك حذف هذه الخصائص و الإبقاء على خاصية واحدة منها فقط ، وذلك لأن الاكسل سيدرجها بصورة افتراضية ، لاحظ التعديل التالي على الكود السابق :

Sub Macro2()
    Range("B9:G17").Borders().LineStyle = xlContinuous
End Sub

بالتوفيق

[m.hindawi]

بسم الله الرحمن الرحيم

احسنت اخي محمد

اختصار هائل و يعطي نفس النتيجة

[halwim]

كيف يمكنني أن أحصل على أساسيات الـ VBA؟

بصورة عامة فإنك تحتاج من أجل الولوج إلى عالم الـ VBA إلى معرفتين أساسيتين:

1. معرفة مبادئ الفيجوال الأساسية :

- الأحداث وكيفية ربط السبب بالنتيجة (ربط الحدث بالإجراء)

- المتغيرات و الثوابت و أنواعها

- خصائص الكائنات

- أداة الشرط IF و الحلقات التكرارية

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

2. معرفة الأهداف في الاكسل وتسلسلها الهرمي.

في الروابط التالية دروس للأخ محمد طاهر عن لغة الـ VBA:

http://www.officena.net/Tips/Excel1-intr.htm

http://www.officena.net/Tips/Excel2-vbeditor.htm

http://www.officena.net/Tips/Excel3-Invoice.htm

http://www.officena.net/Tips/Excel4-Grades.htm

ومن خلال تصفحي السريع لهذه الدروس فإنني أعتقد بأنها تمثل خير بداية لمن يريد الدخول لعالم الـ VBA الممتع . أنصحك الآن بقراءة هذه الدروس لكي تتمكن من متابعة سلسلة النصائح التي سنوردها.

إلى الإدارة الموقرة :

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

[omar elhosseini]

بسم الله الرحمن الرحيم

الاخ halwim

الروابط تعمل

وقمت بحقظهم لك فى المرفقات

Excel.rar

تحياتى

[halwim]

شكرا لك أستاذي الفاضل

سبق اني حاولت فتح الروابط وتظهر لي هذه الرموزالغير مفهومة :

?†ïzغ/=؛Hك~;زخمرZ'¼ِ Œ\‰†ي…4p

أما حاليا ولله الحمد فجميع الدروس التي أرفقتها في ملف واحد، شغالة ومن أبدع مايكون

شكراااا .