دليل VBA متقدم لـ MS Excel: احتراف الأتمتة

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

دليل VBA متقدم لـ MS Excel: احتراف الأتمتة

تتيح لك القدرة على استخدام ترميز VBA في Excel عالمًا كاملاً من الأتمتة. يمكنك أتمتة العمليات الحسابية في Excel، والأزرار، وحتى إرسال البريد الإلكتروني. هناك المزيد من الاحتمالات لأتمتة عملك اليومي باستخدام VBA أكثر مما قد تدرك.

دليل VBA المتقدم لـ Microsoft Excel

الهدف الرئيسي من كتابة كود VBA في Excel هو أنه يمكنك استخراج المعلومات من جدول بيانات، وإجراء مجموعة متنوعة من العمليات الحسابية عليها، ثم كتابة النتائج مرة أخرى إلى جدول البيانات

فيما يلي الاستخدامات الأكثر شيوعًا لـ VBA في Excel.

استيراد البيانات وإجراء العمليات الحسابية
حساب النتائج من ضغط المستخدم على زر
إرسال نتائج العمليات الحسابية بالبريد الإلكتروني إلى شخص ما

باستخدام هذه الأمثلة الثلاثة، يجب أن تكون قادرًا على كتابة مجموعة متنوعة من أكواد Excel VBA المتقدمة الخاصة بك.

استيراد البيانات وإجراء العمليات الحسابية

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

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

سيتم تشغيل كود الاستيراد التالي واستيراد ملف CSV إلى جدول بيانات Excel الخاص بك.

Dim ws As Worksheet, strFile As String

Set ws = ActiveWorkbook.Sheets("Sheet1")
Cells.ClearContents

strFile = “c:temppurchases.csv”

With ws.QueryTables.Add(Connection:="TEXT;" & strFile, Destination:=ws.Range("A1"))
     .TextFileParseType = xlDelimited
     .TextFileCommaDelimiter = True
     .Refresh
End With

افتح أداة تحرير Excel VBA وحدد كائن Sheet1. من مربعات القائمة المنسدلة للكائن والطريقة، اختر Worksheet و Activate. سيؤدي هذا إلى تشغيل الكود في كل مرة تفتح فيها جدول البيانات.

سيؤدي هذا إلى إنشاء دالة Sub Worksheet_Activate(). الصق الكود أعلاه في هذه الدالة.

يقوم هذا بتعيين ورقة العمل النشطة إلى Sheet1، ومسح الورقة، والاتصال بالملف باستخدام مسار الملف الذي حددته بالمتغير strFile، ثم تقوم حلقة With بالدوران عبر كل سطر في الملف ووضع البيانات في الورقة بدءًا من الخلية A1.

إذا قمت بتشغيل هذا الكود، فسترى أن بيانات ملف CSV يتم استيرادها إلى جدول البيانات الفارغ الخاص بك، في Sheet1.

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

ترتيب الإجراءات التي يجب أن يتخذها الكود الخاص بك هو:

إنشاء عمود نتائج جديد يسمى taxes.
التكرار عبر عمود units sold وحساب ضريبة المبيعات.
كتابة نتائج العمليات الحسابية في الصف المناسب في الورقة.

سيحقق الكود التالي كل هذه الخطوات.

Dim LastRow As Long
Dim StartCell As Range
Dim rowCounter As Integer
Dim rng As Range, cell As Range
Dim fltTax As Double

Set StartCell = Range("A1")

'Find Last Row and Column
LastRow = ws.Cells(ws.Rows.Count, StartCell.Column).End(xlUp).Row
Set rng = ws.Range(ws.Cells(2, 4), ws.Cells(LastRow, 4))

rowCounter = 2
Cells(1, 5) = "taxes"

For Each cell In rng
     fltTax = cell.Value * 0.05
     Cells(rowCounter, 5) = fltTax
     rowCounter = rowCounter + 1
Next cell

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

الصق كود VBA أعلاه أسفل الكود السابق، وقم بتشغيل البرنامج النصي. سترى النتائج تظهر في العمود E.

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

حساب النتائج من خلال الضغط على زر

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

تعتبر أزرار التحكم مفيدة إذا كنت تريد التحكم في العمليات الحسابية المستخدمة. على سبيل المثال، في نفس الحالة المذكورة أعلاه، ماذا لو كنت تريد استخدام معدل ضريبة بنسبة 5٪ لمنطقة ما، ومعدل ضريبة بنسبة 7٪ لمنطقة أخرى؟

يمكنك السماح بتشغيل نفس كود استيراد CSV تلقائيًا، ولكن اترك كود حساب الضريبة ليتم تشغيله عند الضغط على الزر المناسب.

باستخدام نفس جدول البيانات المذكور أعلاه، حدد علامة التبويب Developer، وحدد Insert من مجموعة Controls في الشريط. حدد عنصر تحكم push button ActiveX من القائمة المنسدلة.

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

