همه چیز در مورد ماکرو در اکسل-قسمت اول

ماکرو دریچه ای به امکانات بیشتر

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

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

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

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

معمولا از ماکرو برای انجام کارهای تکراری و روتین روزانه استفاده میشه و هر چقدر در این حوزه حرفه ای تر بشیم می تونیم کارهای بیشتری رو با استفاده از ماکرو انجام بدیم و استفاده از کیبورد رو در کارهای روز مره به حداقل برسونیم و این دقیقا کاری هست که کاربرای حرفه ای این حوزه انجام میدن.

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

چرا از ماکرو در اکسل استفاده کنیم؟

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

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

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

نحوه ایجاد ماکرو در اکسل

برای ایجاد ماکرو در اکسل دو راه وجود داره:

1. استفاده از امکان Macro Recorder
2. استفاده از محیط Visual Basic Editor

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

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

برای ضبط ماکرو مراحل زیر رو طی می کنیم:

1. از تب Developer گزینه Record Macro رو انتخاب می کنیم.

شکل ۳- نمایش گزینه Record Macro در تب Developer

همچنین می تونیم برای ضبط ماکرو از نوار وضعیت (Status Bar) هم استفاده کنیم که مطابق شکل زیر روی آیکن نمایش داده شده کلیک می کنیم تا ضبط ماکرو شروع بشه.

شکل ۴- نمایش گزینه Record Macro در نوار وضعیت (Status Bar)

راه سوم برای ضبط ماکرو استفاده از کلید های ترکیبی Alt + L + R هست ولی نه به صورت هم زمان بلکه با فاصله و پشت سر هم.

2. در مرحله قبل بعد از فعال کردن گزینه Record Macro، پنجره Record Macro مطابق شکل زیر نمایش داده میشه که در اون مشخصات ماکرویی که می خواهیم ضبط کنیم رو وارد می کنیم.

شکل ۵- پنجره Record Macro

در ادامه بخش های مشخص شده در شکل ۵ رو توضیح خواهیم داد:

• در قسمت اول همان طور که مشخص هست نام انتخابی برای ماکرو رو وارد می کنیم که پیشنهاد می کنیم که یک اسم مختصر و مفید باشی و تا حدی گویای عملکرد ماکرو باشه. در نام انتخابی می تونیم از حروف، اعداد و خط تیره یا آندرلاین هم استفاده کنیم اما نکته ای که وجود داره این هست که ابتدای اسم حتما باید با یک حرف شروع بشه و استفاده از فاصله در اسم انتخابی هم مجاز نیست.
• در قسمت دوم برای اجرای ماکرو می تونیم یک کلید میانبر تعریف کنیم و تکمیل این قسمت اختیاری هست. در این قسمت می تونیم از حروف بزرگ و کوچک استفاده کنیم. پیشنهاد می کنیم حتما در این قسمت از الگوی Ctrl + Shift + letter برای وارد کردن حروف استفاده کنید چون اگر مثلا کلید میانبر رو به صورت Ctrl + S تعریف کنیم در این حالت دیگه اگر فایل حاوی ماکرو، باز باشه نمی تونیم با استفاده از Ctrl + S فایل رو ذخیره کنیم.
• در فیلد سوم مشخص می کنیم که ماکرو در کجا ذخیره بشه که سه حالت وجود داه:

Personal Macro Workbook: ماکرو را در یک فایل اکسل به نام Personal.xlsb ذخیره می کنه. همه ماکروهای ذخیره شده در این فایل، هر زمان که از Excel استفاده می کنیم در دسترس هستن.

This Workbook: این گزینه به صورت پیش فرض تعریف شده. در این حالت ماکرو در فایل فعلی ذخیره می شه و وقتی که فایل رو باز می کنیم یا اون رو با کاربرای دیگه به اشتراک میذاریم در دسترس خواهد بود.

New Workbook: با انتخاب این گزینه یک فایل جدید ایجاد می شه و ماکرو در فایل جدید ضبط میشه.

