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

۵/۵ - (۱ امتیاز)

فهرست

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

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

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

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

که در مقالات اول و دوم در مورد روش اول توضیح دادیم در این مقاله قصد داریم در مورد محیط Visual Basic Editor توضیحات بیشتری رو ارائه کنیم.

نوشتن و ویرایش ماکرو در محیط Visual Basic Editor

محیط Visual Basic Editor محیطی هست که اکسل در اون تمام ماکرو هایی که چه به صورت ضبط شده ایجاد شدن و یا به صورت دستی نوشته شدن رو نگهداری می کنه.

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

برای وارد کردن کد ماکرو مورد نظر به شکل زیر عمل می کنیم:

یک فایل اکسل باز می کنیم و با زدن دکمه های Alt + F11 وارد محیط Visual Basic Editor می شیم؛
مطابق شکل ۱ در سمت چپ صفحه و در قسمت Project Explorer، بر روی فایل اکسل مورد نظر کلیک راست می کنیم و از قسمت Insert گزینه Module رو انتخاب می کنیم؛

شکل ۱- ایجاد Module جدید در محیط Visual Basic Editor

حالا در پنجره ای که در سمت راست باز شده مطابق شکل ۲، کد مورد نظرمون رو وارد می کنیم (البته اگر با زبان VBA آشنایی دارین) و یا کدی که کپی کردیم رو Paste می کنیم.

شکل ۲- وارد کردن کد VBA در ماژول ایجاد شده

نکته کاربردی:
برای افزایش سرعت اجرای ماکرو می تونیم کد های زیر رو به ماکرو اضافه کنیم. کد های زیر رو در ابتدای کد، بعد از تمام خطوط کد که با Dim شروع می شن (این کدها برای تعریف متغیر هست که تعریف متغیر هم بحث مفصل و مهمی هست که باید حتما یاد گرفته بشه) وارد می کنیم:

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

۱ ۲	Application.ScreenUpdating = False Application.Calculation = xlCalculationManual

و کد های زیر رو قبل از عبارت End Sub اضافه می کنیم:

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

۱ ۲	Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic

نمونه ای از بکار گیری این کد ها در شکل زیر نمایش داده شده:

شکل ۳- اضافه کردن کد برای افزایش سرعت اجرای ماکرو

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

حالا برای اینکه ماکرو را اجرا کنیم می تونیم دکمه F5 رو بزنیم یا از علامت مثلث سبز رنگ که در نوار بالایی ماژول قرار گرفته مطابق شکل ۲ استفاده کنیم.
و در نهایت فایل اکسل حاوی ماکرو رو همان طور که قبلا هم گفتیم به صورت Excel macro-enabled workbook ذخیره می کنیم اگر این گزینه رو زمان ذخیره کردن انتخاب نکنیم، پیغام زیر نمایش داده می شه که در این حالت گزینه No رو می زنیم که پنجره Save as باز خواهد شد و در این پنجره در قسمت Save as type گزینه Excel macro-enabled workbook رو انتخاب می کنیم و فایل رو ذخیره می کنیم.

شکل ۴- پیغام نمایش داده در صورت ذخیره فایل حاوی ماکرو به صورت معمولی

ویرایش ماکرو و اشکال زدایی (debug) کد های VBA

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

بعد از اینکه فایل اکسل حاوی ماکرو رو باز کردیم با زدن دکمه های Alt + F11 وارد محیط VBE می شیم.
در سمت چپ محیط VBE و از قسمت Project Explorer روی ماژول مورد نظرمون دوبار کلیک می کنیم یا اینکه روی ماژول کلیک راست می کنیم و گزینه View Code رو انتخاب می کنیم و با این کار پنجره حاوی کد های VBA نمایش داده می شه
حالا برای اینکه ماکرو رو تست کنیم و اشکال های اون رو در بیاریم کلید F8 رو می زنیم. با اینکار فرآیند اشکال زدایی (debug) شروع میشه به این شکل که تاثیری که هر خط کد نوشته شده روی شیت اعمال می کنه برامون نمایش داده میشه و هر خطی از کد که در حال اجرا باشه مطابق شکل زیر با رنگ زرد مشخص میشه

شکل ۵- نحوه debug کردن کد ها و ویرایش ماکرو در محیط VBE

برای اینکه از حالت debug خارج بشیم روی دکمه Reset که در شکل ۵ نمایش داده شده کلیک می کنیم. به همین صورت میتونیم کدهای ایجاد شده توسط اکسل رو ویرایش کنیم. ویرایش ماکرو کار مهمی هست که حتما باید انجام بشه.

