برنامه نويسي در محيط ويژوال بيسيك كاربرد در اكسل(vb)

[!MAHSA!]

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


الف- عملگر &
چنانچه بخواهیم دو رشته موجود در یک برنامه را پشت سر هم بیاوریم از این عملگر استفاده می نماییم.
مثال اول: برنامه ای خواهیم نوشت که در یک MASSAGE BOX دو عبارت VISUAL وbasic را به هم ارتباط داده و نمایش دهد:

Sub aaa()​

​

s1 = "visual basic"​

​

s2 = " for aplcation"​

​

s = s1 & s2​

​

MsgBox s ​

​

End sub​

​

ب- کد vbnewline یا chr(13)
این عملگر، عملیات inter را در محیطی مثل word انجام می دهد. به این معنی که رشته دوم در سر بعدی رشته اول نشان داده خواهد شد.
مثال دوم:می خواهیم در یک مسیج باکس عبارتVBA this class name is به صورتی که عبارت VBA در زیر عبارت قبلی قرار گیرد، نمایش داده شود.
رشته مورد نظر بایستی به شکل زیر نوشته شود:

Sub bbb()​

​

s = "this class name is"​

​

s = s & vbNewLine​

​

s = s & "VBA"​

​

MsgBox s​

​

End Sub​

​

​

​

نکته: از هر یک از عبارات vbnewline یا chr(13) می توان جهت استفاده در برنامه، بهره برد.


ج – تابع LEN:

این تابع تعداد کرکترهای موجود در رشته شما را می شمارد. توجه به این نکته حائز اهمیت است که رشته شما می تواند داده های موجود در یک سل اکسل باشد. ساختار کلی تابع LEN به شکل زیر می باشد:
Len( )
که در داخل پرانتز رشته مورد نظر ما قرار خواهد گرفت.

مثال سوم:می خواهیم تعداد کرکتر های موجود در نام Ali Fatehi توسط برنامه در یک مسیج باکس نمایش داده شود.برنامه مورد نظر ما با استفاده از تابع بالا می بایست شکلی مانند زیر داشته باشد:

Sub ccc()​

​

s = Len("ALI FATEHI")
​

​

MsgBox s​

​

End Sub
​

 

[!MAHSA!]

د- تابع left-right

این تابع به برنامه می گوید از سمت چپ یا راست رشته ما تعدادی را جدا کن و به ما نشان بده:

ساختار کلی این تابع به شکل زیر می باشد:

Left(string,i)

که در داخل پرنتز ابتدا رشته مورد نظر ما و سپس تعداد کرکتری که از این رشته می باست جدا و نمایش داده شود، جایگزین خواهد شد. این دو داده با یک علامت"," از هم جدا می شوند.



مثال چهارم:می خواهیم با استفاده از تابع فوق، برنامه ای بنویسیم که چهار رقم سال 1359 در تاریخ بسیار مهم 1359/12/22 را جدا نموده و نمایش دهد. ساختار چنین برنامه ای به شکل زیر نوشته خواهد شد:

Sub ddd()

s = "1359/12/22"

t = Left(s, 4)

MsgBox t

End Sub
​

ه- تابع :TRIM

این تابع هر چه کرکتر خالی قبل یا بعد رشته باشد را در نظر نگرفته و باقی مانده را در نظر می گیرد. با این توضیح واضح است که ما در این تابع با دو ساختار راست و چپ کار خواهیم داشت:

ساختار کلی به شکل زیر می باشد:

L/R TRIM()



مثال پنجم:می خواهیم در رشته ای مانند" VBA " تنها سه کلمه VBA نمایش داده شود. ساختار چنین برنامه ای به شکل زیر خواهد بود:

Sub eee()

s = " VBA"

t = Trim(s)

MsgBox t

End Sub
​

و- تابعMID :



این تابع به ما می گوید:

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

MID(STRING,START,LENGTH)



مثال ششم:می خواهیم برنامه ای بنویسیم که در عبارت visual basic تنها کلمه basic در یک مسیج باکس نمایش داده شود.برنامه به شکل زیر نوشته خواهد شد:

Sub fff()

s = "visual basic"

t = Mid(s, 8, 5)

MsgBox t

End Sub
​

ز- تابع INSTR



