Introduction to VBA Features and Applications

What Is VBA?

A macro is a sequence of instructions. This sequence of instructions automates some aspects of Excel you do regularly. When you’re automating some of your work in Excel using Macro, you can perform more efficiently and with fewer errors. For example, you may create a macro to format and print your month-end sales report. After the macro is developed, you can then execute the macro to perform many time-consuming procedures automatically.

A user doesn’t have to be a power user to create and use simple VBA macros. Casual users can simply turn on Excel’s macro recorder: Excel will record your actions and will convert them into a VBA macro. When you execute this macro, Excel performs the actions again.

Advanced users, though, can write code that tells Excel to perform tasks that can’t be recorded. For example, you can write procedures that display custom dialog boxes, process data in a series of workbooks, and even create special-purpose add-ins.

Note: You may confuse VBA with VB (VB stands for Visual Basic). VB is a programming language. It lets you create executable programs (EXE files). Though VBA and VB have a lot in common, they are different programming languages.

When to Use VBA and Why?

Automation of Repetitive Tasks

Imagine a spreadsheet in Excel containing data on sales of different employees and you want to single out the ones who sold higher than a certain amount. A normal option would be to use the conditional formatting feature to highlight them and find out. Now imagine there are thirty of those spreadsheets in the workbook. Of course, you can follow the same procedure thirty times. Nevertheless, it would be boring and time-consuming. If we use VBA instead, we could just write the code that does the required action once in a module and run the code for as many numbers as possible.

This is one of the most important reasons people use VBA.

Extending Scopes of User Interaction

VBA provides some functionalities that normal Office applications do not offer. Keep in mind that there are so many features that are not showing on the ribbon that you still need to enable through settings. Still, it doesn’t compare to the features VBA can deliver. And if you are in a scenario where you need to utilize those features, you might be in dire need to use VBA.

Interacting Between Office Applications

VBA makes moving information from one office application to another quite easy. For example, you might need to move your Excel data for a PowerPoint presentation or maybe in a report in Word. A simple copy and paste sometimes don’t cut it when you need to present it in a certain way or the process might be too lengthy. VBA offers ways to do such things with ease.

Creative Way of Doing Things

Building up logic to process information and complete actions is fun stuff for many people. You may need time to adjust to the coding languages and environment, especially if you are new to coding and stuff, but VBA provides fun ways to perform otherwise complex processes.

Common Uses of VBA

Usage in Finance

• Analyzing Large Amounts of Data: In the finance world, it is pretty common to store, analyze and evaluate huge amounts of data on a daily basis for decision-making purposes. Although Excel provides some benefits for that, it still cannot rival VBA.
• Create/Maintain Complex Models: We can use Excel VBA to create trading and risk, management models. We can use these models to track, evaluate, and forecast trends of stock and find a suitable time to buy a particular one.
• Creating Better Investment Options: Excel VBA offers more flexibility when it comes to analyzing and forecasting. So we can evaluate and predict any business model more closely and invest according to the results of the predicted return on investments. This removes the interference of human error and provides calculated risks which in turn help decision makers make logical decisions.

Usage in Marketing

VBA can widely be used in marketing sectors also. With the help of VBA, we can analyze marketing metrics for social media, collect data online, create interactive content calendars, create forecasting and graphic models, create invoices and develop charts that can help us promote a certain product or help strengthen the position of an organization.

Usage in Programming

VBA’s usage in programming is limitless. VBA provides more flexibility than any of the Office applications by default. It is not only limited to helping us store data more easily, analyze scientific data, and develop databases and charts from databases but also provides means of performing Excel or other Office actions and many more. One relevant example would be to create custom functions and define tasks that it would do when the function is called upon. That way we can make our custom functions to perform certain tasks that Excel, by default, does not provide.

Read More: What You Can Do with VBA

Features of VBA

Macro: Macro has always been an important part of Excel VBA going back as far as Excel 2003. You can record a macro in Excel through the Record Macro option from the Developer tab. Or as experts tend to prefer, you can write your own lines of code. Both have their advantages and disadvantages.

Workbook and Worksheet Object: Excel VBA can contain objects. These objects can contain other objects which can have another within them. This can go on. In fact, the whole workbook itself is an object in Excel VBA that we consider has different objects within it. The workbook leads the hierarchy of the objects.

Range Objects: Range is a collection of cells in Excel. A range object is the same. Range objects can start from 2 cells to the spreadsheet’s maximum capacity. It is the most important object in terms of VBA’s usage. Different functions of range objects include copying, moving, naming these ranges, formatting, etc.

Collections: Excel functions objects can both be singular and plural forms. Such as a worksheet and worksheets, range and ranges, etc. The plural variants are collections.

Loop: Loops are the fundamental portion of Excel VBA that automates long and repetitive tasks. Loop, as the name suggests, creates a loop and executes a single task multiple times for all object components or any set number of times described in a loop. There are three types of loops in Excel VBA- the For loop, the Do While loop, and the Do Until loop.

Array: Like other programming languages, Arrays in VBA indicate a structure that contains the same type of data in the same place. Arrays can both be one-dimensional and multi-dimensional.

String Manipulation: String manipulation means the conversion of other types of data into a string type to work with it in different scenarios. VBA’s STR function does that. It is a built-in function and one of the popular ones.

Date/Time: The DATE function in Excel can help with the manipulation of date and time. It returns the current date system.

Properties and Methods: You can set properties for objects. It calls out their approaches. Setting a property changes the control of an object. Meanwhile, calling out means calling objects makes the object perform its task.

Procedure: This is the execution style of the code. VBA, like any other programming language, goes through line by line and executes each line of code one after the other. There are two types of procedures. One is sub procedure and the other is called a function.

