What You Can Do with VBA (6 Practical Uses)

VBA stands for Visual Basic for Applications, and it is a rich programming language offering a vast array of applications. In this article, we’ll demonstrate what you can do with VBA in Excel by discussing a handful of ways to apply VBA to clean and format data, automate repetitive tasks, insert PivotTable, make custom functions, etc.

What You Can Do with VBA: 6 Suitable Tasks

Now, VBA has endless applications, and you have to identify how and where to utilize it. That said, now, let’s explore a couple of examples where you can employ VBA. Therefore, without further delay, let’s start.

1. Data Cleaning and Formatting

Data cleaning is an essential and rather tedious process, but Excel’s VBA can help you save time and effort. So, let’s see it in action.

1.1 Removing Blank Rows

Sometimes your dataset may have blank rows that you need to remove. However, removing them one by one can be particularly challenging. Rather, we can write a few lines of VBA code to do the heavy lifting for us. Hence, just follow these steps.
Here, we have a dataset of the List of Idioms in the B4:C14 cells, which contains a handful of idioms as shown in the picture below.

📌 Steps:

First and foremost, navigate to the Developer tab >> click the Visual Basic button.

This opens the Visual Basic Editor in a new window.

Next, go to the Insert tab >> select Module.

For your ease of reference, you can copy the code from here and paste it into the window as shown below.

Sub Choose_Empty_Rows()
Dim row As Range
Dim choose As Range
Dim region As Range
 
  If TypeName(Selection) <> "Range" Then
    MsgBox "Choose a Range of Cells .", vbOKOnly, "Choose Empty Rows Macro"
    Exit Sub
  End If
  
  If Selection.Cells.Count = 1 Then
    Set region = ActiveSheet.UsedRange
  Else
    Set region = Selection
  End If
 
  For Each row In region.Rows
    If WorksheetFunction.CountA(row) = 0 Then
      If choose Is Nothing Then
        Set choose = row
      Else
        Set choose = Union(choose, row)
      End If
    End If
  Next row
  
  If choose Is Nothing Then
    MsgBox "Could not find any Blank Rows", vbOKOnly, "Choose Empty Rows Macro"
    Exit Sub
  Else
    choose.Select
  End If
  
End Sub

⚡ Code Breakdown:

Now, I will explain the code shown below which selects the blank rows. In this case, the code is divided into 3 steps.

In the first portion, the sub-routine is given a name, here it is Choose_Empty_Rows().
Next, define the variables row, choose, and region as Range.
In the second portion, use the If statement to check if a range has been selected.
Then, use a second If statement to check whether multiple cells are present in the selected range.
In the third portion, combine the For Loop and If statements to loop through all the cells in the selected range and count the number of blank rows.
In turn, select the blank rows using the Select method. However, if there are no blank cells, then return the message “Could not find any Blank Rows”.

Thirdly, close the VBA window >> select the B4:C18 cells >> click the Macros button.

This opens the Macros dialog box.

Following this, select the Choose_Empty_Rows macro >> hit the Run button.

In turn, all the blank rows are now selected >> press the CTRL + – (Minus) key >> select the Shift cells up option to delete the unwanted rows.

Finally, the results should look like the image given below.

1.2 Changing Text to Uppercase

Although you can capitalize text using formulas, however, it can be difficult to interpret. Furthermore, if you need to do this often, then you may consider the code below.

📌 Steps:

First of all, move to the Developer tab >> click the Visual Basic button.

This opens the Visual Basic Editor in a new window.

Then, navigate to the Insert tab >> choose Module.

Next, you can copy the code from here and paste it into the window as shown below.

Sub Uppercase()

Dim i As Range
Set i = Selection
For Each cell In i
    cell.Value = UCase(Left(cell.Value, 1)) & Right(cell.Value, Len(cell.Value) - 1)
Next cell
End Sub

⚡ Code Breakdown:

Now, I will explain the VBA code for changing text to uppercase. Here, the code is divided into 2 parts.

Firstly, the sub-routine is given a name, and the variables are defined.
Secondly, assign the Selection property to the variable i. This allows us to choose a range of cells where we can perform operations.
Thirdly, we use a For loop to run the UCase function which capitalizes the first letter extracted by the LEFT function and joins it with the text returned by the RIGHT function.

In the following step, close the VBA window >> select column C >> hit the Macros button.
Now, choose the Uppercase Macro >> press the Run button.

Lastly, the output should look like the picture shown below.

2. Worksheet-Related Tasks

Worksheet-related tasks such as inserting a table of contents, merging multiple worksheets, etc. can be easily automated using VBA macros.

2.1 Creating Table of Contents

A table of content offers a quick and convenient way to navigate to the worksheet of your choice with just one click. It’s simple & easy, just follow along.

