How to use VBA in MS Excel 2016

What is VBA in MS Excel?

The application is a programming language. Events drive this language. That is, you perform an action that creates a result. This allows the user to use the office developer tools in the application.

It enables the user to develop automated processes and APIs and implement the user-defined functions in your Microsoft application. It is a very powerful language used in MS Excel to develop it more closely.

Why use Visual Basic Application?

The interface for VBA is comparatively simpler. It does not require hardcore programming. By using VBA, the user can create applications using simple commands. It increases the efficiency of MS Excel and other Microsoft office software functionalities.

Implementing VBA in MS Excel

You can use the VBA to implement simple macros if you are not a developer. These simple macros will enable the user to automate the basic processes. But if you are a developer or want to develop powerful tools, you can use the VBA to make custom programs.

Starting with VBA

These are some of the basics that you should know before beginning with the programming.

• Variable: They are used in a programming language that enables the user to store changing values in the system's memory. In VBA, we have to declare the variable before assigning its values.
• There are certain rules that the user must know before using the variables:
• Each programming language has a list of reserved words, and the user cannot use these words as variable names.
  1. There should not be spaces between the characters of the variable name.
  2. Though it is unnecessary, the user should use names related to the value stored in the variable. It makes it easier to understand the nature and function of a variable. The program becomes easier to read.
• Arithmetic Operators: They perform mathematical operations in the programming language. These operators include +, -, *, and /. the meaning of the symbol is the same as in mathematics. The BODMAS rule decides the order of precedence.
• Logical operator: The VBA allows the user to perform logical operations between different variables. The user can perform logical AND, OR, NOT operations in VBA. There are additional operators in VBA. These are True/ False. The user can also use the If statement to make decisions in VBA.

Enabling the Developer Tab in MS Excel

To enable the Developer tool in MS Excel, the user needs to follow the given steps:

1. Open the MS Excel application and build a new workbook in it.
2. Then select the ribbon start button.
3. Click on Options.
4. Then select the customize ribbon.
5. Click on the developer toolbox. Select the checkbox against it. This will open the developer tab in MS Excel.
6. Once performed above step, click on OK.
7. Finally, the developer toolbox will appear in the ribbon.

Printing Hello World Using VBA

Now let us create a program using the VBA in MS Excel. You will first have to assign a name to the program. When you make a program using the VBA, the program should begin with Sub, and the ending is marked by ending the Sub by typing End Sub. Sub here represents a subroutine, and End Sub will end that subroutine.

Syntax of a Program in VBA

Let us make a program that will take input from the user, where the user can enter their name, and in return, it will display a message printing Hello and then the user's name.

So, follow the above steps to open the developer tools in MS Excel. You will be able to program once the developer tools are enabled. You can start writing your program in VBA.

1. Open a Workbook in the MS Excel.
2. Save the workbook in an Excel macro-enabled worksheet format. That is with an extension .xlsm.
3. Select the Developer tools.
4. Now open the Insert drop-down menu. It is located under the Control ribbon.
5. Click the command button to drag it to a location in the worksheet.
6. Performing the above steps will open a dialog box window.
7. Change the name of the macro to btnHelloThere.
   
8. Click on the new button.
9. This will open a window where you can write the VBA code.

Now, let us explain the above code line by line.

• Dim username As String: This statement will create a variable named username. This will allocate memory to the variable. The user can assign text, numeric or other characters as we have defined the datatype as the string for the variable.
• username = InputBox ("Enter your name" ): InputBox is a predefined function in the VBA programming language. The text return in the bracket is displayed inside a window with a field to enter the user's name. The entered name will be stored inside the name variable.
• MSgBox "Hello" + name: This line of code will call another predefined function of the VBA that will display another text message with Hello followed by the value stored in the username variable.

The above code will appear as follows:

1. First, exit the code window from your application after writing the code.
2. Right-click on the 1 button and select the editing text.
3. Enter Say Hello.
4. When you click on Hello,
5. The input box will appear on your screen.
6. Now enter any name on the screen.
7. The name will be assigned to the variable.
8. The results will be displayed, and you will get the final message.

Finally, we built the first program using the VBA in MS Excel.

Creating a Simple EMI Calculator using VBA in MS Excel

Now let us create a program that will enable the user to calculate the EMI. EMI stands for Equated Monthly Instalment. It is the amount the user must pay each month for a given period to repay the loan.

MS Excel will have a built-in function to compute the product's EMI, but we will still write a program to create our calculator. The predefined function to calculate the EMI is PMT. You can call this function directly to compute the EMI in MS Excel.

The syntax for the PMT function is as follows:

Where,

The rate is the interest rate divided by the payments made each year.

Nper: it is the total number of payments made by the user.

Pv: it stands for the present value that is equivalent to the amount of the loan.

Follow the steps to create your own PMT function in MS Excel:

1. Create the user interface using the below cells
   
2. The next step is to add a button in MS Excel. This button will be used to implement the macro.
3. Add the button between the 7 and 8 rows of the spreadsheet.
4. Name the button as btnCalculateEMI_Click.
5. Press the Edit button.
6. This will open the VBA Editor. Here you can write the code for the macro program.
7. Type the given code line by line.

Explanation of the code:

• "Dim monthly_rate As Single" in the function Dim is the MS Excel keyword used to define the variables the program will require. The syntax for defining the variable is as follows:
• Dim variable_name As Data_type. In this macro, the variable names declared are monthly_rate and number_of_periods with the Single datatype and loan_amount, and emi are the variables declared with the Double data type. The single datatype will allow the variable to take a numerical value as input while the variables have double as the datatype can store floating values as input.
• "monthly_rate = Range("B6").Value/Range("B5").Value": Here, we have used the Range predefined function in the VBA. This function allows the user to access the spreadsheet's cells from the VBA. For example, Range("A1") will enable the user to access the value stored in the A1.
• Pmt(monthly_rate, number_of_periods,- loan_amount): The WorksheetFunction in the VBA MS Excel is used to access all the functions in MS Excel.

Once you have typed the whole code in the VBA editor, the display window will look as follows:

8. Now you can Save the macro code.
9. Next, you have to exit the VBA editor in MS Excel.

Now the cells in the spreadsheet in MS Excel will look as follows:

You can enter the value in the EMI Calculator when you click the button. This will return the result.