وبلاگ

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

تعریف ماکرو (Macro):

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

اگر شما جزء افرادی هستید که روزانه با اکسل زیاد کار میکنید و اغلب عملیات شما در اکسل کارهای تکراری هستند و یا اگر جزء افرادی هستید که تازه شروع به یادگیری برنامه نویسی VBA در اکسل کرده اید و میخواهید کارهای خودتون رو تو اکسل توسعه بدین پس این مقاله صددرصد برای شما بسیار مفید خواهد بود.

کاربرد ماکرو در اکسل:

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

مهمترین مزایای استفاده از ماکرو :

  1. ضبط و ذخیره کردن کارهای تکراری در اکسل و استفاده مجدد از آن.
  2. امکان ویرایش و توسعه دادن کدهای ذخیره شده.
  3. یک دستیار خوب برای کسانی که VBA رو تازه شروع کردند.
  4. سرعت دادن به فرایندهای تکراری

مراحل ضبط ماکرو :

1). فعال کردن تب Developer:

تب Developer که مخصوص توسعه دهندگان نرم افزار اکسل می باشد،بصورت پیشفرض فعال نمی باشد و باید آنرا فعال کرد.برای این کار مسیر زیر را برید:

File tab > Options > Customize Ribbon

مسیر بالارو که رفتین از کادر سمت راست تیک Developer رو زده و OK را بزنید تا پنجره Excel Options بسته شود.بعد از این کار یک تب جدید به نام Developer به تب های شما اضافه می شود.

فعال کردن تب Developer
شکل شماره 1 – فعال کردن تب Developer

2). ضبط ماکرو :

برای ضبط ماکرو شما باید ابتدا تب Developer را انتخاب و از گروه Code گزینه Record Macro را بزنید تا پنجره Record Macro باز شود.

ضبط کردن ماکرو (Macro)
شکل شماره 2 – ضبط کردن ماکرو (Macro)
آموزش ماکرو
Macro Name:

نام ماکرو خود را بنویسید.

  • دقیت کنید که در نوشتن نام ماکرو از کاراکترهای ویژه و کلیدی که خود ویندوز از آنها استفاده می کنید،استفاده نکنید.
  • از Space یا خط فاصله نمی توانید استفاده کنید.مثلا برای نوشتن اسم ماکرو My Macro باید بصورت My_Macro بنویسید.
Shortcut Key:

شما می توانید برای اجرای ماکرو یک کلید میانبر برای آن انتخاب کنید.

  • از کلیدهای میانبر خود اکسل و ویندوز استفاده نکنید.مثل Ctrl+F که برای سرچ استفاده میشه.
  • بهتر است از کلید Shift هم برای تعیین کلید میانبر ماکرو استفاده کنید.بدینصورت که هنگام تعریف کلیدمیانبر کلید Shift را هم نگه دارید.مثلا اگر بخواهیم کلید W را به عنوان کلید میانبر تعریف کنیم با نگه داشتن Shift و زدن W کلید نهایی بصورت Ctrl+Shift+W تولید می شود.
Store Macro in:

برای تعیین مکان ایجاد ماکرو کاربرد دارد.

  • Personal Macro Workbook:ذخیره کردن ماکروها در یک کتابخانه شخصی و پنهان که هربار اکسل را باز میکنید آن ماکروها قابل دسترسی و استفاده می باشد.
  • New Workbook:یک ورک بوک جدید ایجاد و ماکرو داخل آن ایجاد شده و لینک آن در ورک بوک جاری هم قابل دسترسی می باشد.
  • This Workbook: ماکرو ایجاد شده در ورک بوک جاری ایجاد و ذخیره می شود.
Description:

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

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

بعد از اتمام عملیات موردنظر شما باید ضبط ماکرو را متوقف کنیدروی عبارت Stop Recording کلیک کنید تاعملیات ضبط متوقف شود.مطابق شکل زیر :

توقف کردن ماکرو پس از اتمام ضبط
شکل شماره 3 – متوقف کردن ماکرو پس از اتمام ضبط

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

عملیات روی ماکرو:

به شکل های بالا اگر دقت کنید یک گزینه به نام Macros وجود دارد که با زدن آن پنجره Macro باز می شود و داخل آن لیست ماکروهای ضبط شده با نام انتخابی شما نمایش داده می شود.یا می توانید از کلید میانبر Alt+F8 استفاده کنید.

مطابق شکل زیر:

مشاهده لیست ماکرو های ضبط شده
شکل شماره 4 – مشاهده لیست ماکرو های ضبط شده

اجرا کردن ماکرو:

برای اجرای ماکروی ضبط شده کافیه نام موردنظر را انتخاب و گزینه Run را بزنید.برای مثال فرض کنید ما در Macro4 یک جدول ایجاد کردیم.عنوان ستون جدول رو تعیین کرده و رنگ فونت نوشته هارو هم تغییر دادیم.حال اگر ما در هرشیتی از همین ورک بوک این ماکرو را Run کنیم دوباره اتوماتیک همون جدول رو برای ما درست میکنه و دیگه نیازی نیست دوباره از اول جدول رو درست کنیم.همچنین شما می توانید کدهای ایجاد این جدول رو با ذخیره کردن بصورت add-in به سایر دوستانتان هم بفرستین.

 

ویرایش کدهای ضبط شده:

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

ویرایش کدهای ضبط شده توسط ماکرو
شکل شماره 5 – ویرایش کدهای ضبط شده توسط ماکرو

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

حذف ماکرو:

برای اینکار کافیه ماکرو موردنظر را انتخاب و دکمه Delete  را زده و در پنجره باز شده Yes را انتخای کنید.

ویرایش اطلاعات ماکرو:

برای تغییر دادن کلید میانبر و توضیحات ماکرو دکمه Options را زده و بعد از انجام تغییرات OK را بزنید.

نکته:از قسمت Options نمی توانید نام ماکرو را تغییر بدهید.برای تغیییر نام ماکرو شما باید مستقیم از داخل کدها آن را تغییر بدین.اگر به شکل فوق دقت کنید در ردیف “کد شروع کننده ماکرو”  نام ماکرویی که ما نوشتیم My_Macro نام دارد.حال میخواهم آنرا به نام Table_builder تغییر بدم که کد به شکل () Sub  Table_builder تغییر پیدا می کند.

Macros in:

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

  • All open Workbooks: نمایش ماکروهای مربوط به کارپوشه هایی که درحال حاضر باز هستند.
  • This Workbooks: فقط نمایش ماکروهای مربوط به کارپوشه ای که درحال حاضر فعال است و داریم با آن کار میکنیم.
  • PERSONAL.XLSB: فقط نمایش ماکروهای مربوط به ماکروهایی که بصورت شخصی و مخفی شده ضبط شده اند.
  • بغیر از گزینه های فوق یک گزینه دیگری وجود دارد که همان نام Workbook های شما را نشان می دهد.مثلا اگر دو ورک بوک به نام های test1 و test2 باز باشد در بین گزینه های بالا دوگزینه دیگه به نام های test1 و test2 هم خواهد بود.شما اینجا می توانید تعیین کنید که ماکروهای مربوط به کدام ورک بوک نمایش داده شود.

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

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

شما می توانید ماکرو ضبط شده را به یک دکمه، Shape یا سایر آبجکت های پشتیبانی شده اختصاص بدین.

لینک کردن ماکرو به یک دکمه
شکل شماره 6 – لینک کردن ماکرو به یک دکمه

ذخیره کردن ماکرو:

فایل های حاوی کد VBA مانند سایر فایل های معمولی ذخیره نمیشوند و باید با 3 فرمت تعریف شده زیر ذخیره شوند:

  1. Excel Macro-Enabled Workbook : فرمت معمول برای ذخیره سازی فایل های حاوی کد VBA
  2. Excel Binary Workbook : فرمت ذخیره سازی فایل های حاوی کد VBA بصورت بهینه تر و با سرعت بیشتر نسبت به فرمت بالایی
  3. Excel Macro-Enabled Template : فرمت ذخیره سازی فایل های حاوی کد VBA بصورت تمپلیت یا الگو
  4.  
فرمت ذخیره کردن فایل های حاوی کد ماکرو
شکل شماره 7 – فرمت ذخیره کردن فایل های حاوی کد ماکرو

نکته:فرمت های بالا نسبت به هم مزایا و معایبی دارند که در مقاله ای جداگانه مفصل بدان خواهیم پرداخت.اگر تفاوت فرمت های بالا برای شما گنگ بود از فرمت Excel Macro-Enabled Workbook بعنوان ذخیره سازی فایل ماکرو استفاده کنید.

 پسوند های معمول اکسل در نسخه های بعد از 2013
شکل شماره 8 – پسوند های معمول اکسل در نسخه های بعد از 2013

آیکون اکسل با فرمت های معمول بعد از نسخه 2013

 پسوند های معمول اکسل در نسخه های بعد از 2013
شکل شماره 9 – پسوند های معمول اکسل در نسخه های قبل از 2013

منبع:

office.com

اشتراک گذاری:

مطالب زیر را حتما مطالعه کنید

45 دیدگاه

