A Complete Guideline to Insert and Run VBA Code in Excel
Many people find it difficult to insert and run VBA code in Excel. This article is a general guide for inserting and running any type of VBA
2 Ways to Insert VBA Code in Excel
There are two ways available to insert a VBA code in Excel.
First, you can create a new Module and then insert your code into it.
Second, you can insert a VBA code directly inside a specific Excel sheet.
Let me discuss both of them in detail.
First Way: Create a New Module to Insert VBA Code in Excel
If you insert a VBA code in a module, then you can apply the code to any worksheet in a workbook.
But when you insert a VBA code in a specific worksheet, the code can only work within that specific sheet.
Here, I’ll show you to insert a VBA code by creating a new Module.
So let’s begin.
Step_1: You have to open the Visual Basic Editor first.
You can use any of the following 3 methods to open the Visual Basic Editor.
Method_1: Right-click on a sheet name and select View Code.
Method_2: You can press the ALT + F11 keys to open the Visual Basic Editor.
Method_3: Click on Visual Basic in the Developer tab under the Code group.
If you can’t see the Developer tab in your Excel, read this guide.
When the Visual Basic Editor is open,
Step_2: Create a new Module from the Insert tab.
Step_3: Now simply insert your VBA code in the new Module.
You can use this code for your practice. The code can automatically remove all the AutoFilters from all worksheets in an Excel workbook.
Public Sub Clear_All_AutoFilters()
Dim pWs As Worksheet
For Each pWs In ActiveWorkbook.Worksheets
If pWs.AutoFilterMode = True Then
pWs.AutoFilterMode = False
End If
Next pWs
End SubSecond Way: Insert VBA Code Directly Inside a Specific Worksheet
If you insert a VBA code directly inside a particular Excel sheet, the code only works inside that specific sheet.
Here’s the Usage Guide
Step_1: You have to open the Visual Basic Editor first.
You can use any of the following 3 methods to open the Visual Basic Editor.
Method_1: Right-click on a sheet name and select View Code.
Method_2: You can press the ALT + F11 keys to open the Visual Basic Editor.
Method_3: Click on Visual Basic in the Developer tab under the Code group.
If you can’t see the Developer tab in your Excel, read this guide.
When the Visual Basic Editor is open,
Step_2: Double-click on a sheet name.
Step_3: Now insert your VBA code in the Visual Basic Editor.
Save Your Excel Workbook as a Macro-Enabled Workbook
You can save a workbook having VBA codes/ Macros in one of the two ways.
- Macro-Free Workbook
- Macro-Enabled Workbook
A Macro-Free Workbook doesn’t preserve any VBA codes or macros inside it.
On contrary, a Macro-Enabled Workbook preserves all the VBA codes intact.
Now I’ll show you to save your Excel Workbook as a Macro-Enabled Workbook.
Step_1: Click on the File tab.
Step_2: Hit Save.
Or, you can just press CTRL + S to save your workbook.
Step_3: When a dialog box appears, click on No.
If you choose Yes, your workbook will be saved as a Macro-free workbook.
Save As window will open.
Step_4: Choose Excel Macro-Enabled Workbook from the Save as type drop-down.
Step_5: And hit Save.
2 Different Scenarios to Run VBA Code in Excel
There are main two types of VBA codes available.
- VBA Subroutine (Also known as Sub Procedure)
- VBA Functions
A VBA Subroutine is a piece of code that performs a specific task as instructed. They don’t have any type of value to return.
On the other hand, the VBA Functions have a value to return. These functions are just like any other built-in Excel functions. You can use them as a standalone function. Or, you can use them in collaboration with other built-in Excel functions.
Scenario #1: Run a Subroutine (Aks Sub Procedure) in Excel
You can run a Subroutine using any of the 3 following methods.
Method_1: Hit the Run Sub/ Userform button.
Method_2: You can press the F5 key.
Method _3: Open the Macro dialog box and run a Subroutine from there.
To open the Macro dialog box,
- Press ALT + F8.
- Or, go Developer tab ➤ Code group ➤ Macros.
If you can’t see the Developer tab in your Excel, read this guide.
Now select the macro that you want to run from the Macro Name list and hit Run.
Scenario #2: Run a User-Defined Function in Excel
You can use the VBA codes to create a user-defined function. The function will work just like the built-in Excel Functions.
Here’s a demo code that creates a user-defined function named ClearCharLeft.
Public Function ClearCharLeft(cell_ref As String, char_num As Long)
ClearCharLeft = Right(cell_ref, Len(cell_ref) - char_num)
End FunctionThis function can remove characters from the left in the Excel sheet.
To run and use a user-defined function created with VBA codes,
Step_1: Save the code in the Visual Basic Editor first by pressing CTRL + S.
Step_2: Then select a cell to insert the function.
Step_3: Type an equal (=) first.
Step_4: Then type the function name that you’ve created.
Step_5: Complete the function syntax.
Step_6: Hit ENTER.
To copy down the formula to an entire column,
Step_7: Select the whole column having the formula at the top cell.
Step_8: Press CTRL + D to fill down the formula.
Next, you will see the formula results in the applied cells.
Conclusion
I tried to discuss every possible way to insert and run VBA code in Microsoft Excel. If you still have any sort of confusion, please state that in the comment box.
