What is Excel VBA? Explained with Real Time Examples

What Is VBA?

Do you know what VBA stands for? VBA full form is Visual Basic for Applications. This simple, but powerful programming language was developed by Microsoft Corp. And now VBA is broadly used with other Microsoft Office applications, for example, MS Word, MS Excel, MS Access, MS PowerPoint, Publisher, etc. This language is utilized in writing programs for the Windows operating system and runs as an internal programming language in Microsoft Office.

VBA allows techies to customize applications beyond what is usually available with MS Office for example creating user-defined functions, automating computer processes, and accessing Windows APIs. It also allows users to build solutions to improve those applications’ proficiencies. If you are facing problems in effectively importing your contacts from MS Outlook into an Excel spreadsheet, in repeatedly cleaning up fifty tables in Word or you want a specific document to ask the user for input when it opens then VBA will help you perform all these tasks. You do not have to install Visual Basics on your PC rather installing MS Office will help you to achieve the objectives. A user can use VBA in all available latest office versions, from MS Office 97 to MS Office 2013. Among all Excel VBA is the widely used VBA. Visual Basic Excel is used for Applications in the Context of Excel. You can get a deeper understanding of this concept via Master of Science in Business Analytics from GGU.

Check out our free courses related to upskill yourself.

How is VBA Used?

VBA is a programming language that can be used for a variety of tasks, and it is utilized by various types of users for those tasks. The various organizations that utilize VBA include the following.

• General Users: Generally, users use Excel and other MS Office programs. The user can utilize the MS Office Suite with VBA language free of cost. VBA is used to create and organize spreadsheets. In addition, it is used in numerous other tasks such as if a user has to automate some Excel features like repetitive tasks, frequent tasks, generating reports, etc then it can be done by using VBA. For this purpose, the user can create a VBA program within Excel to produce format and print monthly sales reports with graphical representations like bar graphs.
• Computer Professional: Computer professionals can use VBA to perform more complicated tasks that would otherwise require more time and resources. VBA offers some new functions that are not available in Excel such as creating custom add-ins that enhance the application’s functionality, rewriting lengthy lines of code, creating custom languages within Microsoft Office applications, and combining the functions of multiple programs, etc.
• Corporate Users: The corporate users of various companies use the VBA programming language to automate critical business procedures and internal processes, accounting procedures, minute records, real-time processing of sales orders, complex data calculations, and more.

Learn business analytics courses online from the World’s top Universities. Earn Masters, Executive PG Programmer Certification, or Global Master Certification to fast-track your career.

Why use Excel VBA

Although VBA cannot be used to directly alter the main Excel software, the users can learn to create macros to optimize their time in Excel. Excel macros are created in two ways.

1. By using Macro Recorder: In this technique as soon as the recorder is turned on, Excel will begin to capture every action the user takes and save it as a macro, or “process”. When the user closes the recorder, the macro is saved and can be linked to a button that, when clicked, will repeat the entire process. To use this technique no prior knowledge of VBA is required.
2. Using VBA: This is a robust method where Excel macros can be programmed using VBA.
    

Also, check out: Job-ready Program in Business Analytics

Where to code Excel VBA

1. Within any Office program, press Alt F11 to open the VBA window. 
2. This will open a window with a file structure tree, the coding section that occupies most of the screen in the center.
3. The coding takes place in the coding section. Here, the user can create macros and save them.
4. Once the macro code has been created and saved, it can be attached to certain triggers in the Excel model.
5. The macro can be activated after pressing a certain worksheet button.

VBA shortcuts in Excel

Some shortcuts that operate while using VBA in Microsoft Excel are listed below.

• Alt F11: To open the VBA editor
• Alt F8: To view all macros
• Alt F4: To exit the VBA Editor and return to Excel
• F1: To display Help
• Ctrl space: Autocomplete 
• Alt F5: To run error handler
• Alt F6: To swap the last two windows
• Alt F11: To switch between Excel and the VBA editor
• F7: To start on the code editor
• F10: To start the menu bar
• Home: Starting of line

You can apply these shortcuts in a practical background with the help of the Executive PG Program in Business Analytics from LIBA. 

What can you do with VBA

Finance is fundamentally about handling vast amounts of data, so VBA is omnipresent in the financial services industry. If you work in finance, VBA is probably running within programs you use on a daily basis. By using VBA, you can accomplish the following things.

1. Write Macros: Macros permit all financial professionals like investment bankers, research analysts, salesmen, traders, Accountants, commercial bankers, clerks, and administrators. To compose macros to quickly analyze and modify massive amounts of data.
2. Update Data: VBA in Excel is used to forecast sales and earnings, produce financial ratios, and build and maintain trading, pricing, and risk management models.
3. Conduct a scenario analysis: You can create various scenarios for portfolio management and investment using Visual Basic. 
4. Organize the information: VBA can also be used to create invoices, forms, and charts, analyze scientific data, and manage data displays for budgeting and forecasting. It can also be used to create lists of customers’ names.
5. Be unconventional: Values can be copied and pasted, cell styles for an entire workbook can be changed, and accelerator keys can be pressed using VBA. You can execute very common tasks in an easier and more automated manner. 
6. Prompt Action: You can communicate with users by using VBA. 

