نکات و آموزش VBA در اکسل

[S.Cheraghi]

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

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

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

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

کامنت ها به وسیله زبان وی بی (VBA) اجرا نمیشن برای همین تأثیری در خروجی برنامه هم ندارن. تو زبان وی بی (VBA) هر چیزی که بعد از کارکتر (‘) بیاید رو به عنوان یک کامنت در نظر میگیره. به همین دلیل تو ویرایشگر کد وی بی (VBA) رنگ کامنت ها به رنگ سبز تغییر میکنند.

حتما بخوانید: چطور اطلاعات را در اکسل وارد کنیم؟
تو مثال زیر یک قطعه کد وجود داره که با استفاده از کامنت ها خطوط مهم، کد رو توضیح دادیم:

' یک برنامه (روال اجرایی) برای جستجو درون سلول های
' که درون شیت فعال هست A1-A100
Sub Find_String(sFindText As String)
Dim i As Integer ' متغیر استفاده شده در حلقه برنامه
Dim iRowNumber As Integer ' متغیر استفاده شده برای ذخیره نتیجه
iRowNumber =
'جستجو در سلول های مشخص شده تا زمانیکه کلمه موجود در متغیر
'پیدا بشه sFindText
For i = 1 To 100
If Cells(i, 1).Value = sFindText Then
' در صورتیکه کلمه مورد نظر پیدا شد شماره ردیف آن را در متغیر مربوطه ذخیره کن و از حلقه خارج شو
iRowNumber = i
Exit For
End If
Next i
' یک پیغام به کاربر نمایش بده و نتیجه جستجو رو نمایش بده
If iRowNumber = Then
MsgBox "String " & sFindText & " not found"
Else
MsgBox "String " & sFindText & " found in cell A" & iRowNumber
End If
End Sub​

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

حتما بخوانید: تابع Sumif اکسل | محاسبه جمع شرطی در یک مجموعه داده

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

هر دقیقه ای که شما صرف ایجاد توضیح مناسب برای کدهاتون میکنید در آینده ساعت ها زمان شما رو برای ویرایش کدها ذخیره میکنه.
تورفتگی کدها
یکی دیگه از اصول خوانایی کد ها که برای کمک به افزایش خوانایی کدها استفاده میشه اضافه کردن تورفتگی ها به کد هست. اگه یه بار دیگه به مثال بالا نگاه کنید میبینید که وجود تورفتگی ها باعث میشه که بهتر متوجه بشید که هر خط کد زیر مجموعه چه دستوری هست. برای ایجاد تو رفتگی در هر خط از دکمه TAB استفاده میشه که به ازای هر بار زدن این دکمه یک تو رفتگی ایجاد میشه.

شکستگی خطوط
بعضی وقت ها به خاطر منطق مسئله مجبور میشیم که یک خط از کد رو خیلی طولانی بنویسیم. وجود خطوط بلند یکی دیگه از اصول خوانایی کد هست باعث میشه که کدها یکدست دیده نشن. از طرفی وسط یک خط نمیشه با زدن اینتر (Enter) به خط بعدی رفت و مابقی کد رو از اونجا نوشت (با انجام این کار به ما پیغام داده میشه که باعث ایجاد خطا میشه). اما یک راه برای ایجاد این شکستگی تو کدهای بلند وجود داره.
زمانیکه میخواید یک خط رو به دو خط تبدیل کنید باید در قسمتی که قصد شکستن خط رو دارید یک فاصله و زیرخط بذارید ( _) و بعد از آن اینتر بزنید. با این کار نشون به اجرا کننده برنامه نشون دادیم ادامه این خط از کد در خط بعدی قرار داده شده.
در مثال زیر نشون میدیم که چطوری شکستگی خطوط باعث افزایش خوانایی کدهای وی بی میشه. دستور If زیر رو در نظر بگیرید:

حتما بخوانید: قواعد فرمول نویسی حرفه ای در اکسل | قسمت اول

