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.
Download Practice Workbook
6 Suitable Uses That You Can Do with VBA
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.
- 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.
- 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
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.
- 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.
- 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.
- 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.
- 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.
- 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
- 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.
- 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.
- 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.
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.