آموزش ماکرونویسی در اکسل — تصویری و گام به گام

اجرای عملیات تکراری و در عین حال، حفظ دقت در انجام محاسبات، از اهداف اولیه استفاده از رایانه‌ها بوده است. در این بین نرم‌افزارهای برنامه‌نویسی، قابلیت تعامل با سخت‌افزار و اجرای فرامین را میسر می‌سازند. برنامه‌های کاربردی مانند اکسل برای اجرای دستورات و عملیات تکراری و همچنین افزایش قابلیت‌های محاسباتی، از زبان برنامه‌نویسی VBA یا ویژوال بیسیک برای برنامه‌های کاربردی (Visual Basic for Applications) بهره می‌برند که اغلب به آن «ماکرو» (Macro) نیز می‌گویند. در این متن به آموزش ماکرونویسی در اکسل می‌پردازیم و شیوه استفاده از آن را بازگو خواهیم کرد.

برای آشنایی بیشتر با اکسل و همچنین زبان برنامه‌نویسی VBA نوشتارهای برنامه نویسی VBA در اکسل و ماکروهای VBA در اکسل — یک راهنمای مقدماتی و بیان اهمیت آن‌ها را مطالعه کنید. همچنین خواندن مطالب چگونه کلمه عبور فایل برنامه‌نویسی‌شده در اکسل (VBA) را بازیابی کنیم؟ و چهار اشتباه رایج در هنگام نوشتن ماکروها در اکسل نیز خالی از لطف نیست.

آموزش ماکرونویسی در اکسل

در محیط «مایکروسافت اکسل» (Microsoft Excel) امکان کدنویسی و ایجاد ماکرو به زبان «VBA» یا «ویژوال بیسیک برای برنامه‌های کاربردی» (Visual Basic for Applications) وجود دارد. به کمک VBA می‌توان ماکروها، فرم‌های کاربری، نمایش یک پیغام یا به طور کلی اجرای کد و دستورات در یک سند در شرایط مشخص را ایجاد کرد. صفحه گسترده اکسل به کمک VBA قادر است محدودیت‌ها را از بین برده و به طور کامل یک برنامه کاربردی کامل در اختیار کاربر قرار دهد. هر چند زبان برنامه‌نویسی VBA ساده است ولی به هر حال باید با مبانی برنامه‌نویسی آشنایی داشته باشید.

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

کلیک کنید

به منظور بهره‌گیری از VBA و آموزش ماکرونویسی در اکسل ابتدا باید وارد این برنامه شده و برگه Developer را فعال و در بین برگه‌های موجود، نمایش دهیم. برای این کار از فهرست File گزینه Options را اجرا کرده و در بخش Customize Ribbon در قسمت سمت راست پنجره، از لیست کشویی Customize the Ribbon، گزینه Developer را فعال کرده و دکمه OK را کلیک کنید.

این کار را به کمک فهرست کلیک راست روی برگه‌ها و اجرای دستور Customize the Ribbon نیز می‌توان انجام داد. در تصویر ۲، نحوه دسترسی به این دستور دیده می‌شود.

ایجاد یک برنامه کاربردی به زبان VBA در اکسل به دو روش صورت می‌گیرد. در یک روش، عملیاتی که باید به صورت تکراری اجرا شوند، بوسیله دستور «ضبط ماکرو» (Macro Recorder) ذخیره شده و هر بار که لازم باشند به کار می‌آیند. در روش دیگر با کدهای ویژوال بیسیک باید دستورات و عملیات را معرفی کرده و به مشابه یک برنامه نویس اشکالات برنامه و معایب را مشخص و رفع کرد. در ادامه هر دو شیوه را فراگرفته و کاربردهای هر یک را بازگو خواهیم کرد.

ضبط یک ماکرو در اکسل

دستور Macro Recorder، ابزاری بسیار مفید برای ساخت ماکرو یا برنامه‌های VBA محسوب می‌شود. بحث آموزش ماکرونویسی در اکسل در ابتدا از این محل آغاز می‌شود. به این ترتیب هنگام ضبط یک ماکرو، هر کاری را که با کاربرگ و سلول‌های خود انجام می‌دهید ضبط شده و بارها قابل اجرا خواهد بود. تنها کاری که باید برای ضبط یک ماکرو انجام دهید، این است که یک بار کار خاص یا عملیات مورد نیازتان را ضبط کنید. در مرحله بعدی، می توانید بارها و بارها با کلیک یک دکمه (که خودتان به ماکرو نسبت داده‌اید) آن عمل یا عملیات را انجام دهید.

