گروه مهندسی سودا
گروه مهندسی سودا
خواندن ۱۰ دقیقه·۲ سال پیش

ساخت نرم افزارهای مهندسی با اکسل

مقدمه ای بر Excel VBA

کلمه VBA مخفف جمله Visual Basic for Applications است. این یک ادغام از زبان برنامه نویسی رویداد محور مایکروسافت ویژوال بیسیک با برنامه های مایکروسافت آفیس مانند Microsoft Excel، Microsoft Word، Microsoft PowerPoint و غیره است.

با اجرای Visual Basic IDE در برنامه‌های مایکروسافت آفیس، می‌توانیم راه‌حل‌ها و برنامه‌های سفارشی‌سازی شده برای افزایش قابلیت‌های آن برنامه‌ها بسازیم.

در میان ویژوال بیسیک برای برنامه های کاربردی، Microsoft Excel VBA محبوب ترین است. دلایل زیادی وجود دارد که چرا ما باید VBA را برای مایکروسافت اکسل یاد بگیریم، از جمله آنها این است که شما می توانید اصول برنامه نویسی ویژوال بیسیک را در محیط MS Excel، بدون نیاز به خرید نسخه ای از نرم افزار مایکروسافت ویژوال بیسیک، یاد بگیرید.

دلیل دیگر یادگیری Excel VBA این موضوع می‌‍باشد که شما می توانید توابع سفارشی سازی شده را برای تکمیل فرمول ها و توابع داخلی مایکروسافت اکسل بسازید. اگرچه MS Excel دارای فرمول ها و توابع داخلی زیادی است، اما آنها برای محاسبات و برنامه های پیچیده خاص کافی نیستند.

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

شما می توانید کد Excel VBA را در هر نسخه از مایکروسافت آفیس، از جمله MS Office 97، MS Office2000، MS Office2002، MS Office2003، MS Office XP، MS Office 2007 و MS Offce 2010 بنویسید.

با استفاده از VBA، می توانید ابزارهای بسیار قدرتمندی بسازید. که در VBA را برای MS Excel با سرعت خود یاد بگیرید.

برنامه MS Excel شامل برنامه های مالی و علمی که می توانند محاسبات مالی و برنامه هایی را انجام دهند که می توانند تجزیه و تحلیل های آماری را انجام دهند.



https://sevdaa.ir/product/vba-in-excel/




ویرایشگر ویژوال بیسیک در اکسل

دو راه وجود دارد که می توانید آموزش برنامه نویسی در اکسل VBA را در MS Excel شروع کنید. اولین مورد این است که یک دکمه فرمان را روی صفحه گسترده قرار دهید و با کلیک بر روی دکمه فرمان برنامه نویسی را شروع کنید تا ویرایشگر ویژوال بیسیک راه اندازی شود.

راه دوم این است که ویرایشگر ویژوال بیسیک را با کلیک بر روی منوی ابزارها اجرا کنید، سپس از منوی کشویی گزینه Macro را انتخاب کنید و ویرایشگر ویژوال بیسیک را انتخاب کنید. اجازه دهید ابتدا با دکمه فرمان شروع کنیم. برای قرار دادن یک دکمه فرمان بر روی صفحه گسترده MS Excel، روی آیتم View در نوار منوی MS Excel کلیک کنید و سپس بر روی نوار ابزار کلیک کنید و در نهایت جعبه ابزار کنترل را انتخاب کنید که پس از آن نوار جعبه ابزار کنترل ظاهر می شود، همانطور که در شکل نشان داده شده است.

سپس بر روی دکمه فرمان کلیک کنید و آن را مانند تصویر در صفحه گسترده بکشید MS Excel

شکل 1.1: نمایش جعبه ابزار کنترل در MS Excel.
شکل 1.1: نمایش جعبه ابزار کنترل در MS Excel.
شکل 1.2: دکمه فرمان در حالت طراحی
شکل 1.2: دکمه فرمان در حالت طراحی


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

بیایید کد را در اینجا بنویسیم:


مثال 1.1

Private Sub CommandButton1_Click ()

Range (“A1:A10).Value=”Visual Basic “

Range (“C11”).Value=Range (“A11”).Value +Range (“B11”).

Value End Sub

دستور اول سلول A1 تا سلول A10 را با عبارت "Visual Basic" پر می کند در حالی که دستور دوم مقادیر سلول A11 و سلول B11 را اضافه می کند و سپس مجموع را در سلول C11 نمایش می دهد.

