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


+ جلسه دوازدهم- عملیات بر روی رشته ها( STRING)

جلسه دوازدهم                 کار با رشته ها(STRING)

 

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

 

الف- عملگر &

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

مثال اول: برنامه ای خواهیم نوشت که در یک 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

 

د- تابع 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

نویسنده : حامد بیرامی ; ساعت ٧:٢٧ ‎ق.ظ ; ۱۳۸۸/۱۱/۱
comment نظرات () لینک

+ جلسه یازدهم - ادامه Function سازی در اکسل

جلسه یازدهم                ادامه function

 

در جلسه گذشته دیدیم که چگونه می توان در محیط اکسل یک 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 را انتخاب می کنیم و در پنجره موجود کنار برنامه ای که نوشته شده است را تیک می زنیم.از هم اکنون برنامه جدید نوشته شده ما به عنوان تابع ثابتی در اکسل مان قابل استفاده است. 

نویسنده : حامد بیرامی ; ساعت ٦:٢٠ ‎ق.ظ ; ۱۳۸۸/۱۱/۱
تگ ها:
comment نظرات () لینک

+ جلسه دهم- ادامه مبحث DO-LOOP آغاز FUNCTION نویسی در اکسل

جلسه دهم

ادامه 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)

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

نویسنده : حامد بیرامی ; ساعت ۱:٠٢ ‎ب.ظ ; ۱۳۸۸/۱٠/۳٠
comment نظرات () لینک

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

جلسه نهم:

 

در این جلسه می خواهیم پروسه ایجاد یک شمارنده(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  استفاده می شود حلقه به محض وقوع شرط حلقه خاتمه می یابد( به عبارتی حلقه تا زمانیکه شرط برقرار نیست ادامه پیدا می کند)

نویسنده : حامد بیرامی ; ساعت ۱۱:۳۱ ‎ق.ظ ; ۱۳۸۸/۱٠/٢۱
تگ ها:
comment نظرات () لینک

+ جلسه هشتم- حلقه های 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   برنامه ایجاد نمود.

نویسنده : حامد بیرامی ; ساعت ۱۱:۳٠ ‎ق.ظ ; ۱۳۸۸/۱٠/٢۱
تگ ها:
comment نظرات () لینک

+ جلسه هفتم-ساخت 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 اکسل آورده شده است .

نویسنده : حامد بیرامی ; ساعت ۱۱:٢۸ ‎ق.ظ ; ۱۳۸۸/۱٠/٢۱
تگ ها:
comment نظرات () لینک

+ جلسه ششم- ساختار برنامه 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


ساختار حلقه ای 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

نویسنده : حامد بیرامی ; ساعت ۱۱:٢٦ ‎ق.ظ ; ۱۳۸۸/۱٠/٢۱
تگ ها:
comment نظرات () لینک

+ جلسه پنجم- دستور شرطی 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 شرط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

نویسنده : حامد بیرامی ; ساعت ۱۱:٢٥ ‎ق.ظ ; ۱۳۸۸/۱٠/٢۱
تگ ها:
comment نظرات () لینک

+ جلسه چهارم- متغیرها در ویژوال بیسیک

جلسه چهارم:

 

پاک کردن اطلاعات با استفاده از برنامه نویسی:

اطلاعاتی را در یک شیت اکسل وارد می کنیم

روش اول بدین صورت است که می توان یک ماکرو برای آن نوشت و بر روی سل نوشته شده راست کلیک کرده و گزینه 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

نویسنده : حامد بیرامی ; ساعت ۱۱:٢۳ ‎ق.ظ ; ۱۳۸۸/۱٠/٢۱
تگ ها:
comment نظرات () لینک

+ جلسه سوم- شروع برنامه نویسی در اکسل

جلسه سوم                                   برنامه نویسی در اکسل

 

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

از ابزار های 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  استفاده نمود.

 

 

 

 

نویسنده : حامد بیرامی ; ساعت ۱٠:٥٤ ‎ق.ظ ; ۱۳۸۸/۱٠/٢۱
تگ ها:
comment نظرات () لینک

← صفحه بعد