همه چیز در مورد ماکرو در اکسل- بخش دوم

در آموزش قبلی با ماکرو و نحوه ضبط ماکرو آشنا شدیم. حالا در ادامه با نحوه اجرا، ذخیره سازی و … آشنا خواهیم شد.

اجرای ماکرو ضبط شده

برای اجرای یک ماکرو روش های مختلفی وجود داره در این بخش چهار روش برای انجام این کار رو توضیح می دیم:

1. اگر در زمان تعریف مشخصات ماکرو (قبل از ضبط ماکرو) کلید میانبر برای اجرای ماکرو تعریف کرده باشیم، می تونیم با زدن کلید های میانبر ماکرو رو اجرا کنیم.
2. میتونیم با زدن کلید های ترکیبی Alt + F8 یا انتخاب گزینه Macros از تب Developer، پنجره Macro رو مطابق شکل زیر باز کنیم. حالا کافیه ماکرو مورد نظر رو از لیست سمت چپ پنجره انتخاب کنیم و دکمه Run رو بزنیم.

شکل ۱- اجرای ماکرو با استفاده از دکمه Run در پنجره Macro

ذخیره سازی ماکرو در اکسل

فارغ از اینکه ماکرو رو ضبط کرده باشیم یا کد VBA ماکرو رو نوشته باشیم، برای ذخیره سازی فایل اکسل حاوی ماکرو به صورت زیر عمل می کنیم:

1. دکمه Save یا کلید های میانبر Ctrl + S رو می زنیم؛
2. مطابق شکل زیر در پنجره Save As و در قسمت Save as type گزینهExcel Macro-Enabled Workbook (*.xlsm) رو انتخاب می کنیم و دکمه Save رو می زنیم.

شکل ۲- نحوه ذخیره سازی فایل حاوی ماکرو در پنجره Save As

وقتی که یک فایل حاوی ماکرو رو ذخیره می کنیم آیکن فایل به شکل زیر نمایش داده میشه:

شکل ۳- نحوه نمایش فایل اکسل حاوی ماکرو

فعال کردن ماکروها در اکسل

به دلیل مسائل امنیتی، تمام ماکرو ها در اکسل به طور پیش فرض غیرفعال هستند. وقتی که یک فایل اکسل حاوی ماکرو رو باز می کنیم در قسمت بالایی فایل مطابق شکل زیر یک نوار زرد رنگ نمایش داده میشه که بهمون میگه که ماکروهای موجود در فایل غیرفعال هستند. برای فعال سازی ماکرو کافیه دکمه Enable Content رو بزنیم. البته روش های دیگری هم برای اینکار وجود داره اما این روش راحت ترین روش هست که مطمعنا همه کاربران به روش های دیگه ترجیحش میدن.

شکل ۴- نحوه نمایش فایل اکسل حاوی ماکرو

تغییر تنظیمات مربوط به ماکرو

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

1. از تب File گزینه Options رو انتخاب می کنیم؛
2. از لیست نمایش داده شده در سمت چپ پنجره، گزینه Trust Center رو انتخاب می کنیم و بعد روی گزینه Trust Center Settings… کلیک می کنیم؛
3. در پنجره Trust Center روی گزینه Macro Settings که در سمت چپ قرار گرفته کلیک می کنیم و مطابق شکل زیر، گزینه دلخواه رو برای نمایش فعال/ غیرفعال کردن ماکرو ها انتخاب می کنیم.

شکل ۵- پنجره مربوط به تنظیمات (فعال / غیر فعال کردن) ماکروها

همان طور که در شکل ۵ مشخص هست گزینه چهارم، حالتی هست که در اون تمام ماکرو ها فعال می شن که اکیدا توصیه می کنیم این حالت انتخاب نشه چون از نظر امنیتی می تونه برای سیستم خطرناک باشه. (به این دلیل که هر کسی فایلی حاوی کد براتون بفرسته و شما فایل رو باز کنید، کد اجرا میشه و این کد میتونه ویروس و … باشه)

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