نکته: پس از ضبط، می‌توانید به سادگی ویرایشگر ویژوال بیسیک را نمایش داده و کدهای تولید شده توسط ماکرو را مشاهده کرده یا تغییر دهید. بنابراین به راحتی می‌توانید دستورات را با عملیات صورت گرفته مطابقت داده و زبان VBA را یاد بگیرید.

برای ضبط یک ماکرو، از برگه View‌ در بخش Macros گزینه Record Macros را فعال کنید. به این ترتیب پنجره‌ای به مانند زیر باز می‌شود.

در تصویر 3 مشخص است که بخش Macro name‌، برای در نظر گرفتن یک نام برای ماکرو به کار رفته و همچنین Shortcut key نیز برای ایجاد کلید میانبر اختصاص یافته است. اگر می‌خواهید این ماکرو فقط در کاربرگ جاری فعال باشد، در بخش Store macro in گزینه This workbook را انتخاب کنید.

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

کلیک کنید

فعال کردن گزینه Personal Macro Workbook باعث ایجاد یک پرونده مختص ماکرو خواهد شد. بخش Description نیز به توضیحات اختصاص دارد. نوشتن مطلبی در مورد نحوه اجرا و عملیات صورت گرفته توسط ماکرو در این قسمت کاربران را یاری می‌رسانند تا پارامترها و حاصل اجرای ماکرو را بهتر درک کنند.

با فشردن دکمه OK، ضبط ماکرو آغاز می‌شود. در اینجا عملیات ضبط شده در ماکرو را برای قالب‌بندی یک سلول در نظر می‌گیریم.

1. پنجره قالب‌بندی سلول (Format Cell) را باز کنید.
2. رنگ زمینه Fill را به زرد و قلم (Font) را روی اندازه ۲۰ تنظیم کنید.
3. تراز سلول (Alignment) را روی وسط قرار داده و بوسیله Border یک کادر دور سلول بکشید.
4. دکمه OK را برای پنجره قالب‌بندی کلیک کنید.
5. دکمه Stop Recording را از قسمت Macro انتخاب کنید.

به این ترتیب یک ماکرو برای تغییر رنگ زمینه سلول و تغییرات گفته شده، ساخته خواهد شد.

برای اجرای این ماکرو، کافی است با انتخاب دستور View Macro‌ از قسمت Macros، پنجره ماکروهای ذخیره شده را ظاهر کرده و ماکرو مورد نظرتان را انتخاب کنید. سپس با فشردن دکمه RUN، ماکرو شما اجرا خواهد شد. به یاد داشته باشید که ماکرو با نام Macro1 ثبت شده است.

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

نکته: اگر از قسمت Macro گزینه Use Relative Reference را انتخاب کرده باشید، موقعیت نسبی سلول‌ها در کد ماکرو قرار می‌گیرد.

عملیات صورت گرفته در این ماکرو در برنامه و دستورات زیر قابل مشاهده است.

1Sub Macro1()
2'
3' Macro1 Macro
4'
5
6'
7    With Selection
8        .HorizontalAlignment = xlCenter
9        .VerticalAlignment = xlBottom
10        .WrapText = False
11        .Orientation = 0
12        .AddIndent = False
13        .IndentLevel = 0
14        .ShrinkToFit = False
15        .ReadingOrder = xlContext
16        .MergeCells = False
17    End With
18    
19    With Selection.Font
20        .Name = "Calibri"
21        .FontStyle = "Regular"
22        .Size = 20
23        .Strikethrough = False
24        .Superscript = False
25        .Subscript = False
26        .OutlineFont = False
27        .Shadow = False
28        .Underline = xlUnderlineStyleNone
29        .ThemeColor = xlThemeColorLight1
30        .TintAndShade = 0
31        .ThemeFont = xlThemeFontMinor
32    End With
33    
34     With Selection.Interior
35        .Pattern = xlSolid
36        .PatternColorIndex = xlAutomatic
37        .Color = 65535
38        .TintAndShade = 0
39        .PatternTintAndShade = 0
40    End With
41    
42End Sub