تابع INSTRدر رشته مورد نظر ازهر جایی که بخواهیم شروع می کند، رشته اول را در نظر می گیرد، دنبال رشته یا متغیر خاصی بگردد و شماره آن را به ما بگوید:



ساختار کلی به شکل زیر می باشد:

INSTR(START, STRING 1 , STRING 2)

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

مثال هفتم: می خواهیم برنامه ای بنویسیم که در آن در زشته ای مانند aaapaa به ما بگوید که حرفP کدامین کرکتر این رشته می باشد:

Sub ggg()

s = "aaapaa"

i = InStr(1, s, "p")

MsgBox i

End Sub
​

مثال آخر:

یک تاریخ مشخص مانند تاریخ مهم 1355/12/22 را که با ممیز"/" از هم جدا شده اند را در نظر می گیریم. برنامه ای بنویسید که سال ماه و روز را جدا نموده و در سه کادر پیغام نمایش دهد.

Sub hhh()

s = "1359/12/22"

i = InStr(1, s,"/")

j = InStr(i + 1, s,"/")

y = Mid(s, 1, i – 1)

m = Mid(s, i + 1, (j - 1) – i)

d = Mid(s, j + 1)

MsgBox y

MsgBox m

MsgBox d

End Sub
​

 

[!MAHSA!]

در جلسه گذشته دیدیم که چگونه می توان در محیط اکسل یک FUNCTION تعریف نمود.

در این جلسه به بررسی چند مثال دیگر برای FUNCTION نویسی در اکسل می پردازیم و در نهایت به این مساله که چگونه می توان تابعی را برای کلیه اکسلهایمان تعریف نمود. تابعی با فرمول ثابت که در تمامی اکسلهای کامپیوترمان عمل نماید.
مثال اول:
می خواهیم تابعی بنویسیم که سن افراد را بگیرد و چنانچه زیر 18 سال است بگوید خیلی جوان، بین 18 تا 65 را مناسب و بالای 65 سال را به عنوان خیلی پیر معرفی نماید.ساختار چنین تابعی به شکل زیر می باشد:

Function check_old(old)​

​

Select Case old​

​

Case Is <= 18​

​

" check_old = "too young​

​

Case 18 To 65​

​

" check_old = "ok​

​

Case Is > 65​

​

check_old = "too old"​

​

End Select​

​

End Function​

​

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

Function BMI(weight, height)​

​

BMI = weight / (height ^ 2)​

​

End Function​

​

​

​

Function check_weight(BMI)​

​

Select Case BMI​

​

Case Is <= 15​

​

check_weight = "under weight"​

​

Case 15 To 25​

​

check_weight = "normal"​

​

Case Is > 25​

​

check_weight = "over weight"​

​

End Select​

​

End Function​

​

نکته: تابعی که ما در آن جلسه معرفی نمودیم تنها در همان WORKSHEET ای که برنامه در آن نوشته شده است کاربرد دارد. چنانچه ما برنامه ای داریم و می خواهیم FUNCTION ای بسازیم و این تابع را برنامه اکسل به عنوان یک تابع همیشگی که قابل استفاده در تمام اکسلهای دیگر که بر روی کامپیوتر موجود می باشد، بپذیرد و قابل استفاده باشد، می بایست از روش زیر استفاده نمود:
ابتدا برنامه نوشته شده را می بندیم و وارد محیط اکسل می شویم.از گزینه FILE منوی SAVE AS را انتخاب می کنیم.در پنجره موجود از کمبو باکس زیر صفحه آخرین گزینه را یعنیMICOSOFT EXCELL AD-INN را انتخاب کرده و در هر جایی که مایلیم چنین برنامه هایی SAVE شوند، ذخیره می نماییم.
حال وارد اکسل جدیدی می شویم.از منوی TOOLS گزینه ADD-INS را انتخاب می کنیم و در پنجره موجود کنار برنامه ای که نوشته شده است را تیک می زنیم.از هم اکنون برنامه جدید نوشته شده ما به عنوان تابع ثابتی در اکسل مان قابل استفاده است.

 

[!MAHSA!]

ادامه do-loop

در جلسه گذشته با حلقه شرطی DO-LOOP تا اندازه ای آشنا شدیم. در این جلسه می خواهیم با ارائه مثالهایی بیشتر با کاربرد این حلقه در برنامه نویسی ویژوال بیسیک بپردازیم.


