Where to write, paste and run Excel macros

What follows are what our experts consider the best approaches to these basic problems. Where our preferred approach isn’t available, we discuss the workarounds. A summary is listed in the table below:

How do I ensure macros can run in my Excel workbook?

To use Excel Visual Basic you must be using the desktop app. Macros are not available in the web app.

If you open an Excel workbook in the desktop app that does not currently contain code, you are always permitted to add code to the file and to run it. When you open a file with pre-existing code, the rules depend on your security settings.

Firstly, you must ensure Excel’s security settings permit code to run. This can be found in the Macro Settings sub-menu from Excel Options->Trust Center->Trust Center Settings. It is directly accessible from the Developer ribbon where available. If the setting reads ‘Disable VBA macros without notification’ then you cannot run macros in files where they have been saved before. Our experts would recommend ‘Disable VBA macros with notification’ from the options list as this puts the user in control. Note that you will have to close and re-open any open files for your changes to take effect.

If you are disabling macros with notification, you will generally be asked to provide explicit consent for code to run by clicking ‘Enable Content’ in a bar at the top of your spreadsheet. Once upon a time, you would have had to provide consent every time a workbook was opened. Now, if a file hasn’t moved and consent has been given in the past, macros would generally be permitted by default.

If a file is being opened for the first time and is not in a trusted location, Microsoft may simply block code from running. This ‘Blocked Content’ message is served in a red bar, and is always be seen if you open a file directly from an e-mail. If content is blocked, you can either save it to a trusted location (typically a Microsoft cloud service or corporate network) or unblock it manually. The manual process involves locating the file in Windows, right-clicking to view Properties, and checking the Unblock box on the General tab.

Finally, if a macro containing file is being opened for the first time and the file is saved in a trusted location, you will be prompted to Enable Content when opening the file.

How do I open the Visual Basic Editor to write or paste code?

On a Windows machine, the fastest method is to use the keyboard shortcut ALT+F11. The simplest method is to click the Visual Basic button on the left of the Developer ribbon, assuming it is visible. If you are expect to use macros regularly, you should ensure your Excel environment contains the Developer ribbon. Note the above methods can only work in the desktop app as the web app does not include support for Visual Basic.

If you cannot currently see the Developer ribbon, you will need to customise your copy of Excel. If you right-click on the ribbon on a Windows machine, you will see an option to ‘Customize Ribbon’. It is also available within Excel Options. On the right is a list of ‘Main Tabs’ which are essentially the ribbon headings you can see at the top of Excel. Towards the bottom of the list, there will be an option ‘Developer’ which should be checked.

Once you have opened Visual Basic Editor, a grey screen indicates no coding environment is currently selected in the file but that’s ok because it should be a considered choice.

Where should I paste my AI generated code?

When writing a macro, AI should provide instructions on where to paste its output. This is likely to be one of three environments; a sheet, a workbook, or a separate module. These environments can be found in the Project Explorer window within Visual Basic Editor. Double clicking on the named environment will bring up a white space into which code can be pasted.

If you cannot see a Project Explorer window within Visual Basic Editor, you can bring it up through the View menu, or by pressing Ctrl + R. If there is no existing code in the file, you will see a tree headed by a ‘VBA Project’ alongside your filename with a branch for ‘Microsoft Excel Objects’. You may need to expand the tree or branch using the adjacent + symbol. On closer inspection, you will see the objects are simply a list of all the sheets in the file, with an option for ThisWorkbook at the bottom.

If you requested code to process data on a single sheet and have been instructed to paste the code into that sheet, then you should be able to find your sheet there. Note the sheet names that appear on the tabs in Excel are in brackets on the right of each entry in Project Explorer after a name like ‘Sheet1’. If you were told to paste code in ThisWorkbook then you should locate ‘ThisWorkbook’ which will be at the bottom because it follows all the ‘SheetX’ entries in the alphabet.

If you were told to paste code in a specific module, you will need to right-click on the VBA Project containing your file’s name, and select to Insert->Module. This will create a new branch in the explorer tree called ‘Module1’. If you are becoming more experienced with Visual Basic, and are attempting to add several subroutines to your file, it is possible you may need to re-name the module. Once you have double-clicked on ‘Module1’, you can do this in the Properties Window. This can be accessed through the View menu or F4. There is a single property called ‘(Name)’. You can overwrite the current name with whatever is required.

Should I write code in a sheet, in ThisWorkbook, or in a separate code module?

