به نام خدا

 

    تماس با مدیر سایت/Contact  ایمیل من

 وبلاگ من



-->

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

ماکرو نویسی در اکسل ,

یکشنبه 16 بهمن 1384

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

koorosh_zahedi@yahoo.com

----------------------------------------------------------------------

ترجمه: میلاد امیریان‌فر
ماهنامه شبکه - شهریور ۱۳۸۴ شماره 57

اشاره :

معمولا اكثر ما برای برآورده كردن نیازهای كاریمان در 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، می‌توانید یك كلید میانبر برای اجرای آن تعریف كنید و حتی آن را به خط ابزار هم اضافه نمایید.

     


بقیه مطالب وبلاگ l یک مثال ساده در ماکرو نویسی
l ایجاد یک ماجول در ماکرو
l اولین درس ماکرو
l آغاز ماکرو نویسی در اکسل
l تبلت پی سی چیست؟
l سیستم عامل آندروید چیست ؟
l ملکه تبلت‌ درCES 2011 کیست؟
l چگونه یك متخصص امنیتی شوم؟
l تحلیلى اقتصادى از تاثیر اینترنت و فناورى اطلاعات بر بازارها و موسسات بیمه‌
l راه‌اندازی بزرگ‌ترین مرکز فناوری دنیا در چین
l معرفی MRTG به عنوان نرم افزار Monitoring شبکه
l نرم‌افزار یک ‌بیستم صادرات هند را شامل می‌شود
l سایت انستیتوی فیلم آمریكا
l What is Chief Information Officer
l مدیریت زنجیره تأمین با استفاده از فناوری‌های بی‌سیم و موبایل

ساخت وبلاگ در میهن بلاگ

شبکه اجتماعی فارسی کلوب | اخبار کامپیوتر، فناوری اطلاعات و سلامتی مجله علم و فن | ساخت وبلاگ صوتی صدالاگ | سوال و جواب و پاسخ | رسانه فروردین، تبلیغات اینترنتی، رپرتاژ، بنر، سئو