کاربرد عبارت Until IsEmpty در حلقه شرطی DO-LOOP
در جلسه گذشته مثالی زدیم که تلاش بر این بود که برنامه ای نوشته شود تعداد سلهای پر در یک ستون اکسل را نشان دهد. در آنجا دیدیم که از خط برنامه زیر در برنامه جهت رسیدن به این هدف استفاده نمودیم:

Loop While Cells(i, 1)<>"" ​

​

به جای این عبارت در صورتی که از حلقه شرطی DO-LOOP استفاده می کنیم می توانیم از عبارت زیر استفاده نماییم:

Loop Until IsEmpty(Cells(i, 1))​

​

لذا برنامه به شکل زیر اصلاح می گردد:

Sub do_loop()​

​

i = 0 ​

​

Do​

​

i = i + 1​

​

Loop Until IsEmpty(Cells(i, 1)) ​

​

Cells(1, 2) = i - 1​

​

End Sub​

​

مثال دوم: می خواهیم برنامه ای بنویسیم مجموع مقادیر 1 تا 100 را بدون آنکه در سلهای جدول این اعداد را نشان دهد، در یک MASSAGE BOX نشان دهد.برنامه به شکل زیر نوشته می شود:

Sub do_loop2()​

​

i = 1​

​

Sum = 0​

​

Do​

​

Sum = Sum + i​

​

i = i + 1​

​

Loop While i <= 100​

​

MsgBox Sum​

​

End Sub​

​

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

Sub do_loop3()​

​

i = 1000
​

​

Do​

​

i = i - 1​

​

Loop While IsEmpty(Cells(i, 1))​

​

MsgBox i​

​

End Sub​

​

​

​

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

جهت نیل به این منظور وارد محیط VBA شده
برای اینکه برنامه ای در این محیط بنویسیم به جای عبارات SUB و در آخر برنامه END SUB از عبارت FUNCTION به صورت و فرمت زیر استفاده می شود.دقت شود که در پرانتزی که مقابل عبارت FUNCTION قرار دارد، متغیرهایی که مورد نیاز برنامه است و می بایست در برنامه استفاده گردد، وارد می شود و با یک "," از هم جدا می شود.

FUNCTION( , , , ) AS VARIANT
محاسبات برنامه
END FUNCTION

می خواهیم تابعی بنویسیم با معرفی نمودن سه مالفه در آمد، هزینه و درصد مالیات، خالص در آمد را محاسبه نماید:

Function netprofit(income, cost, tax) As Variant
t = 1 - tax
netprofit = (income - cost) * t
End Function

وارد محیط excel می شویم.حال از از گزینه insert حالت function را انتخاب می کنیم.در پنجره باز شده از کمبو باکس مقابل عبارت select a category or گزینه user difind را انتخاب می کنیم.ملاحظه می کنیم که برنامه جدید ما در اینجا وجود دارد. حال برای اجرای این برنامه دو حالت پیش روی ماست.
الف- استفاده از گزینه user difand برای اجرای برنامه که در این حالت پنجره ای باز می شود و از ما می خواهد که هر یک از سه متغیر برنامه را مشخص کنیم تا در نهایت حاصل را به ما گزارش نماید.


دوم- نوشتن فرمول در یکی از سلهای اکسل:اینکه وارد یکی از سلهای اکسل شویم در اینجا مانند هر برنامه دیگری می نویسیم:
=NETPROFIT(A1;B1;C1)
بدیهی است سلهای داخل پرانتز به ترتیب در اینجا در بر گیرنده در آمد، هزینه و درصد مالیات می باشد.

 

[!MAHSA!]

در این جلسه می خواهیم پروسه ایجاد یک شمارنده(CONTER)، طریقه جمع نمودن مقادیر عددی موجود در یک سطر یا ستون و... و در نهایت معرفی حلقه شرطی LOOP-WHILE بپردازیم.