همان طور که در بخش اول این مقاله توضیح دادیم، ضبط کردن ماکرو کار ساده ای هست، اما برای اینکه ماکرو های مفید و کاربردی رو ضبط کنیم، باید بدونیم در پشت صحنه ضبط ماکرو چه اتفاقی داره میفته. برای همین یک لیست از مواردی که توسط ماکرو قابل ضبط هستند و همچنین لیستی از موارد غیر قابل ضبط رو در ادامه ارائه می کنیم تا با علم به این موارد از این به بعد ماکروهای بهتری رو بتونیم ضبط کنیم. لازم به ذکر هست که در هر دو لیست فقط تعدادی از موارد آورده شده نه همه موارد.

موارد قابل ضبط توسط ماکرو

1. انتخاب سلول ها با ماوس یا صفحه کلید: در این حالت فقط محدوده ای که بلافاصله قبل از انجام کاری انتخاب کردیم ضبط خواهد شد. به عنوان مثال، اگر محدوده A1:A10 رو انتخاب کرده باشیم و بعد روی سلول A11 کلیک کنیم، فقط انتخاب سلول A11 ضبط خواهد شد.
2. فرمت سلول: که می تونه شامل رنگ سلول، تنظیمات خطوط اطراف سلول، رنگ فونت و غیره باشه.
3. فرمت اعداد: مثل درصد، واحد پول و غیره
4. ویرایش فرمول ها و مقادیر: تغییرات بعد از زدن دکمه Enter ضبط خواهند شد.
5. پیمایش (Scrolling) و حرکت بین پنجره های اکسل و جابه جایی بین شیت های یک فایل و فایل های اکسل مختلف.
6. اضافه کردن، نام گذاری کردن، حرکت دادن و حذف شیت ها
7. ایجاد، باز کردن و ذخیره کردن فایل های اکسل
8. اجرای سایر ماکرو ها

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

1. سفارشی سازی (Customizations) نوار اکسل و نوار ابزار دسترسی سریع (Quick Access toolbar)
2. تمام کارهایی که در پنجره Conditional Formatting یا Find and Replace انجام می شه (فقط نتیجه این کارها ضبط می شن)
3. تعامل با برنامه های دیگر، برای مثال عملیات کپی کردن از یک فایل اکسل در یک فایل ورد قابل ضبط شدن نیست.
4. هر کاری که در محیط VBA Editor انجام بشه قابل ضبط شدن نیست. این مورد در واقع بزرگترین محدودیت ضبط ماکرو هست و به طور کلی هر کاری که در محیط برنامه نویسی انجام میشه قابلیت ضبط شدن در ماکرو نداره که شامل موارد زیر هست:
   • ایجاد توابع سفارشی
   • نمایش پنجره های سفارشی (custom dialog boxes)
   • ایجاد حلقه هایی مانند For Next ،For each ، Do while و غیره.
   • ارزیابی کردن شرط ها. در VBA می تونیم از عبارت های IF Then Else برای ارزیابی شرط استفاده کنیم و در صورتی که شرط برقرار باشه و یا برقرار نباشه، کد هایی رو جهت اجرا وارد کنیم که این موارد توسط ماکرو قابلیت ضبط نخواهد داشت.
   • کد هایی که در VBA برای اجرای یک رویداد نوشته می شن. در VBA می تونیم رویداد های زیادی رو با وارد کردن کدهای مربوط به آن ها اجرا کنیم برای مثال: باز کردن یک فایل اکسل، محاسبه دوباره یک شیت، تغییر محدوده انتخابی و غیره.
   • استفاده از آرگومان ها. هنگام نوشتن ماکرو در ویرایشگر VBA، می تونیم برای انجام یک کار خاص، آرگومان های ورودی را برای ماکرو بنویسیم. ماکرو ضبط شده نمی تونه هیچ آرگومانی داشته باشه چون مستقل هست و به هیچ ماکرو دیگری متصل نیست.
   • درک کردن منطق انجام کار ها. برای مثال اگر ماکرویی رو ضبط کنیم که سلول های خاصی رو در یک سطر کپی بکنه، در این حالت Excel فقط آدرس سلول های کپی شده رو ضبط می کنه. ولی با وارد کردن کد VBA، می توانیم منطق کپی کردن رو کدگذاری کنیم.
   • زمان هم در ضبط ماکرو ضبط نمیشه. یعنی یک سلول و کلیک کنید و ده ثانیه بعد یک کار دیگه انجام بدید این فاصله ضبط نمیشه و عملیات مورد نظر پشت سر م و بدون وقفه ضبط و بعدا اجرا خواهند شد.

