VBA in Excel | How to create Visual Basic for Applications

What is VBA in Excel?

Visual Basic for Applications (VBA) is a programming language developed by Microsoft to enhance office applications. VBA helps to develop automation processes and user-defined functions in Microsoft excel. It also helps in manipulating the user interface features of the applications.

What is a layman’s language in a computer?

To perform an activity on the computer, you have to tell the commands to the computer and these commands are said in layman’s language. Layman’s language is very simple and easy to understand.

Computer programming is a set of actions we tell the computer to do. You can also use english statements to tell the computer what to do and these statements fall in the category of high level languages. VBA is a high-level language that is used to develop powerful excel programming.

Why VBA?

In VBA, we can write the instructions in English for creating the applications. VBA is one of the programming languages which is easy to learn and it also has an user interface which is user friendly in such a way that the user can drag and drop the interface controls. 

Applications of VBA

MS-Excel itself provides a lot of inbuilt functions but they provide only the essential and common functions. Those functions would not be the solution to our complex calculations. To overcome this, we use VBA as it is one of the most transparent solutions.

For example, saving each worksheet as a pdf seems to be a tiresome task. But, it’s easy to do if we use VBA.

VBA Terminologies

1. Modules

The place where the user writes the code is called modules.

To insert a module, follow the step:

Go to insert and click on the module. Once this is done, the window creates  ‘module1’. Inside a module, a user can write the VBA code with procedures followed. The procedure is the series of VBA statements instructing what to do.

2. Procedure

Group of statements which instructs Excel, what and how to perform are procedures.  The task can either be simple or complex and it all depends on the procedure. The good practise is to keep the code simple and small. Sub and Function are two main types of procedures.

a. Function

A group of statements which is reusable and can be called anywhere in the program is known as function. This completely saves the time by eliminating the need of writing the same logic as code again and again. This widely helps the programmers to split the big programs into small and simpler functions. The user can write user-defined functions and those statements are segmented between Function and End Function.

b. Sub-Procedures

Sub-procedures perform the work more like functions. The function can or cannot return a value and the sub-procedures do not return a value. They are also called even without a call keyword and they are written between the sub and end substatements in the code editor. 

3. Comments

The user comments in the program for user information and better understanding. It includes information about the programming and the author of the program. Comments are ignored while executing the program.

There are two ways to insert comments in the code:

• Any statement which starts with a single quote(‘)
• Any statement which starts with the keyword “REM”

Introduction to Visual Basic for Applications

Let’s get into the basics of VBA:

1. Variable

Declaration of variables are required for memory locations. 

Rules for creating variables:

a. No spaces between the variable name: 

A variable name could not contain space in between the words. So, if you want to create a variable such as cost price, then you have to type the variable name as cost_price.

b. Use a meaningful name:

Meaningful names makes your code easy to read and it looks more professional.

c. Avoid using reserved words: 

Reserved words have special meaning in excel and hence it is not accessible as variable names. Example: lecturer

2. Arithmetic operators in VBA

The BODMAS rule applies here also, so consider it while working with the expressions using multiple different arithmetic operators. The types of operators are:

3. Logical operators in VBA

Logical operators are also considered and they can also be included in the VBA code. They are:

a. If condition

b. OR condition

c. NOT condition

d. AND condition

e. TRUE condition

f. FALSE condition

VBA Editor

To work with VBA in Excel, the user should know about the Visual Basic Editor also. The Visual Basic Editor is also known as VBA Editor, VB Editor, VBE EDITOR. 

Visual Basic for Applications editor is a powerful tool and it is a separate application. It opens up whenever the user opens the workbook and by default, it’s hidden and so the user has to activate it to access it. VBA Editor is an interface for writing scripts.

Accessing the VBA Editor:

We can access the VBA editor using 3 methods.

a. Using a Keyboard Shortcut

b. Enabling the developer tab

c. Enabling from worksheet tab

a. Using keyboard shortcut

It is one of the easiest and simplest methods to access the VBA editor.

• Press Alt+F11 key to open a separate window for the VBA editor.
• When you press again, it takes you back to the spreadsheet.

NOTE: The shortcut key for mac workbooks is either Opt + F11 or Fn + Opt + F11.

b. Enabling the Developer Tab

To enable the developer tab in Excel, follow the below steps:

• Click on the file tab and choose options from the left side of the panel.
• Click on the customize ribbon label from the left side panel of the dialog box.
• Select the developer checkbox and finally click the OK button.

c. Enabling from worksheet tab

We can view the worksheet’s code from the sheet tab also. In order to do that, right-click on the worksheet and choose the view code option.

VBA – My First Program

Now we will see how to program in VBA programming language. All the programs in VBA start with “Sub” and end with “End sub”. Here, the sub stands for a subroutine and the name is what you have to assign to your program.

Sub name()
**write your program **
End Sub

VBA from Excel:

Step – 1: Go to the Developer tab and click on the visual basic icon.

Step – 2: The VBA editor opens up and from there you can select the Excel sheet where you want to run the code. To open a particular sheet VBA editor, double click on that worksheet.

It will open a blank VBA editor on the right side of the dialog window. 

Step – 3: To read and display a program, we need an object in a messagebox.

a. Write “Sub” and then the “program name” i.e., DataFlair here.

b. Write a message to display it in the MsgBox  i.e., Welcome to DataFlair!

c. End the program by End Sub.

Step – 4: Run this code by clicking on the run button on the top of the editor menu.

Step – 5: When you run the code, another window pops out. Here, select the worksheet where you want to display the message and click on the “Run” button from the dialog box.