• در فیلد آخر در شکل ۵ هم می تونیم شرحی از کارکرد ماکرو رو بنویسیم که اختیاری هست اما توصیه می کنیم این قسمت رو تکمیل کنید تا اگر تعداد ماکرو ها زیاد بودن براحتی بتونین با مشاهده توضیحات، خیلی سریع ماکرو مورد نظرتون رو پیدا کنید.

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

3. در این مرحله کارهایی که می خواهیم در قالب ماکرو ضبط بشن رو انجام می دیم.
4. بعد از پایان کار، دکمه Stop Recording از تب Developer مطابق شکل زیر انتخاب می کنیم.

شکل ۶- انتخاب دکمه Stop Recording از منوی Developer

همچنین می تونیم از نوار وضعیت هم مطابق شکل زیر Stop Recording رو انتخاب کنیم.

شکل ۷- انتخاب دکمه Stop Recording از نوار وضعیت

در ادامه مثالی از ضبط ماکرو ارائه می کنیم.

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

1. ابتدا سلول یا سلول هایی که می خواهیم در آن ها فرمت تعریف کنیم رو انتخاب می کنیم؛
2. از تب Developer یا نوار وضعیت، دکمه Record Macro رو می زنیم؛
3. در پنجره Record Macro فیلد ها رو به شکل زیر تکمیل می کنیم و دکمه OK رو می زنیم؛

شکل ۸- تکمیل فیلدهای پنجره Record Macro برای تعریف فرمت در سلول ها

4. حالا در سلول های انتخابی برای مثال فرمت متن را به صورت بولد تعریف می کنیم و رنگ زمینه آبی برای سلول انتخاب می کنیم و چیدمان متن در سلول را به صورت وسط چین تعریف می کنیم؛

5. بعد از پایان کار دکمه Stop Recording رو از تب Developer یا نوار وضعیت می زنیم. حالا یک ماکرو ضبط شده داریم و در هر محدوده ای که بخواهیم فرمت مورد نظر اعمال بشه فقط کافیه محدوده رو انتخاب کنیم و کلید های ترکیبی Ctrl + Shift + F رو بزنیم.

مراحل بالا در ویدئو زیر نمایش داده شده:

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

تمامی تنظیمات مربوط به ماکرو ها در پنجره با نام Macro قابل انجام هست. برای دسترسی به این پنجره کافیه از تب Developer و از زیر مجموعه Code گزینه Macros رو مطابق شکل زیر انتخاب کنیم:

شکل ۹- نحوه دسترسی به پنجره Macro از تب Developer

با انتخاب گزینه Macros پنجره ای به شکل زیر نمایش داده میشه که در اون لیستی از ماکرو هایی که در تمام فایل های باز موجود هستند قابل مشاهده هست.

شکل ۱۰- پنجره Macro

همان طور که در شکل ۱۰ مشخص هست تعدادی کلید در سمت راست این پنجره قرار گرفته که هر کدوم عملکرد مشخصی دارند.

Run: با زدن این دکمه ماکروی انتخاب شده (که در لیست نمایش داده شده در سمت چپ به حالت انتخاب درآمده) اجرا میشه.

: Step into این امکان رو می ده تا ماکرو را در محیط Visual Basic Editor اشکال زدایی (دیباگینگ) و آزمایش کنیم.

Edit: با زدن این دکمه ماکرو انتخابی در محیط Visual Basic Editor نمایش داده میشه و می تونیم کد های موجود در ماکرو رو ویرایش کنیم.

Delete: از این کلید برای حذف ماکرو استفاده می کنیم.

Options: از این قسمت می تونیم مشخصات ماکرو که در ابتدای ضبط ماکرو وارد کردیم مثل کلید میانبر و توضیحات نحوه عملکرد ماکرو را تغییر بدیم.

در مورد تنظیمات امنیتی هم یک مقاله با نام تنظیمات امنیتی ماکرو در اکسل داریم که حتما بهش نگاه کن.

نمایش Macro در محیط Visual Basic Editor

