ماکروهای VBA در اکسل — یک راهنمای مقدماتی و بیان اهمیت آن‌ها

ماکروها در اکسل از طریق خودکار سازی فرایندهایی که به طور مکرر اتفاق می‌افتند، صرفه‌جویی زمانی زیادی ایجاد می‌کنند. اما واقعیت این است که ماکروها محدود هستند. به طور خیلی ساده ممکن است در زمان ضبط کردن ماکرو دچار خطایی بشوید و به همین دلیل است که فرایند ضبط کردن ماکرو دشوار است.

استفاده از VBA برای ایجاد ماکروها باعث می‌شود در این خصوص آزادی عمل بیشتری پیدا کنید. می‌توانید به اکسل بگویید که دقیقاً چه کار کند و این کار را به چه شیوه‌ای انجام دهد. همچنین به تابع‌ها و قابلیت‌های بیشتری دسترسی پیدا می‌کنید. اگر به طور منظم از اکسل استفاده می‌کنید، می‌ارزد که وقت خود را صرف یادگیری شیوه ایجاد ماکروهای VBA بکنید. در ادامه این مسئله را بیشتر توضیح داده‌ایم.

VBA چیست؟

VBA اختصار عبارت Visual Basic for Applications به معنی «زبان برنامه‌نویسی ویژوال‌بیسیک برای برنامه‌ها» است. شما می‌توانید از این زبان برنامه‌نویسی برای بسیاری از برنامه‌های مایکروسافت استفاده کنید. ویژوال‌بیسیک خود یک زبان برنامه‌نویسی است و VBA نسخه خاصی از آن به منظور استفاده در برنامه‌های مایکروسافت است. مایکروسافت پشتیبانی از ویژوال‌بیسیک را در سال 2008 متوقف کرد؛ اما VBA همچنان قدرتمند به کار خود ادامه می‌دهد.

فیلم آموزش آشنایی با برنامه نویسی VBA برای خودکار کردن برنامه های Office (رایگان) در فرادرس

کلیک کنید

خوشبختانه VBA برای افراد غیر برنامه‌نویس کاملاً ساده است و رابط کاربری آن کمک زیادی به ویرایش دستورات می‌کند. بسیاری از دستورهایی که استفاده می‌کنید، دارای پیشنهادهایی هستند و به طور خودکار کامل می‌شوند که این مسئله باعث می‌شود بتوانید به سرعت اسکریپت‌هایی را بنویسید.

با این حال آشنایی با VBA به مدت زمان نسبتاً زیادی نیاز دارد.

مزیت‌های ماکروهای VBA در اکسل

سؤالی که ممکن است از خود بپرسید این است که اگر VBA دشوارتر از ضبط کردن ماکرو است، پس چرا باید از آن استفاده کنیم؟ پاسخ این است که با استفاده از ماکروهای VBA قدرت بسیار بیشتری به دست می‌آورید.

وقتی از ماکروهای VBA استفاده می‌کنید به جای کلیک بر روی نقاط مختلف فایل صفحه گسترده و ضبط کردن کلیک‌های مختلف می‌توانید به همه محدوده اکسل دسترسی داشته باشید و از تابع‌ها و قابلیت‌های زیادی بهره بگیرید. بدین منظور کافی است شیوه استفاده از VBA را بیاموزید.

زمانی که مهارت کافی در VBA پیدا کردید، می‌توانید همه کارهایی را که در یک ماکروی معمولی به زمان زیادی نیاز داشت، خیلی سریع‌تر انجام دهید. نتایج کار نیز قابلیت پیش‌بینی بیشتری دارد چرا که به اکسل می‌گویید که دقیقاً چه کاری انجام دهد و هر گونه ابهامی از میان می‌رود.

زمانی که یک ماکرو VBA ایجاد می‌کنید امکان ذخیره‌سازی و اشتراک آن به همه افراد برای بهره‌گیری از آن ساده خواهد بود. این مسئله به طور خاص زمانی که با افراد زیادی همکاری می‌کنید و همه این افراد باید کارهای مشابهی در اکسل انجام دهند اهمیت زیادی می‌یابد.

در ادامه نگاهی به یک ماکرو VBA ساده خواهیم داشت تا با طرز کار آن بیشتر آشنا شویم.

نمونه‌ای از ماکرو VBA در اکسل

در این بخش یک ماکرو ساده را معرفی می‌کنیم.

فیلم آموزش برنامه نویسی وی بی ای در اکسل – VBA در Excel – مقدماتی در فرادرس

کلیک کنید

در صفحه گسترده ما نام‌های کارمندان، شماره فروشگاهی که کارمند در آن کار می‌کند و فروش فصلی آن‌ها ذکر شده است.

این ماکرو فروش‌های فصلی هر فروشگاه را با هم جمع کرده و مجموع حاصل را در سلول‌های صفحه گسترده ذخیره می‌کند.

