Macros in Two Workbooks Interfere with Each Other

by Allen Wyatt
(last updated February 26, 2022)

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, and Excel in Office 365.

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

Adding Lines in an Equation Editor Matrix

If you are using matrices in your equations, you can add lines to a matrix by applying this tip.

Discover More

Changing the Color Inside a Shape

Adding a shape to your workbook is easy. If you want to fill the shape with a color, you'll want to follow the ...

Discover More

Rounding to the Nearest $50

When preparing financial reports, it may make your data easier to understand if you round it to the nearest multiple, ...

Discover More

Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!

More ExcelTips (ribbon)

Recording a Macro

One of the most common ways of creating macros is to use Excel's macro recorder. This tip shows how easy it is to use the ...

Discover More

Telling which Worksheets are Selected

If your macro processes information on a number of worksheets, chances are good that you need your macro to figure out ...

Discover More

Deriving an Absolute Value in a Macro

Need to figure out an absolute value within your macro code? It's easy to do using the Abs function, described in this tip.

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 nine minus 5?

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.