جلسه هشتم- حلقه های FOR -NEXT
جلسه هشتم:
حلقه ها
جهت انجام کارهای تکراری از حلقه ها استفاده می شود. در ابتدا با حلقه های 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 برنامه ایجاد نمود.
تگ ها:
+ جلسه هفتم-ساخت MSSAGE BOX در محیط اکسل با استفاده از برنامه ویژوال بیسیک
جلسه هفتم:
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 اکسل آورده شده است .
تگ ها:
+ جلسه ششم- ساختار برنامه SELECT CASE در اکسل
جلسه ششم:
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
ساختار حلقه ای FOREach - NEXT
حلقه ها دستوراتی هستند که می توان جهت انجام دستورات تکراری از آنها استفاده نمود. حلقه FOREach برای کار بر روی عضوهای(members) یک مجموعه(collection) استفاده می شود. مجموعه شامل تعدادی شیء یکسان است. به عنوان مثال Range("A1:A10") یک collection است زیرا تعدادی عضو به نام سلول است که همگی از یک جنس هستند. worksheet های یک فایل اکسل مجموعه ای به نام Worksheets تشکیل می دهند. چارتهای یک فایل اکسل مجموعه ای به نام Charts تشکیل می دهند.
الگوی زیر الگوی استفاده از یک حلقه FOREach در برنامه نویسی می باشد:
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
تگ ها:
+ جلسه پنجم- دستور شرطی IF در محیط ویژوال بیسیک
جلسه پنجم دستورهای شرطی(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 شرط1then
دستورات
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
تگ ها:
+ جلسه چهارم- متغیرها در ویژوال بیسیک
جلسه چهارم:
پاک کردن اطلاعات با استفاده از برنامه نویسی:
اطلاعاتی را در یک شیت اکسل وارد می کنیم
روش اول بدین صورت است که می توان یک ماکرو برای آن نوشت و بر روی سل نوشته شده راست کلیک کرده و گزینه clear contact را انتخاب نمود.
روش دوم بدین صورت است که از فرمول زیر استفاده نمود:
Range("B6:D14").ClearContents
متغیرها(Variables)
گاهی اوقات در فرمول نویسی در اکسل نیاز به متغیر می شود
عددی حروفی تاریخ متغیرهای منطقی
انواع متغیر:
Stringحروفی
Integer عددی
Date
منطقی(boolean) که مقادیر True یا False را می گیرد.
نام متغیر:
اسم متغیر حداکثر 256 کرکتر باید باشد و در ابتدای آن عدد نباید باشد و از . – و فاصله نمیتوان در نام عنوان متغیر استفاده نمود.
معرفی متغیر:
برای معرفی متغیر به شکل زیر عمل می کنیم:
Dim نام متغیر As نوع متغیر
Dim password As String, birthday As Date
متغیر های تاریخ9 در داخل # # و متغیرهای حروفی در داخل " " گذارده می شود.
مثال :فرض کنیم سه شیت داریم که در هر سه شیت اطلاعات داریم. می خواهیم اطلاعات شیت یک را پاک کرده و شیت دو را در شیت یک قرار داده و اطلاعات شیت سوم را از انتهای شیت دوم بنویسد:
برای این منظور ابتدا ماکرویی را ایجاد می کنیم و ایده های اصلی را به بهره گیری از کدهای این ماکرو می گیریم و برای نوشتن برنامه استفاده می نماییم.
توضیح و یا دآوری:
فرومول (COUNT(A:A)= در اکسل تعداد ردیفهای پرشده در درستون a را می دهد.
پس از نوشتن ماکرو وارد محیطVBA می شویم و می بینیم که اطلاعات زیر نوشته شده است:
Sub dll()
dll Macro
Macro recorded 2009/12/20 by hamed.beirami
' Keyboard Shortcut: Ctrl+g
Range("A1:A32").Select
Selection.ClearContents
Sheets("Sheet2").Select
Range("A1:A30").Select
Selection.Copy
Sheets("Sheet1").Select
Range("A1").Select
ActiveSheet.Paste
Sheets("Sheet3").Select
ActiveWindow.SmallScroll Down:=-54
Range("A1:A70").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Range("A31").Select
ActiveSheet.Paste
End Sub
مساله موجود این است که در انتهای اطلاعات وارد شده در شیت اول(پس از دلیت کردن) اطلاعات شیت سوم را وارد نماید.برای این منظور باید تغییراتی در ماکرو نوشته شده در اکسل ایجاد کرد و با استفاده از اطلاعات ستون c و تعریف دو متغیر x1 ,x2 و اضافه نمودن در سطرهای برنامه نوشته شده به این هدف رسید. بخش اصلی برای حل این مساله استفاده از روش آدرس دهی مختصاتی برای معرفی ناحیه ها است . زیرا نواحی که انتخاب می شوند تغییر میکنند و می بایست از دستور ٍٍ cells(i,j) استفاده کنیم
Sub rep()
Range("A:a").Select
Selection.ClearContents
Sheets("Sheet2").Select
Dim x1, x2, x3
(x1 = Cells(1, 3
Range("A1", Cells(x1, 1)).Select
Selection.Copy
Sheets("Sheet1").Select
Range("A1").Select
ActiveSheet.Paste
Sheets("Sheet3").Select
( x2 = Cells(1, 3
Range("A1", Cells(x2, 1)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
x3 = x1 + 1
Cells(x3, 1).Select
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=-3
Range("B1").Select
End Sub
تگ ها:
+ جلسه سوم- شروع برنامه نویسی در اکسل
جلسه سوم برنامه نویسی در اکسل
ایجاد دکمه جدید در صفحه اکسل:
از ابزار های 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,D1:D5").Value = 4
را وارد کنید و نتیجه را با RUN نمودن ببینید.
معرفی سلولها با استفاده از متغیرها
دستور زیر را واد کنید:
A = 1
B = 2
C = 3
Range(Cells(A, A), Cells(B, B)).Value = 6
که در این صورت متغیرهای A B C را می توان برای برنامه تعریف کرد و در دستور CELLS استفاده نمود.