Step – 6: The program will get executed and the message displays in a messagebox.

Input Box

The user can enter the values in the input box. After entering the values, if the user clicks on the OK button, the text in the text box will be returned. The user can also press enter to return the value in the text box or if the user clicks on the cancel button, then the function returns an empty string(“”).  

Syntax

InputBox( prompt[, title][, default][, xposition][,yposition][, helpfile, context])

Parameter Description

Prompt −This is one of the required parameters. This is actually a string which is displayed as a message in the message dialog box. The maximum length is approximately 1024 characters. If a message extends more than a line, then it can be separated by a carriage return character (Chr(13)) or a linefeed character (Chr(10)) .

Title − This is one of the optional parameters. The title is a string expression and this expression appears as the title bar of the dialog box. The title considers the application name as the title if it is not provided. 

Default − This is one of the optional parameters. This parameter helps to display the default text in the text box. 

xposition − This is one of the optional parameters. This represents the input box distance from the left side of the screen horizontally. If this parameter is not provided, the input box appears at the center.   

yposition − This is one of the optional parameters. This represents the input box distance from the left side of the screen vertically. If this parameter is not provided, the input box appears at the center.   

Helpfile − This is one of the optional parameters. The helpfile is a string expression which identifies the Help file to use for the dialog box.

context − This is one of the optional parameters. The context is a numeric expression which identifies the assigned help context number to the appropriate help topic. If this parameter is provided then a helpfile must also be provided.

We will now see how to create a basic VBA program that displays an input box to ask for the user’s name and  display a message

Once, you have enabled the DEVELOPER tab in excel:

• Open a new workbook.
• Save it as excel macro enabled worksheet format *.xlsm
• Click on the DEVELOPER tab.
• Click on INSERT from the ribbon.
• Select a command button from the drop down menu.

•  Draw the command button anywhere on the worksheet, a dialogue window appears.
• Rename the macro name to DataFlair.
• Click on the new button.

• A VBA code window appears.

Enter the following instruction codes:

Dim username As String
username = InputBox("Enter your name")
MsgBox "Welcome to DataFlair,  " + username

Here,

• “Dim username as String” creates a variable called username. 
• “username = InputBox(“Enter your name”)” . This builts an InputBox function which displays a window with the caption “Enter your name”. 
• “MsgBox” holds the message, Welcome to DataFlair,  ” + username”  and this function displays a message box which says Welcome to DataFlair, username.
• Close the code window.
• Edit the text by right clicking on the button.

• The button is now renamed to hey and now, click on the button.
• An input box appears and there enter your name  i.e. Lexi here.

• You will get the following message box

• Finally, now we know how to create a program in excel using vba.

VBA MsgBox

A message box appears in the excel workbook and this function helps in creating a message box. The message box is created in such a way that it waits for the user to click on a button and the activity is performed according to the button the user clicks.  

Syntax

MsgBox (prompt [, buttons ][, title ][, helpfile, context ])

Parameter Description

Prompt − This is one of the required parameters. This is actually a string which is displayed as a message in the message dialog box. The maximum length is approximately 1024 characters. If a message extends more than a line, then it can be separated by a carriage return character (Chr(13)) or a linefeed character (Chr(10)) .

Buttons − This is one of the optional parameters. The buttons are created by the numeric expression and these expressions specify the type of buttons to display in the MsgBox, the icon style and the default button identity of the message box. The default value for button is 0.

Title − This is one of the optional parameters. The title is a string expression and this expression appears as the title bar of the dialog box. The title considers the application name as the title if it is not provided. 

Helpfile −  This is one of the optional parameters. The helpfile is a string expression which identifies the Help file to use for the dialog box.

Context − This is one of the optional parameters. The context is a numeric expression which identifies the assigned help context number to the appropriate help topic. If this parameter is provided then a helpfile must also be provided.

The Buttons parameter can take any of the following values −

Return Values in VBA

To know which button the user has clicked, the msg box function returns few values and those values are:

• vbOK – The user clicked the ‘OK’ button. 
• vbCancel – The user clicked the ‘Cancel’ button.
• vbAbort – The user clicked the ‘Abort’ button.
• vbRetry – The user clicked the ‘Retry’ button.
• vbIgnore – The user clicked the ‘Ignore’ button.
• vbYes – The user clicked the ‘Yes’ button.
• vbNo – The user clicked the ‘No’ button.

To create a message box, follow the steps:

Step 1: Open an excel workbook and save it as an excel macro-enabled workbook. 

Step 2:  Add a button using macros. 

In order to create a macro, 

• Go to the developer tab and click on insert from the ribbon.
• Choose the command button.
• Assign a macro by providing a name and click on the new button.

Step 3: Finally, press ok 

Step 4: Once the code window appears, type the following code.  

Message Box Code:

Sub DataFlair_Click()
   MsgBox ("Welcome to DataFlair")
End Sub

Step 5: Save the code.

Once you click on the button, the following output appears.

A message box displays a message, “Welcome to DataFlair ” with an “OK” Button. 

Personal & Business Applications of VBA in Excel

Macros will automate most of the routine tasks and it makes the work much simpler. Macros are widely used in personal and as well as business applications. The user can create powerful programs in excel using VBA for business purposes.

Summary

1. VBA stands for Visual Basic for Application. 

2. It is a programming language used to create applications in excel. 

3. VBA makes your excel sheet more powerful and consumes less amount of time if you have repetitive tasks to be done everyday.

4. VBA can be used for personal and as well as business purposes.