همانطور که مشاهده می‌کنید، هر بخش از قالب‌بندی در یک قسمت از کد، انجام شده است. مشخص است بسیاری از دستورات مربوط به کد بالا، اضافه هستند. برای مثال از آنجایی که هیچ عمل ادغام سلولی (Merge) صورت نگرفته، دستور MergeCells = False هیچ عملی انجام نخواهد داد.

ایجاد دکمه فرمان برای اجرای ماکرو

پس از اینکه ماکرو را ذخیره کردید، می‌توانید برای اجرای آن یک دکمه در بخش «نوار ابزار دسترسی سریع» (Quick Access Toolbars) اختصاص دهید. تصویر ۴ به معرفی روند انجام این کار اختصاص دارد. البته واضح است که ابتدا باید فهرست File گزینه Options را انتخاب کرده باشید.

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

کدنویسی و ایجاد یک ماکرو

موارد بسیاری وجود دارد که شما نمی‌توانید با Macro Recorder دستورات مورد نیاز خود را به VBA تبدیل کنید. به عنوان مثال، نمی‌توانید با Macro Recorder طیف وسیعی از داده‌ها را مرور کرده و برای بعضی از آن‌ها تغییر خاصی ایجاد کنید. علاوه بر این، Macro Recorder از کدهای اضافی برای روشن‌تر شدن عملیات استفاده می‌کند که در صورت نوشتن کدهای VBA توسط شما، بسیاری از آن‌ها غیر ضروری محسوب می‌شوند.

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

کلیک کنید

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

ایجاد دکمه فرمان

برای قرار دادن یک دکمه فرمان در صفحه کاری یا کاربرگ اکسل، مراحل زیر را انجام دهید.

1. در برگه Developer، روی Insert کلیک کنید.
2. در گروه کنترل‌های ActiveX، روی دکمه Command کلیک کنید.
3. یک «دکمه فرمان» (Command Button) روی صفحه کاربرگ خود بکشید. این دکمه به طور خودکار با نام CommandButton1 ایجاد خواهد شد.

اختصاص یک ماکرو به دکمه دستوری

بخش بعدی در آموزش ماکرونویسی در اکسل اختصاص دکمه به یک ماکرو است. برای اختصاص یک ماکرو (یک یا چند خط کد) به دکمه فرمان (Command Button)، مراحل زیر را انجام دهید.

1. بر روی CommandButton1 راست کلیک کنید، البته از قبل مطمئن شوید که دکمه Design Mode در بخش Controls فعال است.
2. از فهرست کلیک راست روی View Code کلیک کنید. درست به مانند تصویر ۷، با این کار پنجره «ویرایشگر کد ویژوال بیسیک» (Visual Basic Editor) ظاهر می شود.
3. نشانگر خود را بین بخش‌های Private Sub CommandButton1_Click و End Sub قرار دهید.
4. کد نشان داده شده در تصویر 8 را اضافه کنید.
5. پنجره ویرایشگر ویژوال بیسیک را ببندید.
6. «حالت طراحی» (Design Mode) را به حالت غیرفعال درآورید. بر روی دکمه فرمان موجود در صفحه کلیک کنید.