If (index = 1 And sColor1 = "red") Or (index = 2 And sColor1 = "blue") Or (index = 3 AndsColor1 = "green") Then​

با اضافه شدن شکستگی خط، همان ساختار رو به صورت زیر میتونیم استفاده کنیم:

If (index = 1 And sColor1 = "red") Or _
(index = 2 And sColor1 = "blue") Or _
(index = 3 And sColor1 = "green") Then​

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

منبع آموزش:
گروه تخصصی اکسل پدیا | توابع اکسل | اکسل چیست
3 روش برای افزایش خوانایی کدهای وی بی (VBA)

 

[S.Cheraghi]

منبع آموزش:
گروه تخصصی اکسل پدیا | توابع اکسل | اکسل چیست
تعریف متغیر ها و ثابت ها در وی بی (VBA) | قسمت اول

تعریف متغیر ها و ثابت ها در کدنویسی وی بی (VBA)
در وی بی (VBA) هم مثل سایر زبان های برنامه نویسی از متغیرها و ثابت ها جهت ذخیره مقادیر استفاده میشه. همینطور که از اسم این ها مشخصه، با تعریف متغیر ها، مقدار متغیرها تغییر میکنه ولی عموما ثابت ها مقادیری بدون تغییر به خود میگیرن.
به عنوان مثال شما میتونید از یک ثابت با نام PI برای ذخیره کردن عدد 3.14159265 استفاده کنید شاید سوالی که پیش بیاد این باشه که عدد پی همیشه همین مقدار خواهد بود و تغییر نمیکنه، پس چرا باید عدد اون رو به یک ثابت اختصاص بدیم؟ این کار رو برای راحتی دسترسی انجام میدیم، فرض کنید بخواید این عدد 9 رقمی رو تو قسمت های مختلف از کدتون استفاده کنید، قطعا استفاده از ثابت PI که قبلا تعریف کرده ایم بسیار راحت تره.

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

جنس داده ها (Data Types)
هر متغیر و ثابتی برای ذخیره یک جنس از داده تعریف میشه. پس مهمه که تو تعریف متغیر ها و ثابت ها بدونید که چه جنسی باید انتخاب بشه. در جدول زیر انواع داده به همراه توضیحی برای کدوم و بازه ای که میتونن تو خودشون ذخیره کنن معرفی شده.

حتما بخوانید: چطور اطلاعات را در اکسل وارد کنیم؟

برای مشاهده جدول با ظاهر مناسب به منبع آموزش مراجعه کنید​

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

نکته:
استفاده از کوچکترین متغیرهای مورد نیاز در کدها باعث میشه که سرعت برنامه افزایش پیدا کنه و حجم برنامه کمتر بشه. با این کار از حافظه استفاده بهینه میکنید.

حتما بخوانید: قواعد فرمول نویسی حرفه ای در اکسل | قسمت دوم

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

Dim Variable_Name As Data_Type​

در کد بالا به جای Variable_Name نام متغیری که شما قصد دارید تعریف کنید قرار بدید و به جای Data_Type باید یکی از جنس های متغیر در جدول بالا استفاده بشه. به عنوان مثال:

Dim sVAT_Rate As Single
Dim i As Integer​

تعریف ثابت ها هم شبیه تعریف متغیر ها هست به جز زمانیکه قصد تخصیص مقدار در همان ابتدای تعریف ثابت هست. از مثال زیر برای تعریف متغیرها در وی بی (VBA) استفاده کنید:

Const iMaxCount = 5000
Const iMaxScore = 100​

تا اینجا با انواع متغیرها آشنا شدیم و نحوه معرفی اونها رو یاد گرفتیم. در قسمت بعدی این آموزش منتظر این باشید که به سوال زیر جواب بدیم:

چرا باید متغیرها رو در کدها تعریف کنیم؟

منبع آموزش:
گروه تخصصی اکسل پدیا | توابع اکسل | اکسل چیست
تعریف متغیر ها و ثابت ها در وی بی (VBA) | قسمت اول

 