کپی کردن یک ماکرو در فایل اکسل دیگر

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

راه اول: کپی کردن ماژول حاوی ماکرو

برای اینکار قدم های زیر رو طی می کنیم:

هر دو فایل اکسل (مبدا و مقصد) رو باز می کنیم؛
وارد محیط VBE می شیم؛
در قسمت Project Explorer ماژول حاوی ماکرو مورد نظر رو که در زیر مجموعه فایل اکسل مبدا قرار گرفته انتخاب می کنیم و با درگ کردن اون رو به زیر مجموعه فایل اکسل مقصد منتقل می کنیم.

در شکل زیر Module1 از فایل اکسل با نام book1 در فایل اکسل دیگر با نام book2 کپی شده:

شکل ۶- کپی کردن ماژول حاوی ماکرو از طریق درگ کردن

راه دوم:کپی کردن کد های ماکرو

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

شکل ۷- کپی کردن کد های ماکرو از یک فایل در فایل دیگر

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

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

روش اول:

از تب Developer گزینه Macros رو انتخاب می کنیم و یا کلید های میانبر Alt + F8 رو می زنیم؛
در پنجره Macro از لیست سمت چپ پنجره، ماکرو مورد نظرمون رو انتخاب می کنیم و دکمه Delete رو مطابق شکل زیر می زنیم.

شکل ۸- حذف ماکرو با استفاده از پنجره Macro

نکته:
برای اینکه تمام ماکرو های موجود درهمه ی فایل های باز رو ببینیم، باید در قسمت Macros in گزینه All Open Workbooks رو انتخاب کنیم.

روش دوم:

در این روش از محیط VBE برای حذف ماکرو استفاده می کنیم. یکی از مزیت های این روش این هست که می تونیم با حذف یک ماژول، تمام ماکروهای موجود در اون ماژول رو هم حذف کنیم. برای انجام این کار مراحل زیر رو طی می کنیم:

در محیط VBE و در قسمت Project Explorer روی ماژول مورد نظر کلیک راست می کنیم و از لیست نمایش داده شده گزینه Remove رو انتخاب می کنیم؛
پس از انتخاب گزینه Remove پیغامی نمایش داده میشه که می پرسه آیا می خواهیم ماژول مورد نظر رو به جای دیگه ای منتقل کنیم یا نه؟ که در این حالت گزینه No رو انتخاب می کنیم.

در صورتی که بخواهیم فقط یکی از ماکروهای موجود در یک ماژول رو حذف کنیم باید ماژول رو در محیط VBE باز کنیم و سطر های مربوط به ماکرو مورد نظر رو از ماژول پاک کنیم یا اینکه می تونیم از پنجره Macros که در قسمت قبل توضیح دادیم برای حذف ماکرو مورد نظرمون استفاده کنیم.

نحوه انتقال ماکرو در اکسل

اگر بخواهیم کد های VBA رو با شخص دیگه یا در یک سیستم دیگه به اشتراک بذاریم، سریعترین راه انتقال (Export) ماژول حاوی کد به صورت فایل با پسوند .bas هست. برای انتقال ماکرو مراحل زیر رو طی می کنیم:

انتقال ماکرو به فایل دیگر

فایل اکسلی که حاوی ماکرو هست رو باز می کنیم؛
کلید های Alt + F11 رو می زنیم و وارد محیط VBE می شیم؛
در قسمت Project Explorer روی ماژول مورد نظر کلیک راست می کنیم و گزینه Export File رو مطابق شکل زیر انتخاب می کنیم؛

شکل ۹- انتقال ماکرو از یک فایل (Export)

بعد از انتخاب گزینه Export File در پنجره نمایش داده شده مطابق شکل زیر آدرس فایلی که می خواهیم ماکرو به آن جا منتقل شود را مشخص می کنیم و کلید Save رو می زنیم. دقت داشته باشید که پسوند ذخیره فایل در این حالت همان طور که در شکل زیر هم مشخص هست (bas) هست.

شکل ۱۰- پنجره Export File

انتقال ماکرو از فایل دیگر (Import)

برای اینکه یک فایل با فرمت (bas) به فایل اکسل منتقل کنیم مراحل زیر رو طی می کنیم:

فایل اکسلی که می خواهیم ماکرو را به آن منتقل کنیم باز می کنیم؛
وارد محیط VBE می شیم؛
در قسمت Project Explorer مطابق شکل ۱۱ روی اسم پروژه کلیک راست می کنیم و گزینه Import File رو انتخاب می کنیم؛
فایل با پسوند bas رو انتخاب می کنیم و روی دکمه Open کلیک می کنیم؛

