آموزش ماکرونویسی - برنامه نویسی در اکسل به روش من

از ماکرونویسی نهراسید! بسیار ساده است!

صفحه اصلي | آرشيو مطالب | تماس با من                    ali_fatehi@yahoo.com

آموزش  مشاورهداستان وبلاگ من

 

كتاب ماكرونويسي و برنامه نويسي كار بردي به زبان VBA در Excel

به نگارش اينجانب منتشر شد
مولف: علي فاتحي
ناشر: انتشارات سازمان بورس

 مراكز پخش

«نمايشگاه و فروشگاه انتشارات بورس»
شماره تماس:  88648192
(تهران - ميدان ونك - خ ملاصدرا- شماره 25)
اطلاعات كتاب و سفارش خريد آنلاين از انتشارات بورس

«انتشارات صانعي»

شماره تماس:  5385  6640
(
تهران- خ انقلاب- روبروي  دانشگاه تهران- پلاك 1290)
www.saneibook.com

 

.....در زماني كوتاه و به راحتي مي توانيد در اكسل برنامه و ماكرو بنويسيد؛ حتي اگر شما برنامه نويس نباشيد ماكرونويسي و برنامه نويسي ابزاري مناسب براي كاربراني است كه مي خواهند در Excel بهتر و پوياتر عمل كنند. زبان برنامه نويسي مورد استفاده در Excel بسيار شبيه زبانهاي محاوره اي است و VBA

 (Visual Basic for Applictions )
نام دارد.
......

20 صفحه اول كتاب را از اينجا برداريد.


اين كتاب براي كاربراني نوشته شده كه با برنامه‌نويسي و ماكرونويسي آشنايي نداشته يا آشنايي كمي دارند و مي‌خواهند در نرم‌افزار اكسل كارهاي بهتر و پوياتري انجام دهند و با زباني ساده و به دور از پيچيدگي مفاهيم اوليه و كاربردي را گام به گام ارايه نموده است. مثال‌ها متنوع و كاربردي‌اند. از برنامه‌هاي ساده  و "تابع تبديل عدد به حروف" گرفته تا "پياده‌سازي مفاهيم مهندسي مالي" در كتاب آموزش داده شده‌اند..

 

 

 

 

آخرين مطالب

حلقه ها

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

ساختار کلی این حلقه به شکل زیر ا ست: 

  For counter= start  To end  [Step step]

دستورات     

  Next [conter]

 مثال : برنامه ای می نویسیم که سلولهای  ناحیه A1 ‌تا A10 را به صورت یکی درمیان پر نماید:

 


ادامه مطلب

 

یک پنجره پیغام(Message Box) از سه بخش به شرح ذیل ساخته شده است:

 

عنوان پیغام                       , شرح پیغام                            گزینه های پیغام

Message box هایی که در طول یک برنامه به آنها نیاز داریم شامل دو گروه می باشد:

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

ساختار ساده اینگونه پیغامها به صورت زیر در محیط VBA به صورت زیر است:

Sub message()

MsgBox "hello my friends"

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  در قسمت میانی فرمول توجه کنید.

نکته: اطلاعات کامل در مورد Message Box در HELP اکسل آورده شده است .

 

اگر دربرنامه با توجه به مقادیرمشخصی که یک متغیر دارد تصمیم گیری شود از ساختار SELECT CASE استفاده می کنیم. این دستوربا کاربردی مانند دستور IFمی باشد.

برای استفاده ار این دستور شروط مورد نظر را بر روی یک متغیر اعمال می کنیم. 

 

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

 

Select Case  نام متغیر

 Case           حالت اول

دستورات

 Case حالت دوم

دستورات

.

.

.

 

Case Else

دستورات

 End Select

 


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

 

 


ادامه مطلب

 

برنامه هایی که تا اینجا نوشته شدند همگی دارای کدهایی بودند که پشت سر هم انجام میشدند.

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

 

