When it comes to boosting output or gaining time at work, macros are your best friend. Using the VBA coding language, we can automate anything from minor to large processes. We are aware that you may have occasionally considered some of Excel’s limits, but thanks to the VBA code, you can do away with them altogether. In this article, we will provide some handy VBA macro example codes in Excel.
Whether you are a beginner or an experienced Excel user, we hope that this reference will provide you with valuable insights into the world of VBA macros and how they can be used to improve your productivity and efficiency.
Download Practice Workbook
You may download the following Excel workbook for better understanding and practice yourself.
How to Launch and Insert Code in Visual Basic Editor in Excel?
The Developer tab contains the VBA applications including creating and recording macros, Excel Add-ins, Forms controls, importing and exporting XML data, etc. This is our starting point in the world of VBA; just follow along.
📝 Note: By default, the Developer tab remains hidden. You can learn to enable the Developer tab by following this linked article.
- Once enabled, move to the Developer tab, then click on the Visual Basic button in the Code group.
It launches the Microsoft Visual Basic for Applications window.
- Now, click the Insert tab and choose Module from the list. Afterward, we get a small Module window to insert our VBA code.
The advantage of inserting the code in the Module is that we can apply this code to all the worksheets in this Excel workbook. Conversely, we can make our code only available for a specific worksheet.
How to Run Macro in Excel?
After creating a code, the most important task is to run it for a specific task. We can run our code of the module in two different ways. The first one is:
- After completing the code script, click the green-colored play button to Run the code. You can press F5 on the keyboard to do the same task.
- At first, click on the Developer tab and then select Macros in the Code group of commands on the ribbon.
- Instantly, a Macro dialog box will pop up. You can see multiple Macro names available which you have created for this workbook. Simply, select one and click on the Run button.
That’s how you can easily initiate the code.
25 Useful VBA Macro Example in Excel
To move on to the next step, we will use the below dataset to perform the exercises. This is the “Tech gadgets Sales Summary” of a particular shop. This dataset includes the “SL. No”, “Product Name”, “Units”, “Unit Price”, and “Total Price” under columns B, C, D, E, and F respectively.
Note: This is a basic dataset to keep things simple. In a practical scenario, you may encounter a much larger and more complex dataset.
Now, we’ll utilize this dataset to create and show 25 fundamental VBA macro examples in Excel. So, let’s explore them one by one.
Not to mention, here, we have used the Microsoft Excel 365 version; you may use any other version according to your convenience. Please leave a comment if any part of this article does not work in your version.
Example 1: Adding Serial Number
If you work with a lot of data, this example will enable you to automatically add serial numbers to your Excel spreadsheet. Also, you can do it by using the Fill Handle. But this process is more efficient and less time-consuming for a bigger dataset.
Here, we have a blank Column B. We have to fill up these cells with the serial number.
You can use the below code to complete this task.
Sub Add_Serial_Numbers() 'Declaring variable Dim x As Integer 'Using For loop For x = 5 To 14 Cells(x, 2).Value = x - 4 Next x End Sub
The objective of this macro is to adjoin serial numbers in Column B of an Excel sheet. The macro utilizes a loop to cycle over the rows from Row 5 to Row 14 (where x denotes the row number), deducting 4 from each row’s row number and assigning the result to the cell in Column B that corresponds to that row.
After running this macro, it will add serial numbers in the spreadsheet from 1 to 10 in the B5:B14 range.
Example 2: Converting All Formulas into Values
If you want to know the conversion from the formulas to values, this example could be of help. Here, you will find all the formulas in Column F.
You can use the following code.
Sub Convert_Formulas_To_Values() 'Specifying the formula range cells With ActiveSheet.UsedRange 'Converting the formulas to values .Value = .Value End With End Sub
- This code begins with a Sub statement that defines the name of the macro, which in this case is “Convert_Formulas_To_Values“.
- The With statement is used to specify the range of cells that contain formulas in the active sheet. The UsedRange property of the ActiveSheet object is used to get the range of cells that contain data in the sheet.
- Inside the With statement, the .Value property is used twice to convert the formulas in the range to their resulting values. This is accomplished by setting the value of the cell to its current value.
- Finally, the End With statement is used to end the With block.
When this code is run, it converts all the formulas in the active sheet to their resulting values.
This can be useful when you want to preserve the results of a calculation or analysis, but no longer need the original formulas.
Note: This action cannot be undone, so it’s a good idea to make a backup copy of your worksheet before running this macro.
Example 3: Auto-fitting Columns and Rows
Here, we’ll show how we can resize column widths and row heights automatically. It’s significant for the attractiveness of the sheet.
Sometimes, we have column width which isn’t essential to display the full-length value. As a result, it becomes very unsightly for us. See the image below to understand the problem better.
The same thing goes for improper row heights.
Firstly, we’ll discuss how we can solve the column width resizing issue automatically so that all columns get auto-fitted to their content length. We have to just apply a simple VBA code to solve this problem.
The following is the code for this example.
Sub Auto_Fit_Columns() Cells.Select 'Auto fit the entire column Cells.EntireColumn.AutoFit End Sub
Just run the code and see the magic. All columns get auto-fitted in this particular sheet.
Auto-fitting rows are similar to the previous task. We just have to replace the EntireColumn object with the EntireRow object in the code.
Sub Auto_Fit_Rows() Cells.Select 'Auto fit the entire row Cells.EntireRow.AutoFit End Sub
And the result of executing this code is before our eyes.
Example 4: Inserting Multiple Rows and Columns
Sometimes, we need to add new rows to give new entries in our worksheet. On the other hand, if we want to add new fields inside our data range, we have to add new columns. To tackle these problems, we can use two simple VBA macros in our Excel file.
First and foremost, we will show how to insert rows in the sheet.
Below is the code for this example.
Sub Insert_Multiple_Rows() On Error GoTo Handler ' Get the number of rows to insert from the user Dim row_num As Integer row_num = InputBox("Enter the number of rows to insert:", "Insert Rows") ' Insert the rows below the active cell ActiveCell.Offset(1).Resize(row_num).EntireRow.Insert Handler: Exit Sub End Sub
ActiveCell.Offset(1).Resize(row_num).EntireRow.Insert in this VBA code is to insert a certain number of rows beneath the active (selected) cell in an Excel worksheet. Here’s how this line of code works:
- ActiveCell refers to the presently selected cell in the worksheet.
- Offset(1) moves the active cell down one row. This is so that the new rows can be inserted below the current cell rather than above it.
- Resize(row_num) changes the selection’s size to match the number of rows indicated by the row_num variable, which corresponds to the number of rows the user supplied in the input box earlier in the code.
- EntireRow selects the full resized row or rows.
- Last but not least, Insert adds a new row or rows to the worksheet beneath the active cell.
Before executing the code, make sure to select the cell under which you want to add multiple rows. Let’s say we want to add rows below the row of SL. No. 3. So, we selected cell B7 and then run the code.
As soon as the code runs, an input box pops up to get the number of rows to be inserted below the selected cell. Here, input your preferred number to get new rows of this count.
- In this case, we inserted 2. Then, click OK.
Instantly, it adds two rows beneath Row 7.
For inserting multiple columns in our dataset, we’ll use almost identical code. The code is the following.
Sub Insert_Multiple_Columns() ' Get the number of columns to insert from the user Dim col_num As Integer col_num = InputBox("Enter the number of columns to insert:", "Insert Columns") ' Insert the columns to the right of the active cell ActiveCell.Offset(0, 1).Resize(, col_num).EntireColumn.Insert End Sub
Before initiating the code, we selected cell D4, as we want to insert columns after Column D. Then run the code.
- Again, enter your desired integer number and click OK.
The following image is resemblance to the final output.
Example 5: Inserting a Row After Every Other Row
In this example, we’ll demonstrate how you can insert a row after every other row in your dataset. Copy and paste this code into your code module.
Sub Insert_Row_After_Every_Other_Row() 'Declaring Variables Dim Xrng As Range Dim row_count As Integer Dim j As Integer Set Xrng = Selection 'Counting rows row_count = Xrng.EntireRow.Count 'using For loop For j = 1 To row_count 'Inserting row after every other row ActiveCell.EntireRow.Insert ActiveCell.Offset(2, 0).Select Next j End Sub
Just make sure to select the range before executing the code. Here, we selected the B6:F14 range in our dataset. The reason for starting from Row 6 is that Excel inserts the row above the selected cell by default.
The result is as follows.
Example 6: Hiding and Unhiding Rows and Columns
First, we’ll hide rows and columns separately. It’s an easy task.
For hiding rows, the code is the following.
Sub Hide_Rows_Columns() ' Get the range selected by the user Dim rng As Range Set rng = Selection ' Hide the rows and columns in the selected range rng.Rows.Hidden = True 'rng.Columns.Hidden = True End Sub
Here, you can see that we have kept a line of code in the comment form. Because we’ll use this part to hide columns.
Just select the row or rows which you want to hide and then run the code. For example, we want to hide rows 7 and 8. So, we selected them and ran the code. As a result, those rows got hidden.
Similarly, we can hide the columns that we prefer. Simply, we have to uncomment a line from the previous code.
Sub Hide_Rows_Columns() ' Get the range selected by the user Dim rng As Range Set rng = Selection ' Hide the rows and columns in the selected range 'rng.Rows.Hidden = True rng.Columns.Hidden = True End Sub
You can see columns D and E aren’t visible also.
Now, we’ll unhide these hidden rows and columns with just one click. Here is the code for this purpose.
Sub Unhide_Rows_Columns() 'Unhide columns Columns.EntireColumn.Hidden = False 'Unhide rows Rows.EntireRow.Hidden = False End Sub
The resulting output is as follows. The entire dataset gets back its initial presence.
Example 7: Finding Last Used Row in an Excel Worksheet
Here, we’ll find the last used row in the active worksheet and show this row number through a message box.
Row 14 is the last used row in our case.
The following code is the master key to do that.
Sub Last_Used_Row() Dim LUR As Integer LUR = Cells(Rows.Count, 2).End(xlUp).Row MsgBox "The last used row in this sheet is ROW " & LUR End Sub
- We used the Cells function to select the cell in the last row of Column B (Rows.Count returns the total number of rows in the worksheet, and End(xlUp) moves up from the bottom of the sheet to the last cell with a value).
- The Row property returns the row number of the selected cell.
Simply, you get a message box with the message containing the last used row number after running the code.
Example 8: Changing Case of Characters
To exemplify, we’ll demonstrate how to change the case of characters in Excel with VBA macro. In this case, we’ll take the column of “Product Name” into consideration. Firstly, we’ll convert them all to upper case, and after that to proper case. And, in the last, to lowercase.
This macro code works for converting all the text values to uppercase.
Sub Convert_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
Here, we used Xrng.HasFormula = False in the If statement to omit values that got formula. If the value isn’t the output of a formula, then we changed its case only. We used the UCase function to achieve the main part.
Make sure to select the range before executing the code. We selected cells in the C5:C14 range. Therefore, the final results show all the uppercase text in the below image.
Now, we’ll take this output as our new input and change them all to the proper case. In the proper case, the first letter of each word gets written in capital letters, and the remaining in small letters.
Here’s the working code to do this.
Sub Convert_Proper_Case() 'Declaring variable Dim rng As Range 'Using For Each loop For Each rng In Selection 'Reading the worksheet text range cells If WorksheetFunction.IsText(rng) Then 'Converting all the selected range to proper case rng.Value = WorksheetFunction.Proper(rng.Value) End If Next rng End Sub
Don’t forget to select the range before initiating the code. The final looks like the image below. You can understand its functionality better in the highlighted texts.
At this time, we’ll convert all the texts in Column C to lowercase characters. Use this code to do this.
Sub Convert_Lower_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 = LCase(Xrng.Value) End If Next Xrng End Sub
Select the range before you run the code and the final results are displayed in the graphic below, which only has lowercase text.
Example 9: Highlighting Multiple Blank Cells
While you can highlight blank cells utilizing conditional formatting or the go-to Special dialog box, it’s easier to use a macro if you need to do so frequently. Once built, you can save this macro in your own macro workbook or add it to the Quick Access Toolbar. VBA Macro example could be the way to get it in a faster approach.
If you look carefully, you can see multiple blank cells in our worksheet.
The VBA code to do that is the following.
Sub Highlight_Blank_Cells() 'Declaring variable Dim MyData As Range Set MyData = Selection 'Highligting the selected data range with color MyData.Cells.SpecialCells(xlCellTypeBlanks).Interior.Color = vbGreen End Sub
Select the data range and run the macro. And after that, it will show you the below image with highlighted blank cells in your worksheet.
Example 10: Unmerging All Cells in Active Sheet
Occasionally, we have merged cells in our worksheet. There could be any reason to unmerge them. Merged cells cause problems in the time of copy and pasting. Let’s leave this and come to the real point. How can we get rid of these merged cells? So easy. Just unmerge them. In the following image, there are some merged cells highlighted with a different background fill color.
Let’s unmerge those cells. Watch the following code to do this.
Sub UnmergeCells() ' Declare variables |Dim mergedCell As Range Dim cell As Range ' Loop through each merged cell in the active sheet For Each mergedCell In ActiveSheet.UsedRange.Cells If mergedCell.MergeCells Then ' Copy the merged value to the unmerged cells mergedCell.MergeCells = False If IsError(mergedCell.MergeArea.Value) Then ' Skip over cells that contain errors Else mergedCell.Value = mergedCell.MergeArea.Value End If End If Next mergedCell End Sub
- The first line declares the sub-procedure “UnmergeCells“.
- The next four lines declare two variables, “mergedCell” and “cell“, as Ranges. These variables will be used to store the merged cells and individual cells, respectively.
- Then, the “For Each” loop starts and iterates through each cell within the “UsedRange” of the active sheet.
- The “If” statement checks whether the current cell is merged. If it is merged, then the code continues to execute the following lines. If it is not merged, then it skips to the next cell.
- The “mergedCell.MergeCells = False” line unmerges the cell by setting the “MergeCells” property to False.
- The “If IsError(mergedCell.MergeArea.Value) Then” line checks if the merged cell contains an error. If it does, then the code skips to the next cell.
- After that, the “mergedCell.Value = mergedCell.MergeArea.Value” line copies the value of the merged cell to the unmerged cell.
- The loop continues to iterate through each cell in the UsedRange until all merged cells have been unmerged and their values have been copied to the unmerged cells.
- The sub-procedure is closed with the “End Sub” statement.
Now the values in those cells are not center aligned. They got top-aligned as they are not merged anymore. And if you select one of them, you can see the Merge & Center command isn’t highlighted as they got unmerged now.
Example 11: Highlighting Cells with Misspelled Words in Excel
Spell checking is not available in Excel like it is in Word or PowerPoint. We can see there are some misspelled words in our dataset. We’ve marked them with red rectangular boxes.
Therefore, you can use this code to instantaneously highlight every cell that contains a spelling error.
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 MyCheck.Font.Color = vbWhite End If Next MyCheck End Sub
In this code, “If Not Application.CheckSpelling(Word:=MyCheck.Text) Then”, this line uses the Application.CheckSpelling method to check for spelling errors in the text of the current cell (MyCheck.Text). If the CheckSpelling method returns False, which indicates a spelling error, then the code inside the If statement will be executed.
MyCheck.Interior.Color = vbRed
MyCheck.Font.Color = vbWhite
This code sets the interior color of the current cell to red (vbRed) and the font color to white (vbWhite) to highlight the misspelled word.
Consequently, each highlighted misspelled word you have will show up in the below image after running the above macro.
Example 12: Printing Sheet Name
The active sheet will receive all the sheet names by this macro code. When you run this code, it will copy the name of all of the accessible sheets to the current sheet. The present condition of our sheet is like the following.
After initiating the code, it will get filled up with the sheet names in Column B. Following is the working code for this example.
Sub Print_Sheet_Names() 'Declaring variable Dim j As Integer 'Using For loop to count the number of sheets For j = 1 To Sheets.Count 'Print all the sheets available in the workbook Cells(j + 3, 2).Value = Sheets(j).Name With Cells(j + 3, 2).Borders .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With Next j End Sub
This VBA code copies the names of all the sheets available in the workbook. It uses a For loop to iterate through each sheet and prints its name in a specific cell using the Cells method. Additionally, it also applies a border to the cell containing the sheet name.
Finally, it gets all the names of the worksheet after running the code.
Example 13: Sorting Worksheets Alphabetically
Normally worksheets are arranged according to the period of their creation. But we can sort them alphabetically if we wish so. That means if we had 3 worksheets named B, C, and A (sorted according to creation period), they would sort themselves and will appear in the sequence A, B, and C before us.
In the below image here, all the sheet names are not in order alphabetically.
This macro code might be quite helpful if you have a workbook with lots of worksheets and you want to organize them alphabetically.
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
After running the above macro, you will all the sheet names in order alphabetically.
Now they are sorted according to their name, not to their formation timeline.
Example 14: Inserting Multiple Worksheets in an Excel Workbook
If you wish to add several worksheets to your workbook at once, you can use this code. You will see an input box to enter the total number of sheets you want to enter when you run this macro code. It is one of the time-saving VBA Macro examples. The code for this example is beneath.
Sub Add_Multiple_Sheets() 'Declaring variable Dim SheetsNumber As Integer 'Put the number of sheets SheetsNumber = InputBox("Enter number of sheets to insert", "Insert Sheets") 'Adding the additional sheets after the current active sheet Sheets.Add After:=ActiveSheet, Count:=SheetsNumber End Sub
This code simply adds multiple sheets after the active worksheet.
Right away you run the code, an input box will open to get the number of worksheets to insert after the active sheet. In this case, we wrote 2 and clicked OK.
Immediately, it’ll add two blank sheets after the operating sheet.
Example 15: Unhiding Multiple Sheets
If a workbook you are using includes numerous sheets that are hidden, you must unhide each sheet one at a time. If there are numerous hidden sheets, this process could take some time. In the image below, there is one sheet visible in the workbook, all others are hidden.
This code will display every worksheet in the workbook.
Sub Unhide_Multiple_Sheets() 'Declaring variable Dim hidden_sheet As Worksheet 'Using For Each loop For Each hidden_sheet In ActiveWorkbook.Worksheets 'All the sheets will be visible hidden_sheet.Visible = xlSheetVisible Next hidden_sheet End Sub
So, after executing the code, all other sheets are visible/unhidden again.
Example 16: Protecting and Unprotecting All Worksheets
This is the example you can use to protect all of your worksheets at once. This code will go over each worksheet individually and secure it. Also, you will get the code to unprotect them at once.
The VBA code to protect your worksheets is the following.
Sub Protect_Multiple_Sheets() 'Declaring variable Dim MySheet As Worksheet 'Using For loop For Each MySheet In Worksheets 'Protecting multiple sheets MySheet.Protect Next MySheet End Sub
Now, most of the commands and features on the ribbon get greyed out. That means they are not available now. You cannot use them to edit, or change your worksheet.
If you tried to enter any value or change any value, Excel will show a message box like the following.
Now, if we want to unprotect sheets again, just use the code below.
Sub Unprotect_Multiple_Sheets() 'Declaring variable Dim MySheet As Worksheet 'Using For loop For Each MySheet In Worksheets 'Unprotecting multiple sheets MySheet.Unprotect Next MySheet End Sub
You can notice that this code is almost similar to the previous code. We just changed the . Protect method to .Unprotect method.
Again, we become able to edit anything on the worksheet.
Example 17: Exporting Individual Worksheets as PDF Files
PDF files are easy to share, can be opened on most devices, and preserve the formatting of the original document. Exporting individual worksheets as PDF files is a useful feature when you need to share specific sheets of a workbook with others who may not have access to the original Excel file.
Here’s the VBA code that exports individual worksheets in a workbook as PDF files.
Sub SaveWorksheetsAsPDFs() Dim ws As Worksheet Dim path As String 'Get the path to save the files to path = Application.ActiveWorkbook.path & "\" 'Loop through each worksheet and save as PDF For Each ws In Worksheets ws.ExportAsFixedFormat Type:=xlTypePDF, Filename:=path & _ ws.Name & ".pdf", _ Quality:=xlQualityStandard, IncludeDocProperties:=True, _ IgnorePrintAreas:=False, OpenAfterPublish:=False Next ws End Sub
The current worksheet is saved as a PDF file inside the loop using the Worksheet object’s ExportAsFixedFormat method. There are numerous parameters that must be given for the method:
- Type: This describes the file format; for PDF files, it is “xlTypePDF” in this example.
- Filename: This gives the name and location of the finished PDF file. The name of the worksheet and the “.pdf” extension are combined with the path variable to form the whole filename.
- Quality: The PDF quality option, in this case “xlQualityStandard,” is specified by this.
- IncludeDocProperties: In this code, it is set to True, indicating that document properties should be included in the output file.
- IgnorePrintAreas: This specifies whether to ignore print areas and print the entire worksheet, which is set to False in this code.
- OpenAfterPublish: This specifies whether to open the saved PDF file after it’s created, which is set to False in this code.
Each worksheet in the active workbook is saved as a separate PDF file in the designated file location when this code is executed. When you need to send individual worksheets from a workbook to other people as PDF files, this code may be very much helpful.
Example 18: Adding Timestamp to Workbook Name While Saving
Adding time automatically to the filename is a good way to easily know when the last work was done. We can do that through VBA macro. This code will save a copy of the main workbook with a timestamp in its name.
Sub SaveWorkbook_With_Timestamp() Dim ts As String Dim wbName As String 'Get the current timestamp ts = Format(Now(), "yyyy-mm-dd_hh-mm-ss") 'Get the workbook name and path wbName = ThisWorkbook.Name 'Add the timestamp to the workbook name wbName = Left(wbName, InStrRev(wbName, ".") - 1) & "_" & ts & ".xlsm" 'Save the workbook with the new name ThisWorkbook.SaveAs wbName End Sub
Here, “wbName = Left(wbName, InStrRev(wbName, “.”) – 1) & “_” & ts & “.xlsm”
This line uses the Left() function to get all characters of the wbName variable from the beginning to the position of the last dot (.) minus 1, which removes the file extension. Then, it appends an underscore, the timestamp (ts variable), and the file extension “.xlsm” to the wbName variable, creating a new name with the timestamp appended.
Note: This will save the workbook as a macro-enabled workbook (.xlsm) since the original workbook likely has VBA code in it. If the original workbook is not macro-enabled, you may need to change the file format to the appropriate type (e.g., .xlsx) in the SaveAs method. And that’s it! The workbook will be saved with the timestamp appended to its name, allowing you to create unique copies of the workbook with different timestamps for version control or tracking purposes.
You can see the output. A new copy of this file is saved with the perfect timestamp in its name and also in the same folder of the source workbook.
Example 19: Erasing Blank Worksheets
As you can see we have a blank worksheet named “Erase Blank Worksheet” in our workbook. It’s just beside the worksheet “Highlight Cells Misspelled Word”.
This macro will erase all the blank worksheets from the workbook we are using while running the following macro code.
Sub Erasing_Blank_Sheets() 'Declaring variable Dim MySheet As Worksheet Application.DisplayAlerts = False Application.ScreenUpdating = False 'Using For Each loop For Each MySheet In ActiveWorkbook.Worksheets 'using this statement to Count blank sheets If WorksheetFunction.CountA(MySheet.UsedRange) = 0 Then 'Deleting blank sheets MySheet.Delete End If Next MySheet Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub
Now, we can execute the code. As a result, there is no blank sheet in the workbook after running the above macro code.
You can observe the blank sheet named “Erase Blank Worksheet” isn’t available now. It gets deleted with our macro.
Example 20: Inserting Different Color Code Index
In this example, we will insert numbers from 1 to 10 and their respective color index in the next column. We have the worksheet in this state now.
In Column B, we’ll enter numbers and in the right next column, we’ll insert their respective color index. The following VBA code will do the rest of the work.
Sub Insert_Multple_Colors() 'Using variable Dim j As Integer 'Using For loop For j = 1 To 10 Cells(j + 4, 2).Value = j 'Inserting the color code index Cells(j + 4, 3).Interior.ColorIndex = j Next End Sub
- The For loop will repeat 10 times, with the variable “j” being set to each value between 1 and 10.
- Cells(j + 4, 2).Value = j: This line sets the value of the cell in column 2 (B) and row “j + 4” to the value of “j“. This means that the first number will be inserted into cell B5, the second into B6, and so on.
- Cells(j + 4, 3).Interior.ColorIndex = j: This line sets the color of the cell in column 3 (C) and row “j + 4” to the color with index number “j“.
Finally, you will observe the following output in the image with different color code indexes.
Excel has 56 built-in colors with corresponding index numbers, and this code uses the index number to set the background color of the cell.
Example 21: Sorting Data by Column
In the following image, we can see that our data isn’t arranged properly. Their serial numbers aren’t arranged properly but rather in a random serial.
We’ll sort this dataset based on the serial numbers in Column B. We need a short code for doing this. The code is given below.
Sub Sort_Data_by_Column() Range("B4:F14").Sort Key1:=Range("B4"), Order1:=xlAscending, Header:=xlYes End Sub
The Sort method takes three arguments:
- Key1: This specifies the column by which the data is sorted. In this case, the sort key is set to the range “B4“, which means that the data is sorted based on the values in Column B.
- Order1: This specifies the sort order. In this case, the sort order is set to “xlAscending“, which means that the data is sorted in ascending order (e.g 0, 1, 2, 3,…, 10).
- Header: This specifies whether the range has a header row that should not be sorted. In this case, the header is set to “xlYes“, which means that the first row of the range (“B4:F4“) is treated as a header row and is not sorted with the data.
Run the code and look they are in perfect order now.
Read More: How to Import Data from a Website to Excel?
Example 22: Highlighting Top 3 Values
In this example, we opt to highlight the top 3 values in the “Total Price” column with a different background color. How can we do that with VBA?
This simple code could be the solution for you.
Sub Highlight_Top3() Dim rng As Range Dim cell As Range Dim cnt As Integer Dim max_Val As Variant 'Get the range of the Total Price column Set rng = Range("F5:F14") 'Loop through the range and find the top 3 values For cnt = 1 To 3 max_Val = Application.WorksheetFunction.Large(rng, cnt) For Each cell In rng If cell.Value = max_Val And cell.Interior.ColorIndex = xlNone Then cell.Interior.Color = RGB(255, 255, 0) Exit For End If Next cell Next cnt End Sub
- The For loop is used to loop through the range and find the top 3 values. The loop starts at 1 and ends at 3, meaning it will run 3 times to find the top 3 values.
- The max_Val variable is set to the cnt-th largest value in the range using the Large function. The Large function returns the cnt-th largest value in the range.
- The inner For Each loop is used to loop through each cell in the range.
- The If statement is used to check if the value in the current cell is equal to the max_Val and if the cell is not already highlighted. If both conditions are true, the Interior.Color property of the cell is set to yellow using the RGB function. The RGB function returns a color value based on the red, green, and blue components passed as arguments.
You can see the result here.
Example 23: Applying Paste Special
We do have the Paste Special VBA Macro example to copy data and paste it on another cell range, just like a worksheet. It enables us to paste the data exactly as it is, just the formulas, just the values, or all three in the same way.
The code is the following.
Sub Paste_Special() 'Copy the specified range of data set Range("B4:F14").Copy 'Paste in the specified range Range("H4").PasteSpecial xlPasteAll Cells.EntireColumn.AutoFit Application.CutCopyMode = False End Sub
This code copies the B4:F14 range and pastes it in the H4 range using the Paste Special method.
Finally, you will see the same data set on the right side of the worksheet.
Example 24: Refreshing Pivot Tables in Workbook
Look, we have created a PivotTable from the data in the B4:F10 range. If you don’t know how to create a PivotTable in Excel, you can follow the linked article.
Then, if we change the D7 cell value from 85 to 90, you will see that the total price in the F7 cell is updated below according to the respective changing value. But, in the PivotTable, no change occurs.
The following code will help you to update your pivot table easily.
Sub Refresh_PivotTables() 'Declaring variable Dim MyTable As PivotTable 'Using For loop For Each MyTable In ActiveSheet.PivotTables 'Refreshing the table MyTable.RefreshTable Next MyTable End Sub
If your workbook contains multiple pivot tables, you can use this code to update them all at once.
After running the above macro code, it will show the updated value in the PivotTable according to our corresponding dataset.
Example 25: Creating User-Defined Function
Microsoft provides us with a variety of built-in capabilities to make the VBA Macro example perform more quickly. However, we can program our own functions using VBA, often referred to as “User Defined Functions” (UDF). In Excel VBA, they are also referred to as “custom functions.”
To calculate the total price, we can build a custom function of our own which will take the unit and unit price as arguments. The code is given below.
Function Total_Price(x As Long, y As Long) As Long 'Using Product formula Total_Price = x * y End Function
You can write this custom function in the worksheet like the other functions we use in Excel. As we are showing macro-related examples, we prefer to use this function in another VBA macro to take it into action.
We can use the following VBA macro in this case.
Sub Calculate_Total_Price() Dim LRow As Long Dim i As Long 'Get the last row of data in column B LRow = Cells(Rows.Count, 2).End(xlUp).Row 'Loop through each row of data For i = 5 To LRow 'Starting from row 5 because row 1 to 4 are header rows 'Calculate the total price using the _ Total_Price function and insert the value in column F Cells(i, 6).Value = Total_Price(Cells(i, 4).Value, Cells(i, 5).Value) Next i End Sub
After executing the code, we can get the total prices in Column F in our sheet. Clicking on the cells in the F5:F14 range will show that no formula or function is showing in the cell. Because we added value directly through the VBA code.
How to Enable Macro in Excel?
Before starting this section, remember that enabling macros can increase the security risks in your spreadsheet, so it’s important to only enable macros from trusted sources.
Because of security issues, Microsoft has blocked VBA macros by default. To use the macro, you have to enable it first.
Clicking the Enable Content button on the yellow security notification bar that shows at the top of the sheet when you initially access a workbook with macros is the simplest and fastest way to enable macros for a single workbook.
How to Change Macro Setting from Trust Center?
Also, you can change the macro setting that allows you to open any macro-enabled file without any security warning. To do this, you have to change the setting from the Trust Center. Just follow the steps below:
- Firstly, click on File >> Options.
- Then, from the Excel Options dialog box, select Trust Center >> Trust Center Settings.
- Now, in the Trust Center wizard select Macro Settings >> choose Enable VBA macros option.
- Lastly, click OK and you are ready to go.
Now, you don’t need to enable content every time you open a new macro-enabled Excel workbook.
How to Test and Debug VBA Macro Code?
You can easily test and debug a macro using the F8 key on the keyboard. You can then see the impact of each line on your worksheet as you go through the macro code line by line. Yellow highlighting indicates the line that is presently being run.
Simply, press the Reset button on the toolbar to get out of debug mode.
How to Save VBA Macro in Excel?
Generally, Excel files are saved in .xlsx format. But it’s different in the case of macro-enabled Excel files. They get the .xlsm extension after their name. To save it, follow the steps below.
- At first, press CTRL + S keyboard shortcut command to save any file.
- Then, in the Save As dialog box, choose Excel macro-Enabled Workbook format in the Save as type field and click on the Save button.
Now the macro-enabled Excel file has been saved, and it can be opened and used with macros enabled.
How to Delete Macro from Excel?
You need to delete macros from your file for several reasons. If a workbook contains a macro that we no longer need or that is potentially harmful, it is a good practice to delete it to reduce the risk of accidental or malicious execution. See the following steps to delete macros from the workbook.
You can easily delete any macro from the Macro dialog box. We have already discussed how we can open the Macro dialog box.
- Just select the macro you want to delete and click on the Delete button.
This is the easiest way to delete macros. You can do it in another way: using the Visual Basic Editor.
- Firstly, right-click on the module name on Project Explorer.
- Then, you can find the remove option in the context menu. Just click on it.
Overall, deleting macros from a workbook can improve the security, file size, clarity, and efficiency of the workbook, especially if we don’t need the macros anymore or if they are potentially harmful.
Things to Remember
- Always save a backup copy of your Excel file before making any changes to it with the VBA code.
- When writing VBA code, use comments to explain what your code does. This will make it easier for you (and others) to understand the code in the future.
- Use meaningful variable names to make your code easier to read and understand.
- Be aware of the limitations of VBA macros. Some Excel features may not be accessible through VBA, and there may be performance issues with very large data sets.
Frequently Asked Questions
1. Can I record a macro in Excel?
Yes, you can use the macro recorder in Excel to record a series of actions and generate VBA code based on those actions. However, the resulting code may not be as efficient or customizable as handwritten code.
2. Can VBA macros be used in other Microsoft Office applications?
Yes, we can use VBA macros in other Microsoft Office applications, such as Word and PowerPoint. However, the specific VBA code required may differ depending on the application.
3. Are VBA macros secure?
VBA macros can pose security risks if they we don’t create or use them properly. Macros can potentially contain malicious code, so it’s important to enable macro security settings in Excel and only run macros from trusted sources.
4. What are some common uses for VBA macros in Excel?
We can use VBA macros for a variety of tasks in Excel, such as automating data entry, formatting reports, creating custom functions, and manipulating data. Some common examples include automating financial models, generating charts and graphs, and performing data analysis.
To sum up, VBA macros are a strong tool that may assist Excel users in automating processes, manipulating data, and developing customized solutions that are catered to their particular needs.
In this reference, we’ve included multiple VBA macro example that highlights some of the language’s most important key aspects and functionalities. Together with some tips and best practices for using VBA macros, we also addressed several frequently asked questions on the subject. Excel users can save time and effort, streamline their workflow, and get better results by adhering to these best practices and utilizing VBA macros.
Additionally, if you want to read more articles on Excel, you may visit our website, ExcelDemy. If you have any questions, comments, or recommendations, kindly leave them in the comment section below.