Where to Put VBA code?

We are now going to learn how to copy and paste a VBA code into your excel workbook as follows.

Where to Put Sub Procedures?

We can place the VBA code in a Workbook Module or Personal.xlsb File Module. The difference is that, in the workbook module, you would be able to use the code only for that workbook. When you put the workbook in the Personal.xlsb file module, you can run the code in all of your excel workbooks.

infographics where to put my vba code

VBA code for Workbook

You need to put all the VBA codes to select a single cell or a range of cells in the module of the workbook. Let us say we have the following code to print a message.

Sub Print_Message()
Msgbox "Hey! This is how you copy paste your code"
End Sub

Follow these steps now.

  • Copy the above code and open the excel workbook.
  • Press Alt and F11 keys. This opens the VBA editor.
  • Go to the Project Explorer window and click on the VBA project for your workbook.
  • Go to the Insert tab and insert a new module.
  • Right-click on the new module in the project explorer window and choose the option view code.
  • Paste your code in the code window of the Module.
Where to put your code in vba editor

Run the Sub Procedure

To run this sub-procedure named “Print_message”, perform these steps.

  • Move your cursor anywhere within the Sub and End sub keywords of the Print_Message sub procedure.
  • Press the F5 key to run this Sub Procedure. This opens the excel workbook automatically. (Use Alt and Tab keys to switch between the Excel workbook and VBA editor )
  • You would see the following output of Print_Message() Sub Procedure.
Run a Macro using VBA editor

VBA Code for All your Workbooks – Personal.xlsb

When you take a new module in the Personal.xlsb file project and add your code to it, then you can use that Sub Procedure in all of your excel workbooks. Personal. xlsb is an excel file that is stored in our computer and opens every time we open excel. This file is kept hidden by default and acts as a common base for putting code that needs to be used in all your Excel workbooks.

Inserting New Module in Personal.xlsb
  • Open the VBA editor.
  • Select the VBA Project (PERSONAL.XLSB)
  • Go to the Insert tab and click on Module.
  • Paste the Following Code in Module 1 of Personal.xlsb
  • Press F5 to run the code.
Inserting code in New Module in Personal.xlsb

Now open any other workbook and you will find this code in Personal.xlsb VBA project.

This is how you place your VBA code.

Thank you for reading.

Leave a Comment