برای اجرای برنامه، باید با کلیک بر روی دکمه اکسل در گوشه سمت چپ نوار ابزار از ویرایشگر ویژوال بیسیک خارج شوید. زمانی که در محیط MS Excel هستید می توانید با کلیک بر روی دکمه طراحی از حالت طراحی خارج شوید و سپس بر روی دکمه فرمان کلیک کنید.


شکل 1.3: IDE ویرایشگر ویژوال بیسیک در MS Excel
شکل 1.3: IDE ویرایشگر ویژوال بیسیک در MS Excel

اجرای VBA فوق خروجی را همانطور که در شکل 1.4 نشان داده شده است به شما می دهد


شکل 1.4
شکل 1.4

کد های VBA اکسل

نوشتن کد VBA اکسل تقریباً دقیقاً مشابه نوشتن کد در ویژوال بیسیک است، به این معنی که باید از سینتکس های (به مجموعه قوانین نوشتاری درون یک زبان برنامه نویسی، Syntax (سینتکس) می گویند) مشابه ویژوال بیسیک استفاده کنید.

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

Range(“cell Name”).Value=K or Range(“Range of Cells”).Value=K

Where Value is the property of Range and K can be a numeric value or a string

مثال 1.2

Private Sub CommandButton1_Click ()

Range (“A1”).Value= “VBA”

End Sub

مثال بالا وقتی کاربر دکمه فرمان را فشار می دهد، متن "VBA" را در سلول A1 صفحه گسترده MS Excel وارد می کند. همانطور که در مثال 1.3 نشان داده شده است، می توانید از Range بدون خاصیت Value نیز استفاده کنید:

Example 1.3

Private Sub CommandButton1_Click ()

Range ("A1") = 100

End Sub

در مثال بالا، با کلیک بر روی دکمه فرمان، مقدار 100 را در سلول A1 صفحه گسترده MS Excel وارد کنید. مثال زیر نحوه وارد کردن مقادیر را به یک محدوده از سلول ها نشان می دهد:

Example 1.4

Private Sub CommandButton1_Click ()

Range ("A1:A10") = 100

End Sub

کار با متغیرها در Excel VBA

  • مفهوم متغیرها

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

  • نام متغیرها

قوانین زیر هنگام نامگذاری متغیرها در VBA آمده است

❖ باید کمتر از 255 کاراکتر باشد

❖ هیچ فاصله ای مجاز نیست

❖ نباید با عدد شروع شود

❖ پریود مجاز نمی باشد

  • اعلان متغیرها

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

  • انواع داده های عددی

انواع داده های عددی انواع داده هایی هستند که از اعداد تشکیل شده اند که می توان آنها را به صورت ریاضی با عملگرهای مختلف حسابی استاندارد مانند جمع، تفریق، ضرب، تقسیم و غیره محاسبه کرد. در VBA داده های عددی به 7 نوع تقسیم می شوند


  • انواع داده های غیر عددی

شما می توانید متغیرها را به صورت ضمنی یا صریح اعلام کنید. برای مثال sum=text1.text به این معنی است که متغیر sum به طور ضمنی و آماده دریافت ورودی در textbox1 اعلام شده است. نمونه های دیگر از اعلام ضمنی عبارتند از volume=8 و label=”Welcome”. از طرف دیگر، برای اعلان صریح، متغیرها معمولاً در بخش عمومی پنجره کد با استفاده از دستورات Dim اعلان می شوند. در اینجا نحو است:

متغیر Name را به عنوان DataType کم کنید

مثال 2.1

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.

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

برای رشته با طول متغیر، فقط از همان قالب مثال 2.1 بالا استفاده کنید. با این حال، برای رشته با طول ثابت، باید از قالبی که در زیر نشان داده شده است استفاده کنید:

Dim VariableName as String * n

جایی که n تعداد کاراکترهایی را که رشته می تواند نگه دارد را مشخص می کند. برای مثال، Dim yourName به عنوان رشته * 10 به این معنی است که yourName نمی تواند بیش از 10 کاراکتر داشته باشد.

مثال 2.2

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

Private Sub CommandButton1_Click()

Dim YourName As String, BirthDay As Date, Income As Currency YourName = "Alex"

BirthDay = "1 April 1980"

Income = 1000

Range("A1") = YourName

Range("A2") = BirthDay

Range("A3") = Income

End Sub

شکل 2.1: صفحه خروجی برای مثال 2.2
شکل 2.1: صفحه خروجی برای مثال 2.2

استفاده از Option Explicit

