إذا كنت بدأت للتو باستخدام VBA ، فأنت تريد البدء في دراسة دليل VBA للمبتدئين. ولكن إذا كنت خبيرًا متمرسًا في VBA وكنت تبحث عن أشياء أكثر تقدماً يمكنك إجراؤها باستخدام VBA في Excel ، فاستمر في القراءة.
تفتح القدرة على استخدام ترميز VBA في Excel عالمًا كاملاً الأتمتة. يمكنك أتمتة العمليات الحسابية في Excel ، أزرار الضغط ، وحتى إرسال البريد الإلكتروني. هناك إمكانيات أكثر لأتمتة عملك اليومي باستخدام VBA مما قد تدرك.
دليل VBA المتقدم لبرنامج Microsoft Excel
الهدف الرئيسي لكتابة رمز VBA في Excel هو أنه يمكنك استخراج المعلومات من جدول بيانات ، قم بإجراء مجموعة متنوعة من العمليات الحسابية عليه ، ثم اكتب النتائج مرة أخرى إلى جدول البيانات
فيما يلي الاستخدامات الأكثر شيوعًا لـ VBA في Excel.
مع هذه الأمثلة الثلاثة ، يجب عليك تكون قادرًا على كتابة مجموعة متنوعة من التعليمات البرمجية المتقدمة الخاصة بـ Excel VBA.
استيراد البيانات وإجراء العمليات الحسابية
واحدة من أكثر الأشياء شيوعًا التي يستخدمها الناس لـ Excel لـ يقوم بإجراء عمليات حسابية على البيانات الموجودة خارج Excel. إذا كنت لا تستخدم VBA ، فهذا يعني أنه يتعين عليك استيراد البيانات يدويًا وتشغيل الحسابات وإخراج هذه القيم إلى ورقة أو تقرير آخر.
In_content_1 الكل: [300 × 250] / dfp : [640x360]->باستخدام VBA ، يمكنك أتمتة العملية بالكامل. على سبيل المثال ، إذا كان لديك ملف CSV جديد تم تنزيله في دليل على جهاز الكمبيوتر الخاص بك كل يوم اثنين ، فيمكنك تهيئة رمز VBA ليتم تشغيله عند فتح جدول البيانات الخاص بك لأول مرة صباح يوم الثلاثاء.
رمز الاستيراد التالي سوف قم بتشغيل واستيراد ملف CSV في جدول بيانات Excel الخاص بك.
Dim ws As Worksheet, strFile As StringSet ws = ActiveWorkbook.Sheets("Sheet1") Cells.ClearContents strFile = “c:\temp\purchases.csv” With ws.QueryTables.Add(Connection:="TEXT;" & strFile, Destination:=ws.Range("A1")) .TextFileParseType = xlDelimited .TextFileCommaDelimiter = True .Refresh End With
افتح أداة تحرير Excel VBA وحدد كائن الورقة 1. من مربعي القائمة المنسدلة للكائن والطريقة ، اختر ورقة العملو التنشيط. سيؤدي هذا إلى تشغيل الشفرة في كل مرة تفتح فيها جدول البيانات.
سيؤدي ذلك إلى إنشاء وظيفة Sub Worksheet_Activate (). الصق الشفرة أعلاه في تلك الوظيفة.
يؤدي هذا إلى تعيين ورقة العمل النشطة على الورقة 1، ومسح الورقة ، والاتصال بالملف باستخدام مسار الملف الذي حددته مع المتغير strFile، ثم باستخدامحلقات مستمرة عبر كل سطر في الملف وتضع البيانات في الورقة بدءًا من الخلية A1.
إذا قمت بتشغيل هذا الرمز ، فسترى أن بيانات ملف CSV يتم استيراده إلى جدول البيانات الفارغ ، في الورقة 1.
الاستيراد ليست سوى الخطوة الأولى . بعد ذلك ، تريد إنشاء رأس جديد للعمود الذي سيحتوي على نتائج الحساب الخاصة بك. في هذا المثال ، دعنا نقول أنك تريد حساب الضريبة بنسبة 5٪ المدفوعة على بيع كل عنصر.
ترتيب الإجراءات التي يجب أن تتخذها الشفرة هو:
ستنفذ التعليمة البرمجية التالية جميع هذه الخطوات.
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، وحدد إدراجمن مجموعة عناصر التحكمفي الشريط. حدد زر الضغطعنصر تحكم ActiveX من القائمة المنسدلة.
ارسم زر الضغط على أي جزء من الورقة بعيدًا عن المكان الذي ستذهب إليه أية بيانات.
انقر بزر الماوس الأيمن على زر الضغط وحدد الخصائص. في نافذة الخصائص ، قم بتغيير التسمية التوضيحية إلى ما تريد عرضه على المستخدم. في هذه الحالة ، قد يكون حساب الضريبة بنسبة 5٪.
سترى هذا النص ينعكس على زر الدفع نفسه. أغلق نافذة الخصائص، وانقر نقرًا مزدوجًا على زر الضغط نفسه. سيؤدي هذا إلى فتح نافذة محرر الشفرة ، وسيكون المؤشر داخل الوظيفة التي سيتم تشغيلها عندما يضغط المستخدم على زر الضغط.
الصق رمز حساب الضريبة من القسم أعلاه في هذه الوظيفة ، مع الحفاظ على مضاعف معدل الضريبة عند 0.05. تذكر تضمين السطور التاليين لتحديد الورقة النشطة.
Dim ws As Worksheet, strFile As String
Set ws = ActiveWorkbook.Sheets("Sheet1")
الآن ، كرر العملية مرة أخرى ، مع إنشاء زر ضغط ثانٍ. اجعل التسمية التوضيحية حساب الضريبة بنسبة 7٪.
انقر نقرًا مزدوجًا فوق هذا الزر والصق نفس الرمز ، لكن اجعل مُضاعِف الضريبة 0.07.
الآن ، وفقًا للزر الذي تضغط عليه ، فإن عمود الضرائب سوف يتم حسابها وفقًا لذلك.
بمجرد الانتهاء من ذلك ، سيكون لديك أزرار الدفع على الورقة الخاصة بك. سيبدأ كل منهم عملية حساب ضريبية مختلفة وسيكتب نتائج مختلفة في عمود النتائج.
لإرسال رسالة نصية ، حدد قائمة Developer، وحدد وضع التصميممن مجموعة عناصر التحكم في الشريط لتعطيل وضع التصميم. هذا سوف تفعيل أزرار الضغط.
حاول تحديد كل زر ضغط لترى كيف يتغير عمود نتيجة "الضرائب".
نتائج حساب البريد الإلكتروني لشخص ما
ما إذا كنت ترغب في إرسال النتائج في جدول البيانات إلى شخص ما عبر البريد الإلكتروني؟
يمكنك إنشاء زر آخر يسمى ورقة البريد الإلكتروني إلى Bossباستخدام نفس الإجراء أعلاه. سيتضمن رمز هذا الزر استخدام كائن Excel CDO لتكوين إعدادات البريد الإلكتروني SMTP ، وإرسال النتائج بالبريد الإلكتروني بتنسيق يمكن للمستخدم قراءته.
لتمكين هذه الميزة ، تحتاج إلى تحديد أدوات و المراجع. مرر لأسفل إلى Microsoft CDO لمكتبة Windows 2000، وقم بتمكينه ، وحدد موافق.
هناك ثلاثة أقسام رئيسية للرمز تحتاج إلى إنشاء لإرسال رسالة بريد إلكتروني وتضمين نتائج جدول البيانات.
الأول هو إعداد متغيرات للاحتفاظ بها الموضوع ، إلى ومن العناوين ، ونص البريد الإلكتروني.
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 = "[email protected]"
strTo = "[email protected]"
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 وخادم SMTP في Gmail (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 protected]"
.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 protected] وكلمة المرور مع تفاصيل حسابك الخاصة.
أخيرًا ، لبدء إرسال البريد الإلكتروني ، أدخل الرمز التالي.
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 الفريدة الخاصة بك.