همان طور که در ابتدای مقاله گفتیم Macro مجموعه ای از دستورها و فرمان هایی هست که با زبان VBA نوشته شدن. وقتی ماکرو ضبط می کنیم تمامی کلیک هایی که انجام میدیم رو اکسل به زبان VBA ترجمه میکنه. حالا اگر بخواهیم ماکرو که به زبان VBA ترجمه شده رو ببنیم باید وارد محیط Visual Basic Editor بشیم. همان طور که از اسمش مشخص هست در این محیط می تونیم کدهای نوشته شده رو ویرایش کنیم و تغییرات مورد نظرمون رو اعمال کنیم. برای وارد شدن به این محیط هم می تونیم از کلید های میانبر Alt + F11 استفاده کنیم و هم می تونیم مطابق شکل زیر از تب Developer گزینه Visual Basic رو انتخاب کنیم.

شکل ۱۱- دسترسی به محیط Visual Basic از تب Developer

اگر برای اولین بار هست که محیط Visual Basic Editor رو می بینید، لطفاً دلسرد نشید و صبر داشته باشید تا کم کم با این محیط آشنا بشین. همان طور که در شکل زیر مشخص هست این پنجره از بخش های مختلفی تشکیل شده که در اینجا دو بخش اصلی رو توضیح میدیم.

شکل ۱۲- پنجره Visual Basic Editor

Project Explorer: در این قسمت لیستی از تمامی فایل های باز و شیت های موجود در آن ها نمایش داده شده. همچنین در این بخش لیستی از ماژول ها و یوزر فرم ها هم وجود داره.

Code Window: با انتخاب هر آیتمی در قسمت Project Explorer، در این قسمت می تونیم کدهای VBA تعریف شده رو مشاهده و ویرایش کنیم و کدهای جدید بنویسیم.

حالا وقتی یک ماکرو ضبط می کنیم در محیط Visual Basic Editor اتفاقات زیر می افته:

1. یک ماژول جدید به نام (Moduel1) ایجاد می شه؛
2. کد VBA مربوط به Macro ضبط شده هم در پنجره Code Window نوشته میشه.

در صورتی که بخواهیم کد VBA مربوط به یک ماژول مشخص رو مشاهده کنیم، کافیه روی ماژول مربوطه (در اینجا Moduel1) دو بار کلیک کنیم تا کد نوشته شده به زبان VBA در Code Window مطابق شکل ۱۲ نمایش داده بشه. یک کد ماکرو از قسمت های زیر تشکیل شده:

نام Macro

در زبان VBA هر ماکرویی با عبارت Sub شروع میشه که در ادامه این عبارت اسم Macro نمایش داده میشه و با عبارت End Sub هم به پایان می رسه که Sub مخفف کلمه Subroutine هست. همان طور که در شکل زیر مشخث هست اسم Macro Header_Formatting هست که در ادامه عبارت Sub قرار گرفته.

شکل ۱۳- نحوه نمایش نام Macro در VBA

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

توضیحات

خطوطی که با علامتر” ‘ ” شروع شدن و به رنگ سبز هستن نشان دهنده توضیحات (Comment) هستند که فقط برای ارائه اطلاعات تکمیلی هست و قابلیت اجرا ندارند. توضیحات رو می تونید حذف کنید بدون اینکه به عملکرد ماکرو صدمه ای وارد بشه.

وقتی یک ماکرو ضبط می کنیم یک الی سه خط به صورت کامنت اضافه خواهد شد که این سه خط شامل: نام ماکرو، توضیحات و کلیدهای میانبر اجرای Macro هستند که در شکل ۱۴ نمایش داده شده.

کد های اجرایی

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

شکل ۱۴- نمایش توضیحات و کد های اجرایی در یک Macro ضبط شده

در این مقاله با مفهوم ماکرو و تفاوت اون با VBA آشنا شدیم و نحوه ضبط Macro رو با ارائه مثال توضیح دادیم. در بخش های بعدی این مقاله نکات بیشتری در خصوص نحوه کار با ماکروها در محیط Visual Basic Editor و ضبط Macro ارائه خواهیم کرد.