شمارنده(COUNTER)
در جلسات گذشته دیدیم که برای جمع نمودن تعداد اطلاعات موجود در یک سطر یا یک ستون از فرمول =COUNT( ) در یک سل از سلهای اکسل استفاده می شود. در این مرحله می‌خواهیم با استفاده از متغیر COUNTER، شمارش تعداد کرکترها صورت پذیرد.
بدین منظور ابتدا باید مقدار کانتر را برابر صفر گذاشته و در مرحله بعدی پس از گذاردن شرط برنامه، کانتر را مساوی کانتر +1 قرار می دهیم:
در مثال زیر تعداد نمرات موجود دانش آموزان یک کلاس وجود دارد. می خواهیم برنامه ای بنویسیم که تعداد افرادی که قبول شده اند و تعداد افرادی که قبول نشده‌اند را در یک سطر اکسل نوشته و خود نمرات را نیز برحسب قبولی و یا رد شدن افراد تغییر رنگ دهد:
برای نیل به این هدف می بایست از یک حلقه FOR و یک شرط IF استفاده نمایم.لذا برنامه به شکل زیر نوشته می شود:

Sub ccc()​

counter = 0​

For i = 1 To 20​

If Cells(i, 1).Value >= 10 Then​

counter = counter + 1​

Cells(i, 1).Font.ColorIndex = 5​

Else​

Cells(i, 1).Font.ColorIndex = 3​

End If​

​

Next i​

​

Cells(21, 1).Value = counter​

Cells(22, 1).Value = 20 - counter​

​

End Sub​

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

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

Sub aaa()​

​

Sum = 0​

​

For i = 1 To 100 Step 2​

Cells(i, 1) = i​

Sum = Sum + i​

Next​

​

Cells(1, 2) = Sum​

End Sub​

نکته: به عبارت sum=sum+I توجه کنید. در نوشتن یک شمارنده ما به شماره قبلی یک 1 اضافه می کردیم ولی در جمع نمودن تعداد اعداد در این برنامه باید I انتخابی را مد نظر قرار دهیم.I ای که توسط شروط موجود برنامه ایجاد گردیده است.
همانطور که برای متغیر COUNTER نیز عنوان شد، SUM نیز یک متغیر است. متغیری جهت انتخاب، جایگزینی، جمع نمودن و در نهایت جایگزینی مجدد.
استفاده از حلقه شرطی DO-LOOP حلقه های شرطی حلقه هایی اند که تا زمانی که شرط برقرار باشد و یا نباشد –برحسب نیاز برنامه- مورد استفاده قرار می گیرد.
ساختار کلی یک حلقه DO-LOOP به شکل زیر است:
روش اول: در این روش شرط در ابتدا بررسی می شود

DO ​

دستورات​

LOOP WHILE یاUNTIL شرط مورد نظر​

روش دوم: در این روش شرط انتها بررسی می شود

​

DO WHILE یاUNTIL شرط مورد نظر​

دستورات​

LOOP ​

​

به این مفهوم که:
انجام بده این کار را تا زمانی که بتونی انجام بدی....
یا
انجام بده این کار را تا زمانی که نتونی انجام بدی....

در اینجا می خواهیم با ذکر برنامه ساده ای کاربرد این حلقه را به اختصار توضیح دهیم. به عنوان نمونه می خواهیم برنامه ای در محیط VBA بنویسیم که اعداد موجود در یک ستون اکسل را تا جایی که عدد وجود دارد جمع نماید.چنین برنامه ای باید به شکل زیر نوشته شود:

Sub do_loop()​

i = 0​

​

Do​

i = i + 1​

Loop While Cells(i, 1)<>"" ​

​

Cells(1, 2) = i – 1​

​

End Sub​

​

ملاحظه می شود که برنامه اعداد I را در ستون مذکور تا جایی که به سل خالی برسید با هم جمع می کند.توضیح بیشتر و برنامه های بیشتر در مورد این حلقه شرطی به جلسه بعد موکول می شود.

نکته-وقتی از WHILE استفاده می شود مادامیکه شرط برقرار باشد دستورات انجام میشوند
وقتی از Until استفاده می شود حلقه به محض وقوع شرط حلقه خاتمه می یابد( به عبارتی حلقه تا زمانیکه شرط برقرار نیست ادامه پیدا می کند)

 

[!MAHSA!]

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

For counter = start To end [Step step]

دستورات
Next [counter]


برای مثال برنامه ای می نویسیم که در سلهای اکسل به صورت یکی درمیان پر نماید:
جهت نیل به این هدف از فرمول زیر استفاده می شود:

For i=1 to 10 step 2​

Cells(1,i)=i​

Next i​