If you are using worksheet or workbook events such as Worksheet_Change or Workbook_Open, these routines must be placed directly in the sheets or workbooks that trigger the code. Beyond that, it often won’t matter but our Excel consultants would advise writing code within a module where possible.

The argument for writing code within sheets is that your code can refer to cells on that sheet without ever having to name it explicitly. The same applies at the workbook level. Code written in a module will assume you’re referring to the active sheet or workbook unless explicitly told otherwise. If you don’t want to risk code making changes to the wrong sheets or workbooks, modular code will be slightly lengthier than sheet or workbook code.

Let’s consider the example of a macro operating on a single sheet. One example would be a script that restores conditional formatting to Range("A1:Z100"). If you want to ensure the code only runs within Sheet1, and does not run on other sheets, you would need to place the code within Sheet1 or change the references to Sheet1.Range("A1:Z100").

Now consider how the macro will be triggered. We can only choose where to write the code that is going to be manually triggered. Assuming you don’t want to force users to learn keyboard shortcuts, add a custom ribbon or require the Developer ribbon, you will end up assigning a macro to a textbox or button that will live on your Excel sheet.

This creates a technical issue if the code is contained in the sheet or workbook. If you subsequently copy the sheet containing the textbox or button, it can end up inadvertently creating a link to the original code. As with an Excel formula linking two files, this link can get broken. The result is that it’s best to write code within a module. Also, in this scenario, you can be very confident of the active sheet when the macro is run so it’s low risk to write the code without explicit sheet references.

How do I understand code that uses numbers to identify cell rows and columns?

If you record a macro that puts an ‘X’ in cell A1 of a worksheet, it will refer to the cell as Range("A1"). AI written code may refer to the same cell as Cells(1, 1). This row-column notation identifies columns by numbers not letters and the row comes first. You can toggle Excel to display numbers at the top of all columns under Excel Options->Formulas->Working with Formulas by checking the ‘R1C1 reference style’ box. This will help you interpret Visual Basic’s row-column notation without having to work out column AX is column 50.

The reason a lot of code refers to columns by numbers is that a computer can be more easily told to cycle through every column from 1 to 10, than through every column from A to J. Likewise, if you need the third column of a table starting in column E, it’s more natural to locate column ‘5+2’ than column ‘E+2’. It’s also easier to cycle through rows if the row’s identity is separated from the column’s identity by a comma and no quotation marks are required in the cell reference.

If you want to automate the bulk processing of data, you nearly always need to cycle through cells. If you have asked AI to solve the problem, it may be your first ever macro and will almost certainly include references of the form Cells(<row number>, <column number>). Although AI could be prompted to rewrite the code with column letters, it is faster to toggle Excel’s interface to display column numbers than get AI to toggle the entire code.

How can I trigger my Visual Basic macros to run?

Assuming your code isn’t tied to specific events, all macros can be found by clicking the Macros button on the Developer ribbon. They can also be run from within Visual Basic Editor. However, it is more user-friendly to add buttons to your Excel worksheets or, occasionally, to assign them to their own buttons in the ribbon. Keyboard shortcuts are worthwhile if the code is for personal use.

There are three options for adding buttons to worksheets. The first two can be found through the Developer ribbon, where you can add Form or ActiveX controls. ActiveX controls are somewhat antiquated in that any assigned code must live within the same worksheet and the formatting options are very limited. Form controls are a little better but you are restricted to creating a series of grey boxes.

Our consultants prefer to use textboxes or shapes which are available from the Insert ribbon. You can display whatever text you like in a shape or textbox and can format to fit your wider sheet design. If you want a shape to trigger a macro, you can right-click on it and select ‘Assign Macro’. That brings up the same list of subroutines as when you click the Macros button on the Developer ribbon but, once assigned, users will only have to interact with something that looks like a button.

The main advantage of a button is that it can be placed where it is needed. The fact it will be visible on-screen with an appropriate label (such as ‘Insert Row’) means other users will know how to use it. If you are the only user of the sheet, it will be available with a single click.

Being the only user of your macros opens up the option of a keyboard shortcut which will require you to hold down CTRL, optionally shift, and any other key. These can be assigned by clicking Macros on the Developer ribbon, selecting a macro’s name and clicking Options. The same menu has a Run option. The downside of keyboard shortcuts is that other users may not be aware they exist.

If you need other users to be able to run the same code throughout your workbook, you can add buttons to Excel’s ribbon. As they may not be found by untrained users, they generally only make sense if your file contains several macros and the untrained users are forced to interact with your custom menu to get any use out of the file.

By Ed Bolton, founder of Excel4Business Ltd. Last reviewed September 2026.