الإشارة إلى المراجع References في ورقة العمل في VBA Excel

14 أكتوبر 2022

https://academy.hsoub.com/apps/productivity/office/microsoft-excel/%D8%A7%D9%84%D8%A5%D8%B4%D8%A7%D8%B1%D8%A9-%D8%A5%D9%84%D9%89-%D8%A7%D9%84%D9%85%D8%B1%D8%A7%D8%AC%D8%B9-references-%D9%81%D9%8A-%D9%88%D8%B1%D9%82%D8%A9-%D8%A7%D9%84%D8%B9%D9%85%D9%84-%D9%81%D9%8A-vba-excel-r806/

متابعون 0

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

الإشارة إلى مرجع من عدة مجالات غير متجاورة في VBA Excel

نشير في VBA Excel إلى عدة مجالات باستخدام تابع المجال Range أو تابع الدمج Union، كما نستخدم خاصية المناطق Areas للإشارة إلى مجموعة مجالات محددة في ورقة العمل.

باستخدام الخاصية Range ونمط الترميز A1

يمكن الإشارة إلى أكثر من مجال باستخدام الخاصية Range في اكسل VBA بكتابة أسماء المجالات وفق نمط الترميز A1 بين علامتي اقتباس، حيث يشير الترميز A1 إلى أكثر من مجال بوضع علامة الفاصلة بين كل مجالين منفصلين أو أكثر. يحدد المثال التالي ثلاث مجالات في الورقة Sheet1 في نفس الوقت:

Sub MultiRange()
  Worksheets("Sheet1").Range("C2:D4,G4:H7,B7:D9").Select
End Sub

باستخدام الخاصية Range مع المجالات المسماة

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

مثال: يسمي الإجراء التالي ثلاثة مجالات بالأسماء التالية: MyRange1 و MyRange2 و MyRange3 ثم يحددهم معًا باستخدام الشيفرة التالية:

Sub NamedRangesSelect()
  ThisWorkbook.Names.Add Name:="MyRange1", RefersTo:=Worksheets("Sheet1").Range("C2:D4")
  ThisWorkbook.Names.Add Name:="MyRange2", RefersTo:=Worksheets("Sheet1").Range("G4:H7")
  ThisWorkbook.Names.Add Name:="MyRange3", RefersTo:=Worksheets("Sheet1").Range("B7:D9")
  Range("MyRange1,MyRange2,MyRange3").Select
End Sub

ملاحظة: يمكن الإشارة إلى المجالات المسماة مسبقًا في إكسل دون استخدام الكود وذلك بتحديد كل مجال ثم كتابة اسمه في حقل Name Box.

باستخدام تابع الدمج Union

يستخدم تابع الدمج Union في اكسل VBA لدمج عدة مجالات إلى كائن مجال واحد.

مثال: ينشئ المثال التالي كائن مجال اسمه MyUniounRange يدمج فيه ثلاث مجالات منفصلة A1:B5 و D3:F4 و G5:I5 في كائن واحد. ثم يجعل خلاياه محاطةً بحدود من كافة الاتجاهات ومحتويات جميع الخلايا تساوي 10. تتميز هذه العملية بالسرعة في التنفيذ لكامل كائن المجال المدمج في نفس الوقت.

Sub UniounRange()
  Dim Rg1 As Range, Rg2 As Range, Rg3 As Range, myUniounRange As Range
  Set Rg1 =   Worksheets("Sheet1").Range("A1:B5")
  Set Rg2 = Worksheets ("Sheet1").Range("D3:F4")
  Set Rg3 = Worksheets ("Sheet1").Range("G5:I5")
  Set myUniounRange = Union(Rg1, Rg2, Rg3)
  myUniounRange.Value = 10
  myUniounRange.HorizontalAlignment = xlHAlignCenter
  myUniounRange.Borders.LineStyle = xlContinuous
End Sub

باستخدام خاصية المناطق Area

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

Sub RangeAreas()
  Worksheets ("Sheet1").Cells(2, 3) = Selection.Areas.Count    