شکل ۱۱- انتقال ماکرو به یک فایل (Import)

مثال هایی از ماکرو

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

Unhide کردن تمام شیت های موجود در یک فایل

همان طور که در کد زیر مشخص هست از عبارت ActiveWorkbook برای مشخص کردن فایلی که فعال هست، از حلقه For برای بررسی تمام شیت های موجود در فایل و برای Unhide کردن هر شیت هم از امکان Visible استفاده شده.

Sub Unhide_All_Sheets ()
    Dim wks As Worksheet
    For Each wks In ActiveWorkbook.Worksheets
        wks. Visible = xlSheetVisible
    Next wks
End Sub

Sub Unhide_All_Sheets ()

Dim wks As Worksheet

For Each wks In ActiveWorkbook.Worksheets

wks. Visible = xlSheetVisible

Next wks

End Sub

Hide کردن شیت فعال

برای فراخوانی شیت فعال در VBA از عبارت ActiveSheet استفاده می کنیم. کد زیر شیت فعال رو از حالت Visible به حالت پنهان (Hidden) تغییر میده.

Sub Hide_Active_Sheet ()
    ActiveSheet.Visible = xlSheetHidden
End Sub

Sub Hide_Active_Sheet ()

ActiveSheet.Visible = xlSheetHidden

End Sub

Unmerge کردن تمام سلول های ادغام شده در یک محدوده انتخابی

اگر بخواهیم عملیات خاصی رو در یک محدوده به جای کل شیت انجام بدیم، از عبارت Selection استفاده می کنیم. به عنوان مثال ، کد زیر همه سلولهای ادغام شده در یک محدوده انتخابی رو، با یک حرکت به حالت اولیه بر می گردونه.

Sub Unmerge_Cells ()
    Selection.Cells. UnMerge
End Sub

Sub Unmerge_Cells ()

Selection.Cells. UnMerge

End Sub

نمایش پیام به کاربر

برای نشان دادن برخی پیام ها به کاربران، از عملکرد MsgBox استفاده می کنیم. در کد زیر نمونه ای ساده از نمایش پیام ارائه شده:

Sub Show_Message ()
    MsgBox ("Hello World!")
End Sub

Sub Show_Message ()

MsgBox ("Hello World!")

End Sub

معمولا کاربرد ارسال پیام در ماکرو برای این هست که قبل از انجام یک عملیات خاص (مثلا Unmerge کردن یک محدوده) از کاربر تایید انجام کار گرفته بشه. به طور مثال در مورد ماکرو مربوط به Unmerge کردن می تونیم پیامی مطابق ماکرو زیر اضافه کنیم و اگر کاربر پاسخ Yes داد کار ادامه پیدا بکنه.

Sub Unmerge_Selected_Cells ()
    Dim Answer as String
    Answer = MsgBox ("Are you sure you want to unmerge these cells?", vbQuestion + vbYesNo, "Unmerge Cells")
    If Answer = vbYes Then
        Selection.Cells. UnMerge
    End If
End Sub

Sub Unmerge_Selected_Cells ()

Dim Answer as String

Answer = MsgBox ("Are you sure you want to unmerge these cells?", vbQuestion + vbYesNo, "Unmerge Cells")

If Answer = vbYes Then

Selection.Cells. UnMerge

End If

End Sub

برای اینکه کد بالا رو تست کنیم اول باید کد رو در محیط VBE (همان طور که در این مقاله آموزش دادیم) کپی کنیم و بعد در یکی از شیت های فایل اکسل، حداقل یک محدوده که حاوی سلول های Merge شده هست رو انتخاب کنیم و بعد ماکرو رو Run می کنیم. بلافاصله بعد از اجرای ماکرو پیغام زیر نمایش داد.

شکل ۱۲- نمونه پیغام تعریف شده در ماکرو

همان طور که گفتیم این کد ها تنها مثال های ساده ای از کد های VBA هستن که صرفا برای آشنایی ارائه شدن و می تونید با کپی و ویرایش ماکرو ها در محیط VBE و اجرای آن ها عملکردشون رو چک کنید.

در این مقاله هم از سری مقالات ماکرو سعی کردیم به ابعاد دیگه ای از این حوزه بپردازیم و بیشتر محیط Visual Basic Editor رو معرفی کنیم، نحوه ویرایش ماکرو رو بگیم و همچنین نمونه ای از کد های VBA که برای خودکار سازی یک سری از کارها در اکسل بود رو ارائه کردیم.