به همین ترتیب و با استفاده از تکنیک step در حلقه ها می توان از اعداد زوج با step 2 و اعدادفرد با step یک یا سه بهره جست.
حلقه های تو در تو با استفاده از چندین حلقه for می توان امور تکراری پیچیده تر و بیشتری را انجام داد.
در مثال زیر تلاش ما بر این است که یک جدول ضرب 10*10 در محیط اکسل ایجاد نماییم.
برای رسیدن به این منظور به راحتی و با استفاده از 2 حلقه می توان این برنامه را به شکل زیر نوشت:

Sub ZARB()​

For i = 1 To 10​

For J = 1 To 10​

Cells(i, J) = i * J​

Next J​

Next i​

End Sub​

استفاده از ساختارهای شرطی در حلقه ها حال می خواهیم که در همین جدول مضارب 5 را با تغییر فونت مشخص نماید:
برای این منظور باید بعد از سطر چهارم برنامه خط زیر را نوشت:

If i = 5 Or j = 5 Or i=10 or j = 10 Then​

Cells(i, j).Font.Size = 25​

End If​

می توان در برنامه موجود کار پیغامی مبنی بر اینکه آیا مایل به ذخیره نمودن برنامه هستید یا خیر، پس از if برنامه ایجاد نمود.
​

 

[!MAHSA!]

جلسه هفتم:
Massage Box در اکسل:
یک پنجره پیغام(Massage Box) از سه بخش به شرح ذیل ساخته شده است:
عنوان پیغام , شرح پیغام گزینه های پیغام
Massage box هایی که در طول یک برنامه به آنها نیاز داریم شامل دو گروه می باشد:
الف- پیغامهایی که صرفا اطلاعاتی بوده و به کاربر مفهومی را بدون آنکه عملیاتی بر روی داده ها انجام دهد انتقال می دهد.مانند پیغامهای خوش آمد گویی.
ساختار ساده اینگونه پیغامها به صورت زیر در محیط VBA به صورت زیر است:

Sub message()​

MsgBox "hello my frends"​

End Sub​

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

Sub message()​

x = MsgBox("do you want to continue?", vbYesNoCancel, "message box")​

If x = vbYes Then​

Rows("1000:2000").Select​

Selection.Delete Shift:=xlUp​

ElseIf x = vbNo Then​

Rows("1000:2000").Select​

End If​

End Sub​

همانطور که در خط دوم برنامه ملاحظه می گرددساختار کلی یک کادر پیغام شامل سه بخش عنوان، شرح و گزینه های پیغام می باشد که به علامت , از هم جدا می شود. به عبارت VBA در قسمت میانی فرمول توجه کنید.
نکته: اطلاعات کامل در مورد Massage Box در HELP اکسل آورده شده است .
​

 

[!MAHSA!]

جلسه ششم:


Select CASE

دستور SELECT CASE دستوری با کاربردی مانند IF می باشد. در جهت استفاده از این دستور ما نیازمند تعریف متغیری مانند X هستیم و سپس برای متغیر مورد اشاره شروط مورد نیاز را می گذاریم.


شکل کلی دستور Select Case

Select Case نام متغیر​

Case حالت اول ​

دستورات ​

Case حالت دوم ​

دستورات ​

.​

.​

.​

​

Case Else​

دستورات ​

End Select​

در زیر برنامه ای نوشته شده است که برای مقادیر مختلف موجود در سل A1 یک شیت اکسل عبارات A , B, C, … را در سل مقابل آن می گذارد.

Sub level( )​

x = Range("a1").Value​

Select Case x​

Case 17 To 20​

Range("b1").Value = " A" ​

Case 14 To 17​

Range("b1").Value = " B" ​

Case 12 To 14​

Range("b1").Value = " C" ​

Case 10 To 12​

Range("b1").Value = " D" ​

Case 0 To 10​

Range("b1").Value = " E" ​

Case Else​

Range("b1").Value = "false"​

End Select ​

End Sub​

ساختار حلقه ای FOR Each - NEXT
حلقه ها دستوراتی هستند که می توان جهت انجام دستورات تکراری از آنها استفاده نمود. حلقه FOR Each برای کار بر روی عضوهای(members) یک مجموعه(collection) استفاده می شود. مجموعه شامل تعدادی شیء یکسان است. به عنوان مثال Range("A1:A10") یک ‍collection ‌ است زیرا تعدادی عضو به نام سلول است که همگی از یک جنس هستند. worksheet های یک فایل اکسل مجموعه ای به نام Worksheets تشکیل می دهند. چارتهای یک فایل اکسل مجموعه ای به نام Charts تشکیل می دهند.
الگوی زیر الگوی استفاده از یک حلقه FOR Each در برنامه نویسی می باشد:

