What is VBA?

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

  1. Execute VBA program in a MsgBox
  2. 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-

Enable Developer Option

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."

What is VBA?

Step 2) Select "Options" from the menu list as shown in screen shot below.

What is VBA?

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

What is VBA?

Step 4) Your developer tag should now appear in the main ribbon

What is VBA?

First, we will see how we can create a command button on the spreadsheet and execute the program.

How to write Macros in Excel

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.

Sub name()

.

.

. End Sub

Step 1) Under Developer tab from the main menu, click on "Visual Basic" icon it will open your VBA editor.

What is VBA?

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.

What is VBA?

It will open a VBA editor on the right-hand side of the folder. It will appear like a white space.

What is VBA?

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

What is VBA?

Step 4) In next step you have to run this code by clicking on the green run button on top of the editor menu.

What is VBA?

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

What is VBA?

Step 6) When you click on Run button, the program will get executed. It will display the msg in MsgBox.

What is VBA?

How to use ActiveX control in VBA

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.

What is VBA?

Step 2) To insert "clickcommandbutton1" drag the mouse cursor to excel sheet.

What is VBA?

Step 3) Then right click on the command button and select option "View Code".

What is VBA?

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."

What is VBA?

Step 5) In next step, save code file and then exit the editor. To return to the Excel file click the excel sheet icon What is VBA? on the extreme left.

What is VBA?

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.

What is VBA?

Step 7) When design mode is off, there will be no green highlight on it. Now you can Click on the command button.

What is VBA?

Step 8) Click on "CommandButton1". It will print "Guru99 VBA Tutorial" in the cell range "A1 to A10".

What is VBA?

Summary:

  • 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".