ساختار تصمیم یا شرطی IF

 

  • ساختار اولیه وساده: 

 

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

1.              مفهوم متغیر

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

1-    نام متغیر

2-    نوع داده ای(Data Type)

1.1        نامگذاری متغیر

برای نامگذاری یک متغیر قواعد زیر می بایست رعایت گردند:

  • نام هر متغیر کمتر از 255 کاراکتر می باشد

  • فاصله خالی(space) مجاز نمی باشد

  • نقطه  مجاز نمی باشد

  • نام متغیر با عدد شروع نمی شود

  • برخی کاراکترها مانند ! ،  @ ، # ، $ ، % ، ^ ، & ، * ، ) ، ( ، +   نمی توانند مورد استفاده قرار گیرند

 

 

مثال برای نامهای معتبر و نامعتبر

نام معتبربرای یک متغیر

نام نامعتبر برای یک متغیر

My_Car

My.Car 

ThisYear

1NewBoy

Long_Name_Can_beUSE

He&HisFather                  *& is not acceptable

Group88

Student ID                       * Spacing not allowed

 

1.2        نوع داده ای(Data Type)

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

الف- نوع داده ای عددی( Numeric Data Type) : این شکل از داده ها قابلیت محاسباتی دارند و می توانند توسط عملگرهایی نظیر چهار عمل اصلی مورد استفاده قرار گیرند. در VBA این گروه شامل 7 نوع داده مطابق جدول زیر است:


 

Numeric Data Types

Data Type

Storage 

Range of Values

Byte

1 byte

0 to 255

Integer

2 bytes

-32,768 to 32,767

Long 

4 bytes

-2,147,483,648 to 2,147,483,648

Single

4 bytes

-3.402823E+38 to -1.401298E-45 for negative values
1.401298E-45 to 3.402823E+38 for positive values.

Double

8 bytes

-1.79769313486232e+308 to -4.94065645841247E-324 for negative values
4.94065645841247E-324 to 1.79769313486232e+308 for positive values.

Currency

8 bytes

-922,337,203,685,477.5808 to 922,337,203,685,477.5807

Decimal

12 bytes

+/- 79,228,162,514,264,337,593,543,950,335 if no decimal is use
+/- 7.9228162514264337593543950335 (28 decimal places).

 

ب- نوع داده ای غیر عددی ( Noun-Numeric Data Type)

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

 

Noun-Numeric Data Types

Data Type

Storage

Range

String(fixed length)

Length of string

1 to 65,400 characters

String(variable length)

Length + 10 bytes

0 to 2 billion characters

Date

8 bytes

January 1, 100 to December 31, 9999

Boolean

2 bytes

True or False

Object

4 bytes

Any embedded object

Variant(numeric)

16 bytes

Any value as large as Double

Variant(text)

Length+22 bytes

Same as variable-length string

در VBA برای معرفی متغیر نیاز است تا نام و نوع داده ای به آن تخصیص یابد.

1.3        معرفی متغیرها

برای معرفی یک متغیر لازم است نام متغیر و نوع داده ای آن را  مشخص کنیم. (البته می توان از نوع داده ای صرفنظر کرد که در اینصورت نوع داده ای  Variantفرض میشود) برای این منظور از دستور Dim به شکل زیر استفاده می شود:

Dim variableName as DataType

 

مثال:

Dim password As String Dim yourName As String

Dim firstnum As Integer

Dim secondnum As Integer

Dim total As Integer

Dim BirthDay As Date

می توان معرفی متغیرها را به شکل زیر ترکیب نمود

Dim password As String, yourName As String, firstnum As Integer.

متغیر های تاریخ در داخل #  # و متغیرهای حروفی در داخل "   " گذارده می شود.

در مثال بعدی سه نوع متغیر رشته ای ، تاریخی و ارز آورده شده است.

Private Sub CommandButton1_Click( )

Dim YourName As String

Dim BirthDay As Date

Dim Income As Currency

