آشنایی با برنامه نویسی اکسل (VBA) و ماکرو (قسمت سوم)

به نام خداوند یخشنده مهربان

در ابتدا بایستی از بابت تاخیر دراز مدت در ارائه مطلب از همه دوستان عذرخواهی کنم.

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

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

حالا مطالب را با هم مرور می کنیم:

VBA در یک نگاه

اگر بخواهیم نگاهی گذرا بر آنچه که VBA در مورد آن کاربرد دارد داشته باشیم می توانیم نگاهی به موارد زیر بیندازیم. البته می توان به تفصیل درباره هر کدام از آنها صحبت کرد.

عملیاتهایتان را در VBA با استفاده از نوشتن و یا رکورد کردن آنها در یک VBA Module انجام می دهید. که این کار را با استفاده از محیط ادیتور VBA که VBE نام دارد انجام می دهید.یک VBA Module شامل  Sub Procedureها می باشد. که در حالت عادی کاربرد خاصی ندارد و در حقیقت کدی می باشد که یک عمل را برای شما انجام می دهد. در زیر شما یک Sub Procedure را می بینید که Test نام دارد. این برنامه حاصل 1+1 را نمایش می دهد.

Sub Test ()

Sum = 1 + 1

MsgBox "The answer is "& Sum

End Sub

یک VBA Module همچنین می تواند شامل یکسری Function Procedure باشد.Function Procedure  یک مقدار را بر می گرداند. شما می توانید آنرا از یک VBA Procedure دیگر فراخوانی کنید و یا اینکه حتی از آن بعنوان یک تابع در یک فرمول بهره ببرید. نمونه­ای از یک Function Procedure که AddTwo نام دارد را در زیر مشاهده می کنید. این تابع دو عدد دریافت نموده (بعنوان آرگومان) و حاصل جمع دو عدد را بر می گرداند.

Function AddTwo (arg1, arg2)

AddTwo = arg1 + arg2

End Function

عناصر قابل دستکاریِ VBA. اکسل حدود 100 نوع آبجکت که قابل دستکاری هستند را برای شما فراهم می آورد. مثالهایی از این عناصر را می توان یک Workbook، Worksheet، یک محدوده سلول ( Range)، یک نمودار( Chart ) و یا یک شکل در نظر گرفت. بطور کلی شما عناصر زیادی در دسترس دارید که می توانید آنها را دستکاری نمایید.

عناصر در یک سلسله قرار می گیرند. عناصر می توانند بعنوان یک نگهدارنده برای سایر عناصر عمل کنند. و برنامه اکسل در بالاترین سطح این سلسله قرار دارد. اکسل خودش به تنهایی یک آبجکت می باشد که Application نامیده می شود و سایر آبجکت­ها مانند عناصر Workbook و عناصر CommandBar را شامل می شود.  آبجکت Workbook شامل سایر عناصر مانند عناصر Worksheet و عناصر چارت نیز می شود. آبجکت Worksheet شامل عناصر Range و عناصر Pivot Table می شود. عبارت Object اشاره به چیدمان این عناصر دارد.

عناصر هم نوع، Collection را تشکیل می دهند. بطور مثال، Worksheets Collection شامل تمامی Worksheetهای موجود در یک Workbook خاص می شود. Charts Collection شامل تمامی عناصر چارت موجود در یک Workbook می شود. Collection ها خودشان هم آبجکت هستند.

با استفاده از یک نقطه(Dot) می توان به یک آبجکت و عناصر اش اشاده داشت. بطور مثال برای اشاره به Workbook با نام Book1.xls می توان به شیوه زیر اقدام کرد:

Application.Workbooks ("Book1.xls")

این اشاره­ ایست به Workbook با نام Book1.xls در Workbooks Collection. Workbooks Collection در Application Object قرار گرفته است. و با رفتن به یک مرحله بالاتر، شما می توانید به Sheet1 در Book1.xls اشاره کنید. مانند زیر:

Application.Workbooks ("Book1.xls").Worksheets ("Sheet1")

همانند مثال زیر و با رفتن به یک مرحله پایین تر می توانید به یک سلول خاص در یک شیت خاص اشاره کنید. در این مثال، سلول A1

Application.Workbooks("Book1.xls").Worksheets ("Sheet1").Range ("A1")

اگر از یک Reference مشخص صرفنظر کنید، اکسل از آبجکت فعال استفاده می کند. اگر Book1.xls بعنوان Workbook فعال باشد، شما می توانید براحتی از Reference قبلی استفاده کنید همانند زیر:

Worksheets ("Sheet1").Range ("A1")

اگر بدانید که Sheet1 شیت فعال باشد، می توانید براحتی به سلول A1 اشاره کنید.