For Each عضو In مجموعه​

دستورات​

NEXT​

در برنامه ای که در زیر نوشته شده عملیات مورد نظر بر روی ردیفهای 1 تا 10 ستون A انجام می شود.برنامه به گونه ای نوشته شده است که اعداد کمتر از 10موجود در ستون اول، را Bold ‌کند

​

حال با استفاده و ترکیب دو دستور FOR-NEXT به سادگی برنامه مورد اشاره به شرح ذیل نوشته می شود:

Sub range_level ()​

​

Sub a()​

​

​

For Each c In Range("a11:h20")​

If x < 10 Then c.Font.Bold = True​

Next​

​

End Sub​

استفاده از دستور ROW در برنامه:

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

Sub range_level()​

Dim c As Range​

For Each c In Range("a1:a10")​

x = c.Value​

i = c.Row​

​

Select Case x​

​

Case 17 To 20​

Cells(i, 2) = "A"​

Case 14 To 17​

Cells(i, 2) = "B"​

Case 12 To 14​

Cells(i, 2) = "C"​

Case 10 To 12​

Cells(i, 2) = "D"​

Case 0 To 10
​

Cells(i, 2) = "E"​

Case Else​

Cells(i, 2) = "ERROR"​

End Select​

​

Next​

​

End Sub
​

 

[!MAHSA!]

جلسه پنجم دستورهای شرطی(if then else)
ساختار اولیه وساده:

if شرط then​

دستورات​

end if​

مثال: در سلول A1 عددی قرار دارد که سن شخصی را نشان میدهد. میخواهیم با توجه به سن در سلول B1 پیغامی بنویسیم

Sub grade()​

​

If Range("a1") >= 17 Then​

Range("b1") = "good"​

End If​

​

End Sub​

ساختار کامل دستور شرطی If

If شرط1 then​

دستورات​

Elseif 2شرط then​

دستورات​

​

Elseif شرط آخر then​

دستورات​

​

​

Else​

دستورات​

Endif​

مثال:برنامه ای بنویسید که برای نمرات بالای 17 عبارت A بین 17 تا 14 عبارت B و کمتر از 14 عبارت Cرا تایپ نماید.

جواب:

Sub grade()​

​

If Range("a1").Value >= 17 Then​

\ Range("b1").Value = "A"​

ElseIf Range("a1").Value < 17 And Range("a1").Value >= 14 Then​

Range("b1") = "B"​

Else​

Range("b1").Value = "C"​

End If​

End Sub

 

[!MAHSA!]

جلسه سوم برنامه نویسی در اکسل ایجاد دکمه جدید در صفحه اکسل:


از ابزار های DESIGN MODE در منوی ماکر COMMAND BUTTON را انتخاب می کنیم دکمه جدید را انتخاب کرده و بر روی آن دابل کلیک می کنیم تا صفحه ویژوال بیسیک باز شود. در صفحه ایجاد شده دستور زیر را وارد می کنیم:


Range("A1:A10").Value = 10 با این دستور به اکسل گفته شده که A1 تا A10 را گرفته و به آن مقدار 10 را بدهد.

از عبارت RANGE("@") برای مشخص کردن ناحیه ای در اکسل و از عبارت VALUE=. برای تغییر مقدار سلولها استفاده می کنیم. حال از این صفحه خارج شده و بر روی دکمه گونیا(در منوی ویژوال بیسیک نوار خاکستری رنگ بالای صفحه) کلید کرده و EXIT DESIGN MODE را می زنیم. بر روی دکمه جدید راست کلید کرده و مشخصات را را انتخاب کرده و در این حالت مشخصات دکمه از قبیل رنگ و نام و ... را می توان تغییر داد.