YourName = "Sadeghi"

BirthDay = #1 April 1980#

Income = 10000000

Range("A1") = YourName

Range("A2") = BirthDay

Range("A3") = Income

End Sub

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

 

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

توضیح و یا دآوری:

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

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

 

Sub dll()

 

www.alifatehi.persianblog.ir

 

 

    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

    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

    Range("B1").Select

 

End Sub


 

1. کار با سلولها و ناحیه ها

(Cells and Ranges objectsَ)

از رایج ترین و پرکاربردترین کارها در اکسل کار با  سلولها و ناحیه ها  است. انتخاب یک ‍cell ‌ یا Range، وارد نمودن فرمول، تغییر رنگ و قلم و‌ وارد نمودن یک عدد از جمله کارهایی است که می‌توان در مورد آنها انجام داد. معمولا در ابتدا Range ‌ را مشخص و سپس یکی از ویژگیهای(Propertires) آن را تنظیم کرده  یا یک متد(Method) را بر آن اعمال می‌کنیم.

یک Range می‌تواند شامل تنها یک سلول یا چندین سلول باشد. این سلولها می‌توانند در کنار یکدیگر یا بطور پراکنده باشند. در ادامه متداول ترین روشهای تعریف و کار با شی Range  ارایه می‌شود

.


ادامه مطلب

1.   شروع برنامه نویسی

 

روشهای مختلفی برای شروع و ایجاد یک برنامه با VBA وجود دارد. یک روش ساده ایجاد یک command button بر روی صفحه گسترده محیط اکسل و شروع برنامه نویسی با کلیک بر روی  آن است. روش دیگر نوشتن توابع در داخل  ویرایشگر VB  یا همان     VBE  می باشد. کار را با روش اول شروع می کنیم

1.1      روش اول : ایجاد یک برنامه با استفاده از command button

 

برای قرار دادن  command button بر روی محیط اکسل مسیر زیر را طی می کنیم:

View منوی  àToolbaràControl ToolBox

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

بعد از طی مسیر فوق Control ToolBox ظاهر میشود. command button را انتخاب و آنرا بر روی صفحه گسترده محیط اکسل اکسل قرار میدهیم. یک دگمه در محیط اکسل ظاهر میشود.

با کلیک کردن بر روی command button  بلافاصله  Visual Basic Editor یا  همان  VBE  ظاهر می شود. بلافاصله میتوانید دستورات خود را همانند شکل تایپ کنید. (شکل بعدی)

با دقت در این شکل ملاحظه میشود که:

دستور اول در نظر دارد مقدار(Value) سلولهای A1 تا  A10را با عبارت "Visual Basic for Application" پرکند.

دستور بعدی نیز حاصلجمع سلولهای  A11  و B11 را در سلول C11 قرار میدهد.

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

 

 خروجی و رابط برنامه

 

 

 

 

برای اجرای این برنامه به محیط اکسل برمی گردیم. (میتوان از F11  +   ALT نیز استفاده کرد).

با فشردن آیکن Exit Design Mode   از    Control Toolbox از حالت طراحی خارج می شویم. پس از آن با کلیک بر روی  command button برنامه اجرا می شود که خروجی آن در شکل نشان داده شده است.

 

1.2      ایجاد یک برنامه ساده  در  VBE

برای نوشتن یک برنامه در محیط برنامه نویسی اکسل مراحل زیرانجام می شود:

1- ورود به محیط برنامه نویسیی یا همان Visual Basic Editor

 برای اینکار چند روش وجود دارد

·        استفاده از کلیدهای میانبر:  F11  + ALT

·        استفاده از منوی اکسل:  Tools-> Macro-> Visual Basic Editor

·        استفاده از ToolBar یا همان نوار ابزار Visual Basic و فشردن آیکون Visual Basic Editor

به هر حال با یکی از روشهای فوق وارد محیط برنامه نویسی می شویم

2- ورود به  منوی  Insert  و انتخاب گزینه Module  