Sub StoreSales()

Dim Sum1 As Currency
Dim Sum2 As Currency
Dim Sum3 As Currency
Dim Sum4 As Currency

For Each Cell In Range("C2:C51")
   Cell.Activate
   If IsEmpty(Cell) Then Exit For
   If ActiveCell.Offset(0, -1) = 1 Then
      Sum1 = Sum1 + Cell.Value
   ElseIf ActiveCell.Offset(0, -1) = 2 Then
      Sum2 = Sum2 + Cell.Value
   ElseIf ActiveCell.Offset(0, -1) = 3 Then
      Sum3 = Sum3 + Cell.Value
   ElseIf ActiveCell.Offset(0, -1) = 4 Then
      Sum4 = Sum4 + Cell.Value
End If
Next Cell
Range("F2").Value = Sum1

Range("F3").Value = Sum2
Range("F4").Value = Sum3
Range("F5").Value = Sum4

End Sub

این کد ممکن است طولانی و پیچیده باشد؛ اما آن را تجزیه خواهیم کرد تا عناصر منفرد آن را مشاهده کرده و در مورد مفاهیم مقدماتی VBA بیشتر بیاموزید.

اعلان رویه (Sub)

در آغاز ماژول عبارت «()Sub StoreSales» را داریم. این عبارت یک رویه جدید به نام «StoreSales» تعریف می‌کند.

شما می‌توانید تابع نیز تعریف کنید، تفاوت در این است که تابع‌ها مقادیری را باز می‌گرداند و رویه‌ها چنین خصوصیتی را ندارند. اگر با دیگر زبان‌های برنامه‌نویسی آشنا باشید، باید بدانید که رویه‌ها معادل متد در اغلب زبان‌های برنامه‌نویسی دیگر هستند. در این مورد ما نیازی به بازگشت مقدار نداریم و بنابراین از یک sub استفاده می‌کنیم.

در انتهای ماژول عبارت «End Sub» را داریم که به اکسل می‌گوید کار ما با ماکرو VBA پایان یافته است.

اعلان متغیرها

نخستین خطوط کد در اسکریپت ما با عبارت «Dim» آغاز می‌شود. Dim در VBA دستوری برای اعلان یک متغیر است.

بنابراین Dim Dum1 یک متغیر جدید به نام Sum1 ایجاد می‌کند. با این حال باید به اکسل بگوییم که نوع متغیر چیست. بنابراین باید یک نوع داده را انتخاب کنیم. انواع بسیار مختلفی از داده در VBA وجود دارند که فهرست کامل آن را می‌توانید در این صفحه راهنمای مایکروسافت ببینید.

از آنجا که ماکرو VBA قرار است با مقادیر مالی سروکار داشته باشد، باید از نوع داده Currency استفاده کنیم.

بدین ترتیب عبارت «Dim Sum1 As Currency» به اکسل می‌گوید که یک متغیر Currency به نام Sum1 ایجاد کند. هر متغیر برای تعیین نوع خود به کلیدواژه As نیازمند است.

آغاز یک حلقه (Loop)

حلقه‌ها قدرتمندترین ابزار در هر زبان برنامه‌نویسی محسوب می‌شوند. اگر با حلقه‌ها آشنایی ندارید می‌توانید از این مقاله استفاده نمایید. در این مثال ما از یک حلقه For استفاده می‌کنیم که در مقاله فوق نیز توضیح داده شده است.

فیلم آموزش برنامه نویسی وی بی ای در اکسل – VBA در Excel – تکمیلی در فرادرس

کلیک کنید

حلقه ما به صورت زیر خواهد بود:

For Each Cell in Range("C2:C51")
[a bunch of stuff]
Next Cell

این قطعه کد به اکسل می‌گوید که بر روی سلول‌ها در محدوده‌ای که ذکر کرده‌ایم بچرخد. از شی‌ء Range Object یک نوع خاصی از شی‌ء در VBA است استفاده کرده‌ایم. وقتی به این صورت -Range(“C2:C51”)- از آن استفاده می‌کنیم در واقع به اکسل می‌گوییم که محدوده مورد نظر ما آن 50 سلول است.

عبارت «For Each» به اکسل می‌گوید که ما قصد داریم بر روی هر یک از سلول‌ها در محدوده کاری انجام دهیم. «Next Cell» پس از هر کاری که می‌خواهیم انجام دهیم تکرار می‌شود و اکسل حلقه را از نو شروع می‌کند.

همچنین عبارت زیر را داریم: «If IsEmpty(Cell) Then Exit For».

آیا می‌توانید حدس بزنید این عبارت چه می‌کند؟ البته از نظر تکنیکی استفاده از یک حلقه Loop بهتر است؛ اما بنا بر مقاصد آموزشی در این نوشته از حلقه For Loop استفاده شده است.