حال از این صفحه خارج شده و بر روی دکمه گونیا(در منوی ویژوال بیسیک نوار خاکستری رنگ بالای صفحه) کلید کرده و EXIT DESIGN MODE را می زنیم. وقتی که از حالت DESIGN خارج شدیم حال بر روی دکمه کلیک کرده و مشاهده می کنیم که درسلهای مشخص شده ارزش عددی 10 را قرار می دهد. می توان به جای 10 یک عبارت را در داخل " " قرار داد و در این حالت به جای 10 آن عبارت نوشته می شود. توجه: در صورتی که از علامت ' در ابتدای یک خط برنامه نویسی در محیط VB استفاده شود آن خط سبز شده و عملیاتی بر روی آن خط اجرا نخواهد شد. این خط اصطلاحا Comment (توضیحات)‌نامیده می شود.


Comment حالت اجرایی ندارد و از آن برای ارایه توضیحات استفاده میشود. Comment ها به خوانایی برنامه بسیار کمک میکنند. دستور زیر را می نویسیم: Range("D1").Value = Range("B1") + Range("C1") دستور زیر را در یک سط دیگر می نویسیم:


Range("E1").Value = "=A1+A2" ملاحظه می شود که در سطر 1E ارزشهای موجود در A1 و A2 جمع زده می شود. نکته : به علامت = در داخل کوتیشن توجه شود. اگر به محیط اکسل بازگردیم و درسلولهایی که فرمول نوشته می شود توجه کنیم دیده می شود که محتوای این سلولها با علامت = شروع می شود. یعنی اکسل به محتوای یک سلول نگاه میکند اگر با علامت = شروع شود آنرا فرمول تلقی می کند و نه مقدار. پس ما هم از همین روش استفاده میکنیم و وقتی در نظر داریم که فرمولی را در یک سلول وارد کنیم می بایست با علامت = شروع کنیم. دستور SELECT با انجام این دستور سلهای مورد اشاره انتخاب می شود می توان بعد از RANGE از دستور بالا استفاده نمود. دستور زیر را وارد کنید: Range("F1:F10").Select و دکمه را RUN می کنیم.ملاحظه می شود که سطرهای مورد اشاره های انتخاب شد. می توان به جای : از علامت , استفاده نمود که در این حالت به معنی AND می باشد. مواردی که در بالا اشاره گردید خلاصه ای از سه دستور RANGE VALUE SELECT بود که اشاره ای مختصر به آن گردید. مطالبی که ارایه شد مقدمه ای بود تا آمادگی لازم برای ورود به مبحث برنامه نویسی ایجاد شود.


1. شروع برنامه نویسی روشهای مختلفی برای شروع و ایجاد یک برنامه با VBA وجود دارد. روش اصلی و مستقیم نوشتن کدها در داخل ویرایشگر VB یا همان ‌VBE می‌باشد. یک روش ساده دیگر نیز وجود دارد و آن ایجاد یک command button بر روی صفحه گسترده محیط اکسل و شروع برنامه نویسی با کلیک بر روی آن است. کار را با روش اول شروع می‌کنیم 1.1 روش اصلی: ایجاد و اجرای یک برنامه در VBE برای ایجاد یک برنامه در محیط برنامه نویسی اکسل مراحل زیرانجام می‌شود: 1- ورود به محیط برنامه نویسی یا همان Visual Basic Editor برای انجام اینکار چند روش وجود دارد: · استفاده از کلیدهای میانبر: F11 + ALT ‌ ·


استفاده از منوی اکسل:

Tools-> Macro-> Visual Basic Editor · استفاده از ToolBar یا همان نوار ابزار Visual Basic و فشردن آیکون Visual Basic Editor به هر حال با یکی از روشهای فوق وارد محیط برنامه نویسی می‌شویم.


2- ایجاد Module:

ورود به منوی Insert و انتخاب گزینه Module 3- ایجاد یک رویه یا Sub Procedure نوشتن فرمان (کلمه کلیدی) Sub ‌ و سپس نام برنامه فشردن دگمه Enter بلافاصله بطور اتوماتیک فرمان End Sub در یک خط جدید اضافه می‌شود.


4- نوشتن کدهای برنامه VBA: کدهای برنامه را خط به خط بین فرمانهای Sub و End Sub می‌نویسیم. به عنوان مثال: Sub MyProgram() Range("A1:A10").Value = "Visual Basic For Applications" Range("A11")=10 Range("B11").Value = 20 Range("C11").Value = "=A11+B11" End Sub 5-


