مروری بر ماکرونویسی‌ در اکسل‌

معمولا اکثر ما برای برآورده کردن نیازهای کاریمان در Excel، از وجود ماکروهای پیش‌فرض در اکسل استفاده می‌کنیم که البته در غالب موارد نیز به کمک ما می‌آیند و مشکلات را برطرف می‌کنند. ولی مسلم است که آن‌ها نمی‌توانند پاسخگوی تمام نیازها باشند. مثلاً این ماکروها نمی‌توانند قبل از آن‌که روی سلول کاری انجام دهند، محتویات آن را چک کنند. همچنین نمی‌توانند از وجود پنجره‌هایpop-up برای ارتباط با کاربر استفاده کنند. بنابراین باید برای رسیدن به تمام مقصودهای خود، از راه دیگری استفاده کنید. یعنی ماکروهای موردنیاز خود را بنویسید که این مستلزم آشنایی شما با زبان اسکریپت اکسل و (VBA) می‌باشد. در این مقاله سعی بر آن است با نوشتن یک ماکروی ساده شما را با قسمتی از اسکریپت‌نویسی آشنا کنیم.

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

در ساخت این ماکرو به شما می‌آموزیم که برای اجرا کردن یک دستور، چگونه از کلیدهایی مثل OK یا Cancel استفاده کنید. همچنین خواهید آموخت چگونه یک فرم بسازید یا آن را کنترل کنید و محتویات آن را تحلیل نمایید. شما خواهید آموخت که چگونه محتویات سلول‌هایی را که انتخاب کرده‌ایم، به وضعیت دلخواه تغییر دهیم.

در خلال ساخت این ماکرو، با ارائه توضیحات کافی در هر مرحله، با خیلی از مسائل آشنا خواهیم شد. ماکرویی که در این مقاله قرار است نوشته شود، داده‌های هر سلول را، که از نوع String باشد، به حروف بزرگ یا کوچک یا ترکیبی از هر دو تبدیل خواهد کرد. (در اکسل توابعی نظیر UPPER یا LOWER و PROPER وجود دارند که می‌توانند حالت متن را تغییر دهند، اما برخلاف Word، هنگامی که متن را در سلول‌ها وارد می‌کنیم، به‌طور مستقیم نمی‌توانیم از آن‌ها استفاده کنیم.)