[S.Cheraghi]

در آموزش آشنایی با متغیر ها و ثابت ها در مورد انواع آنها صحبت کردیم و نحوه معرفی متغیر ها و ثابت ها رو گفتیم. امروز میخوایم در مورد علت های تعریف متغیر ها و ثابت ها و دامنه تعریف آنها صحبت کنیم. مسئله ای مهم و ظریف که یادگیری اون درک صحیحی از برنامه نویسی وی بی (VBA) به شما میده.

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

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

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

از طرف دیگه متغیرهای از جنس Variant مدت زمانی بیشتری برای پردازش نسبت به اکثر متغیرها نیاز داره از این رو در برنامه هایی که تعداد متغیرهای غیرضروری از این جنس زیاده برنامه کندتری خواهید داشت.

جلوگیری از خطای تایپی (Typo Bugs)
اگه همیشه متغیرها رو تعریف میکنید راحت تر میتونید از گزینه VBA Option Explicit (در ادامه توضیح میدیم) که برای ایجاد الزام جهت تعریف متغیرها است، استفاده کنید. این کار باعث میشه خطاهای ناشی از تایپ اشتباه نام متغیر در برنامه از بین بره. این نکته رو با یک مثال توضیح میدم:
حتما بخوانید: کاربرد ماکرو در اکسل چیست؟
فرض کنید از یک متغیر با نام “SVat_Rate” استفاده میکنید. ممکنه زمانیکه میخواید یک مقدار به این متغیر تخصیص بدید کد زیر رو بنویسید (که خیلی احتمالش زیاده)

SVatRate = 0.175
که مقدار 0.175 رو به متغیر اختصاص بده اما این کار انجام نشده! (یک بار دیگه به نام متغیری که اول گفتیم و متغیری که در کد استفاده کردیم دقت کنید). درحالیکه اگر از گزینه VBA Option Explicit استفاده کنید اکسل شما رو مجبور میکنه که هر متغیر جید رو اول تعریف کنید (به همین دلیل متغیرهایی که نام آنها اشتباها تایپ میشه به عنوان متغیر جدید شناسایی میشن و اکسل پیغام میده که باید تعریف بشه و شما متوجه خطا میشید). در این مثال اکسل پیغام میده که متغیر SVatRate قبلا تعریف نشده و باید آن را تعریف کنید.

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

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

بنا به دلایل بالا همیشه توصیه میکنم که همیشه همه متغیرها رو در برنامه نویسی وی بی (VAB) قبل از استفاده تعریف کنید و از گزینه Option Explicit (در ادامه توضیح میدیم) استفاده کنید تا مطمئن بشید همه متغیرها تعریف شدند.

گزینه Option Explicit در تعریف متغیر ها و ثابت ها
با فعال سازی گزینه Option Explicit شما مجبور میشید که تمامی متغیرهایی رو که در کد وی بی (VBA) استفاده میکنید رو تعریف کنید. از طرفی با فعال بودن این گزینه، متغیرهایی که تعریف نشده اند رو قبل از اجرای برنامه با رنگ کردن آنها مشخص میکنه. برای استفاده از این مورد کافیه دستور زیر رو در بالای هر صفحه کد بنوییسد.
حتما بخوانید: قواعد فرمول نویسی حرفه ای در اکسل | قسمت دوم
Option Explicit
از طرفی اگه میخواید که همیشه این کد بالای صفحات کدنویسی شما باشه میتونید از طریق مسیر زیر این کار رو انجام بدید تا به صورت خودکار این کد اضافه بشه:

• در صفحه ویرایشگر ویژوال بیسیک یا وی بی گزینه Tools> Options رو انتخاب کنید.
• از پنجره باز شده تپ Editor رو انتخاب کنید.
• تیک مربوط به گزینه Required Variable Declaration رو بزنید و OK کنید.

شکل 1 – متغیر ها و ثابت ها – ایجاد الزام برای تعریف متغیرها در وی بی​

