فهرست
حتی اگر برنامه‌نویسی را بلد نیستید، مکان‌های زیادی (کتاب‌ها، وب‌سایت‌ها، انجمن‌ها) وجود دارد که می‌توانید کد ماکرو VBA آماده برای تعداد زیادی از کارهای معمولی در اکسل را پیدا کنید. در تجربه من، اکثر کاربران دیر یا زود مجموعه شخصی ماکروهای خود را برای خودکارسازی فرآیندهای معمول جمع آوری می کنند، خواه تبدیل فرمول ها به مقادیر، نمایش مبالغ در کلمات یا جمع کردن سلول ها بر اساس رنگ باشد. و در اینجا مشکل ایجاد می شود - کد ماکرو در ویژوال بیسیک باید در جایی ذخیره شود تا بعداً در کار استفاده شود.
ساده ترین گزینه این است که با رفتن به ویرایشگر ویژوال بیسیک با استفاده از میانبر صفحه کلید، کد ماکرو را مستقیماً در فایل کاری ذخیره کنید. دگرساز+F11 و اضافه کردن یک ماژول خالی جدید از طریق منو درج - ماژول:
با این حال، این روش دارای چندین معایب است:
- اگر تعداد زیادی فایل کار وجود دارد و همه جا به یک ماکرو نیاز است، مانند ماکرو برای تبدیل فرمول ها به مقادیر، باید کد را کپی کنید. در هر کتاب.
- نباید فراموش شود ذخیره فایل در فرمت ماکرو فعال (xlsm) یا در قالب کتاب باینری (xlsb).
- هنگام باز کردن چنین فایلی حفاظت ماکرو هر بار اخطاری صادر می کند که باید تایید شود (خوب، یا حفاظت را به طور کامل غیرفعال کنید، که ممکن است همیشه مطلوب نباشد).
یک راه حل ظریف تر ایجاد خواهد بود افزونه خودتان (افزونه اکسل) - یک فایل جداگانه با فرمت خاص (xlam) که شامل تمام ماکروهای "مورد علاقه" شما است. مزایای این روش:
- کافی خواهد بود یک بار افزونه را متصل کنید در اکسل – و می توانید از رویه ها و عملکردهای VBA آن در هر فایلی در این رایانه استفاده کنید. بنابراین نیازی به ذخیره مجدد فایل های کاری خود در قالب های xlsm- و xlsb نیست، زیرا. کد منبع در آنها ذخیره نمی شود، بلکه در فایل افزودنی ذخیره می شود.
- حفاظت ماکروها هم شما را اذیت نخواهید کرد. افزونه ها طبق تعریف منابع قابل اعتمادی هستند.
- می تواند انجام دهد تب جدا روی نوار اکسل با دکمه های زیبا برای اجرای ماکروهای افزودنی.
- افزونه یک فایل جداگانه است. خود حمل آسان از رایانه ای به رایانه دیگر، آن را با همکاران به اشتراک بگذارید یا حتی آن را بفروشید
بیایید کل فرآیند ایجاد افزونه مایکروسافت اکسل خود را مرحله به مرحله مرور کنیم.
مرحله 1. یک فایل افزودنی ایجاد کنید
مایکروسافت اکسل را با یک کتاب کار خالی باز کنید و آن را با هر نام مناسب ذخیره کنید (مثلا MyExcelAddin) در قالب افزودنی با دستور فایل - ذخیره به عنوان یا کلیدها F12، نوع فایل را مشخص می کند افزونه اکسل:
لطفاً توجه داشته باشید که اکسل به‌طور پیش‌فرض افزونه‌ها را در پوشه C:UsersYour_nameAppDataRoamingMicrosoftAddIns ذخیره می‌کند، اما در اصل، می‌توانید هر پوشه دیگری را که برای شما مناسب است مشخص کنید.
مرحله 2. افزونه ایجاد شده را متصل می کنیم
حالا افزونه ای که در مرحله آخر ایجاد کردیم MyExcelAddin باید به اکسل متصل شود. برای انجام این کار، به منو بروید فایل – گزینه ها – افزونه ها (فایل - گزینه‌ها - افزودنی‌ها)، روی دکمه کلیک کنید درباره ما (برو) در پایین پنجره در پنجره باز شده روی دکمه کلیک کنید مرور (مرور کردن) و محل فایل افزونه خود را مشخص کنید.
اگر همه چیز را درست انجام دادید، پس ما MyExcelAddin باید در لیست افزونه های موجود ظاهر شود:
مرحله 3. ماکروها را به افزونه اضافه کنید
افزونه ما به اکسل متصل است و با موفقیت کار می کند، اما هنوز یک ماکرو در آن وجود ندارد. بیایید آن را پر کنیم. برای انجام این کار، ویرایشگر ویژوال بیسیک را با میانبر صفحه کلید باز کنید دگرساز+F11 یا با دکمه ویژوال بیسیک برگ توسعه دهنده (توسعه دهنده). اگر زبانه ها توسعه دهنده قابل مشاهده نیست، می توان آن را از طریق نمایش داد فایل - گزینه ها - راه اندازی نوار (فایل - گزینه ها - سفارشی کردن نوار).
باید یک پنجره در گوشه سمت چپ بالای ویرایشگر وجود داشته باشد پروژه (اگر قابل مشاهده نیست، آن را از طریق منو روشن کنید مشاهده - Project Explorer):
این پنجره همه کتاب‌های کاری باز و افزونه‌های مایکروسافت اکسل از جمله ما را نمایش می‌دهد. پروژه VBA (MyExcelAddin.xlam) آن را با ماوس انتخاب کنید و از طریق منو یک ماژول جدید به آن اضافه کنید درج - ماژول. در این ماژول، ما کد VBA ماکروهای افزودنی خود را ذخیره می کنیم.
می‌توانید کد را از ابتدا تایپ کنید (اگر برنامه‌نویسی را بلد هستید)، یا آن را از جایی آماده کپی کنید (که بسیار ساده‌تر است). بیایید برای آزمایش، کد یک ماکرو ساده اما مفید را در ماژول خالی اضافه شده وارد کنیم:
پس از وارد کردن کد، فراموش نکنید که روی دکمه ذخیره (دیسکت) در گوشه سمت چپ بالا کلیک کنید.
کلان ما FormulasToValuesهمانطور که به راحتی می توانید تصور کنید، فرمول ها را به مقادیر در یک محدوده از پیش انتخاب شده تبدیل می کند. گاهی اوقات به این ماکروها نیز گفته می شود روش. برای اجرای آن، باید سلول های دارای فرمول را انتخاب کنید و یک کادر محاوره ای ویژه باز کنید ماکرو از زبانه توسعه دهنده (توسعه دهنده - ماکروها) یا میانبر صفحه کلید دگرساز+F8. به طور معمول، این پنجره ماکروهای موجود را از همه کتاب‌های کاری باز نشان می‌دهد، اما ماکروهای افزودنی در اینجا قابل مشاهده نیستند. با وجود این، می توانیم نام رویه خود را در فیلد وارد کنیم نام ماکرو (نام ماکرو)و سپس بر روی دکمه کلیک کنید دویدن (اجرا کن) – و ماکرو ما کار خواهد کرد:
| |
در اینجا شما همچنین می توانید یک میانبر صفحه کلید برای راه اندازی سریع یک ماکرو اختصاص دهید - دکمه مسئول این کار است پارامترهای (گزینه ها) در پنجره قبلی درشت دستور:
هنگام تخصیص کلیدها، به خاطر داشته باشید که آنها به حروف بزرگ و کوچک و به چینش صفحه کلید حساس هستند. بنابراین اگر ترکیبی مانند کلیدهای Ctrl+Й، در واقع، در آینده باید مطمئن شوید که طرح بندی را روشن کرده اید و اضافی را فشار دهید تغییربرای گرفتن حرف بزرگ
برای راحتی، می‌توانیم دکمه‌ای برای ماکرو خود به نوار ابزار دسترسی سریع در گوشه سمت چپ بالای پنجره اضافه کنیم. برای انجام این کار، را انتخاب کنید فایل – گزینه ها – نوار ابزار دسترسی سریع (فایل - گزینه ها - سفارشی کردن نوار ابزار دسترسی سریع)و سپس در لیست کشویی در بالای پنجره گزینه ماکرو. پس از آن کلان ما FormulasToValues را می توان با دکمه روی پنل قرار داد اضافه کردن (ADD) و یک نماد برای آن با دکمه انتخاب کنید تغییر دادن (ویرایش):
مرحله 4. توابع را به افزونه اضافه کنید
اما رویه های کلان، نیز وجود دارد ماکروهای تابع یا به نام آنها UDF (تابع تعریف شده توسط کاربر = تابع تعریف شده توسط کاربر). بیایید یک ماژول جداگانه در افزونه خود ایجاد کنیم (فرمان منو درج - ماژول) و کد تابع زیر را در آنجا قرار دهید:
به راحتی می توان دریافت که این تابع برای استخراج مالیات بر ارزش افزوده از مقدار شامل مالیات بر ارزش افزوده مورد نیاز است. البته دوجمله ای نیوتن نیست، اما برای ما به عنوان مثالی برای نشان دادن اصول اولیه عمل خواهد کرد.
توجه داشته باشید که نحو یک تابع با یک رویه متفاوت است:
- ساخت و ساز استفاده می شود عملکرد …. تابع پایان در عوض فرعی … پایان فرعی
- پس از نام تابع، آرگومان های آن در داخل پرانتز نشان داده شده است
- در بدنه تابع، محاسبات لازم انجام می شود و سپس نتیجه به متغیری با نام تابع اختصاص می یابد.
همچنین توجه داشته باشید که به این تابع نیازی نیست و اجرای آن مانند رویه ماکرو قبلی از طریق کادر محاوره ای غیرممکن است ماکرو و دکمه دویدن. چنین تابع ماکرو باید به عنوان یک تابع کاربرگ استاندارد (SUM, IF, VLOOKUP…) استفاده شود، یعنی فقط در هر سلولی وارد کنید و مقدار مقدار را با VAT به عنوان آرگومان مشخص کنید:
... یا از طریق کادر محاوره ای استاندارد برای درج یک تابع (دکمه fx در نوار فرمول)، انتخاب یک دسته تعریف شده توسط کاربر (تعریف شده توسط کاربر):
تنها لحظه ناخوشایند در اینجا عدم وجود شرح معمول عملکرد در پایین پنجره است. برای اضافه کردن آن باید موارد زیر را انجام دهید:
- ویرایشگر ویژوال بیسیک را با یک میانبر صفحه کلید باز کنید دگرساز+F11
- افزونه را در پنل Project انتخاب کرده و کلید را فشار دهید F2برای باز کردن پنجره Object Browser
- پروژه افزودنی خود را از لیست کشویی در بالای پنجره انتخاب کنید
- روی تابع ظاهر شده کلیک راست کرده و command را انتخاب کنید پروژه های ما.
- توضیحات عملکرد را در پنجره وارد کنید توضیحات:
- فایل افزودنی را ذخیره کنید و اکسل را مجددا راه اندازی کنید.
پس از راه اندازی مجدد، تابع باید توضیحاتی را که وارد کرده ایم نمایش دهد:
مرحله 5. یک برگه افزودنی در رابط ایجاد کنید
آخرین، اگرچه اجباری نیست، اما لمس دلپذیر ایجاد یک تب جداگانه با یک دکمه برای اجرای ماکرو است که پس از اتصال افزونه ما در رابط اکسل ظاهر می شود.
اطلاعات مربوط به برگه هایی که به طور پیش فرض نمایش داده می شوند در کتاب موجود است و باید در یک کد XML خاص قالب بندی شوند. ساده ترین راه برای نوشتن و ویرایش چنین کدهایی با کمک برنامه های ویژه - ویرایشگرهای XML است. یکی از راحت ترین (و رایگان) برنامه Maxim Novikov است Ribbon XML Editor.
الگوریتم کار با آن به شرح زیر است:
- تمام پنجره های اکسل را ببندید تا هنگام ویرایش کد XML افزودنی، هیچ تضاد فایلی وجود نداشته باشد.
- برنامه Ribbon XML Editor را اجرا کنید و فایل MyExcelAddin.xlam ما را در آن باز کنید
- با دکمه زبانه ها در گوشه سمت چپ بالا، قطعه کد را برای برگه جدید اضافه کنید:
- باید نقل قول های خالی قرار دهید id برگه و گروه ما (هر شناسه منحصر به فرد)، و در برچسب – نام تب ما و گروهی از دکمه های روی آن:
- با دکمه دکمه در پانل سمت چپ، یک کد خالی برای دکمه اضافه کنید و برچسب ها را به آن اضافه کنید:
- برچسب متن روی دکمه است
- imageMso - این نام مشروط تصویر روی دکمه است. من از یک نماد دکمه قرمز به نام AnimationCustomAddExitDialog استفاده کردم. اگر کلمات کلیدی "imageMso" را جستجو کنید، نام همه دکمه های موجود (و چند صد مورد از آنها وجود دارد!) را می توانید در تعداد زیادی از سایت های اینترنت پیدا کنید. برای شروع، می توانید به اینجا بروید.
- onAction – این نام رویه callback است – یک ماکرو کوتاه ویژه که ماکرو اصلی ما را اجرا می کند FormulasToValues. شما می توانید این روش را هر طور که دوست دارید نامگذاری کنید. کمی بعد اضافه می کنیم.
- می‌توانید صحت همه کارهای انجام شده را با استفاده از دکمه سبز رنگ در بالای نوار ابزار بررسی کنید. در همان مکان روی دکمه با فلاپی دیسک کلیک کنید تا همه تغییرات ذخیره شوند.
- Ribbon XML Editor را ببندید
- اکسل را باز کنید، به ویرایشگر ویژوال بیسیک بروید و رویه برگشت تماس را به ماکرو خود اضافه کنید KillFormulasبه طوری که ماکرو اصلی ما را برای جایگزینی فرمول ها با مقادیر اجرا می کند.
- ما تغییرات را ذخیره می کنیم و با بازگشت به اکسل، نتیجه را بررسی می کنیم:
این همه چیز است - افزونه آماده استفاده است. آن را با رویه‌ها و عملکردهای خود پر کنید، دکمه‌های زیبا اضافه کنید - و استفاده از ماکروها در کارتان بسیار آسان‌تر خواهد شد.
- ماکروها چیست، چگونه از آنها در کار خود استفاده کنیم، کد ماکرو را از کجا در ویژوال بیسیک دریافت کنیم.
- چگونه هنگام باز کردن یک کتاب کار در اکسل یک صفحه نمایش اسپلش ایجاد کنیم
- کتاب ماکرو شخصی چیست و چگونه از آن استفاده کنیم