How to use VBA Script in Excel?

|
| By Webner

How to use VBA Script in Excel?

VBA (Visual Basic for Application) is a high-level programming language that is used for MS Excel. It uses English like statements to write instructions for the computer. It also enhances the functionality of the excel by allowing the script to access excel sheets data.

VBA Basics:-

Basics of VBA is as same as any programming language i.e
1) Variables
2) Arithmetic Operators
3) Logical Operators

To start working in VBA in Excel, first, we have to enable Developer option in Excel.

Steps to enable Developer Option:-

1) Open excel.
2) Create new workbook.
3) Go to options.
Use VBA Script in Excel

4) Click on Customize ribbon.
5) Select Developer checkbox.
6) Click Ok.

Note:- There will now be a visible developer tab in the Menu.

Steps to write Macro/Program in the Excel :-

1) Select Developer option in the Menu.
Use VBA Script in Excel

2) Click on Insert and select a button (Drag the command button anywhere on worksheet).

3) Write the name of Macro in the dialog window.
Use VBA Script in Excel

4) Right click on new button and select macro OR select macros from menu.

5) Following code window will appear :-
Use VBA Script in Excel

6) Write the code and click on save button.
Dim name As String
name = InputBox(“Enter your name”)
MsgBox “Hello ” + name

7) Close the code window.
Use VBA Script in Excel

8) Go to the button and click on it.
Use VBA Script in Excel

Note:- An input box will appear on click of the button. Enter the value and click ok. Then a message box will appear with the message Hello and value entered in the inputbox.

Basic Syntax of writing the code in VBA excel:-

1) Create a function:-
Sub functionName()
Code…..
Code…..
End Sub

2.) Define a variable:-
Dim a As single
i.e. Dim is a keyword , a is the name of the variable , As is a keyword , single is the data type
Note:- We can also define multiple variables in one line
For example:- Dim a As single, b As double

3.) Define loops:-
For loop
For i = 1 to 5
Statements…..
……….
Next i
Do while loop
Do While i < 6 Statement…… …….. Loop 4.) Writing comments:-
Use ‘ operator to write a value in excel script
‘ This is a comment
5.) Assignment of a value to a variable:-
Dim a As Single
a = 5;
Or
a = InputBox(“Enter a value”) ‘input box appears and asks for a value

Leave a Reply

Your email address will not be published. Required fields are marked *