End Sub

الإشارة إلى جميع الخلايا في ورقة العمل في اكسل VBA

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

Worksheets("اسم ورقة العمل").Cells

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

مثال: يحدد الإجراء التالي جميع خلايا ورقة العمل في الصفحة Sheet1 في المصنف الحالي ويمسح محتوياتها:

Sub SelectAllCels()
  Worksheets("Sheet1").Cells.ClearContents
End Sub

الإشارة إلى خلايا أو مجالات متعلقة بخلايا أخرى في اكسل VBA

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

مثال: لإعطاء الأمر بتحديد الخلية التي على بعد صفين وعمودين من الخلية B3 التي تمثل الخلية Cells(3, 2).

Sub offsetRanges()
  Worksheets("Sheet1").Cells(3, 2).Offset(2, 2).Select
End Sub

مثال: لإعطاء الأمر بجعل قيم المجال B1:B5 مساوية لقيم المجال H1:H5 (الذي يقع بانزياح أفقي 6 خلايا من العمود B1:B5 وعلى نفس البعد شاقولي بانزياح 0 خلية) ثم الأمر بتحديد المجال H1:H5. نلاحظ سرعة تطبيق الأوامر على كافة خلايا كائن المجال المدمج دفعة واحدة، بخلاف الطريقة التي تستخدم الخاصية Cells والتي تحتاج الدخول في حلقة تكرارية وتطبيق الأوامر على الخلايا واحدة تلو الأخرى.

Sub offsetFill()
  Worksheets("Sheet1").Range("B1:B5").Value = Range("B1:E5").Offset(0, 6).Value
End Sub

الأمر بتحديد الخلايا في إكسل VBA

تعطي الصيغة Select الأمر بالتحديد وتستخدم مع أوراق العمل والمجالات:

فلتحديد مجال معين في ورقة معينة نحدد الورقة أولا بالصيغة:

Worksheets("اسم ورقة العمل").Select

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

Worksheets("اسم ورقة العمل").Range("عنوان أو اسم المجال").Select

مثال: يحدد الإجراء التالي المجال B2:D4 في الورقة Sheet1:

Sub ObjectSelect()
  Worksheets("Sheet1").Select
  Worksheets("Sheet1").Range("B2:D4").Select
End Sub

اقتباس

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

مثال: يرجع الإجراء التالي رسالة خطأ إذا كان الورقة الفعالة هي ورقة أخرى غير Sheet1

Sub ObjectSelect()
  Worksheets("Sheet1").Range("B2:D4").Select
End Sub

الإشارة إلى الخلايا المحددة في إكسل VBA

نشير إلى الخلايا المحددة في ورقة العمل في VBA باستخدام كائن التحديد Selection وهو من كائنات التطبيق. ونعبر عنه بالصيغة:

Application.Selection

مثال: يضع الإجراء التالي حدودًا للمجال المحدد في ورقة العمل النشطة بالأزرق الفاتح ويكتب عنوان التحديد في الخلية B2:

Sub SelectionRange()
  Application.Selection.Interior.ColorIndex = 20
  Worksheets("Sheet1").Cells(2, 2) = Selection.Address
End Sub

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

مثال: يبين الإجراء التالي نوع الكائن المحدد ويكتب النتيجة في الخلية B3:

Worksheets("Sheet1").Cells(2, 3) = TypeName(Selection)

فإذا كان الكائن شكلًا مستطيلً يرجع Rectangle، وإذا كان خطًا أو سهمًا يرجع Line، وإذا حددنا صورةً يرجع Picture، وإذا حددنا مربع نص مُدرج يرجع TextBox، وإذا حددنا مخططًا يرجع ChartObject، وإذا حددنا منطقة الرسم منه يرجع PlotArea، فإذا حددنا المخطط يرجع Series، وهكذا.

الإشارة إلى الخلايا النشطة Active Cell في إكسل VBA

