Macros in Two Workbooks Interfere with Each Other

Written by Allen Wyatt (last updated February 26, 2022)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021


3

When Bruce has two workbooks open, the macros from the workbooks interfere with each other. Bruce stepped through one subroutine in the active workbook only to see it jump to a macro in the other workbook. There was no call procedure to do this; it just jumped. Bruce wonders what would cause this strange behavior.

This is a hard one to troubleshoot remotely; it is something that can be figured out much easier when actually working with the two workbooks.

There are some things, however, that can be suggested as starting points. First, you'll want to check to make sure that there are no name conflicts between the macros in the two workbooks. Identical procedure names can, under some circumstances, cause confusion in the execution of VBA code.

Second, take a look at any variables that may be defined at a global scope. If the same variable names are defined as "global" in both workbooks, it can give undesired results.

The most likely scenario, however, is that one of the workbooks has one or more event handlers in play, and they are getting triggered. For instance, let's say that Workbook1 has a regular macro in it, and that Workbook2 has an event handler that is triggered whenever something changes in that workbook. If your macro in Workbook1 is using a cell reference that results in a change in Workbook2, then that will trigger the event handler and, all of a sudden, you'll be running code in Workbook2 (and understandably so).

The solution is to make sure that your macro in Workbook1 is very specific in how it references cells to make sure it is only referencing cells in Workbook1. That will prevent the event handler in Workbook2 from taking over.

Closely related to this is that Workbook2 may have some macros that are triggered automatically after a given time. For instance, a macro could be developed that runs every five minutes. If that is happening in Workbook2, then even if you are running a macro in Workbook1, when the appointed time arrives, the macro in Workbook2 takes over until it is done running.

It is going to take some sleuthing to see what, exactly, is happening. Bruce is starting out correctly, single-stepping through the subroutine in Workbook1. If the jump to code in Workbook2 always happens at exactly the same step in Workbook1, then check to see if the previous line in Workbook1's code made any change in Workbook2 (intentional or inadvertent). Then examine the modules in Workbook2 to see if it is actually some event handler that has been triggered. If so (and you don't want it triggered), then your code in Workbook1 will need to turn off events before the change and then turn on events afterward.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (12844) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Creating Unnumbered Endnotes

Endnotes are indispensable in some types of writing. You may want to create endnotes, however, that don't follow the ...

Discover More

Turning Off Automatic Capitalization

Type some information into a worksheet, and you may notice that Excel automatically capitalizes some of your information. ...

Discover More

Calculating the First Business Day of the Month

Want to know which day of the month is the first business day? The easiest way to determine the date is to use the ...

Discover More

Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!

More ExcelTips (ribbon)

Stopping Excel from Deleting Macros from a Workbook

When working with very large workbooks, it is possible for Excel to behave erratically. This tip looks at ways you can ...

Discover More

Finding Columns of a Certain Width

If you need to find out how many columns are set to be a specific width, you'll need a macro to help determine the info. ...

Discover More

Reversing Cell Contents

Macros are great at working with text. This tip presents an example that shows this versatility by reversing the contents ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 2 + 8?

2022-03-05 22:22:56

Mic

Oh -- if you do use the method I detailed below to open spreadsheets in separate processes, be aware that copying and pasting between spreadsheets will not accommodate formulas and formatting. That's the big drawback. You CAN still copy and paste the *contents* of a cell to another spreadsheet, but you can't, for example, select a cell, do a Ctrl+c, and then switch to another spreadsheet and do a Ctrl+v. (This has never been an issue for me, but I wanted to mention it in case this is something you need to do.)


2022-02-28 05:05:43

Dave S

This problem can arise with add-ins. Our company finance dept rolled out an add-in for project budget forecasting. We then found some in-house Excel applications started generating odd error messages. Debugging revealed an event-handler in the add-in was being activated - the work-around was simply to disable the add-in when not being used.


2022-02-26 23:20:07

Mic

I believe you can solve this problem by opening each spreadsheet in a new instance of Excel as a separate process, something that is extremely simple to do. As separate processes, their macros should not interfere with each other at all. There's no easy way for me to test this, but I would literally bet money on it. (BONUS: This also solves the problem of spreadsheets sharing an "undo stack"! "Undo" will then *only* undo changes to the current spreadsheet; it will *not* jump back and forth between spreadsheets, because its process is separate from other spreadsheets.)

If you follow the below very quick and easy steps to create an Excel shortcut on your desktop that contains a special "switch", all you have to do to open any spreadsheet in a new, independent instance of Excel is to drag the spreadsheet file onto the shortcut. That's it.

Follow these steps to create the shortcut:
1. Find excel.exe in File Explorer and drag it to the desktop to create a shortcut there.
2. Right-click on the shortcut icon and select "Properties".
3. In the Properties window, in the "Target" text box, go to the end of the text and add " /X" (without the quotes).
/X is a startup switch that tells Excel to start a new instance of Excel as a separate process.
The "Target" text should now read something like this (your specific path may differ):
"C:\Program Files\Microsoft Office\root\Office16\EXCEL.EXE" /X
4. Click "OK".

That's it. Now every time you want to open a spreadsheet, drag it onto the shortcut icon and drop it there. It will open in a new instance of Excel, so macros and undos should not interfere with each other in any way.

Best regards to all,
Mic


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.