دليل VBA في Excel لأتمتة المهام المتكررة وإنشاء قوالب بيانات ذكية

مقدمة: لماذا تحتاج إلى أتمتة العمل داخل Excel؟

إذا كنت تتعامل يومياً مع جداول بيانات متشابهة، أو تُكرر الخطوات نفسها في كل شهر، فإن استخدام VBA، وهو اختصار Visual Basic for Applications، يمكن أن يوفّر عليك وقتاً كبيراً ويقلّل الأخطاء اليدوية. الفكرة الأساسية هنا هي تحويل الخطوات الروتينية إلى أوامر برمجية قابلة للتنفيذ بضغطة زر.

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

نظرة عامة على المشروع

رغم أن VBA يُصنَّف في بعض البيئات على أنه تقنية قديمة نسبياً، فإنه ما يزال أداة قوية جداً داخل Microsoft Excel، خصوصاً عند الحاجة إلى أتمتة الأعمال المكتبية المتكررة بسرعة ومن دون تعقيد.

سنقوم في هذا المقال ببناء Macro يقوم بالمهام التالية:

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

لا تحتاج إلى خبرة سابقة في VBA حتى تتابع الشرح، لكن يُفضَّل أن تكون لديك معرفة أساسية باستخدام Excel.

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

إنشاء ملف عمل يدعم وحدات الماكرو

ابدأ بإنشاء ملف Excel جديد، ثم احفظه بصيغة .xlsm، وهي صيغة Excel Macro-Enabled Workbook. هذا النوع من الملفات يسمح بتخزين وتشغيل أكواد VBA.

إعداد ورقة المدن Cities

أعد تسمية أول ورقة عمل إلى Cities، ثم أضف أسماء المدن في العمود الأول، بحيث تكون كل مدينة في صف مستقل. على سبيل المثال:

• Amsterdam
• Barcelona
• Berlin
• Brussels
• London
• Rome

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

إعداد ورقة التاريخ Date

أنشئ ورقة جديدة باسم Date، ثم ضع التاريخ في الخلية B1. سنعتمد على هذا التاريخ لتحديد الشهر والسنة اللذين سيُبنى على أساسهما القالب.

يفضل استخدام تنسيق تاريخ مناسب لعرض الشهر والسنة فقط، لأن عدد الأيام يختلف من شهر لآخر، كما تختلف أيام الأسبوع أيضاً.

تفعيل تبويب المطور وفتح محرر Visual Basic

إذا كانت هذه أول مرة تستخدم فيها VBA، فقد تحتاج إلى إظهار تبويب Developer داخل شريط أدوات Excel. بعد تفعيله، ستتمكن من فتح محرر Visual Basic وكتابة الأكواد الخاصة بك.

من داخل المحرر، يمكنك كتابة وحدات Macro أو دوال Function وربطها بالمصنف الحالي.

إنشاء أول Macro في VBA

تبدأ وحدات Macro في VBA بالكلمة المفتاحية Sub، وتنتهي بـ End Sub. مثال أولي:

Sub CreateTemplate()
End Sub

ولجعل هذا الإجراء ينفّذ شيئاً مفيداً، يمكننا إنشاء ورقة جديدة تلقائياً:

Sub CreateTemplate()
    'Adding a new worksheet called NewSheet to the workbook
    Sheets.Add.Name = "NewSheet"
End Sub

عند تشغيل هذا الكود، سيُنشئ Excel ورقة باسم NewSheet.

وإذا كنت تريد تحديد موقع الورقة الجديدة بعد ورقة معيّنة، فاستخدم المعامل After:=:

Sub CreateTemplate()
    'Adding a new worksheet after the Date worksheet
    Sheets.Add(After:=Sheets("Date")).Name = "NewSheet"
End Sub

قراءة عدد المدن باستخدام المتغيرات

قبل إنشاء أوراق لكل مدينة، نحتاج إلى معرفة عدد الصفوف المستخدمة في ورقة Cities. يمكن اختبار ذلك باستخدام Debug.Print:

Sub CreateTemplate()
    Debug.Print Worksheets("Cities").UsedRange.Rows.Count