توجه: کادر سمت چپ (Project- VBAProject) با دو گزینه به نام‌های Sheet1 (Sheet1) و ThisWorkbook Project Explorer در بخش (VBAProject(Book1 همراه است. اگر Project Explorer (کادر سمت چپ) قابل مشاهده نیست، روی برگه View کلیک کرده و گزینه Project Explorer را فعال کنید. اگر پنجره Code (بخش سمت راست) برای Sheet1 قابل مشاهده نیست، روی Sheet1 (Sheet1) کلیک کنید. در حال حاضر می توانید عبارت Option Explicit را نادیده بگیرید.

کدی که در بخش قبلی معرفی شد، برای نوشتن عبارت «Hello» در سلول A1 است. به این ترتیب هر بار روی دکمه CommandButton1 کلیک کنید این عبارت در سلول A1 نوشته خواهد شد.

حال دستورات مربوط به Macro1 را به دکمه CommandButton1 اضافه می‌کنیم. به این منظور باید ابتدا ویرایشگر ویژوال بیسیک را نمایش دهیم.

ویرایشگر ویژوال بیسیک

در بخش بعدی آموزش ماکرونویسی در اکسل از کدهای ویژوال بیسیک استفاده می‌کنیم. برای باز کردن پنجره ویرایشگر ویژوال بیسیک، از برگه Developer، روی دکمه Visual Basic کلیک کنید. پنجره‌ای به مانند تصویر زیر ظاهر خواهد شد.

کدهایی که در ادامه دیده می‌شود را در بخش CommandButton1_Click وارد کنید. به این ترتیب با هر با کلیک کردن این دکمه، عبارت Hello نوشته شده و قالب‌بندی طبق مشخصاتی که در Macro1 تنظیم شده روی این سلول اعمال می‌شود.

فیلم مجموعه آموزش اکسل – مقدماتی تا پیشرفته در فرادرس

کلیک کنید

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

1Private Sub CommandButton1_Click()
2
3
4 With Selection
5        .HorizontalAlignment = xlCenter
6        .VerticalAlignment = xlBottom
7        .WrapText = False
8        .Orientation = 0
9        .AddIndent = False
10        .IndentLevel = 0
11        .ShrinkToFit = False
12        .ReadingOrder = xlContext
13        .MergeCells = False
14        .Value = "Hello"
15    End With
16    
17    With Selection.Font
18        .Name = "Calibri"
19        .FontStyle = "Regular"
20        .Size = 20
21        .Strikethrough = False
22        .Superscript = False
23        .Subscript = False
24        .OutlineFont = False
25        .Shadow = False
26        .Underline = xlUnderlineStyleNone
27        .ThemeColor = xlThemeColorLight1
28        .TintAndShade = 0
29        .ThemeFont = xlThemeFontMinor
30    End With
31    
32     With Selection.Interior
33        .Pattern = xlSolid
34        .PatternColorIndex = xlAutomatic
35        .Color = 65535
36        .TintAndShade = 0
37        .PatternTintAndShade = 0
38    End With
39    
40End Sub
41

در زبان VBA، امکان استفاده از حلقه (Loop)، توابع شرط (IF) و غیره وجود دارد. به همین جهت VBA یک زبان برنامه‌نویسی کامل است که برای کار در محیط نرم‌افزارهای آفیس، بهینه و سفارشی شده است.

ذخیره سازی پرونده دارای کد ویژوال بیسیک

نکته مهم برای آموزش ماکرونویسی در اکسل آن است که اگر پرونده کاری (WorkBook) شامل کدهای ویژوال بیسیک است و ماکرو در آن ثبت کرده‌اید، باید هنگام ذخیره سازی قالب فایل را از XLSX به قالب XLSM تغییر دهید. در این حالت، کدهای برنامه نویسی نیز در داخل کارپوشه ذخیره می‌شوند. در غیر اینصورت با پیغام خطایی مانند تصویر ۱۰ مواجه خواهید شد.

در صورتی که بدون کدها می‌خواهید کارپوشه را ذخیره کنید، دکمه YES را انتخاب کنید. ولی اگر می‌خواهد کدهای برنامه‌نویسی را هم در کنار اطلاعات کارپوشه ذخیره کنید، دکمه NO را کلیک کرده و سپس از لیست نوع فایل (File Type) گزینه XLSM یا Excel Enabled-Macro Workbook را فعال نموده و برای فایل نام و محل ذخیره سازی را مشخص کنید.

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

خلاصه و جمع‌بندی

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

در این نوشتار از مجله فرادرس با استفاده از دکمه دستوری (Command Button) که در بخش ActiveX وجود دارد، ماکرو یا کدهای VBA را اجرا کرده و تغییراتی در کاربرگ اطلاعاتی بوجود آوردیم. همچنین ویرایش کدها و دستورات لازم برای ذخیره کردن کارپوشه با ماکرو را نیز یادآوری کردیم.