الخلية النشطة Active Cell في إكسل VBA هي الخلية الفعالة في إكسل والتي يُكتب اسمها أو عنوانها في مربع الاسم. وهي خاصية من خصائص كائن التطبيق Application object، يعبر عنها بالصيغة التالية:

Application.ActiveCell

وترجع كائن مجال يمثل خلية واحدة Cell Object هي الخلية النشطة، ولا توجد خاصية ترجع كائن خلية إلا من خلال خاصية ActiveCell.

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

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        Worksheets("Sheet1").Cells.Clear
        Application.ActiveCell.Value = "الأهداف الذكية"
        Application.ActiveCell.Interior.ColorIndex = 4
        Application.ActiveCell.Offset(-1, -2) = "قابلة للتحقيق"
        Application.ActiveCell.Offset(-1, 2) = "قابلة للقياس"
        Application.ActiveCell.Offset(-2, 0) = "محددة"
        Application.ActiveCell.Offset(2, -1) = "ذات صلة"
        Application.ActiveCell.Offset(2, 1) = "محدة زمنيًا"
End Sub

الإشارة إلى مجال في ورقة العمل النشطة في إكسل VBA

نشير إلى مجال معين في ورقة العمل النشطة في إكسل VBA بالصيغة التالية:

ActiveSheet.Range("اسم أو عنوان المجال المطلوب")

وترجع مجالًا في ورقة العمل VBA النشطة في المصنف النشط. وفي حال عدم وجود ورقة عمل نشطة فلا تستجيب الخاصية ولا ترجع شيئًا.

مثال: يحدد الإجراء التالي المجال A2:F3 في ورقة العمل النشطة أيًأ كانت، وكلما فعلنا ورقة جديدة يحدد الإجراء نفس المجال في الورقة الجديدة المفعلة. فإذا لم تكن هناك أية ورقة عمل مفعلة فلا يتغير التحديد.

Sub MyActivesheet()
    Activesheet.Range("A2:F3").Select
End Sub

الإشارة إلى كامل الصفوف أو الأعمدة التي تحوي مجال محدد في VBA

تشير الصيغة التالية إلى كامل الصف أو الصفوف التي تحوي مجال محدد:

Range.EntireRow

وتشير الصيغة التالية إلى كامل العمود أو الأعمدة التي تحوي مجال محدد:

Range.EntireColumn

ترجع كل من الصيغتين السابقتين كائن مجال Range object يمثل كامل الصفوف أو الأعمدة التي تحوي مجالًا محددًا.

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

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Worksheets("Sheet1").Cells.Clear
    Application.Selection.EntireRow.Borders.LineStyle = xlContinuous
    Application.Selection.EntireColumn.Borders.LineStyle = xlContinuous
End Sub

الإشارة إلى الصف أو العمود الأخير في ورقة العمل في إكسل VBA

نشير إلى ترتيب الصف الأخير في ورقة العمل VBA باستخدام الخاصية Rows.Count والتي ترجع عدد الصفوف في ورقة العمل 1048576. ونشير إلى ترتيب العمود الأخير في ورقة العمل باستخدام الخاصية Columns.Count والتي ترجع عدد الأعمدة في ورقة العمل 16384.

يرجع الإجراء التالي عدد الصفوف في ورقة العمل 1048576 في الخلية A1. ويرجع عدد الأعمدة في ورقة العمل 16384 في الخلية A2، ثم يكتب الرقم 7 في آخر خلية في ورقة العمل ثم يحددها.

Sub LastCell()
  Worksheets("Sheet1").Cells(1, 1) = Rows.Count
  Worksheets("Sheet1").Cells(2, 1) = Columns.Count
  Cells(Rows.Count, Columns.Count) = 7
  Cells(Rows.Count, Columns.Count).Select
End Sub

ملاحظة: هذه الصيغ أكثر استخدامًا مع الكائن Range والكائن UsedRange والكائن Selection لترجع الصف الأخير Range.Rows.Count والعمود الأخير Range.Columns.Count من تلك المجالات.

خاتمة

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