End Sub

سيظهر الناتج في نافذة Immediate Window. لكن بدلاً من طباعة القيمة فقط، من الأفضل تخزينها داخل متغير:

Sub CreateTemplate()
    Dim NumberOfCities As Integer
    NumberOfCities = Worksheets("Cities").UsedRange.Rows.Count
    Debug.Print NumberOfCities
End Sub

في VBA نستخدم الكلمة Dim للتصريح عن المتغير، مع تحديد نوعه مثل String أو Integer أو Double.

إنشاء ورقة لكل مدينة باستخدام الحلقات

الآن يمكننا استخدام حلقة For لإنشاء ورقة عمل مستقلة لكل مدينة موجودة في ورقة Cities:

Sub CreateTemplate()
    Dim CityName As String
    Dim NumberOfCities As Integer
    Dim SheetIndex As Integer

    NumberOfCities = Worksheets("Cities").UsedRange.Rows.Count

    For SheetIndex = 1 To NumberOfCities
        CityName = Worksheets("Cities").Cells(SheetIndex, 1)
        Sheets.Add(After:=Sheets(Sheets.Count)).Name = CityName
    Next SheetIndex
End Sub

هنا يحدث الآتي:

• المتغير SheetIndex يتحرك من الصف الأول إلى آخر صف مستخدم.
• في كل دورة، نقرأ اسم المدينة من العمود الأول.
• ثم ننشئ ورقة جديدة تحمل اسم تلك المدينة.

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

إنشاء دالة لحساب عدد أيام الشهر

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

Function DaysInMonth(DateInput As Date)
    DaysInMonth = Day(DateSerial(Year(DateInput), Month(DateInput) + 1, 1) - 1)
End Function

تعتمد هذه الدالة على DateSerial() للحصول على آخر يوم في الشهر المطلوب، ثم تعيد رقم هذا اليوم، أي عدد أيام الشهر.

إضافة تواريخ الشهر إلى كل ورقة مدينة

بعد حساب عدد الأيام، يمكننا كتابة جميع التواريخ داخل كل ورقة مدينة:

Sub CreateTemplate()
    Dim CityName As String
    Dim DateIndex As Integer
    Dim NumberOfCities As Integer
    Dim NumberOfDays As Integer
    Dim ReportDate As Date
    Dim SheetIndex As Integer

    NumberOfCities = Worksheets("Cities").UsedRange.Rows.Count

    For SheetIndex = 1 To NumberOfCities
        CityName = Worksheets("Cities").Cells(SheetIndex, 1)
        Sheets.Add(After:=Sheets(Sheets.Count)).Name = CityName
    Next SheetIndex

    ReportDate = Worksheets("Date").Cells(1, 2)
    NumberOfDays = DaysInMonth(ReportDate)

    For SheetIndex = 1 To NumberOfCities
        CityName = Worksheets("Cities").Cells(SheetIndex, 1)
        For DateIndex = 1 To NumberOfDays
            Worksheets(CityName).Cells(DateIndex + 1, 1) = DateSerial(Year(ReportDate), Month(ReportDate), DateIndex)
        Next DateIndex
    Next SheetIndex
End Sub

لاحظ أننا استخدمنا DateIndex + 1 لأن الصف الأول سنخصصه لاحقاً للعناوين.

إضافة رؤوس الأعمدة داخل القالب

يمكنك إضافة العناوين يدوياً باستخدام Range:

Worksheets(CityName).Range("B1") = "Minimum"
Worksheets(CityName).Range("C1") = "Mean"
Worksheets(CityName).Range("D1") = "Maximum"

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

استخدام المصفوفات لتطبيق مبدأ DRY

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