به گفتگوی ما بپیوندید و دیدگاه خود را با ما در میان بگذارید.

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

    • سلام.

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

      UserForm1.Show

      UserForm1 نام فرم شماست که می تواند متغیر باشد.مثلا اگر نام فرم شما MyForm بود، کد رو بصورت زیر بنویسید:

      MyForm.show

      برای تکمیل این آموزش،مطلب زیر رو هم حتما بخونید:

      فرم ورود اطلاعات در اکسل و روش طراحی سریع آن

      در آخر هم مطابق آموزش ماکرو رو به یک دکمه اختصاص بدین.

    • سلام
      سوالی داشتم
      داده هایی را در شیت یک به صورت سطری داریم
      همون داده ها عینا در شیت 2 به صورت ستونی کپی شده اند. در شیت آنالیزهایی انجام میشه و یک عدد به عنوان نتیجه به دست می آید مثلا x. می خواهیم عدد x به شیت اول منتقل شود. این برای فرد یک بود

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

  • سلام من تعداد زیادی فایل حجیم حاوی اطلاعات هواشناسی دارم. هر فایل اکسل حاوی داده های روزانه ایستگاه های سینوپتیک کشور برای یک سال هست. برای اینکه بتونم از داده ها استفاده کنم باید اطلاعات هر ایستگاه رو از فایل ها استخراج کنم و به صورت مجزا در یک فایل اکسل ذخیره کنم به طوری که هر فایل حاوی اطلاعات یک ایستگاه برای تمام سال ها باشه. برای این کار میخوام از ماکرو استفاده کنم ولی نمیدونم چطوری باید تعریف کنم. فرض کنید سالهای من از 1970 شروع میشه اطلاعات سال اول از سطر 1 تا 365 خواهد بود (البته بعضی از سال ها کبیسه هست و 366 روزه) بعد از کپی و پیست کردن این سال، برای سال بعد باید از 366 به بعد ذخیره کنه. چطور میتونم این کارو با ماکرو انجام بدم؟؟

    • سلام. وقت بخیر…

      شما با ماکرو اطلاعات رو به ترتیب وارد فایل کنید و در آخر یک ماکرو هم برای ردیف زنی داده ها بصورت Auto Fill ضبط کنید.

      مثلا بعد از وارد کردن داده ها، در ستون A با ضبط ماکرو اولین شماره یعنی 1 رو وارد کنید و به سمت پایین درگ کنید.

  • با تشکر از زحمات فوق العاده تون
    ببخشید، اگر بخوایم یه برنامه بنویسیم که عدد ۱ رو یادداشت کنه و بعد از اون عدد ۲ همینطور با هربار اجرای ماکرو این عدد یکی افزایش پیدا کنه، چیکار باید کرد؟
    خیلی ممنونم

    • سلام. وقت بخیر.

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

      مثلا:

      1+(“Range(“A10000”)=Range(“A10000

  • سلام .
    من یه ماکرو رو ضبط کردم که از یه سلول در سطر تغییراتی رو انجام میده و بعد در شیت بعدی اون تغییرات انجام شده وارد میکنه . حالا میخوام این ماکرو رو بسط بدم تا روی سلول تمام سطر های بعد هم همین کار بکنه . چطور اینکار رو بکنم .

    • سلام وقت بخیر.

      در تب Developer هنگام ضبط ماکرو یک گزینه ای به نام Use Relative References وجود داره.هنگام انتخاب محدوده از کلید ترکیبی Ctrl+Shift و کلیدهای جهتی روی کیبورد استفاده کنید و محدوده رو کپی کنید.

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

  • من نمیخوام اطلاعات رو کپی کنم . ببین توی شیت اول تو سلول A1 عدد 10 رو داریم و تو سلول B1 عدد 12 و تو سلول C1 عدد 15 داریم و به همین ترتیب در سلول های زیرین همین شیت عدد های متفاوتی داریم .
    من ماکرویی ظبط کردم که در شیت دوم سلول A1 سه تا سلول A,B,C رو به این ترتیب بندازه 10,12,15
    یعنی سه تا سلول رو تو یک سلول در شیت بعد پشت سر هم بندازه و فقط بینش کاما باشه .
    حالا می خوام توی سلول های A2 ,A3,… در شیت دوم همون کار بالا رو تکرار کنه برای سطرهای بعد یعنی A2,B2,C2 و A3,B3,C3 و ….. از شیت اول .
    برای اینکه تکرار بشه چه کار باید بکنم .

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

      شما بیاید داخل شیت اول تو یک ستون کمکی با استفاده از تابع Concatenate سه تا سلول رو در یک سلول ادغام کنید.بعد به همون روشی که در پاسخ قبلی گفتم کل مقدار رو کپی کنید و در شیت دوم Paste کنید.با این تفاوت که مقادیر کپی شده رو بصورت Value در شیت دوم Paste کنید.

  • آره منم دنبال همون فرمول حلقه هستم و در این باره توضیه بدید . چون نمیتونم تو شیت اول سلول ادغام شده بزارم . چو اطلاعات دیگری غیر از ستون A,B,C هم هست . همون حلقه for رو توضیح بدید ممنون میشم.

    • سلام. وقت بخیر

      اگر هدفتان ایجاد یک کامنت هست، میتونید از تب Review گزینه New Comment ایجاد کنید.اینجوری با رفتن موس روی سلول موردنظر کامنت ظاهر خواهد شد.

      یا از کد VBA زیر میتونید در رویداد دلخواه استفاده کنید:


      Range("G75").AddComment
      Range("G75").Comment.Visible = False
      Range("G75").Comment.Text Text:="Javidsoft.ir:"

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

        • بجاری عبارت ثابت شما آدرس سلول رو وارد کنید.

          مثلا:

          Activecell.Comment.Text Text:=Sheet2.Range(“A1

  • سلام . من که توی سایتتون ثبت نام کردم ، برای دوره هم ثبت نام کردم ، اما لینکهای فایل PDF تون خرابه ، برام باز نمیشه

    • سلام. وقت بخیر

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

      • ای بابا ، من ازتون پرسیدم فلان چیز چی میشه میگین کامنت بزار جواب بدم ، کامنت گذاشتم میگین توی دوره ثبت نام کنین . ثبت نام کردم میگین لینکتون خرابه .

        • نگفتیم لینک ها خرابه.عرض کردیم سرور لینک ها رو عوض می کنیم.وگرنه لینک های داخل سایت مشکلی نداره.چندین بار تست کردیم.

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

    • میتونید از کد زیر استفاده کنید :


      ActiveCell.AddComment
      ActiveCell.Comment.Visible = True
      ActiveCell.Comment.Text Text:=(Sheet1.Range("B1"))

  • سلام. من برای یک کاری یک ماکرو نوشتم توی افیس 2010. حالا برای همکارم فرستادم تا انجامش بده ولی اجرا نمی شه و میگه فایلی را که می خوای باز کنی پیدا نمی کنم. اون افیسش 2016 و 2013 است. می تونید یه راهنمایی بکنید که چیکار کنم روی کامپیوترش اجرا بشه؟ ممنون

      • سلام. xlsm است. توی کد باید یه سری فایل xlsx را باز کنه و اطلاعات توش را منتقل کنه به فایل xlsm. روی افیس 2010 عمل میکنه ولی روی افیس 2016 یا 2013 همکارم اعلام می کنه اون فایل xlsx را پیدا نمی کنم

  • سلام.. ضمن تشکر… من یه ماکرو دارم که روی شیت اول نوشته شده و بخوبی کار میکنه… چطور میتونم انجام ماکرو رو برای 15 شیت بعدی در همان فایل اکسل اجرا کنم؟
    یعنی من یه فایل اکسل دارم با 16 شیت، که ماکرو فقط برای شیت اولش اجرا میشه… چطور میتونم کاری کنم که در همون فایل اکسل، ماکرو بر روی همه شیتها عمل کند؟
    ضمنا ظاهرا همکار هستیم بنده فارغ التحصیل 22 سال پیش هستم.

    • سلام.وقت بخیر

      میتونید داخل یک ماژول کدهای مربوط به تمام شیت هارو بنویسید.دقت داشته باشید که حتما درهاتون به اسم شیت ها هم اشاره کنید.

  • سلام اقای مختاری من می خواهیم شروع به ماکرو نوشتن کنم ولی RECORD MACRO غیر فعال می باشه چطوری آنرا فعال کنم

    • سلام.وقت بخیر.

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

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

    • سلام. وقت بخیر.

      این مواردی که فرمودید درحد یک پروژه می باشد و اینجوری نمیشه دقیق تر راهنمایی کرد.

  • سلام خسته نباشيد اگر داخل ماكرو بخوايم بنوسيم : اگر مقدار سلول target عوض شد پس سلول a 1 رو پاك كن چي بايد بنويسيم

  • سلام خسته نباشید
    من یک ماکرو در یک شیت ظبط کردم
    حالا میخوام این ماکرو به طور خودکار برای بقیه شیت ها اجرا بشه
    نه اینکه به هر شیت رجوع کنم و ماکرو رو ران کنم

    • سلام. وقت بخیر

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

  • سلام مهندس
    خدا خیرتون بده با این آموزش عالی ، کلی از کارام جلو افتادم
    خیلی روان و ساده آموزش میدین ایشاله که همیشه موفق باشین
    ممنونم

دیدگاهتان را بنویسید