وقتیکه این کار رو انجام دید از این به بعد این کد Option Explicit به صورت خودکار در بالای همه صفحات کد قرار داده میشه.

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

به عنوان مثال فرض کنید از یک متغیر با نام “SVat_Rate” درون یک Function با نام “Total_Cost” استفاده میکنید. در جدول زیر در مورد دامنه اعتبار متغیر در دو حالت مختلف از تعریف متغیر صحبت کردیم:

Option Explicit
Dim SVAT_Rate As Single
Function Total_Cost() As Double
...
End Function
اگر شما متغیر SVat_Rate رو در بالای ماژول تعریف کنید در اینصورت دامنه اعتبار متغیر به اندازه کل توابع و روال های ماژول هست. به عبارت دیگر متغیر SVat_Rate در تمام برنامه های این ماژول شناخته میشه. در واقع اگر شما یک مقدار رو به این متغیر در تابع Total_Cost تخصیص بدید این مقدار در سایر توابع موجود تو این ماژول باقی میمونه و پاک نمیشه. از طرفی اگر قصد داشته باشید از متغیر SVat_Rate رو در یک ماژول دیگه استفاده کنید به دلیل اینکه خارج از دامنه اعتبار متغیر هست، شناخته نمیشه.

Option Explicit
Function Total_Cost() As Double
Dim SVAT_Rate As Single
...
End Function
اگر شما متغیر SVat_Rate رو در ابتدای تابع Total_Cost تعریف کنید، دامنه اعتبار این متغیر به اندازه همون تابع هست و خارج از تابع Total_Cost شناخته نمیشه. بنابراین اگر سعی کنید از متغیر SVat_Rate در هر تابع دیگه در هر جا استفاده کنید امکانش نیست.

حتما بخوانید: پیوت تیبل (Pivot Table) چیست؟
در مثال بالا متغیری که در سطح ماژول تعریف شده با استفاده از دستور Dim تعریف شد و دامنه اعتبار اون حداکثر درون همون ماژول تعریف شد. حالا اگه بخوایم متغیرها رو طوری تعریف کنیم که در سایر ماژول ها هم در دسترس باشند میتونید از کلمه Public به جای Dim استفاده کنید.

نکته1:
برای متغیرهای در سطح یک ماژول میتونید از کلمه Private به جای Dim استفاده کنید نشاندهنده اینه که دامنه اعتبار متغیر محدود به همان ماژول هست.
نکته2:
مطالبی که در مورد دامنه اعتبار متغیرها گفتیم در مورد ثابت ها هم صادق هست. به عبارت دیگه کلمات Public و Private در هنگام تعریف آنها استفاده میشه اما باید توجه کنید که این کلمات جایگزین کلمه Const نمیشن و در کنار اون قرار میگیرن.


تو مثال زیر نحوه استفاده این کلمات رو برای متغیر و ثابت میبینید:

Option Explicit
Public SVAT_Rate As Single
Public Const IMax_Count = 5000
…
این مثال نشون میده که چطوری از کلمه Public برای تعریف متغیر SVat_Rate و ثابت Imax_Count استفاده شده.

دامنه اعتبار این دو به اندازه کل پروژه تعریف شده هست.

بنابراین متغیر SVat_Rate و ثابت Imax_Rate در هر تابع و روالی که در این پروژه تعریف شده قابل دسترس هست.

Option Explicit
Private SVAT_Rate As Single
Private Const IMax_Count = 5000
...
در این مثال نحوه استفاده از کلمه Private برای تعریف متغیر SVat_Rate و ثابت Imax_Count استفاده شده. دامنه اعتبار این دو در حد ماژولی هست که آنها در آن تعریف شده اند.

به عبارت دیگه SVat_Rate و Imax_Count تنها درون روال ها و توابع تعریف شده درون ماژول در دسترس هست.

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


منبع آموزش:
گروه تخصصی اکسل پدیا | توابع اکسل | اکسل چیست
تعریف متغیر ها و ثابت ها در وی بی (VBA) | قسمت دوم