Acquire the above mentioned expertise with a deep insight into Professional Certificate Program in Data Science and Business Analytics.

Enable the Developer Option in Excel

On the ribbon, it by default hides the Developer tab. Follow the instructions listed below to customize the ribbon.

1. First right-click on the ribbon and then click on the Customize the Ribbon option.
2. Select the Developer checkbox under Customize the Ribbon.

VBA Editor Interface

To open the VBA Interface press the shortcut key Alt F11. You can also open it by clicking the Visual Basic on the Developer tab.

Create an Excel Macro using a Command Button

To create an Excel Macro using a command button follow the below steps.

  1. Go to the Developer tab, then click on Insert, then ActiveX controls and then select the Command button.  

Developer tab > Insert > ActiveX Controls > Command button

  2. Select and drag the command button on your worksheet.

  3. Right-click on the command buttons and select the View Code option. 

  4. Add the VBA code excel that is given below.

Private Sub CommandButton1_Click()
Range(“A1”).Value= “Learnbasics”
End sub

  5. Close the VBA editor and click on the command button on the worksheet.

Create an Input Box

The following steps are to be followed to create an input box in Excel that will prompt a message on your worksheet.

  1. The Syntax used to create an input box is: 

InputBox(prompt[,title][,default][,xpos][,ypos][,helpfile,context])

  2. To add the InputBox to the command button first declare the variable name and keep the type variant to hold any type of value.

  3. Write the following code to show the input box.

Private Sub CommandButton1_Click()
Dim Learnbasics As Variant
Learnbasics = InputBox(“Enter your message”)
Range(“A1”).Value= “Learnbasics”
End sub

  4. Now click on the command button to get a prompt asking you to enter your message.

  5. Enter your message and click OK. It will be entered and shown as input on cell A1.

Variables, Constant, and Operators in VBA

  1. Variable:The data types that are used to store values are known as variables. During program execution, we can modify the variables.

Syntax: Dim <<Name_of_variable >> As <<Type_of_variable>>

VBA data types can be classified into two categories. 

• Numeric Data Types: These data types consist of byte, integer, long, single, double, currency, and decimal. 
• Non-Numeric Data Types: These data types consist of string, date, boolean, object, and variant.

  2. Constants:The fixed value known as a constant is one that cannot be changed while a program is running.

Syntax: 

Const <<constant_name>> As <<constant_type>> = <<constant_value>>

Example: 

Private Sub CommandButton1_Click()
Const MyInteger As Integer = 10
Range(“A1”).Value= MyInteger
Const MyDay As String = Sunday
Range(“C1”).Value= MyDay
End sub

If, If-Else, For and While Loop

If Statement

The ‘If statement’ is a conditional statement. It consists of an if statement followed by another statement. If the condition is true, the codes under the If statement are executed.

Syntax for If statement:

If(boolean_expression) Then
   Statement 1
   …..
   …..
   Statement n
End If

Example: Program to show the implementation of if statement.

Private Sub CommandButton1_Click()
Dim score As Integer, result As String
Score = Range(“A1”).Value
If score >= 10 Then result = “First”
Range(“B1”).Value = result
End sub

If Else Statement

The ‘If statement’ is a conditional statement. This statement consists of an “If” expression followed by another “Else” expression. If the condition is true, the lines under the body of the If statement are executed and if the condition is false, it executes the line under the Else Part.

Example: Program to show the implementation of if statement.

Private Sub CommandButton1_Click()
Dim A As Integer
Dim B As Integer
A = 1500
B = 1000
If A > B Then
MsgBox “A is greater”
Else
MsgBox “B is greater”
End If
End sub

For Loop: 

For loop is a control flow statement. In this, the user can write a loop that can be executed repeatedly till it satisfies the condition.

Example: Program to show the implementation of for loop.

Private Sub CommandButton1_Click()
Dim A As Integer
A = 1500
For i = 0 To Step 2
MsgBox “The value is: ” & i
Next
End sub: 

While Loop:

When statements are true in a while loop, they are carried out until the Wend keyword is reached. The loop is ended and the subsequent statement is executed if the statement is false.

Syntax: 

While condition(s)
   [statements 1]
   [statements 2]
   …
   [statements n]
Wend

Functions and Sub Procedures

• Functions: Code that can be reused and called repeatedly throughout a program is called a function. The code can be reused numerous times in your program. Go to Insert and then click on Module in the VBA window to create the function.

This function will call the below code.

The result will be

• Sub Procedures: With some minor exceptions, sub-procedures and functions are similar. The call keyword is not required to invoke the sub-procedure because it does not return a value.

Example: Program to calculate area using sub procedures.

Conclusion

The basic concept of VBA is described here. This simple, but powerful programming language was developed by Microsoft Corp. The use of Excel VBA, and different types of keywords are illustrated here. Various examples are represented to give an idea about the implementation of an if statement, if else statement, while loop, for loop, etc. Try pursuing Job-ready Program in Business Analytics which will give you an edge over others in the competitive world.