Blog_Banner_Asset
    Homebreadcumb forward arrow iconBlogbreadcumb forward arrow iconBusiness Analyticsbreadcumb forward arrow iconWhat is Excel VBA? Explained with Real Time Examples

What is Excel VBA? Explained with Real Time Examples

Last updated:
26th Jun, 2023
Views
Read Time
9 Mins
share image icon
In this article
Chevron in toc
View All
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.

Ads of upGrad blog
  • 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.

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

  1. Select and drag the command button on your worksheet.
  2. Right-click on the command buttons and select the View Code option. 
  3. Add the VBA code excel that is given below.

Private Sub CommandButton1_Click()

Range(“A1”).Value= “Learnbasics”

End sub

  1. 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])

  1. To add the InputBox to the command button first declare the variable name and keep the type variant to hold any type of value.
  2. 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

  1. Now click on the command button to get a prompt asking you to enter your message.
  2. 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.
  1. 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.

Explore our Business Analytics Programs from World's Top Universities

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 Business Analytics Certification Program from upGrad which will give you an edge over others in the competitive world. 

Profile
Keerthi Shivakumar with strong and innovative strategies to promote the business brand and services globally.
Get Free Consultation

Select Coursecaret down icon
Selectcaret down icon
By clicking 'Submit' you Agree to  
UpGrad's Terms & Conditions

Our Popular Business Analytics Course

Frequently Asked Questions (FAQs)

1What is the full form of VBA?

The full form of VBA is Visual Basic for Applications.

2What is the meaning of VBA?

The VBA meaning is simple. It is a powerful programming language developed by Microsoft Corp. that is 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.

3What is Excel VBA?

Excel VBA is Visual Basic for Applications in the Context of Excel.

4What is the shortcut key to open the VBA window?

Within any Office program, press Alt F11 to open the VBA window.

Explore Free Courses

Suggested Blogs

9 Key Skills Every Business Analyst Needs In Order to Excel
90017
Doctors, engineers, data scientists and other such professions demand a lot of focus, will-power, learning ability, anticipation and other similar men
Read More

by upGrad

08 Feb 2024

Business Analyst Salary in India 2024 [For Freshers &#038; Experienced]
906586
Over the years, Business Analysis has emerged as a core business practice in the enterprise and IT sectors. It pertains to understanding the changing
Read More

by upGrad

29 Oct 2023

Top 11 Industry Applications of Business Analytics in 2024
11521
Business analytics uses sorting, collating, processing, and studying data through iterative methodologies and statistical models to generate meaningfu
Read More

by upGrad

22 Sep 2023

Business Analytics Free Online Course with Certification [2024]
78980
Summary: In this article, you will learn more about Business Analytics Free Online Course with Certification. Why Learn Business Analytics? Getting
Read More

by Rohit Sharma

21 Sep 2023

Top 10 Business Intelligence Tools To Improve Business Decision Making [2024]
5923
Business intelligence (BI) tools are applications that collect and interpret massive volumes of unstructured information from internal and external sy
Read More

by Rohit Sharma

28 Jun 2023

Types of Sampling Methods &#038; Techniques in Business Analytics [With Examples]
6508
Introduction  Dealing with enormous amounts of data is one of the main challenges in data analytics. It would be unnecessary and even impossible to a
Read More

by Keerthi Shivakumar

21 Jun 2023

Top 15 Ways to Improve Excel Skills [Actionable Tips]
5652
Excel is a powerful spreadsheet software widely used in businesses, organizations, and personal finance management. Whether you’re a beginner or
Read More

by Keerthi Shivakumar

19 Jun 2023

What are Requirement Elicitation Techniques? The Complete Guide
8984
Requirement elicitation is a crucial phase in any software development project, as the success of a project largely depends on how well the requiremen
Read More

by Keerthi Shivakumar

19 Jun 2023

What is Financial Analytics &#038; Why it is important?
5661
The world of data analytics is fascinating. After all, isn’t it amazing to deconstruct data, delve into it, and gather meaningful patterns from within
Read More

by Rohit Sharma

18 Jun 2023

Schedule 1:1 free counsellingTalk to Career Expert
icon
footer sticky close icon