Statement: A statement is an instruction or a set of instructions. We can break it down into two parts- a declaration or naming of variables, ranges, functions, etc., and an executable statement. An executable statement is the designated piece of code that defines what a particular action is.

Variables: Generally, by variable, we mean the storage location for a particular item. Excel can store ranges, particular values, cell values, and even worksheets as variables.

Logical Operators: Logical operators are of two types- TRUE and FALSE. Both indicate whether a condition is fulfilled or not in any particular scenario.

Where to Write Code in VBA?

All of the Office applications have a Developer tab in their ribbon. It is usually hidden by default. You need to enable it through the settings. Here is a demonstration of enabling the tab, using modules and running a sample code to show you how it works.

Enabling the Developer Tab

• Click on the File tab on your ribbon.

• Select Options.

• The Excel Options box will open up.
• Go to the Customize Ribbon tab here.
• Select Main Tools under Customize the Ribbon.
• Check Developer under Main Tools in the box below it.

• Click on OK.

This will add the Developer tab to your ribbon.

Inserting a Module in VBA

Once you have the Developer tab, you will have access to the VBA editor window that will enable you to add, edit or remove a VBA code. Individual VBA codes are generally stored in a place called Module. It is usually a good practice to store different codes in different modules.

Steps:

• Go to the Developer tab on your ribbon.
• Select Visual Basic from the Code group section.

• You will get the VBA window open up on your screen.
• Click on the Insert tab on the window.
• Select Module.

This will create a module in the VBA window. You can find the modules on the left side of the window and on the right, you can write the code.

Writing and Running VBA

After you create a module, you can write or insert any valid code there to run it. If there is no error in the code, VBA will execute it line by line and return the final result.

Let’s take a simple VBA code that turns a cell color blue.

• Insert a module and insert the following code there.

Sub change_cell_blue()
Range("B2").Interior.ColorIndex = 37
End Sub

• Press F5 to run the code.

The cell color of B2 of the active spreadsheet will change to light blue.

If you want to run the code later, you can also do that by going to the Developer tab and selecting Macros from the Code group.

• You can see the list of macros the workbook contains. Select the one with the proper sub name (the name you enter after “sub” in the VBA code) and click on Run.

Read More: 20 Practical Coding Tips to Master Excel VBA

Introduction to VBA Features and Applications: 4 Suitable Examples

We have a sample dataset like this.

Example 1 – Inserting Multiple Worksheets

Our workbook has one sheet in it. Let’s try a code that can enter multiple sheets at a time. Usually, you can click on the plus sign beside the sheet name at the bottom to open one sheet.

Steps:

• Insert the following code in a module.

Sub Add_Multiple_Sheets()
'Declaring variable
Dim SheetsNumber As Integer
'Put the number of sheets
SheetsNumber = InputBox("Enter number of sheets to insert", "Enter number of sheets")
'Adding the additional sheets after the current active sheet
Sheets.Add After:=ActiveSheet, Count:=SheetsNumber
End Sub

• Press F5.
• A pop-up box will appear asking for the number of sheets you want to insert. We inserted 3.

• Press OK. There will be three new sheets in the workbook.

Example 2 – Changing Letters from Upper Case to Lower case

Steps:

• Select the range you want to change to upper case. We are selecting the range B5:B10 for the demonstration.
• Open a module and insert the following code in it.

Sub Convert_To_UPPER_Case()
'Declaring variable
Dim Xrng As Range
'Using For Each loop
For Each Xrng In Selection.Cells
'Using If Statement
If Xrng.HasFormula = False Then
'Specify the Range
Xrng.Value = UCase(Xrng.Value)
End If
Next Xrng
End Sub

• Press F5.

The dataset will look like this now with all the cells in the selected range containing upper letters.

Example 3 – Highlight Cells with Misspelled Words Across the Workbook

Let’s make some modifications to the dataset to misspell words.

Steps:

• Insert the following code in a module.

Sub Check_Spelling_Error()
'Declaring variable
Dim MyCheck As Range
'Using For Each loop
For Each MyCheck In ActiveSheet.UsedRange
'Using this statement to check spelling mistake
If Not Application.CheckSpelling(Word:=MyCheck.Text) Then
'Coloring the misspelled words
MyCheck.Interior.Color = vbRed
End If
Next MyCheck
End Sub

• Press F5 to run the code.

Example 4 – Sorting All Sheets Alphabetically

We have four sheets in our spreadsheet. We’re sort them in alphabetical order. This can be useful when you have dozens of sheets.

Steps:

• Insert the following code in a module.

Sub Sort_Worksheets_Alphabetically()
'Fast the code running time
Application.ScreenUpdating = False
'Declaring variables
Dim MySheetCount As Integer, x As Integer, y As Integer
MySheetCount = Sheets.Count
'Using nested For loop to sort the worksheets alphabetically
For x = 1 To MySheetCount - 1
For y = x + 1 To MySheetCount
If UCase(Sheets(y).Name) < UCase(Sheets(x).Name) Then
Sheets(y).Move before:=Sheets(x)
End If
Next y
Next x
Application.ScreenUpdating = True
End Sub

• Press F5.

Some VBA Shortcuts

Security Issues with VBA

Sometimes, with the downloaded files, you may notice the macros are disabled. Microsoft Office will prevent them automatically from running. You can specify folders for macros to run by creating trusted locations. You can create trusted locations from the Options dialog box, then choose Trust Center and Trust Center Settings.

You will find Trust Center Settings in the Excel Options box here like the following figure.

Once you click on it, check the following options and then click on OK.

This should prevent Excel from showing the disabled error message.

Download the Practice Workbook