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.
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.
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.
4) Right click on new button and select macro OR select macros from menu.
5) Following code window will appear :-
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.
8) Go to the button and click on it.
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