اجرای برنامه : برای اجرای برنامه چندین روش وجود دارد · فشردن کلید F5 · فشردن آیکن Rub Sub/User Form از Toolbar · بازگشت به محیط Excel و استفاده از


F11 + ALT 1.2

روش دیگر: ایجاد یک برنامه با استفاده از command button در این روش مراحل زیر را انجام می دهیم ü ایجاد command button برای قرار دادن command button بر روی محیط اکسل مسیر زیر را طی می‌کنیم:

View منوی àToolbaràControl ToolBox (البته اگر قبلا نوار ابزار Visual Basic Editor نیز فعال شده باشد میتوانید Control ToolBox را بطورمستقیم از آن انتخاب کنید.) بعد از طی مسیر فوق Control ToolBox ظاهر میشود.


command button را انتخاب و آنرا بر روی صفحه گسترده محیط اکسل اکسل قرار میدهیم. یک دگمه در محیط اکسل ظاهر میشود. فایل یکفایل دوم ü نوشتن کدهای VBA: با کلیک کردن بر روی command button بلافاصله Visual Basic Editor یا همان VBE ظاهر می‌شود. بلافاصله میتوانید دستورات خود را همانند شکل تایپ کنید. (شکل بعدی) فیال سه با دقت در این کدهای VBA ملاحظه می‌شود که: ü دستور اول مقدار(Value) سلولهای A1 تا A10را با عبارت "Visual Basic for Application" پر می‌کند.


ü دستور بعدی نیز حاصل جمع سلولهای A11 و B11 را در سلول C11 قرار میدهد. ü دستور آخر نیز مقدار سلول D11 را نیز با روش دیگری برابر با حاصلجمع A11 و B11 قرار میدهد. با اندکی دقت متوجه می‌شویم که این دستور با دستور قبلی متفاوت است. خروجی و رابط برنامه ü اجرای برنامه: برای اجرای این برنامه به محیط اکسل برمی‌گردیم. (میتوان از F11 + ALT ‌نیز استفاده کرد). با فشردن آیکن Exit Design Mode از Control Toolbox از حالت طراحی خارج می‌شویم. پس از آن با کلیک بر روی command button برنامه اجرا می‌شود که خروجی آن در شکل نشان داده شده است. دستور CELLS: این دستور با استفاده از مختصات سلولها آنها را مشخص میکند. به عنوان مثال CELLS(2,5) سطر 2 و ستون 5 یعنی E2را مشخص می کند.


در دستور: 'Cells(2,5).Value = 15 در سل 2و5 ارزش 15 را قرار می دهد دو دستور زیر یک مفهوم را در بر دارند: Cells(2,5).Select RANGE("E2").SELECT در دستور زیر Range(Cells(2, 5), Cells(1, 5)).Value = 10 در سلهای مشخص شده ارزش 10 را می گذارد دستور: Range(("A1"), Cells(1, 5)).Value = 10 یک دستور ترکیبی می باشد. که هم از Range استفاده شده و هم از دستور CELLS ملاحظه شد که به 2 صورت می توان سلها را از دو دستور دستورRANGE و دستور CELLS انتخاب کرد و از علامتهای با علامتهای ":" یا " ," استفاده نمود. یادداشت: راهنمای جامعی برای کار با سلولها و ناحیه تهیه شده که بطور جداگانه نیز آمده است. انتخاب سطر و ستون دو دستور زیر طریقه انتخاب سطر ستون و سطر کلی می باشد: Range("A:F").Value = 100 Range("1:5").Value = 100 که مشخص شده در آنها مقدار100 گذارده شود.در این دستورها تمامی ستونهای مابین و تمامی سطهای ما بین نیز ارزش مشخص شده را می گیرند. دستور زیر: Range("A:A,F:F").Value = 100 دستوری است که به برنامه می گوید ستون A و ستون F را به تنهایی ارزش 100 بگذار. می توان به جای ستونها از سطرها نیز استفاده نمود.


دستور زیر: Range("A1:A5,C1:C5,D15").Value = 4 را وارد کنید و نتیجه را با RUN نمودن ببینید. معرفی سلولها با استفاده از متغیرها دستور زیر را واد کنید: A = 1 B = 2 C = 3 Range(Cells(A, A), Cells(B, B)).Value = 6 که در این صورت متغیرهای A B C را می توان برای برنامه تعریف کرد و در دستور CELLS استفاده نمود.