Range ("A1")

آبجکت­ها دارای ویژگیهایی هستند (Properties) شما می توانید Properties را بعنوان تنظیمی (Setting) برای آبجکت در نظر بگیرید. بطور مثال، آبجکت Range ویژگیهایی با نام مقدار (Value) و آدرس (Address) دارد. یک آبجکت Chart ویژگیهایی همانند عنوان (HasTitle) و نوع (Type) دارد. شما می توانید از VBA برای مشخص کردن ویژگیها و تغییر آنها استفاده کنید.

با استفاده از Property Name و Object Name که با استفاده از نقطه (Dot) از هم جدا شده اند، به Property یک آبجکت دسترسی داشته باشید. بطور مثال شما می توانید به Value سلول A1 بصورت زیر دسترسی داشته باشید.

Worksheets ("Sheet1").Range ("A1").Value

می توانید مقادیر را به متغیرها انتصاب دهید. متغیر (Variable) یک عنصر نامگذاری شده است که اشیاء را ذخیره می کند. شما می توانید در کد VBA  خودتان از متغیرها به منظور نگهداری مقادیر عددی، متنی و یا تنظیمات Property استفاده کنید. به منظور انتساب دادن مقدار سلول A1 در Sheet1 به یک متغیر که Interest نامیده می شود بایستی از VBA کد زیر استفاده نمود.

Interest = Worksheets ("Sheet1").Range ("A1").Value

آبجکت­ها دارای متدهایی هستند. Method عبارتست از یک اکشن اکسل که بوسیله آبجکت اجرا می شود. بطور مثال یکی از متدهای مربوط به آبجکت Range متد ClearContents می باشد. این متد محتوای Range را حذف می­کند.

نمایش متد با استفاده از ترکیبی از نقطه (Dot) و نام آبجکت. بطور مثال عبارت زیر محتوای سلول A1 را حذف می کند.

Worksheets ("Sheet1").Range ("A1").ClearContents

VBA همه ساختارهای یک زبان برنامه نویسی مدرن را شامل می شود. شامل آرایه­ ها و حلقه­ های

Loop

ما را از نظرات و سئوالات خوبتان محروم نفرمایید. نظرات شما باعث دلگرمی ماست.

بازگشت دوباره

با سلام به همه ی دوستان.

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

به زودی مطالب بخش آموزش برنامه نویسی در اکسل و آموزشگاه مجازی اکسل به روز رسانی خواهند شد.

آموزش جامع استفاده از Paste Special در اکسل

یکی از موارد بسیار کاربردی در اکسل استفاده از گزینه­ی Paste Special می باشد که متاسفانه عده­ی کمی از آن استفاده می کنند. در این آموزش قصد دارم تا توضیح مفصلی روی تمامی گزینه­ها داشته باشم.

زمانی که ما از قسمتی از سلولها در اکسل کپی (Copy) گرفته و قصد داریم تا آن اطلاعات را در جایی دیگر قرار دهیم (Paste)، اطلاعات بصورت کامل و با تمامی اجزای ظاهری و درونی اعم از فرمول و رنگ و تنظیمات محدود کننده (Validation) به محل جدید کپی می شوند اما با استفاده از امکان Paste Special که در منوی Edit و هم با استفاده از کلیک راست می توان به آن دسترسی داشت، می شود بصورت گسترده­تری از عملیات Copy-Paste استفاده نمود.  حالا بپردازیم به گزینه­های این ابزار:

تنظیمات Paste

1.     All: این گزینه که گزینه­ی پیشفرض نیز می باشد اطلاعات را با تمامی جزئیات به محل جدید کپی می نماید. (شامل فرمول، توضیح، رنگ، حالات کادر و ...)

2.     Formulas: اگر سلول و یا سلولهایی را که انتخاب کرده­ایم دارای فرمول باشند، انتخاب این گزینه باعث می شود تا فقط مقادیر ثابت و فرمولها به محل جدید کپی شوند و اگر روی سلولی که آن را کپی گرفته­ایم دارای فرمت خاصی بود، هیچ کدام از این فرمت بندی را در جای جدید نخواهیم دید.

3.     Values: اگر سلول و یا سلولهایی را که انتخاب کرده­ایم دارای فرمول باشند، انتخاب این گزینه باعث می شود تا فقط مقدار و یا مقادیر حاصل فرمول آنهم بدون هیچ گونه تغییرات فرمت دهی به محل جدید کپی شوند. کاربرد این گزینه برای مواقعی ای است که ما قصد داریم تا حاصل یک فرمول (مقدار) را به جای جدیدی کپی کنیم.

