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 Tasks 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.
📌 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.
Read More: How to Make VBA Code Run Faster (15 Suitable Ways)
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.
Read More: How to Capitalize First Letter of Sentence in Excel (6 Suitable Methods)
2. Worksheet-Related Tasks
Worksheet-related tasks such as inserting a table of content, 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.
Read More: List of 10 Mostly Used Excel VBA Objects (Attributes & Examples)
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.
Read More: Excel Chart Disappears When Data Is Hidden (3 Solutions)
Similar Readings
- 22 Macro Examples in Excel VBA
- 20 Practical Coding Tips to Master Excel VBA
- How to Write VBA Code in Excel (With Easy Steps)
- Types of VBA Macros in Excel (A Quick Guide)
- Introduction to VBA Features and Applications
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.
Read More: How to Use VBA Average Function in Excel (4 Practical Examples)
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.
Read More: How to Alternate Row Colors in Excel Without Table (5 Methods)
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.
Related Articles
- How to Use Select Case Statement in Excel VBA (2 Examples)
- 6 Best Excel VBA Programming Books (For Beginners & Advanced Users)
- How Different Is VBA from Other Programming Languages
- Learn Excel VBA Programming & Macros (Free Tutorial – Step by Step)
- How to Use VBA User Defined Function (4 Suitable Examples)
- VBA Sub Vs Function in Excel (5 Differences)
- What Are Excel Function Arguments (A Detailed Discussion)
Can you please provide me complete VBA videos?