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 code in Excel.

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.

Right-click on a sheet name and select View Code to open Visual Basic Editor

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, this article may help you.

🔴 2 Ways to Add Developer Tab in Excel

Click on Visual Basic in the Developer tab under the Code group

When the Visual Basic Editor is open,

Step_2: Create a new Module from the Insert tab.

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 Sub

Second 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, this article may help you.

🔴 2 Ways to Add Developer Tab in Excel

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.

Insert VBA Code Directly Inside a Specific Worksheet

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.

Save Your Excel Workbook as a Macro-Enabled Workbook

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.

Run Sub/ Userform button to run a sub procedure in Excel

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, this article may help you.

🔴 2 Ways to Add Developer Tab in Excel

Now select the macro that you want to run from the Macro Name list and hit Run.

Open the Macro dialog box and run a Subroutine

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 Function

This function can remove characters from the left in the Excel sheet.

Run a User-Defined Function in Excel

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.

(Visited 21 times, 2 visits today)

Similar Posts

Leave a Reply

Your email address will not be published. Required fields are marked *