4.     Formats: انتخاب این گزینه باعث می شود تا کلیه فرمت موجود روی قسمت کپی گرفته شده به محل جدید انتقال یابد در واقع باعث می شود تا سلولهای جدید، فرمت سلولهای قبلی را به خود بگیرند. کاربرد این قسمت برای مواقعی است که می خواهیم قسمتی از اطلاعات (سلولها) فرمتی مشابه جایی دیگر داشته باشند به همین منظور از قسمتی از اطلاعات که دارای فرمت مورد نظر هستند Copy گرفته و به جای جدید که مایلیم فرمتی مشابه جای قبلی داشته باشد، Paste می کنیم متنها با استفاده از Paste Special و رعایت موارد گفته شده.

5.     Comments: گاهی اوقات، برای کار کردن راحتتر کاربران با پروژه های ایجاد شده در اکسل، روی بعضی از قسمتها (سلولها) توضیحاتی را قرار می دهیم که به Comment معروفند. استفاده از این گزینه فقط توضیحات قرار داده شده روی خانه­­های کپی گرفته شده را به محل جدید منتقل می کند.

6.     Validation: قبل از شروع به توضیح این قسمت، لازم می دانم تا در ابتدا توضیحاتی را در مورد خود ابزار Validation ارائه دهم.

Validation یکی از ابزارهای بسیار جالب موجود در برنامه اکسل می باشد.

خیلی اوقات لازم است تا سلولها را نسبت به اطلاعاتی که در آنها وارد می کنیم محدود کنیم. مثلاً اینکه کاری کنیم تا در یک سلول فقط مقدار عددی وارد شود و یا اینکه فقط یک مقدار تقویم و آنهم در یک بازه­ی زمانی خاص را قبول کند و از این قبیل کاردبردها. با استفاده از Validation می توان محدودیتهایی را برای سلولها از نظر مقادیر ورودی در نظر گرفت. بطور مثال اگر قصد ورود نمرات تحصیلی را داشته باشیم، می توانیم سلولها را به گرفتن عددی بین صفر (0) تا بیست (20) محدود کرد تا احیاناً نمره­ای خارج از محدوده واقعی درج نگردد. توضیحات بیشتر در مورد استفاده از Data Validation در موضوعات دیگری و بطور مفصل توضیح داده خواهد شد.

اگر در هنگام Paste کردن از این گزینه استفاده شود، کلیه تنظیمات مربوط به سلولی که کپی گرفته­ایم، به جای جدید برده می شود.

7.     All using Source theme: انتخاب این گزینه باعث می شود تا همه­ی موارد داده­ای از قبیل مقادیر و فرمولها با همان شکل ظاهری­ای که دارند به محل جدید کپی شوند.

8.     All except borders: اگز بخواهیم تمامی محتوا و مقادیر را بجز کادربندی، به جای جدید کپی کنیم از این گزینه استفاده می کنیم.

9.     Column widths: این گزینه تنظیمات مربوط به عرض ستونها را به جای جدید کپی می کند. مثلاً اگر جدولی داریم که از نظر عرض ستونها می خواهیم عیناً شبیه جدولی دیگر شود، از این گزینه استفاده می کنیم.

10. Formulas and number formats: انتخاب این گزینه باعث می شود تا مقادیر عددی، متنی و فرمولها و دیگر موارد به محل جدید کپی شند اما بدون تنظیمات قالب بندی مانند رنگ فونت و غیره.

11. Values and number formats: کلیه موارد به محل جدید کپی می شوند اما فرمولها کپی نشده و فقط مقادیر حاصل فرمول به محل جدید کپی می شوند منتها بدون تنظیمات قالب بندی.

تنظیمات Operation

در این قسمت می پردازیم به تنظیمات محاسباتی ای که مایلیم در حین Copy-Paste کردن داده­ها انجام پذیرند. بطور مثال فرض کنید که یک جدول عددی داریم و می خواهیم اعداد دیگری را طوری روی این مقادیر کپی کنیم که بعد از دستور Paste اعداد جدید با اعداد قبلی جمع شوند. بدین منظور قبل از صدور دستور کپی گزینه­ی Add را انتخاب می کنیم.

1.     None: این گزینه هیچ گونه دستور محاسباتی و ریاضی را روی اعداد کپی شده انجام نمی دهد. و بصورت پزینه ی پیش فرض این قسمت می باشد.

2.     Add: انتخاب این گزینه باعث می شود تا اعداد کپی گرفته شده با اعدادی که از قبل در خانه­ها موجودند، جمع گردند.

3.     Subtract: انتخاب این گزینه باعث می شود تا اعداد کپی گرفته شده از اعدادی که از قبل در خانه­ها موجودند، تفریق گردند.