📌 Steps:

At the very beginning, run Steps 1-2 from the previous methods to open the Visual Basic editor, insert a new Module and enter the code.

Sub Excel_Table_Of_Contents()

    Dim alerts As Boolean
    Dim y  As Long
    Dim Wrksht_Index As Worksheet
    Dim Wrksht As Variant
    
    alerts = Application.DisplayAlerts
    Application.DisplayAlerts = False
    On Error Resume Next
    Sheets("Table of contents").Delete
    On Error GoTo 0
    Set Wrksht_Index = Sheets.Add(Sheets(1))
    Wrksht_Index.Name = "TOC"
    y = 1
    Cells(1, 1).Value = "TOC"
    For Each Wrksht In ThisWorkbook.Sheets
        If Wrksht.Name <> "Table of contents" Then
            y = y + 1
            Wrksht_Index.Hyperlinks.Add Cells(y, 1), "", "'" & Wrksht.Name & "'!A1", , Wrksht.Name
        End If
    Next
    Application.DisplayAlerts = alerts
End Sub

⚡ Code Breakdown:

Now, I will explain the VBA code used to generate the table of content. In this case, the code is divided into 2 steps.

In the first portion, the sub-routine is given a name, here it is Excel_Table_Of_Contents().
Next, define the variables alerts, y, and Wrksht.
Then, assign Long, Boolean, and Variant data types respectively.
In addition, define Wrksht_Index as the variable for storing the Worksheet object.

Moreover, remove any previous Table of Contents sheet using the Delete method.
Now, insert a new sheet with the Add method in the first position and name it “Table of contents” using the Name statement.
In the second potion, we declare a counter (y = 1) and use the For Loop and the If statement to obtain the names of the worksheets.
Finally, use the HYPERLINK function to generate clickable links embedded in the worksheet names.

Now, click the Run button or press the F5 key to run the macro.

Eventually, your result should appear as shown in the screenshot below.

2.2 Merging Multiple Worksheets into One

Sometimes you may want to merge multiple worksheets into one, now, you could do this manually, however, if there are multiple worksheets then consider the code below. So, just follow along.
Assuming the dataset is shown in the B4:D13 cells. Here, the dataset shows the Product ID, Product, and Sales in USD for January.

Similarly, we also have a dataset for February, as shown below.

📌 Steps:

In the first place, run Steps 1-2 from the previous method to open the Visual Basic editor, insert a new Module, and enter the code.

Sub Combine_Multiple_Sheets()
    Dim Wrk_Sht As Worksheet
    Dim Destination_Sht As Worksheet
    Dim WS_Last As Long
    Dim WS_Range As Range

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With

    Application.DisplayAlerts = False
    On Error Resume Next
    ActiveWorkbook.Worksheets("Combined_Data").Delete
    On Error GoTo 0
    Application.DisplayAlerts = True

    Set Destination_Sht = ActiveWorkbook.Worksheets.Add
    Destination_Sht.Name = "Combined_Data"

    For Each Wrk_Sht In ActiveWorkbook.Worksheets
        If Wrk_Sht.Name <> Destination_Sht.Name Then

            WS_Last = EndRow(Destination_Sht)

            Set WS_Range = Wrk_Sht.Range("B4:D13")

            If WS_Last + WS_Range.Rows.Count > Destination_Sht.Rows.Count Then
                MsgBox "There are not enough rows in the Destination_Sht"
                GoTo ExitTheSub
            End If

            WS_Range.Copy
            With Destination_Sht.Cells(WS_Last + 1, "A")
                .PasteSpecial xlPasteValues
                .PasteSpecial xlPasteFormats
                Application.CutCopyMode = False
            End With

        End If
    Next

ExitTheSub:

    Application.GoTo Destination_Sht.Cells(1)
    Destination_Sht.Columns.AutoFit

    With Application

⚡ Code Breakdown:

Now, I will explain the VBA code used to merge all sheets into one. In this case, the code is divided into 4 steps.

In the first portion, name the sub-routine, here it is Combine_Multiple_Sheets().
Next, define the variables and assign Long and Range data types respectively.
In the second portion, delete the Combined_Data worksheet if it already exists.
Then, add a new worksheet with the name Combined_Data.

In the third potion, use the For Loop and the If statement to loop through every spreadsheet and copy-paste the data into the Combined_Data worksheet.
In the fourth portion, use the AutoFit method to fit the data in the Combined_Data worksheet.

In turn, insert a second Module >> copy and paste this VBA code into the window.