3- نوشتن فرمان (کلمه کلیدی)  Sub ‌ و سپس نام برنامه

4- فشردن دگمه  Enter   

5- بلافاصله بطور اتوماتیک فرمان End Sub  در یک خط جدید اضافه می شود.

6- کدهای برنامه را خط به خط  بین فرمانهای Sub  و End Sub می نویسیم.

 

 به عنوان مثال:

 

Sub MyProgram()

Range("A1:A10").Value = "Visual Basic For Applications"

Range("A11").Value=10

Range("B11").Value = 20

Range("C11").Value = "=A11+B11"

End Sub

 

ملاحظه می شود که در سطر C11 ارزشهای موجود درA11   و B11 جمع زده می شود.

به علامت = در داخل کوتیشن توجه شود.

 

 

7-اجرای برنامه : برای اجرای برنامه چندین روش وجود دارد

·        فشردن کلید F5

·        فشردن آیکن Rub Sub/User Form از   Toolbar

·        بازگشت به محیط   Excel و استفاده از F11  + ALT

 

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

1.3        Select  , Value  , Rangeدستورات

 

دستور  Range برای مشخص کردن یک ناحیه در اکسل استفاده می شود.

 

با انجام دستور  Select سلهای مورد اشاره انتخاب می شود. می توان بعد از  Rrange از این  دستور استفاده نمود.

دستور زیر را وارد کنید:

Range("F1:F10").Select

 

و دکمه  RUN  را بزنیم، ملاحظه می شود که سطرهای مورد ازF1 تا  F10 اشاره های انتخاب میشوند.

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

 

VALUE=

 برای دادن مقدار از این دستور استفاده می کنیم.

 

مواردی که در بالا اشاره گردید خلاصه ای از سه دستور  ,RANGE VALUE,  SELECTبود که اشاره ای مختصر به آن گردید. در اینجا مقدمه ای بر برنامه نویسی ارایه گردید و در جلسات بعدی با برنامه نویسی بیشتر آشنا خواهیم شد. 

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

 


ادامه مطلب

 

1.          مقدمه

VBA  که مخفف عبارت   Visual Basic For Applications یک زبان برنامه‌نویسی است که توسط شرکت نرم افزاری مایکروسافت طراحی شده است.  VBA در Excel و سایر نرم افزارهای آفیس گنجانده شده است.

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

VBA  با Visual Basic) VB) تفاوت دارد. اگر چه این دو شباهاتهایی هم با هم دارند ولی کاربردهای آنها متفاوت است. مهارت در VB به معنای مهارت در VBA نمی باشد اگر چه کار را برای یادگیری و پیشرفت در VBA هموار می کند. از سوی دیگر هر قدر در نرم افزارهای آفیس مهارت بیشتری داشته باشد در VBA توانایی و مهارت بیشتری خواهید داشت.

موارد استفاده از  VBA

·         خودکارسازی(automating) کارهای دستی و طولانی

·          خودکارسازی(automating) امور تکراری

·          سفارشی کردن و ایجاد رابط با کاربر(با استفاده از دگمه ها و منوها)

·          ایجاد توابع جدید که در اکسل وجود ندارد

·       ارتباط با سایر برنامه های office   مانند Word‌ و Access و Powerpoint

 2.            ایجاد ماکرو

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

Macro recorderمانند یک ضبط عمل می کند. در واقع از زمانیکه دگمه record ‌ زده میشود شروع به ضبط  ریز به ریز کلیه عملیات نموده و تا زمانیکه دگمه Stopزده شود اینکار را ادامه میدهد.

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

فیلم آموزشی زیر مراحل کار را خیلی ساده و سریع نمایش میدهد.

 

 http://alifatehi.persiangig.com/video/ExcelMacros.wvx 

 


ادامه مطلب
.: Weblog Themes By Iran Skin :.

درباره وبلاگ

آرشيو مطالب
امکانات وب