4.     Multiply: انتخاب این گزینه باعث می شود تا اعداد کپی گرفته شده در اعدادی که از قبل در خانه­ها موجودند، ضرب گردند.

5.     Divide: انتخاب این گزینه باعث می شود تا اعدادی که از قبل در خانه­ها موجودند بر اعداد کپی گرفته شده تقسیم گردند.

تنظیمات دیگر

1.     Skip blanks: اگر در محدوده کپی گرفت گرفته شده، خانه­های خالی داشته باشیم. زمانی که از Copy-Paste استفاده می کنیم انتخاب این گزینه باعث می شود تا هیچگونه تنظیمات قالب بندی که روی خانه­های خالی قرار داده­ایم به محل جدید کپی نشود. اعم از تنظیمات رنگ فونت، کادربندی و غیره.

2.     Transpose: این گزینه جهت اطلاعات و خانه ها را در موقع Paste کردن عوض می کند. بطور مثال اگر لیست دانش آموزان که کپی گرفته­ایم بصورت افقی باشد، و در هنگام Paste کردن، این گزینه را انتخاب کنیم. باعث می شود تا لیست در جای جدید بصورت عمودی درج گردد.

3.     Paste Link: با انتخاب این گزینه کلیه سلولها بصورت یک ارجاع کپی می شوند. یعنی اینکه مقدار هر سلول وابسته است به سلول نظیر خودش در محل کپی گرفته شده و اگر مقدار یک سلول کپی گرفته شده تغییر یابد، مقدار سلول در جایی که Paste شده نیز تغییر می یابد.

ما را از نظرات و سئوالات خوبتان محروم نفرمایید. نظرات شما باعث دلگرمی ماست.

شروع کار با ایجاد ماکرو - برنامه نویسی اکسل (قسمت دوم)

در بخش گذشته با ماکرو و ویژگیهای آن آشنا شدید. در این قسمت می خواهیم یک ماکرو ایجاد نموده و ویژگیها و شرایط آن را مورد بررسی قرار دهیم.

همانطور که گفته بودم، ماکرو روشی است برای جلوگیری از انجام کارهای تکراری و تسهیل در انجام بسیاری از کارها. برای شروع، ابتدا یک ماکرو را با استفاده از Macro Recorder ایجاد نموده و سپس به بررسی جزییات آن می پردازیم تا با آن بیشتر آشنا شویم.

برای ایجاد یک ماکرو جدید، راه های مختلفی وجود دارد اما آسانترین راه استفاده از Macro Recorder می باشد. اما برای شروع و انجام یک کار عملی توام با آموزش بایستی در ابتدا یک موضوع را در نظر گرفته و سپس مراحل را قدم به قدم جلو برویم.

موضوع مورد بحث ما در این آموزش به قرار زیر است:

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

قبل از پرداختن به مراحل کار لازم به توضیح می دانم که در مورد Paste Special (Paste سفارشی) توضیح مختصری بدهم. در Paste Special امکانی وجود دارد که شما می توانید کاری کنید که زمانی که یک خانه حاوی فرمول را کپی گرفته و در جایی دیگر Paste می کنید، به جای خود فرمول مقدار فرمول قرار داده شود. این مقدار هیچ وابستگی به مقادیر اولیه ندارد و بصورت کاملا ثابت می باشد. ما با دانستن این بحث به سراغ این آموزش می رویم. ( مطالب آموزشی درباره Paste Special آماده شده و به زودی تحت یک مبحث جدید در همین وبلاگ و در بخش آموزشگاه مجازی قرار خواهد گرفت. منتظر باشید)

حالا می پردازیم به مراحل کار

1.     ابتدا یک پروژه جدید اکسل را ایجاد می کنیم و سپس یک فرمول ساده را در روی آن پیاده سازی می کنیم. در اینجا من حاصل جمع دو عدد را بعنوان نمونه در نظر گرفته ام. در این فرمول حاصل جمع دو عدد A1 و B1 در خانه C1 نمایش داده خواهد شد.

2.     حالا پس از نوشته فرمول دکمه ی Enter را می زنیم تا فرمول تثبیت شود.

3.     برای شروع در عملیات ایجاد ماکرو به سراغ Tools->Macro->Record Macro (در اکسل 2003) و یا View->Macros->Record Macro (در اکسل 2007) می رویم و این گزینه را انتخاب می کنیم. برنامه Record Macro از حالا به بعد درست همانند یک دستگاه فیلمبرداری عمل نموده و تمامی وقایع را ثبت می کند. با انتخاب این گزینه یک کادر محاوره ای مقابل شما باز می شود که دارای گزینه های زیر می باشد.