بنابراین، موضوع خوبی وجود دارد که ما برای حل آن ماکرویی بنویسیم. پس ماکروی ما باید سه گزینه lower
(حروف کوچک) Upper (حروف بزرگ) یا Proper را به کاربر پیشنهاد کند و براساس انتخاب کاربر، حروف را تبدیل کند. تابع اصلی‌ای که به ما کمک می‌کند حالت متون را عوض کنیم، Strconv نام دارد که الگوی آن به این صورت است: (Strconv (string, type of conversion

باید فرمی را درست کنیم که سه پیشنهاد مذکور را به کاربر ارائه دهد. اما باید به جای استفاده از کادرهای کنترلی
(check boxs)، از کلیدهای رادیویی استفاده کنیم. زیرا قرار است در هر لحظه تنها یکی از سه گزینه انتخاب شوند و نمی‌توانیم بیش از یک گزینه را انتخاب نماییم.

ساخت فرم
برای ساخت فرم باید در یک workbook جدید باشید و به منویTools/Macro/Visual Basic Editor مراجعه کنید. در برنامه VBA Project Explorer باید یک work sheet جدید داشته باشید و بعد به منوی Insert/userform بروید. برای آن‌که این منو را بزرگ کنید، می‌توانید از گوشه پایین سمت راست آن را بکشید. در ضمن اگر جعبه ابزار را هم روی صفحه ندارید، روی آیکون Toolbox کلیک کنید تا جعبه ابزار نمایان شود.

با استفاده از کنترل‌های Toolbox، یک کنترل Frame را روی دو سوم بالایی User form بکشید. سپس سه Option Button را به فریم خود اضافه کنید. بعد دو Command Button را نیز به پایین آن اضافه کنید. فراموش نکنید شما برای ویرایش هر یک از آیتم‌هایی که تاکنون به فریم خود اضافه کرده‌اید، اختیارات زیادی دارید و می‌توانید، اندازه، مکان، نوع، و شکل آن‌ها را به دلخواه خود تغییر دهید.

حالا روی هر آیتم کلیک کنید. User Form ،Frame و هر کلیدی که اضافه کرده‌اید و سایر تنظیمات را در Properties Window اعمال کنید. (تنظیمات دیگر برای مراحل بعد باقی خواهند ماند).

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

محتویات جعبه Toolbox,چیزهای هستند که با آن‌ها می توانید فرم خود را تهیه کنید و Properties اجازه می‌دهد آن‌ها را چنان که باید به نظر برسند، تنظیم کنید.

محتویات caption همان متنی است که هنگامی که اشاره‌گر ماوس روی هر آیتمی که قرار می‌گیرد ظاهر می‌شود. پس می‌توانید برای هر قسمت، متن مربوط به آن را بنویسید.

در قسمت Accelerator نیز می‌توانید کاراکترهای موردنظر خود را با کلید Alt مرتبط کنید تا در فرمتان به عنوان کلیدهای میانبر مورد استفاده قرار گیرد.

از دو کلید دستوری که به انتهای فرم اضافه کردیم، یکی کلید OK و دیگری کلید Cancel است. استفاده از کلیدCancel از دو جهت اهمیت دارد: اول آن که باید برای آن ارزشی معادل True در نظر بگیریم تا باعث شود هنگامی که کاربر کلید Esc را فشرد، این کد اجرا شود و برنامه بسته شود. همچنین باید Default را هم به‌واسطه تعیین ارزش True برای کلید Cancel تعریف کنیم تا هنگامی که ماکرو اجرا می‌شود، به طور پیش‌فرض کلید Cancel انتخاب شده باشد.

این‌که ماکروی شما مخرب نباشد، مسئله پراهمیتی است. بنابراین شما باید به گونه‌ای ماکرو بنویسید که اگر در حین کار اشتباها کلیدی را زدید یا چیزی را وارد کردید، اتفاق خاصی نیفتد. برای همین ما پیش‌فرض ماکرو را کلیدCancel در نظر گرفتیم تا اگر به اشتباه کلیدی زده شد، اتفاقی در محتویات worksheet شما نیفتد. البته الزام دیگری نیز برای این‌کار وجود دارد و آن فعال نبودن عمل undo است. در واقع اگر در حین اجرای یک ماکرو، به منوی Edit/undo سری بزنید، خواهید دید که غیرفعال است.

از مبحث پیش‌فرض بودن یا پیش‌فرض شدن دو کلید ماکرو که بگذریم، باید به سه آیتم بالای آن یعنی low ،upper و proper هم سری بزنیم و یکی از آن‌ها را نیز به‌عنوان پیش‌فرض ماکرو در نظر بگیریم. برای این‌که به اکسل بگوییم کدام یک از سه آیتم منظور ماست، باید برای یکی از آن‌ها ارزش بیشتری قائل شویم! به عبارت دیگر، باید ارزش یکی از آن‌ها را معادل True در نظر بگیریم که با این کار به‌طور خودکار ارزش سایر آیتم‌ها برابر false در نظر گرفته می‌شود. در حین تعیین کردن ارزش برای آیتم‌ها، مطمئن باشید که در هر لحظه تنها یک آیتم را انتخاب کرده‌اید.

نوشتن کدها
پیش از اضافه کردن کدها به ماکرو، باید بدانیم کدام آیتم قرار است کاری انجام دهد. اگر در این ماکروها کاربر از سه آیتم دارای کلید رادیویی، یکی را انتخاب کرد، قرار نیست اتفاقی بیفتد. در این ماکرو تنها آیتم‌هایی که اجازه دارند کاری انجام دهند، دو کلید OK و Cancel هستند.

کلید Cancel باید فرم ماکرو را از صفحه نمایش حذف کند (یعنی از برنامه خارج شویم) و کلید OK باید حالت متن‌های سلول‌های انتخابی را براساس آنچه کاربر در بالای فرم انتخاب کرده است، تغییر دهد.

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

Private sub cmdcancel – click()

End sub


و شما باید همان‌جا دو خط زیر را وارد کنید:

Unload Me

End


اکنون می‌توانید این قست را با کلیک کردن روی فرم و انتخاب Run Sub/user Form تست کنید. حالت مطلوب در این بخش آن است که اگر روی هر آیتمی کلیک کردید، تنها همان آیتم انتخاب شود و سایر آیتم‌ها از حالت انتخاب خارج شوند. کلید OK نباید کاری انجام‌دهد و شما بتوانید با کلیک کردن روی کلید Cancel یا فشار دادن کلید Esc، از برنامه خارج شوید.

حال روی کلید OK دوبار کلیک کنید. سپس دستورات لا‌زم را میان دو عبارت Sub و End sub وارد کنید. (کد 1)
هنگامی که روی کلید OK کلیک می‌کنید، تابع if بررسی می‌کند که کدام‌یک از Option button‌ها انتخاب شده‌اند. اگر اولی انتخاب شده باشد، متغیر convertChoice به ثابت ویژوال بیسیک یعنی vbUpperCase تبدیل می‌شود. اگر دومی انتخاب شده باشد، متغیر به vbLowerCase تبدیل خواهد شد و اگر هیچ کدام (اولی یا دومی) انتخاب نشود، یعنی سومی را انتخاب کرده‌ایم که بنابراین، متغیر به vbProperCase تبدیل خواهد شد.

در این خط از اسکریپت (بعد از Else) علا‌مت آپاستروف قرار داده شده است که نشان می‌دهد به طور پیش‌فرض این گزینه (گزینه سوم) انتخاب شده باشد.

Else ‘opt Proper is selected

عبارت for به برنامه می‌گوید که تغییراتی را که در خطوط بالاتر اعمال کرده است، تنها برای سلول‌هایی در نظر بگیرد که یک‌بار انتخاب شده‌اند و نوع محتویات آن‌ها هم String باشد.

بنابراین هر سلولی که محتوی داده‌هایی در قالب String باشد، به واسطه توابع ‌Lower یا Upper یا Proper تبدیل خواهد شد و خود این تبدیل، به واسطه آنچه که در متغیر convertchoice ذخیره شده است، اعمال خواهد شد. یعنی:

If var Type (cell.value) = vbstring then

‍Cell. Value = Strconv(Cell.Value, Convertchoice)
End If

کد 1

وجود این چندخط خیلی ضروری است. در واقع If بررسی می‌کند که سلول‌های انتخابی کاربر که جهت انجام تغییراتی به ماکرو معرفی شده‌اند، حاوی String هستند یا نه. به عبارت دیگر، اعمال تغییرات را فقط برای سلول‌هایی که محتوی String باشد، میسر می‌کند. اگر چه هر تلاشی برای تبدیل اعداد با این تابع (strconv) عملی نخواهد بود زیرا ‌تبدیل سایر داده‌ها یا فرمول‌ها می‌تواند باعث آسیب‌دیدن محتویات هر سلول شود.

در انتهای نوشتن ماکرو خود چند خط دیگر نیز برای طریقه نشان دادن فرم روی صفحه و پیغام‌های خطا و یا پرسش‌ها باید به کدهای اصلی اضافه کنیم. تاکنون ماکروی ما به عنوان ضمیمه User Form در حال کار بود. در صورتی که ماکرو باید به‌طور مستقل اجرا شود. برای این منظور، به منوی Insert/Module مراجعه کنید و خطوط زیر را در پنجره مربوط تایپ کنید.

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

البته در حالت‌های ترکیبی، ممکن است استفاده از "Range" مشکلاتی را به همراه داشته باشد، یا حتی ماکروی مربوطه به درستی کار نکند. انتخاب یک تصویر از Clip-Art یا یک نمودار و مشابه آن‌ها، جزء Range انتخابی شما نخواهد بود. بنابراین اگر در آیتم‌های انتخابی شما چنین مواردی باشد، متنی ظاهر خواهد شد و به شما پیشنهاد می‌کند که دوباره Range خود را انتخاب کنید. به این شکل:

"Please select a range and run the maro again"

پنجره حاوی این متن یک آیکون را متن پیام و یک کلید Ok برای تایید را نشان می‌دهد. برای امتحان ماکرویی که نوشتیم، در چند سلول، اطلاعات مختلفی، مانند متن، اعداد و فرمول را در workbook خود وارد و آن را ذخیره کنید. سپس برخی از آن‌ سلول‌ها را انتخاب و ماکرو را اجرا کنید(Tools/Macro/Macros). متون سلول‌ها باید مطابق آنچه شما از سه گزینه ارائه شده انتخاب کرده‌اید تغییر کنند.
آن را برای همه‌جا آماده کنید
برای این‌که ماکرو برای تمام worksheet آماده کار شود، باید آن‌ها را به فایل personal.als خودتان انتقال دهید. اگر یک فایل Personal.als در Projcet Explorer نمایش داده نشود، به اکسل بازگردید و یک ماکروی کوچک درTools/Macro/Record Macro ثبت کنید و در Personal Macro Workbook ذخیره نمایید. در واقع ثبت یک ماکرو، تنها در این workbook تمام آن کاری است که برای ساخت یک Personal.als نیاز دارید.

حال برای آن که ماکرو را به Personal.als انتقال دهیم، باید Form ها و Modulel در یکProject Explorer را بکشیم و روی فایل Personal.als بیندازیم.

اکنون ماکروی شما آماده است تا شما را در تمام worksheet‌ها یاری دهد، و شما با رفتن به منوی Tools/Macro/Macros و انتخاب نام و اعمال سایر تنظیمات در Option، می‌توانید یک کلید میانبر برای اجرای آن تعریف کنید و حتی آن را به خط ابزار هم اضافه نمایید.

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

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *

اجرا شده توسط: همیار وردپرس