انقر بزر الماوس الأيمن فوق زر الضغط، وحدد Properties. في نافذة الخصائص، قم بتغيير Caption إلى ما تريد عرضه للمستخدم. في هذه الحالة، قد يكون Calculate 5% Tax.

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

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

Dim ws As Worksheet, strFile As String

Set ws = ActiveWorkbook.Sheets("Sheet1")

الآن، كرر العملية مرة أخرى، وأنشئ زرًا ثانيًا. اجعل التسمية حساب ضريبة 7%.

انقر نقرًا مزدوجًا فوق هذا الزر والصق نفس الكود، ولكن اجعل مُضاعِف الضريبة 0.07.

الآن، بناءً على الزر الذي تضغطه، سيتم حساب عمود الضرائب وفقًا لذلك.

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

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

حاول تحديد كل زر لمعرفة كيف يتغير عمود نتائج “الضرائب”.

إرسال نتائج الحساب بالبريد الإلكتروني إلى شخص ما

ماذا لو كنت تريد إرسال النتائج الموجودة في جدول البيانات إلى شخص ما عبر البريد الإلكتروني؟

يمكنك إنشاء زر آخر يسمى Email Sheet to Boss باستخدام نفس الإجراء المذكور أعلاه. سيتضمن رمز هذا الزر استخدام كائن Excel CDO لتكوين إعدادات البريد الإلكتروني SMTP، وإرسال النتائج بالبريد الإلكتروني بتنسيق سهل القراءة للمستخدم.

لتمكين هذه الميزة، تحتاج إلى تحديد Tools and References. قم بالتمرير لأسفل إلى Microsoft CDO for Windows 2000 Library، وقم بتمكينه، وحدد OK.

هناك ثلاثة أقسام رئيسية للتعليمات البرمجية التي تحتاج إلى إنشائها لإرسال بريد إلكتروني وتضمين نتائج جدول البيانات.

الأول هو إعداد متغيرات للاحتفاظ بالموضوع وعناوين “إلى” و “من” ونص البريد الإلكتروني.

Dim CDO_Mail As Object
Dim CDO_Config As Object
Dim SMTP_Config As Variant
Dim strSubject As String
Dim strFrom As String
Dim strTo As String
Dim strCc As String
Dim strBcc As String
Dim strBody As String
Dim LastRow As Long
Dim StartCell As Range
Dim rowCounter As Integer
Dim rng As Range, cell As Range
Dim fltTax As Double

Set ws = ActiveWorkbook.Sheets("Sheet1")
strSubject = "Taxes Paid This Quarter"
strFrom = "MyEmail@gmail.com"
strTo = "BossEmail@gmail.com"
strCc = ""
strBcc = ""
strBody = "The following is the breakdown of taxes paid on sales this quarter."

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

Set StartCell = Range("A1")

'Find Last Row and Column
LastRow = ws.Cells(ws.Rows.Count, StartCell.Column).End(xlUp).Row
Set rng = ws.Range(ws.Cells(2, 4), ws.Cells(LastRow, 4))

rowCounter = 2
strBody = strBody & vbCrLf

For Each cell In rng
     strBody = strBody & vbCrLf
     strBody = strBody & "We sold " & Cells(rowCounter, 3).Value & " of " & Cells(rowCounter, 1).Value _
     & " for " & Cells(rowCounter, 4).Value & " and paid taxes of " & Cells(rowCounter, 5).Value & "."
     rowCounter = rowCounter + 1
Next cell

يتضمن القسم التالي إعداد إعدادات SMTP بحيث يمكنك إرسال بريد إلكتروني من خلال خادم SMTP الخاص بك. إذا كنت تستخدم Gmail، فعادةً ما يكون هذا هو عنوان بريدك الإلكتروني في Gmail وكلمة مرور Gmail وخادم Gmail SMTP ‏(smtp.gmail.com).

Set CDO_Mail = CreateObject("CDO.Message") 
On Error GoTo Error_Handling
Set CDO_Config = CreateObject("CDO.Configuration")
CDO_Config.Load -1
Set SMTP_Config = CDO_Config.Fields

With SMTP_Config
.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com"
.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "email@website.com"
.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "password"
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 465
.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
 .Update
End With

With CDO_Mail
     Set .Configuration = CDO_Config
End With

استبدل email@website.com وكلمة المرور بتفاصيل حسابك الخاص.

أخيرًا، لبدء إرسال البريد الإلكتروني، أدخل الكود التالي.

CDO_Mail.Subject = strSubject
CDO_Mail.From = strFrom
CDO_Mail.To = strTo
CDO_Mail.TextBody = strBody
CDO_Mail.CC = strCc
CDO_Mail.BCC = strBcc
CDO_Mail.Send

Error_Handling:
If Err.Description <> "" Then MsgBox Err.Description

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

بعد تمكين ذلك، سيتم إرسال بريدك الإلكتروني. هذا ما يبدو عليه الأمر بالنسبة للشخص الذي يتلقى بريدك الإلكتروني الذي تم إنشاؤه تلقائيًا.

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

Excel