3.1. Macro Name: که نام ماکرو را برای ما مشخص می کند. اگر نام ماکرو ترکیبی از اعداد و حروف می باشد و یا اینکه از چند کلمه حرفی تشکیل شده است، نبایستی بین آنها از کاراکتر Space (جای خالی) استفاده شود و ترجیحاً بهتر است نام ماکرو را بصورت لاتین انتخاب نمایید.

3.2. Shortcut Key: می توان برای راحتی کار در فراخوانی یک ماکرو، روی آن یک کلید میان بر تعربف کرد تا بتوان با استفاده از آن، آنرا آسانتر فراخوانی نمود. بطور مثال اگر در اینجا کلیک کرده و دکمه ی u را تایپ کنیم، برای فراخوانی ماکرو می توان از کلید میان بر Ctrl+u استفاده نمود.

3.3. Store Macro in: مشخص می کند که ماکرو در کجا ذخیره گردد. آیا در داخل همین پروژه؟ یا در پروژه جدید و یا اینکه در Personal Macro Workbook ذخیره گردد. (اگر شما ماکرو را در داخل Workbook ذخیره کنید، فقط در داخل همان پروژه می توانید به آن ماکرو دسترسی داشته باشید. اما اگر آن را در Personal Macro Workbook ذخیره کنید، می توانید در تمامی پروژه های اکسل به آن ماکرو دسترسی داشته باشید.

3.4. Description: اگر مایلید می توانید برای درک بهتر خودتان و یا دیگر افرادی که می خواهند از ماکروی شما استفاده کنند، توضیحاتی را در این کادر بنویسید. (توضیحی مختصر درباره ی عملکرد ماکرو)

4.     پس از انجام تنظیمات دکمه ی OK را می زنیم تا ضبط ماکرو آغاز گردد.

5.     حالا روی سلول حاوی فرمول کلیک نموده و سپس بعد از کلیک راست، گزینه کپی را انتخاب می کنیم.

6.     حالا مجددا روی همان سلول کلیک راست نموده و گزینه ی Paste Special را انتخاب می کنیم.

7.     . بعد از باز شدن کادر Paste Special گزینه ی Values را انتخاب نموده و سپس دکمه ی OK را می زنیم. می بینیم که به جای فرمول در این خانه، حاصل فرمول بصورت عددی ثابت در خانه قرار گرفته است (اگر اعداد A1 و B1 تغییر کنند، حاصل تغییر نخواهد کرد در صورتی که قبل از این چنین نبود. البته شما برای گم  نکردن مسیر، بعد از زدن دکمه ی OK کاری انجام ندهید).

8.     حالا بایستی عملیات ضبط ماکرو را متوقف کنیم. به همین منظور گزینه ی Tools->Macro->Stop Recording (در اکسل 2003) و View->Macros->Stop Recording (در اکسل 2007) را انتخاب می کنیم تا عملیات ضبط ماکرو پایان یابد.

9.     تبریک می گویم. شما اولین ماکروی خودتان را ایجاد نموده اید. حالا می توانید اگر در جایی دیگر  یک سلول حاوی فرمول دارید، با انتخاب آن خانه و فراخوانی ماکرو (چه بصورت اجرا از طریق کلید میان بر و یا از طریق منو) سلول فرمولی را به عددی تغییر دهید.

اگر مایلید تا کد تولید شده در ماکروی مورد نظرتان را نیز ببینید می توانید به Tools->Macro->Macros (در اکسل2003) و View->Macros->View Macros (در اکسل 2007) مراجعه نموده و با انتخاب ماکروی مورد نظر و سپس انتخاب گزینه ی Edit کد ماکروی تولید شده را ببنید و آنرا مورد بررسی قرار دهید. هیچ نگران نباشید. به زودی با تمامی کدهای اینجا آشنا خواهید شد. (کمی صبر داشته باشید)

امیدوارم که این آموزش مورد قبول واقع شده باشد.

لطفا برای هر چه بهتر شدن این آموزش و دلگرمی بیشتر، بنده را از نظرات خود با  "ارسال نظر"  مطلع فرمایید. با تشکر

آشنایی با برنامه نویسی اکسل (VBA) و ماکرو - برنامه نویسی اکسل (قسمت اول)

VBA چیست؟

VBA عبارتست از Visual Basic for Application. که در واقع یک زبان برنامه نویسی برای توسعه نرم افزارهای مایکروسافت می باشد. اکسل هم که بعنوان یکی از نرم افزارهای خانواده مایکروسافت می باشد، شامل این زبان برنامه نویسی می باشد. در یک دید کلی، VBA ابزاریست برای توسعه برنامه­هایی که اکسل را کنترل می کنند.

لطفا VBA را با VB (که مخصوص ویژوال بیسیک می باشد ) قاطی نکنید. VB یک زبان برنامه نویسی است که به شما اجازه می دهد تا بتوانید برنامه­های اجرایی بسازید (همان فایلهای EXE). هر چند VBA و VB از جهات بسیاری متشابهند اما دو چیز متفاوت اند.

ماکرو چیست؟

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

با VBA چه کارهایی می توانید انجام دهید

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

1. نگهداری لیست­های متفاوتی مانند اسامی مشتریان، نمرات دانش آموزان یا اطلاعات محصولات.
2. بودجه بندی و پیش بینی وضع اقتصادی.
3. آنالیز داده­های مهندسی.
4. ایجاد فاکتور و سایر فرمهای کاربردی دیگر.
5. ایجاد و استفاده از نمودارها با استفاده از داده­های متفاوت.
6. کاربردهای متفاوت دیگر...

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

در زیر لیستی از کارهایی که با VBA می توان انجام داد آمده است و در بخش­های بعدی بطور مفصل درباره آنها صحبت خواهیم کرد.

 ·         درج یک عبارت متنی

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

·         اتوماتیک کردن کاری که شما بطور مکرر آنرا انجام می دهید

فرض کنید که شما یک مدیر فروش هستید و قصد دارید تا یک یک گزارش فروش ماهیانه ایجاد کنید. اگر کار شما یک کار شسته رفته باشد، شما می توانید برای انجام این کار از یک برنامه VBA استفاده کنید. و در نهایت با انجام این کار مدیرتان را شگفت زده کنید. (مخصوص آنهایی که همیشه دوست دارند متفاوت ظاهر بشن).

·         اتوماتیک کردن عملیات­های تکراری

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

·         ایجاد یک دستور سفارشی

شما همچنین می توانید با استفاده از دستورات ماکروهای اجرایی که نوشته­اید، منوهای اکسل را سفارشی کنید.

·         ایجاد کارهای آسان

در هر شرکتی شما ممکن است با افرادی برخورد کنید که هیچ آشنایی با کامپیوتر ندارند. با استفاده از VBA شما می توانید انجام یکسری از کارها را برای اینگونه افراد آسان کنید. بطور مثال شما می توانید یک الگوی ورود داده آسان را ایجاد نمایید. به اینصورت شما زمانتان را برای انجام کارهای معمولی از دست نخواهید داد.

·         ایجاد توابع جدید

هر چند اکسل دارای توابع از پیش تعریف شده بیشماری است اما شما این قدرت را دارید تا توابعی را به دلخواه برای فرمولهایتان ایجاد نمایید و من مطمئن هستم که از انجام این کار شگفت زده خواهید شد. جالب تر اینکه در پنجره Insert Function توابع خودتان را نیز خواهید دید و این باعث قشنگ­تر شدن فرمولهایتان می شود.

·         ایجاد برنامه­های مبتنی بر ماکرو-برنامه­های کامل

اگر علاقه­مند به صرف زمان هستید، می توانید از VBA برای ایجاد برنامه­هایی در مقیاس بزرگ که مثلا دارای کادرهای محاوره­ای (Dialogue Box) و یا Help و یا تجهیزات دیگر هستند استفاده کنید.

·         ایجاد Add-Ins های دلخواه برای اکسل

شما احتمالا با بعضی از Add-Ins های موجود در اکسل آشنایی دارید. بطور مثال می توان از Analysis ToolPak نام برد. شما می توانید از VBA برای ایجاد Add-Ins های دلخواهتان استفاده کنید.

در این قسمت بیشتر سعی بر این بود تا با خود VBA بیشتر آشنا شویم و از جلسات بعدی آموزشهای کاربردی را با هم آغاز خواهیم نمود.

لطفا بنده را از نقطه  نظرات خوبتان محروم نفرمایید. نظرات شما باعث دلگرمی و ادامه دادن راه می گردد.

راه اندازی بخش آموزش برنامه نویسی در اکسل VBA Programming

با عرض سلام خدمت همه ی عزیزان و دوستداران برنامه قدرتمند اکسل

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

البته در این آموزش بیشتر سعی من روی آموزش بصورت پروژه ای خواهد بود تا دستآورد خوبی برای همه ی دوستان داشته باشد.

از همین رو، از کلیه دوستانی که مایل به همکاری در این پروژه هستند و یا در این زمینه سئوالات و یا مشکلاتی دارند، خواهش می کنم به آدرس [e-mail]  ایمیل زده و یا نظراتشون رو در قسمت جدیدی که تحت نام "آموزش برنامه نویسی در اکسل (VBA)"  ایجاد نموده ام، بنویسند. امیدوارم که این آموزش هر چند ساده که به عنوان برگ سبزیست تحفه درویش، مورد قبول همه ی دوستان واقع شود.

البته در قسمت "آموزشگاه مجازی" نیز شما می توانید دیگر آموزشهای لازم را که بصورت موضوعی مطرح می شود را نیز پیگیری بفرمایید.

عدم نمايش فرمولها در برنامه اكسل

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

 

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

 

در Format Cell (تنظيمات مربوط به خانه­هاي اكسل) قسمتي وجود دارد به نام  Protection كه داراي دو قسمت انتخابي مي باشد به نام­هاي Locked و Hidden که در این قسمت مورد صحبت ما درباره ی Hidden می باشد.

مخفی نمودن فرمولها (کاربرد Hidden)

در قسمت Format Cell و در تب Protection شما علاوه بر گزینه ی Locked گزینه ی دیگری به نام Hidden را نیز مشاهده می نمایید.

اگر قبل از قفل کردن یک شیت، خانه هایی را که در آنها فرمول نوشته اید را انتخاب نموده و سپس وارد Format Cell  شده و گزینه ی Hidden آنها را انتخاب کنید (تیک بزنید) و سپس شیت را قفل نمایید، خواهید دید که علاوه بر کارهایی که در بالاتر توضیح داده شد، فرمولهای خودتان را نیز از دید کاربر مخفی نموده اید.

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

 

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

امید که مقبول واقع شده باشد.

فایل ضمیمه را می توانید از اینجا دانلود کنید. (نسخه ۲۰۰۳)

فایل ضمیمه را می توانید از اینجا دانلود کنید. (نسخه ۲۰۰۷)

 

ایجاد محدوده ورود مقادیر

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

برای انجام این کار مراحل زیر را دنبال نمایید:

1. ابتدا یک فایل اکسل ایجاد نموده و در آن یک فرمول ساده را بوجود آورید. بطور مثال می توان یک فاکتور ساده را در نظر گرفت که در آن در هر سطر تعداد محصول فروخته شده در قیمت واحد آن ضرب می گردد. (برای توضیحات بیشتر فایل اکسل ضمیمه را از لینک پایین دانلود کنید).

 در Format Cell (تنظيمات مربوط به خانه­هاي اكسل) قسمتي وجود دارد به نام  Protection كه داراي دو قسمت انتخابي مي باشد به نام­هاي Locked و Hidden و گزینه مورد بحث در این قسمت Locked می باشد. ما از قسمت Locked براي اينكه نوشتن مطالب را به بعضي از قسمتها محدود كنيم، استفاده مي كنيم

 ایجاد محدوده مجاز ورود اطلاعات (کاربرد Locked)

همانطور که قبلا اشاره شد، در قسمت Format Cell همه ی خانه ها قسمتی وجود دارد به نام Locked که بصورت پیشفرض برای تمامی خانه ها انتخاب شده است (تیک خورده است). زمانی که ما گزینه ی Tools->Protection->Protect Sheet (در آفیس 2003) و یا گزینه ی Review->Protect Sheet (در آفیس 2007) را انتخاب می کنیم، تمامی خانه هایی که در شیت ما، قسمت Locked آنها انتخاب شده است را قفل می کند. یعنی اجازه ورود اطلاعات یا هر کار دیگری را روی همان محدوده، به ما نمی دهد. البته این عملیات می تواند با دادن پسورد همراه باشد و شما می توانید پسورد را در قسمت Password to unprotect sheet وارد نمایید تا در زمان نیاز فقط خود شما بتوانید آنرا بازگشایی نموده و تغییرات را اعمال نمایید.

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

پس چون می بینیم که در حالت عادی در همه ی خانه های اکسل قسمت Locked انتخاب شده است، با انجام این کار، کل شیت قفل می گردد. حالا برای اینکه کاری کنیم تا بتوان به کاربر اجازه داد تا بتواند در قسمتی از خانه ها، اطلاعات وارد کند، ابتدا قبل از انتخاب گزینه ی قفل گذاری، محدوده مورد نظر (محدوده ای که مایلیم حتی پس از قفل گذاری بتوان در آن ورود اطلاعات داشت) را انتخاب نموده و سپس به Format Cell رفته و تیک Locked را بر می داریم.

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

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

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

 امید که این آموزش مقبول واقع گردد.

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

فایل اکسل ضمیمه را می توانید از اینجا دانلود کنید. (برای اکسل ۲۰۰۳)

فایل اکسل ضمیمه را می توانید از اینجا دانلود کنید. (برای اکسل ۲۰۰۷)

منتظر نظرات و پیامهای خوبتان هستم.

آموزش درج ليست در يك سلول

گاهي اوقات احتياج داريم تا در يك ستون، اطلاعاتي را که دارای آیتمهای یکسانی هستند را بصورت تكراري وارد نماييم.

بعنوان مثال مي توان مدرك تحصيلي را در نظر گرفت كه تمامي كارمندان، اعضا و افراد مختلفي كه مي خواهيم نام مدرك تحصيلي آنها را در سلولها وارد كنيم از چند گزينه تجاوز نمي كند (دیپلم، فوق دیپلم، لیسانس و ...). به همين دليل مي توان با استفاده از روشي ساده از نوشتن هر باره­ی آنها جلوگيري نمود و با ايجاد يك ليست، ورود اطلاعات را كاملا ساده نمود.

براي انجام اين كار مي توان به روش زيراقدام نمود.

 1.     ابتدا سلول و يا سلولهايي را كه مايليم اطلاعات در آنها بصورت ليست درج شود را انتخاب مي كنيم.

2.     حالا وارد منوي Data شده و سپس گزينه Validation را انتخاب مي كنيم.

3.     در اين قسمت شما سه سربرگ (Tab) مي بينيد. وارد سربرگ Setting شده و از قسمت Allow گزينه­ي List را انتخاب مي كنيم.

4.     حالا میرسیم به قسمتی که باید مقادیر لیست را انتخاب کنیم. برای انجام این کار دو راه در پیش رو داریم:

 4.1.  راه اول: بایستی قبل از ورود به این مرحله ابتدا در جایی از شیت، مقادیر را وارد کنیم (تایپ کنیم) و سپس زمانی که به این قسمت رسیدیم، در کادر Source روی دکمه­ی Select Range (دکمه­ای که در انتهای کادر قرار دارد) کلیک نموده و سپس محدوده مقادیر لیست را که قبلا تایپ نموده­ایم را با درگ کردن انتخاب کنیم حالا با زدن دکمه­ی Enter و یا کلیک مجدد بر روی همان دکمه، آیتمهای لیست را مشخص نموده و به مرحله قبل باز می گردیم.

 4.2. راه دوم: تایپ کردن گزینه­های لیست در کادر Source با قرار دادن کاراکتر کاما (,) بین اقلام تایپ نموده و به منظور مشخص نمودن هر کدام از گزینه­ها.

 

 حالا لیست شما آماده است و می توانید به جای تایپ کردن مدرک تحصیلی، آنرا از لیست آماده انتخاب کنید.

 

» از مزایای این کار می توان به داشتن عملیات فیلتر قویتر اشاره نمود چون در بسیاری از موارد و بدلیل عدم دقت کاربران در تایپ، نتایج عملیات فیلترینگ، بصورت مورد انتظار نیستند و با استفاده از لیست در واقع شما یک یکسان سازی در عمل تایپ بعضی از فیلدها را فراهم آورده­اید.

آموزشگاه مجازی اکسل

با سلام

در این قسمت از وبلاگ، آموزشهايي در مورد اكسل و بصورت كاربردي و موضوعي قرار خواهد گرفت. اميد است تا ضمن كمك به تمامي دوستان، با ارسال نظرات خود بنده را در ارائه كاري بهتر حمايت نماييد.

آغازی دوباره با اکسل

با سلام خدمت همه دوستان و كساني كه از اين سايت ديدن مي كنند.

شكر خدا كه بعد از مدت زمان زيادي كه تصميم به ايجاد اين وبلاگ گرفته بودم، بالاخره آنرا ايجاد نمودم. هدف از اين وبلاگ و راه اندازي آن، كمك به كليه كساني است كه در اكسل مشكل دارند و يا اينكه مايلند در مورد آن بيشتر بدانند. از همين رو، كليه دوستان مي توانند سئوالاتشان را در اينجا مطرح كنند تا بنده پاسخگوي سئوالاتشان باشم (انشاالله).
خوشحال مي شوم اگر دوستاني كه در اين زمينه و يا برنامه نويسي در اكسل مهارتهايي دارند به من كمك كنند. از اين دوستان خواهش مي كنم تا با زدن ايميل به بنده نظراتشان را ارائه دهند.

دوستاني كه قصد ارسال سئوال دارند مي توانند در قسمت ارسال نظرات سئوالات خود را مطرح کنند و یا اینکه با ارسال ایمیل به آدرس [e-mail] سئوالات خود را مطرح نمایند تا بعد از ویرایش سئوال، بصورت یک مطلب جامع و همراه با توضیحات در وبلاگ قرار گیرد.

لطفاَ با ارسال نظراتتان بنده را در ارائه اي بهتر ياري بفرماييد.