نکات کاربردی برای ضبط ماکرو

در این بخش نکات مفیدی رو در مورد نحوه ضبط ماکرو ارائه می کنیم که استفاده از این نکات می تونه در زمان انجام کارها صرفه جویی ایجاد کنه و باعث بشه تا ماکرهای کاربردی تری رو ضبط کنیم.

1. استفاده از گزینه Use Relative References برای ضبط ماکرو

اکسل به طور پیش فرض از حالت ارجاع مطلق (absolute referencing) برای ضبط ماکرو استفاده می کنه. در این حالت کد های VBA ایجاد شده، فارغ از اینکه در زمان ضبط ماکرو در کدام شیت ها باشیم، دقیقا به سلول هایی که انتخاب کردیم اشاره می کنن یعنی در زمان اجرای ماکرو، روی هر سلولی کلیک کنیم، ماکرو در سلول هایی که براش تعریف شده اجرا میشه.

علاوه بر حالت قبل یک حالت دیگه هم برای ضبط ماکرو وجود داره به نام ارجاع نسبی (relative referencing). در این حالت کدهای VBA آدرس سلول ها رو به صورت مطلق و غیر قابل تغییر در نمیارن و ماکرو ضبط شده در هر سلولی که به حالت انتخاب در آمده باشه اجرا خواهد شد. برای فعال کردن این امکان مطابق شکل زیر ، از تب Developer و از زیر مجموعه Code بر روی گزینه Use Relative References کلیک می کنیم و بعد شروع به ضبط ماکرو می کنیم. برای غیر فعال کردن این گزینه هم کافیه دوباره روی Use Relative References کلیک کنیم.

شکل ۶- نحوه فعال سازی Use Relative References

برای مثال اگر تنظیمات یک جدول رو در حالت absolute referencing ضبط کنیم، و سلول های A1، A2 و A3 رو قبل از ضبط ماکرو به عنوان سر ستون انتخاب کنیم، در زمان اجرای ماکرو، هر جایی که کلیک کنیم، ماکرو سر ستون های جدول رو در سلول هایی A1 تا A3 ایجاد می کنه. کد VBA در این حالت به صورت زیر هست:

در صورتی که در حالت relative referencing ماکرو رو ضبط کنیم، هر جایی که انتخاب کنیم و ماکرو رو اجرا کنیم جدول دقیقا در محل انتخاب شده ایجاد می شه. کد های VBA در این حالت به صورت زیر هست:

 

2. استفاده از کلید های میانبر برای انتخاب محدوده ها

وقتی که یک سلول یا محدوده ای از سلول ها رو با استفاده از ماوس یا کلیدهای جهت دار انتخاب می کنیم، Excel آدرس سلول ها رو در کد VBA می نویسه. در نتیجه، هر زمان که ماکرو رو اجرا می کنیم، ماکرو ضبط شده دقیقاً روی همان سلول ها اعمال میشه. برای جلوگیری از بروز این اتفاق می تونیم از کلید های میانبر برای انتخاب سلول ها و محدوده ها استفاده کنیم.

برای مثال اگر بخواهیم یک ماکرو که فرمت مشخصی از تاریخ رو بر روی داده ها اعمال می کنه ضبط کنیم و محدوده رو با استفاده از ماوس انتخاب کنیم به صورت زیر عمل می کنیم:

1. روی دکمه Record Macro از تب Developer کلیک می کنیم؛
2. محدوده مورد نظر جدول رو که مطابق شکل زیر A2:B4 هست با ماوس انتخاب می کنیم؛

شکل ۷- ضبط ماکرو برای اعمال فرمت تاریخ در جدول