استفاده ازOption Explicit برای کمک به ما برای ردیابی خطاها در استفاده از نام متغیرها در کد برنامه است. به عنوان مثال، اگر ما یک اشتباه تایپی مرتکب شویم، VBE یک پیغام خطای "Variable not defined" را ظاهر می کند. در واقع، Option Explicit برنامه نویس را مجبور می کند تا همه متغیرها را با استفاده از کلمه کلیدی Dim اعلام کند. استفاده از Option Explicit تمرین خوبی است زیرا از استفاده از نام متغیرهای نادرست به دلیل اشتباهات تایپی جلوگیری می کند، به خصوص زمانی که برنامه بزرگتر شود. با استفاده از Option Explicit، در زمان ما در اشکال زدایی برنامه هایمان صرفه جویی می شود.

وقتی Option Explicit در کد برنامه گنجانده شده است، باید همه متغیرها را با کلمه کلیدی Dim delar کنیم. هر متغیری که اعلام نشده یا اشتباه تایپ نشده باشد باعث می شود برنامه پیغام خطای "متغیر تعریف نشده" را باز کند. قبل از ادامه اجرای برنامه باید خطا را تصحیح کنیم

این مثال از کلمه کلیدی Option Explicit استفاده می کند و نشان می دهد که چگونه یک اشتباه تایپی ردیابی می شود.

Option Explicit

Private Sub CommandButton1_Click()

Dim YourName As String,

password As String YourName = "John"

password = 12345

Cells(1, 2) = YourNam

Cells(1, 3) = password

End Sub

اشتباه تایپی YourNam است و پیغام خطای “variable not defined” نمایش داده می شود.

شکل 2.2: پیغام خطا به دلیل خطای تایپی
شکل 2.2: پیغام خطا به دلیل خطای تایپی


تخصیص مقادیر به متغیرها

پس از اعلام متغیرهای مختلف با استفاده از دستورات Dim، می توانیم مقادیری را به آن متغیرها اختصاص دهیم. قالب کلی یک تکلیف است

Variable=Expression

متغیر می تواند یک متغیر اعلام شده یا یک مقدار ویژگی کنترلی باشد. عبارت می تواند یک عبارت ریاضی، یک عدد، یک رشته، یک مقدار بولی (درست یا نادرست) و موارد دیگر باشد. در اینجا چند نمونه آورده شده است:

firstNumber=100

secondNumber=firstNumber-99

userName="John Lyan" userpass.Text = password

Label1.Visible = True

Command1.Visible = false

ThirdNumber = Val(usernum1.Text)

total = firstNumber + secondNumber+ThirdNumber

انجام عملیات حسابی در Excel VBA

برای محاسبه ورودی از کاربر و تولید نتایج در Excel VBA، می‌توانیم از عملگرهای ریاضی مختلفی استفاده کنیم. در Excel VBA، به جز + و -، نمادهای عملگرها با عملگرهای ریاضی معمولی متفاوت است، همانطور که در جدول 2.3 نشان داده شده است


مثال 2.4

Option Explicit

Private Sub CommandButton1_Click ()

Dim number1, number2, number3 as Single

Dim total, average as Double number1=Cells (1, 1).Value

number1=Cells (2, 1).Value

number3= Cells (3, 1).Value

Total=number1+number2+number3

Average=Total/3

Cells (5, 1) =Total

Cells (6, 1) =Average

End Sub

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


مثال 2.5

Option Explicit

Private Sub CommandButton1_Click()

Dim secondName As String, yourName As String

firstName = Cells(1,1).Value

secondName = Cells(2,1).Value

yourName = firstName + " " + secondName

Cells(3,1) = yourName

End Sub

در مثال بالا، سه متغیر به عنوان رشته اعلام شده است. متغیر firstName و متغیر secondName داده های وارد شده توسط کاربر را به ترتیب در سلول های (1،1) و سلول ها (2،1) دریافت می کنند. به متغیر yourName داده ها با ترکیب دو متغیر اول اختصاص داده می شود. در نهایت، yourName در سلول های (3، 1) نمایش داده می شود. انجام جمع روی رشته ها منجر به الحاق رشته ها می شود، همانطور که در شکل 2.3 زیر نشان داده شده است. اسامی در A1 و A2 به هم پیوسته و در A3 نمایش داده می شوند.

شکل 2.3: الحاق رشته ها
شکل 2.3: الحاق رشته ها


ویژوال بیسیکاکسلبرنامه نویسیبرنامه نویسی در اکسلvba
کاملترین بانک اطلاعاتی در زمینه آموزش طراحی تاسیسات برقی و مکانیکی ساختمان
شاید از این پست‌ها خوشتان بیاید