عبارت‌های If-Then-Else

هسته مرکزی کد ماکروی ما در عبارت‌های If-Then-Else است. در این قطعه کد توالی عبارت‌های شرطی را ارائه کرده‌ایم:

If ActiveCell.Offset(0, -1) = 1 Then
   Sum1 = Sum1 + Cell.Value
ElseIf ActiveCell.Offset(0, -1) = 2 Then
   Sum2 = Sum2 + Cell.Value
ElseIf ActiveCell.Offset(0, -1) = 3 Then
   Sum3 = Sum3 + Cell.Value
ElseIf ActiveCell.Offset(0, -1) = 4 Then
   Sum4 = Sum4 + Cell.Value
End If

اگر کمی دقت کنید بخش بزرگی از کاری که قطعه کد فوق انجام می‌دهد را در می‌یابید. گرچه ممکن است با عبارت «ActiveCell.Offset» آشنا نباشید. دستور «ActiveCell.Offset(0, -1)» به اکسل می‌گوید که به سلولی که یک ستون در سمت چپ سلول کنونی قرار دارد نگاه کند.

در مورد مثال ما منظور از کد فوق این است که ستون شماره فروشگاه مشخص شود. اگر اکسل مقدار 1 را در این ستون ببیند، در این صورت محتوای سلول کنونی را گرفته و به Sum1 اضافه می‌کند. اگر مقدار 2 وجود داشته باشد، محتوای سلول فعلی به متغیر Sum2 اضافه می‌شود و همین طور تا آخر.

اکسل به ترتیب بر روی همه این عبارت‌ها می‌چرخد. اگر عبارت شرطی مقدار صحیح باز گرداند، بخش Then اجرا می‌شود. در غیر این صورت به عبارت ElseIf بعدی می‌رود. اگر به انتها برسد و هیچ یک از شرط‌ها برقرار نباشند، در این صورت هیچ اقدامی صورت نمی‌گیرد.

ترکیب حلقه و شروط باعث اجرای این ماکرو می‌شود. حلقه به اکسل می‌گوید که به همه سلول‌های انتخاب شده برود و شروط باعث می‌شوند که کارهای مورد نظر را انجام دهد.

نوشتن مقادیر سلول

در نهایت نتایج محاسبات در سلول‌های مربوطه نوشته می‌شوند. در ادامه خطوطی که این کار را انجام می‌دهند ارائه کرده‌ایم:

Range("F2").Value = Sum1
Range("F3").Value = Sum2
Range("F4").Value = Sum3
Range("F5").Value = Sum4

با استفاده از «Value.» و نماد تساوی، هر یک از آن سلول‌ها را به مقدار یکی از متغیرها تعیین می‌کنیم. به این ترتیب کار انجام می‌شود. با استفاده از End Sub به اکسل می‌گوییم که کار نوشتن رویه به پایان رسیده است و ماکرو VBA کامل شده است.

زمانی که ماکرو را با استفاده از دکمه Macros در برگه Developer اجرا کنیم، نتایج زیر به دست می‌آیند:

جمع‌بندی بلوک‌های تشکیل دهنده VBA در اکسل

زمانی که برای اولین بار به ماکروی ابتدای این نوشته نگاه کردید، احتمالاً به نظرتان پیچیده آمده است؛ اما پس از این که آن را به قطعات سازنده‌اش تجزیه کردیم، منطق کار روشن‌تر شد. VBA نیز مانند هر زبان اسکریپت نویسی دیگری به زمانی برای یادگیری نیاز دارد.

اما با تمرین، مهارت‌های VBA شما تکمیل‌تر می‌شود و می‌توانید ماکروها را سریع‌تر، دقیق‌تر و با قدرت بیشتری نسبت به زمان ضبط کردن کلیک‌ها، بنویسید.

زمانی که با مشکلی مواجه می‌شوید، کافی است یک جستجوی گوگل بکنید تا به پاسخ سؤال‌هایتان برسید. همچنین مرجع VBA اکسل در وب‌سایت مایکروسافت نیز در صورتی که به دنبال پاسخ یک سؤال فنی‌تر باشید، می‌تواند کمک خوبی بکند.

اگر این نوشته مورد توجه شما قرار گرفته است، پیشنهاد می‌کنیم موارد زیر را نیز مطالعه نمایید:

• آموزش طراحی رابط گرافیکی با برنامه نویسی VBA در اکسل
• آموزش برنامه نویسی VBA در اکسل
• ۴ اشتباه رایج در هنگام نوشتن ماکروها در اکسل
• آموزش نرم‌افزار مایکروسافت اکسل
• چگونه کلمه عبور فایل برنامه‌نویسی‌شده در اکسل (VBA) را بازیابی کنیم؟
• مجموعه آموزش های پروژه محور برنامه نویسی

==