VBA stands for Visual Basic for Applications.
It is a combination of the Microsoft's event-driven programming language Visual Basic with Microsoft Office Applications such as Microsoft Excel.
VBA enables you to automate various activities in Excel like generating reports, preparing charts & graphs, doing calculations, etc. This automation activity is also often referred as Macro. This way it helps users to save their time spent behind running the repetitive steps.
Here we will see two different ways to execute VBA program in Excel
- Execute VBA program in a MsgBox
- Executing VBA program by using the click command button.
Don't worry if the code and the process to created Macro does not make much sense right now. We will re-visit these concepts in detail in succeeding tutorials.
In this tutorial, you will learn-
To execute VBA program, you have to have access to developer option in Excel. Enable the developer option as shown below and pin it into your main ribbon in Excel.
Step 1) Go to main menu "FILE" and selection option "Options."
Step 2) Select "Options" from the menu list as shown in screen shot below.
Step 3) Now another window will open, in that window do following things
- Click on Customize Ribbon
- Mark the checker box for Developer option
- Click on OK button
Step 4) Your developer tag should now appear in the main ribbon
First, we will see how we can create a command button on the spreadsheet and execute the program.
Now we will demonstrate how to program in VBA. All program in VBA has to start with "Sub" and end with "End sub". Here the name is the name you want to assign to your program. While sub stands for a subroutine which we will learn in the later part of the tutorial.
. End Sub
Step 1) Under Developer tab from the main menu, click on "Visual Basic" icon it will open your VBA editor.
Step 2) It will open a VBA editor, from where you can select the Excel sheet where you want to run the code. To open VBA editor double click on the worksheet.
It will open a VBA editor on the right-hand side of the folder. It will appear like a white space.
Step 3) In this step we going to see our fist VBA program. To read and display our program we need an object. In VBA that object or medium in a MsgBox.
- First, write "Sub" and then your "program name" (Guru99)
- Write anything you want to display in the MsgBox (guru99-learning is fun)
- End the program by End Sub
Step 4) In next step you have to run this code by clicking on the green run button on top of the editor menu.
Step 5) When you run the code, another window will pops out. Here you have to select the sheet where you want to display the program and click on "Run" button
Step 6) When you click on Run button, the program will get executed. It will display the msg in MsgBox.
In this section, we will see how to incorporate 'commandclick' button in VBA and execute a program using the button.
Step 1) In this step, click the option "insert button" from the Active X Control. Then select the command button option from it.
Step 2) To insert "clickcommandbutton1" drag the mouse cursor to Excel sheet.
Step 3) Then right click on the command button and select option "View Code".
Step 4) Check you are on the right sheet. A code editor will open. Enter your code.
- In code, we defined range A1 to A10.
- This range defines the cell number in the Excel sheet from "A1 to A10."
- Next, we define the string "Guru99 VBA Tutorial" in code
- So, when you click on "command button" in excel. It should print "Guru99 VBA Tutorial" in the cell range "A1 to A10."
Step 6) In Excel sheet, you will see Design Mode tab is on. Make sure it is "OFF" or else your code will not work.
Step 7) When design mode is off, there will be no green highlight on it. Now you can Click on the command button.
Step 8) Click on "CommandButton1". It will print "Guru99 VBA Tutorial" in the cell range "A1 to A10".
- VBA enables you to automate various activities in Excel like generating reports, preparing charts & graphs, doing calculations, etc.
- All program in VBA has to start with "Sub" and end with "End sub".