Sub CreateTemplate()
    Dim CityName As String
    Dim DateIndex As Integer
    Dim HeaderIndex As Integer
    Dim Headers(2) As Variant
    Dim NumberOfCities As Integer
    Dim NumberOfDays As Integer
    Dim ReportDate As Date
    Dim SheetIndex As Integer

    Headers(0) = "Minimum"
    Headers(1) = "Mean"
    Headers(2) = "Maximum"

    NumberOfCities = Worksheets("Cities").UsedRange.Rows.Count

    For SheetIndex = 1 To NumberOfCities
        CityName = Worksheets("Cities").Cells(SheetIndex, 1)
        Sheets.Add(After:=Sheets(Sheets.Count)).Name = CityName
    Next SheetIndex

    ReportDate = Worksheets("Date").Cells(1, 2)
    NumberOfDays = DaysInMonth(ReportDate)

    For SheetIndex = 1 To NumberOfCities
        CityName = Worksheets("Cities").Cells(SheetIndex, 1)

        For DateIndex = 1 To NumberOfDays
            Worksheets(CityName).Cells(DateIndex + 1, 1) = DateSerial(Year(ReportDate), Month(ReportDate), DateIndex)
        Next DateIndex

        For HeaderIndex = 0 To UBound(Headers) - LBound(Headers)
            Worksheets(CityName).Cells(1, HeaderIndex + 2) = Headers(HeaderIndex)
        Next HeaderIndex
    Next SheetIndex
End Sub

هذا الأسلوب يجعل الكود أكثر مرونة وقابلية للصيانة.

استيراد البيانات من ملف خارجي باستخدام VBA

بعد تجهيز القالب، تأتي الخطوة الأهم: استيراد بيانات درجات الحرارة من ملف آخر. سنبدأ بإجراء جديد باسم ImportData:

Sub ImportData()
    Dim FileLocation As String
    Dim WorksheetTitle As String

    WorksheetTitle = ActiveSheet.Name
End Sub

نستخدم WorksheetTitle لتخزين اسم الورقة النشطة، حتى نعرف أين ستُدرج البيانات.

ثم نضيف نافذة اختيار ملف:

FileLocation = Application.GetOpenFilename

If FileLocation = "False" Then
    Beep
    Exit Sub
End If

Application.ScreenUpdating = False
Set ImportWorkbook = Workbooks.Open(Filename:=FileLocation)
Debug.Print ImportWorkbook.Worksheets(1).Range("B1")
ImportWorkbook.Close
Application.ScreenUpdating = True

هذا الجزء يقوم بما يلي:

• يفتح نافذة لاختيار الملف.
• إذا ألغى المستخدم العملية، يتوقف التنفيذ.
• يعطّل تحديث الشاشة مؤقتاً لتسريع الأداء.
• يفتح ملف البيانات.
• يقرأ قيمة من الخلية B1 لاختبار نجاح الاستيراد.
• يغلق الملف ويعيد تفعيل تحديث الشاشة.

التعامل مع الكائنات في VBA

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

ThisWorkbook.Worksheets(WorksheetTitle).Range("A1") = ImportWorkbook.Worksheets(1).Range("B1")
'This is equivalent to:
ThisWorkbook.Worksheets(WorksheetTitle).Cells(1, 1) = ImportWorkbook.Worksheets(1).Range(1, 2)

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

استخدام القواميس Dictionaries لتنظيم البيانات

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

ابدأ بتعريف المتغيرات الأساسية:

Dim DataIndex As Integer
Dim DaysIndex As Integer
Dim FileLocation As String
Dim Headers(2) As Variant
Dim WorksheetTitle As String

Set TemperaturesDict = CreateObject("Scripting.Dictionary")
Headers(0) = "Maximum"
Headers(1) = "Minimum"
Headers(2) = "Mean"

ولكي تعمل القواميس بسهولة داخل المحرر، تأكد من تفعيل المرجع Microsoft Scripting Runtime من خلال القائمة Tools > References.

تخزين درجات الحرارة داخل قاموس متداخل

For DaysIndex = 11 To ImportWorkbook.Worksheets(1).UsedRange.Rows.Count
    Set DataDict = CreateObject("Scripting.Dictionary")

    For DataIndex = 0 To 2
        DataDict.Add Headers(DataIndex), ImportWorkbook.Worksheets(1).Cells(DaysIndex, DataIndex + 2)
    Next DataIndex

    TemperaturesDict.Add DaysIndex, DataDict