Function EndRow(sheet As Worksheet)
    On Error Resume Next
    EndRow = sheet.Cells.Find(What:="*", _
                            After:=sheet.Range("A1"), _
                            Lookat:=xlPart, _
                            LookIn:=xlFormulas, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlPrevious, _
                            MatchCase:=False).Row
    On Error GoTo 0
End Function

Here, the EndRow function locates the last row in the Combined_Data worksheet.

Following this, choose the Combine_Multiple_Sheets macro and press the Run button.

Subsequently, the results should look like the image below.

3. Automating PivotTables and PivotCharts

Grouping data with Excel’s PivotTable makes it easy to summarize your data. Now, wouldn’t it be great to visualize this summary of your data in a chart? Then you’re in luck since VBA has you covered.

In this section, we’ll show how you can insert a PivotTable and PivotChart with a few lines of code. Now, allow me to demonstrate the process in the steps below.

Let’s consider the Sales Data by Category dataset shown in B4:D14 cells. Here, the first column shows the Item name, followed by the column for Category, and lastly, the Sales column in USD.

📌 Steps:

Initially, follow Steps 1-2 from the previous methods.

Option Explicit
Sub CreatePivotTable()

Dim pt As PivotTable
Dim pc As PivotCache

Sheet8.Activate

Set pc = ActiveWorkbook.PivotCaches.Create(xlDatabase, Range("B4").CurrentRegion)

Sheets.Add , Sheets(Sheets.Count)

Set pt = ActiveSheet.PivotTables.Add(pc, Range("B4"), "Sales_Pivot")

pt.PivotFields("Category").Orientation = xlRowField
pt.PivotFields("Sales").Orientation = xlDataField

End Sub
Sub Piechart()

ActiveSheet.Shapes.AddChart2(251, xlPie).Select

ActiveChart.SetSourceData Source:=Range("$B$4:$D$14")

End Sub

⚡ Code Breakdown:

Here, I’ll explain the code to generate PivotTable and PivotChart. In this case, the code is divided into 3 steps.

In the first portion, enter a name for the sub-routine and define the variables.
In the second portion, activate Sheet8 using the Activate method and assign memory cache using the PivotCache object.
Additionally, insert the PivotTable in a new sheet with the Add method.
Then, position it in the preferred (B4) cell and enter the name Sales_Pivot.
Moreover, add the Pivot Fields i.e. the Category in the RowField and Sales in the DataField.
In the third portion, a new sub-routine is used to insert the chart.
Next, utilize the ActiveSheet property and the Shapes.AddChart2 method to insert a Pie Chart.
Lastly, employ the SourceData property to select the data range for the Pie Chart.

Now, press the F5 key to run the CreatePivotTable () sub-routine.
In turn, execute the Piechart () sub-routine.

After executing the code, your result should look like the picture given below.

4. Solving Complex Equations

Excel is an incredibly handy tool for solving complex numerical equations. One such case is the Colebrook equation which has to be solved numerically, in this regard, you can use VBA to your advantage.

The Colebrook equation shows the relationship between the Reynolds number, pipe Roughness, and Diameter with the Friction factor of the pipe. Now, let us see how to solve this equation in the steps below.

Assuming a dataset that is shown in the B4:D12 cells where we have the distribution of Pipe Roughness and Reynolds Number for a fixed Pipe Diameter. So, without further delay, let’s see each method individually.

📌 Steps:

First, proceed to the Developer tab and open the Visual Basic editor.

Second, copy the code from here and paste it into the window as shown below.

Sub Solve_Colebrook()

Static Computing As Boolean
If Round(Range("E9").Value, 3) <> 0 And Not Computing Then
    Computing = True
    Range("B9").Value = 0.01
    Range("E9").GoalSeek goal:=0, ChangingCell:=Range("B9")
    Computing = False
End If

End Sub

⚡ Code Breakdown:

Now, I will explain the VBA code for solving the Colebrook equation. In this case, we divide the code into 2 portions.

First, the sub-routine is given a name, here it is Solve_Colebrook().
Next, define the variable Computing and assign Boolean data type.

Second, use the If statement to check if the value in the E9 cell is not equal to zero and not equal to Computing.
Now, set the value in the B9 cell to 0.01 using the Range object.
Finally, apply the Goal Seek method to determine the value of the B9 cell (Friction factor) which gives zero in the E9 (RHS – LHS) cell.

In the final step, press the F5 key >> choose Solve_Colebrook macro >> click the Run button.

Consequently, the output should look like the screenshot below.

5. Developing New Custom Functions

Though Excel has a wide range of functions, however, you can define custom functions to simplify your formulas, for instance, you can define a function to count the number of words in a cell.

Let’s consider the List of Best Sellers dataset in B4:C13 cells. Here, the dataset shows the Product ID, and the second column shows a list of the Best-Selling Books.

📌 Steps:

To start, jump to the Developer tab and navigate to the Visual Basic editor.

Now, you can copy the code from here and paste it into the window as shown below.

Function WordCount(rng As Range) As Integer
WordCount = UBound(Split(rng.Value, " "), 1) + 1
End Function

Here, the WordCount function combines the Split and the UBound functions to find the white space character, count each word and returns the total word count.

Finally, type the function WordCount which takes only one argument. In this case, the C5 cell refers to The Black Swan text.

Finally, the results should look like the picture shown below.

6. Highlighting Cell Based on a Condition

For our last use case, we’ll show how to highlight cells based on certain criteria using VBA. Here, we’ll identify the cells containing duplicate values and change cell color based on specific text.

6.1 Identifying Cells with Duplicate Values

Let us consider the Project List dataset shown in the B4:C14 cells. Here, the dataset shows the Names of the staff and their Project Names respectively.

📌 Steps:

To start, go to the Developer tab and move to the Visual Basic editor.
Next, insert a Module and copy the code from here and paste it into the window as shown below.

Sub Color_Identical_Values()
Dim rRange As Range
Dim rCell As Range

Set rRange = Selection

For Each rCell In rRange
    If WorksheetFunction.CountIf(rRange, rCell.Value) > 1 Then
        rCell.Interior.ColorIndex = 27

End If
Next rCell

End Sub

⚡ Code Breakdown:

Here, I will explain the VBA code to identify duplicate values. In this case, the code is divided into 2 parts.

In the first part, the sub-routine is given a name, here it is Color_Identical_Values().
Next, define the variables rRange and rCell respectively. Then, set the Selection property to rRange variable.

In the second portion, use the For-If statement to loop through the values in the cell are identical. If the condition holds then use the ColorIndex property to change the cell color. Otherwise, keep the cell color unchanged.

Following this, close the VBA window >> select the B5:B14 cells >> click the Macros button.
In turn, select the Color_Identical_Values macro >> press the Run button.

Eventually, the output should look like the picture given below.

6.2 Changing Cell Color Based on Value

In the last example, we’ll discuss how to change cell color based on a certain string of text. So let’s see the process in detail.

📌 Steps:

Firstly, select the range of cells C5:C14 >> go to the Name Box, and type in a suitable name, for example, we have chosen Project_Name.

Then, open the Visual Basic editor and paste the following lines of code inside the Module.

Sub Change_Background_Color()

Dim cell_value As Range
Dim project As String
Dim rng As Range

Set rng = Range("Project_Name")

For Each cell_value In rng

project = cell_value.Value
Select Case project

    Case "Alpha"
    cell_value.Interior.Color = RGB(0, 255, 0)

    Case "Gamma"
    cell_value.Interior.Color = RGB(255, 255, 0)

    Case "Beta"
    cell_value.Interior.Color = RGB(255, 0, 0)

End Select

Next cell_value

End Sub

⚡ Code Breakdown:

Now, I will explain the code for changing the cell color, here, the code is divided into two portions.

Firstly, the sub-routine is given a name.
Next, define the variable cell_value, project, and rng.
Then, assign a Range and String data type to each variable.
Following this, insert the Named Range object in the rng variable.

Secondly, in the latter part use the For Loop statement and the Select Case statement to iterate through each of the three cases Alpha, Gamma, and Beta.
Lastly, use the Color property to change the background color of the cells. Here, the RGB (0, 255, 0) is the Green color, the RGB (255, 255, 0) is the Yellow color, and RGB (255, 0, 0) indicates the Red color.

Now, close the VBA window and press the Macros option >> choose the Change_Background_Color macro >> hit the Run button.

Subsequently, the cell color changes according to each Project Name.

Download Practice Workbook

Uses of VBA.xlsm

Merging Worksheets.xlsm

Conclusion

I hope this article helps you understand what you can do with VBA in Excel. If you have any queries, please leave a comment below. Also, if you want to read more articles like this, you can visit our website ExcelDemy, a one-stop Excel solution provider.

Get FREE Advanced Excel Exercises with Solutions!

What You Can Do with VBA (6 Practical Uses)

What You Can Do with VBA: 6 Suitable Tasks

1. Data Cleaning and Formatting

1.1 Removing Blank Rows

1.2 Changing Text to Uppercase

2. Worksheet-Related Tasks

2.1 Creating Table of Contents

2.2 Merging Multiple Worksheets into One

3. Automating PivotTables and PivotCharts

4. Solving Complex Equations

5. Developing New Custom Functions

6. Highlighting Cell Based on a Condition

6.1 Identifying Cells with Duplicate Values

6.2 Changing Cell Color Based on Value

Conclusion

Related Articles

Leave a reply Cancel reply