یکی از قابلیت‌های مفید Excel که توانمندی‌های آن را چند برابر می‌کند، قابلیت نوشتن Macro است. در اکسل می‌توانید با استفاده از زبان نسبتاً ساده‌ی VBA ماکروهایی بنویسید تا امور سریع و ساده انجام شود و همین‌طور با استفاده از VBA می‌توانید توابع موردنیاز که در لیست توابع پیش‌فرض اکسل وجود ندارد را خودتان بنویسید.
در این مقاله با مقدمات برنامه‌نویسی یا ماکرونویسی در نرم‌افزار Excel مایکروسافت آشنا می‌شویم. با ما باشید.
برخی کاربران مبتدی تصور می‌کنند که اکسل صرفاً برای محاسبه و جدول کشیدن و یا امور آماری و مهندسی است اما در حقیقت در اکسل می‌توانید برنامه‌نویسی هم انجام بدهید. البته اکسل ابزاری برای ضبط کردن ماکروها دارد تا کاربر عملیات تکراری را ساده‌تر و سریع‌تر انجام دهد اما این قابلیت ساده، محدودیت‌های زیادی هم دارد. در کنار ضبط کردن Macro می‌توانید از VBA و برنامه‌نویسی اکسل استفاده کنید و کارهای پیچیده و تکراری را به سرعت انجام دهید.
اکسل ده‌ها تابع مفید برای امور محاسباتی و غیرمحاسباتی در اختیار شما قرار می‌دهد اما اگر تابع خاصی لازم داشته باشید که در میان توابع اکسل موجود نیست، می‌توانید آن را به زبان VBA بنویسید.
VBA چیست و چه ارتباطی با ویژوآل بیسیک دارد؟
یکی از زبان‌های برنامه‌نویسی VBA است. Visual Basic for Applications یا به اختصار VBA، زبانی برای نوشتن اپلیکیشن‌ها در نرم‌افزارهای مایکروسافت است. در حقیقت ویژوآل بیسیک یک زبان برنامه‌نویسی عمومی‌تر است و VBA زبان خاص‌تری برای نوشتن اپلیکیشن‌ها است. مایکروسافت در سال ۲۰۰۸ توسعه‌ی ویژوآل بیسیک را رها کرده اما پروژه‌ی VBA همچنان ادامه دارد.
خوشبختانه VBA برای کسانی که کارشان برنامه‌نویسی نیست، زبان ساده‌ای محسوب می‌شود. VBA بسیار ساده است و واسط گرافیکی که برای ویرایش کردن کدهای VBA در نرم‌افزارهای مایکروسافت ارایه شده نیز طراحی بسیار خوبی دارد و کارها را ساده می‌کند. زمانی که مشغول کدنویسی هستید، پنجره‌های پاپ‌آپ کوچک برای پیشنهاد کردن توابع باز می‌شود و قابلیت تکمیل خودکار نیز شما را از به خاطر سپردن نام دقیق توابع و بی‌نیاز می‌کند.
البته تصور نکنید که VBA زبان برنامه‌نویسی بسیار ساده‌ای است و سریعاً می‌توانید از آن استفاده کنید و ماکروها و توابع پیچیده بنویسید.
کاربردها و مزایا و معایب ماکروهای VBA در Excel
منظور از Macro این است که کدهایی بنویسیم تا امور پیچیده به صورت خودکار و با فشار دادن یک کلید میانبر کیبورد یا کلیک روی یک دکمه و غیره، انجام شود. البته روی برخی موس و کیبوردهای گیمینگ نیز دکمه‌های ماکرو وجود دارد که با فشار دادن آن، تعدادی عملیات ساده مثل فشار دادن چند کلید موس یا کیبورد در فواصل زمانی خاص انجام می‌شود و کاربرد آن به ویژه در بازی‌هایی است که عکس‌العمل سریع گیمر، مزیت محسوب می‌شود.
نوشتن ماکروها در اکسل سخت‌تر از ضبط کردن ماکروهای ساده است اما با کدنویسی می‌توانید انجام امور پیچیده‌تری را ممکن کنید. با قابلیت ضبط کردن ماکروها در اکسل، می‌توانید روی برخی سلول‌ها کلیک کنید و امور ساده‌ای انجام بدهید و همه چیز را ضبط کنید تا کارهای تکراری ساده شود اما اگر از برنامه‌نویسی به زبان VBA استفاده کنید، توانایی شما چند برابر می‌شود و علاوه بر این دقیقاً متوجه می‌شوید که قرار است چه محاسبه‌ای با استفاده از داده‌های کدام سلول‌ها انجام شود.
در نهایت می‌توانید ماکروهایی که نوشته‌اید را با دیگران به اشتراک بگذارید تا همان کار تکراری را سریعاً انجام دهند.
فعال کردن قابلیت ماکرونویسی در اکسل
در حالت پیش‌فرض با توجه به اینکه کاربران مبتدی از قابلیت ماکرونویسی یا دقیق‌تر بگوییم، برنامه‌نویسی اکسل استفاده نمی‌کنند، تب مربوط به این موارد غیرفعال است و نمایش داده نمی‌شود. برای فعال کردن نمایش تب مربوطه، ابتدا روی منوی File کلیک کنید و سپس از ستون کناری گزینه‌ی Options را انتخاب کنید. در ستون کناری پنجره‌ی Excel Options روی Customize Ribbon کلیک کنید.
روش سریع‌تر دسترسی به این بخش، این است که روی نوار ابزار بالای صفحه راست‌کلیک کنید و گزینه‌ی Customize the Ribbon را انتخاب کنید.
اکنون تیک Developer را بزنید و سپس روی OK کلیک کنید.
برای نوشتن کدهای VBA کافی است روی تب Developer کلیک کرده و سپس روی Visual Basic کلیک کنید.
در ادامه می‌توانید روی نام صفحه کلیک کنید و کدنویسی را آغاز کنید. برای اجرا کردن متد یا توابع، کافی است از کلید میانبر F5 استفاده کنید که معادل کلیک کردن روی دکمه‌ی Run است.
مثالی ساده از Macro در اکسل
قبل از هر چیز بهتر است با یک ماکروی ساده در نرم‌افزار مایکروسافت اکسل آشنا شویم. فرض کنید صفحه‌ای در اکسل ساخته‌اید که حاوی نام کارمندان است. محل کارکرد هر کارمند را با یک عدد مشخص کرده‌اید و میزان فروش فصلی هر کارمند را نیز روبروی نام وی نوشته‌اید.
اگر بخواهید مقدار فروش هر یک از فروشگاه‌ها را محاسبه کنید و در سلول‌های ستون F بنویسید، می‌توانید از روش ساده‌ی جمع زدن مقدار سلول‌ها استفاده کنید. اما همین کار ساده و سریع را می‌توانید با استفاده از VBA نیز انجام بدهید:
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 StoreSales() ذکر شده که برای تعریف کردن حاصل‌جمع فروش فروشگاه‌ها استفاده می‌شود. در اکسل می‌توانید تابع تعریف کنید و توابع مقداری را برمی‌گردانند اما در عین حال می‌توانید Sub تعریف کنید که مقداری را برنمی‌گرداند. به عبارت دیگر Sub در VBA مثل روش یا متد و به زبان دیگر Method در زبان‌های برنامه‌نویسی مختلف است.
در انتهای کد نیز End Sub ذکر شده که به معنی پایان Sub تعریف شده است.
تعریف کردن متغیرها در VBA
پس از تعریف تابع یا روش، متغیرها تعریف می‌شوند. برای این کار از Dim استفاده می‌شود. بنابراین Dim Sub1 برای تعریف کردن متغیر Sum1 استفاده می‌شود و به همین صورت برای فروش هر فروشگاه، یک متغیر جدید تعریف می‌شود. دقت کنید که در تعریف هر متغیر، می‌بایست نوع آن هم مشخص شود. در VBA انواع داده زیاد است و هر یک کاربری خاصی دارد. در مثال ما متغیر از نوع Currency یا واحد پول است. لذا برای تعریف کردن اولین متغیر، از عبارت زیر استفاده می‌شود:
Dim Sum1 As Currency
در تعریف هر متغیر دیگری می‌توانید از As و نوع داده استفاده کنید و آن را تعریف کنید.
ایجاد حلقه‌ی کارهای تکراری در مایکروهای اکسل
برای انجام عملیات تکراری روی تعداد زیادی سلول، می‌بایست یک حلقه ایجاد شود. برای ساختن حلقه می‌توان از For استفاده کرد. با For Each می‌توانیم دستوراتی را روی تک‌تک عناصر اجرا کنیم تا به آخرین عنصر برسیم.
به عنوان مثال اگر بخواهید کاری را در مورد تمام سلول‌های محدوده‌ی C2 الی C51 انجام بدهید، می‌بایست دستور زیر را استفاده کنید:
For Each Cell in Range("C2:C51")
محاسبات و دستورات
Next Cell
در انتهای حلقه از Next Cell استفاده شده تا تمام محاسبات و دستورات در مورد سلول بعدی تکرار شود.
دقت کنید که محدوده‌ی سلول‌ها با Range مشخص می‌شود و می‌تواند یک یا چند ستون و ردیف باشد که در مثال ما فقط یک ستون است.
دستورات شرطی if و else در ماکروهای اکسل
و اما به مراحل محاسبه‌ی حاصل‌جمع می‌رسیم. برای انتخاب سلول، از دستور Cell.Activate استفاده می‌شود. در شروع مراحل محاسبه، ابتدا یک دستور شرطی قرار می‌دهیم تا در صورت خالی بودن سلول، اجرای حلقه متوقف شود و کاربر مجبور شود که مقدار فروش کارمند را ۰ وارد کند.
If IsEmpty(Cell) Then Exit For
در ادامه به چهار شرط نیاز داریم تا عدد قبل از فروش هر کارمند را بررسی کنیم و اگر ۱ است، فروش را به جمع فروش فروشگاه اول اضافه کنیم و اگر ۲ است، فروش را به حاصل‌جمع فروشگاه ۲ اضافه کنیم و به همین ترتیب دو شرط بعدی را بررسی کنیم.
برای بررسی کردن مقدار سلول ماقبل سلول انتخاب‌شده، از Offset استفاده می‌شود که به دو عدد نیاز دارد. عدد اول حرکت در جهت ارتفاع و عدد دوم حرکت در جهت افقی است. بنابراین دستور زیر معادل انتخاب کردن سلول قبلی در همان ردیف است:
ActiveCell.Offset(0, -1)
شرط را با استفاده از If تعریف می‌کنیم و اگر درست باشد، دستوراتی که پس از Then ذکر می‌شود، اجرا خواهد شد. بنابراین شرط اول به این صورت تعریف می‌شود:
If ActiveCell.Offset(0, -1) = 1 Then
و دستوری که باید اجرا شود، اضافه شدن مقدار سلول انتخاب‌شده‌ی فعلی به متغیر Sum1 است. مقدار سلول فعال با عبارت Cell.Value مشخص می‌شود:
Sum1 = Sum1 + Cell.Value
لذا شرط اول به این صورت خواهد بود:
If ActiveCell.Offset(0, -1) = 1 Then
Sum1 = Sum1 + Cell.Value
برای شرط دوم از ElseIf استفاده کنید چرا که سرعت پردازش را بیشتر می‌کند.
ElseIf ActiveCell.Offset(0, -1) = 2 Then
Sum2 = Sum2 + Cell.Value
و در نهایت مجموع ۴ شرط:
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
تغییر مقدار سلول‌های اکسل با VBA
در حال حاضر مقدار حاصل‌جمع مشخص شده است و تنها کاری که باقی می‌ماند، ذکر حاصل‌جمع روبروی نام هر یک از ۴ فروشگاه است. برای تغییر دادن مقدار سلول‌ها از Value استفاده می‌شود. بنابراین چهار دستور زیر مقدار سلول F2 الی F5 را عوض می‌کند:
Range("F2").Value = Sum1
Range("F3").Value = Sum2
Range("F4").Value = Sum3
Range("F5").Value = Sum4
و به این ترتیب نوشتن ماکرو به پایان می‌رسد!
ماکروهای اکسل دقت و سرعت را صدها برابر بیشتر می‌کنند
در مثال ما تعداد کارمندان کمتر از ۱۰۰ نفر است اما تصور کنید که اگر تعداد کارمندان ۵ هزار نفر باشد، استفاده از تابع Sum یا کلیک کردن روی تک‌تک سلول‌ها و زدن کلید + و ادامه دادن این مراحل، چقدر وقت‌گیر و البته غیردقیق است. ممکن است به اشتباه فروش کارمندی را در فروشگاه دیگری محاسبه کنید!
نوشتن ماکروهای اکسل بسیار ساده نیست اما از نظر دقت و سرعت انجام محاسبات و کارهای تکراری، بسیار عالی هستند. بنابراین بد نیست برای یادگیری VBA در اکسل، حوصله کنید و وقت بگذارید.
makeuseofسیاره‌ی آی‌تی