Next DaysIndex

في هذا الجزء:

• نمر على صفوف البيانات في الملف المستورد.
• ننشىء قاموساً داخلياً DataDict لكل يوم.
• نضيف إليه القيم المرتبطة بالمفاتيح Maximum وMinimum وMean.
• ثم نضيف هذا القاموس إلى القاموس الرئيسي TemperaturesDict.

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

إعادة ترتيب البيانات قبل إدراجها في القالب

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

Headers(0) = "Minimum"
Headers(1) = "Mean"
Headers(2) = "Maximum"

For DaysIndex = 2 To ThisWorkbook.Worksheets(WorksheetTitle).UsedRange.Rows.Count
    If TemperaturesDict.Exists(DaysIndex - 1) Then
        For DataIndex = 0 To 2
            ThisWorkbook.Worksheets(WorksheetTitle).Cells(DaysIndex, DataIndex + 2) = TemperaturesDict(DaysIndex - 1)(Headers(DataIndex))
        Next DataIndex
    End If
Next DaysIndex

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

الكود النهائي لإجراء استيراد البيانات

Sub ImportData()
    Dim DataIndex As Integer
    Dim DaysIndex As Integer
    Dim FileLocation As String
    Dim Headers(2) As Variant
    Dim WorksheetTitle As String

    Set TemperaturesDict = CreateObject("Scripting.Dictionary")

    Headers(0) = "Maximum"
    Headers(1) = "Minimum"
    Headers(2) = "Mean"

    WorksheetTitle = ActiveSheet.Name
    FileLocation = Application.GetOpenFilename

    If FileLocation = "False" Then
        Beep
        Exit Sub
    End If

    Application.ScreenUpdating = False
    Set ImportWorkbook = Workbooks.Open(Filename:=FileLocation)

    For DaysIndex = 11 To ImportWorkbook.Worksheets(1).UsedRange.Rows.Count
        Set DataDict = CreateObject("Scripting.Dictionary")

        For DataIndex = 0 To 2
            DataDict.Add Headers(DataIndex), ImportWorkbook.Worksheets(1).Cells(DaysIndex, DataIndex + 2)
        Next DataIndex

        TemperaturesDict.Add DaysIndex, DataDict
    Next DaysIndex

    Headers(0) = "Minimum"
    Headers(1) = "Mean"
    Headers(2) = "Maximum"

    For DaysIndex = 2 To ThisWorkbook.Worksheets(WorksheetTitle).UsedRange.Rows.Count
        If TemperaturesDict.Exists(DaysIndex - 1) Then
            For DataIndex = 0 To 2
                ThisWorkbook.Worksheets(WorksheetTitle).Cells(DaysIndex, DataIndex + 2) = TemperaturesDict(DaysIndex - 1)(Headers(DataIndex))
            Next DataIndex
        End If
    Next DaysIndex

    ImportWorkbook.Close
    Application.ScreenUpdating = True
End Sub

أفضل ممارسات لتحسين كفاءة أكواد VBA

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

متى يكون هذا النهج مناسباً في العمل الحقيقي؟

هذا النوع من الأتمتة مفيد جداً في حالات مثل:

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

وكلما زاد عدد الخطوات الروتينية التي تنفذها يدوياً، زادت القيمة التي تحصل عليها من تعلم VBA.

الخلاصة التقنية

يُعد VBA خياراً عملياً وفعّالاً لأتمتة المهام داخل Excel، خاصة عندما تكون بيئة العمل تعتمد على الملفات والجداول اليومية أو الشهرية. في هذا المثال، رأينا كيف يمكن تحويل عملية كانت ستستهلك وقتاً وجهداً إلى نظام شبه تلقائي ينشئ القوالب، يملأ التواريخ، وينظم استيراد البيانات بدقة. تقنياً، الجمع بين Sub وFunction وLoops وArrays وDictionaries يمنحك أساساً ممتازاً لبناء أدوات أكثر تقدماً داخل بيئة Excel نفسها، دون الحاجة إلى حلول خارجية معقدة.