Month: April 2021

In this article, you will see step by step way to insert and run VBA code in Excel.

After reading this you will be able to save VBA code in Excel permanently. 

Insert VBA code in a workbook

The very first step for inserting VBA code (macro) or recording macro is to make sure you have the Developer Tab.

The developer tab offers various tools for developing VBA and automating things in Excel.

By default Developer tab is not present in Excel ribbon, to add developer tab –

• Right mouse click on any tab.
• Click on Customize The Ribbon.

• This will bring Excel options pane with Customize ribbon options. Tick on Developer tab then click on OK.

Now, you can see the Developer tab in Excel ribbon.

Here is another way to add the developer tab.

Suppose you want to insert the below VBA code.

This VBA code will generate serial numbers starting from the active cell.

Sub generateSrNumber()
 'serial number from active cell
 Dim uptoSrNo As Long
 Dim cel As Range, sr As Long
 uptoSrNo = Application. _
 InputBox("Insert last number of series", Type:=1)
 Set cel = ActiveCell
 sr = 1
 Do While sr <= uptoSrNo
   cel.Offset(sr - 1).Value = sr
   sr = sr + 1
 Loop
End Sub

To insert the above VBA code follow the below steps.

• Go to Developer Tab. Click on Visual Basic option to open VBA IDE. Alternatively, you can press ATL+F11 shortcut key to open VBA IDE.

• In VBA IDE click on Insert Tab then click on Module to create a new Standard module.

• Now copy the required VBA code or macro.
• Double click on the newly created module then place the cursor in the code area and paste the code.

• Save workbook either in Macro format (.xlsm) or in Binary format (.xlsb). This will save VBA code in the Excel workbook permanently unless you delete it. 

If you do not save in the required format then after reopening of workbook VBA code will not be there.

Running VBA code in Excel

Though there are many ways to run VBA code or macro, here I will show you the easiest one.

• In Developer, tab click on the Macro option. Alternatively, you can use ALT+F8 shortcut key.

• In appeared pane select the VBA code name or macro name or Sub which you want to run.
• Click on Run button to run or execute the selected macro.

On this Macro pane you can select the macro name then click on Edit button to view VBA code for editing or reading.

Suggested Readings

How to run macro in Excel|Create macro button

How to generate random numbers in Excel without duplicates using VBA