3. دکمه های Ctrl + 1 رو می زنیم؛
4. در پنجره Format Cells از قسمت Date فرمت مورد نظرمون رو برای تاریخ انتخاب می کنیم و دکمه OK رو می زنیم؛
5. حالا دکمه Stop Recording رو از تب Developer می زنیم.

در این حالت کد های VBA ایجاد شده به صورت زیر هستند:

حالا هر بار که این ماکرو ضبط شده رو اجرا کنیم، محدوده A2:B4 انتخاب میشه و اگر سطر های بیشتری به جدول اضافه کنیم، ماکرو فقط در محدوده A2:B4 اجرا خواهد شد.

حالا ماکرو رو در حالتی ضبط می کنیم که محدوده مورد نظرمون رو با استفاده از کلید های میانبر انتخاب کنیم. برای انتخاب محدوده در این حالت ابتدا مطابق شکل ۷ روی سلول A2 کلیک می کنیم و بعد کلید های ترکیبی Ctrl + Shift + End رو می زنیم. با این کار محدوده مورد نظرمون بدون استفاده از ماوس انتخاب می شه. در این حالت فقط سطر ابتدایی کد VBA با حالت قبل متفاوت هست که در شکل زیر نمایش داده شده:

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

به جای کلید های میانبر Ctrl + Shift + End می تونیم از کلید های زیر هم استفاده کنیم تا تمام سلول های حاوی داده در سمت راست و پایین سلول فعال انتخاب بشن:

Ctrl + Shift + Right: تمام سلول های حاوی داده که در سمت راست سلول فعال قرار دارند رو انتخاب می کنه.

Ctrl + Shift + Down: تمام سلول های حاوی داده که در پایین سلول فعال قرار دارند رو انتخاب می کنه.

استفاده از این کلید های میانبر در مقایسه با حالت قبل (استفاده از Ctrl + Shift + End)، باعث ایجاد دو سطر کد VBA مطابق شکل زیر میشه که عملکرد هر دو یکی هست.

3. برنامه ریزی دقیق برای ضبط ماکرو

در زمان ضبط ماکرو اکسل تقریبا تمام کارهایی که انجام میدیم رو ضبط می کنه که می تونه شامل اشتباهاتی باشه که انجام میدیم و با زدن دکمه Ctrl + z اصلاحشون می کنیم و در نهایت با سطر های زیادی از کد های اضافی مواجه می شیم. در این حالت می تونیم کدهای VBA اضافی رو در محیط VBA Editor پاک کنیم و یا ضبط ماکرو رو متوقف کنیم، ماکرو ضبط شده رو حذف کنیم و یک ماکرو جدید ضبط کنیم.

4. ذخیره سازی و تهیه نسخه پشتیبان (Back Up) قبل از اجرای ماکرو

وقتی که یک ماکرو اجرا می کنیم دیگه نمی تونیم فایل رو به حالت قبل از اجرای ماکرو برگردونیم پس بهتر هست قبل از اجرای ماکرو یک نسخه کپی از فایل تهیه کنیم تا اگر اجرای ماکرو تغییرات نا خواسته ای رو در فایل ایجاد کرد دوباره بتونیم به فایل اصلی دسترسی داشته باشیم. اگر هم نتیجه اجرای ماکرو اون چیزی که انتظارش رو داشتیم نشد، می تونیم فایل رو بدون اینکه ذخیره کنیم ببندیم.

5. ضبط کردن ماکرو در بخش های کوچک

معمولا وقتی می خواهیم یه مجموعه از کارها رو به صورت خودکار انجام بدیم، ترجیح می دیم که تمام آن ها رو در قالب یک ماکرو ضبط کنیم. اما به دو دلیل بهتره این کار رو انجام ندیم:

6. ضبط کردن یک ماکرو طولانی و بدون اشتباه کار سختی هست و برای همین وقت و انرژی زیادی رو باید صرف انجام این کار کنیم؛
7. تست و اشکال زدایی (debug) یک ماکرو طولانی هم کار سختی هست.

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

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