مروري بر ماكرونويسي‌ در اكسل‌

[Sardabir]

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

[farshid_mi]

چکیده :

در اکسل کاربر می‌تواند توابعی را تعریف کند که User Defined Function یا به اختصار UDF نامیده می‌شوند، در این مقاله نحوه تعریف این توابع و بکار گیری آن توضیح داده شده است.
مطالب به صورت خود آموز و قدم به قدم توضیح داده شده است .

برای دیدن ادامه مطلب کلیک کنید.

[gholipour1983]

با سلام خدمت سردبیر محترم

بنده در Visualbasic برنامه اکسل میخوام کدی بنویسم که اطلاعاتی مربوط به مشخصات سیستم را بدهد و برای اینکار Dll System.Management لازم است.
اما از قسمت Tools که می خوام این Dll را رفرنس بدهم به من اجازه نمی دهد
برای حل این مشکل چه باید بکنم لطفا راهنماییم فرمائید.


با تشکر

[yuanlili001]

stone crusher