Thread: User Function Hijacks macros?

Okay, I have not seen this discussed here before; perhaps some of you gurus are aware of this, but it caught me by surprise.

I have been seeing instances of macros seemingly being hijacked by a user function in another spreadsheet. Typically, I would see this while stepping thru the macro. Suddenly, and with no call from the original macro, the code would divert to a user function in another spreadsheet, iterate several times, and then jump back to the original sub. This only occurs when the spreadsheet containing the user function is open.

I traced this down & it appears that this is caused by the fact that in this particular case, the the user function has a time function (NOW() or TODAY()) as part of an argument. Apparently, whenever a worksheet is recalculated, ALL instances of NOW() and TODAY() are updated, even on sheets which are not active. This updating in turn caused the user function to run, which made it appear that the macro I was stepping thru jumped to the function.

I have attached a sample spreadsheet if you'd like to see this. The user function in question is Negplus1(), and in the sample spreadsheet it is used to adjust Excel's NETWORKDAYS function to not count the current day. More details are contained in the sample spreadsheet. I added a message box to the user function so you can easily see when it runs. Any change to a cell on any open worksheet will cause it to run.

The solution for me was to replace instances of NOW() and TODAY() in the formulae with a hard reference to a cell in the spreadsheet. I then added a Workbook_Activate event handler to set that cell equal to the current date when the spreadsheet is activated. Therefore, there is no longer an application time function within the called function, so the problem went away. The Negplus1 function calls are still evaluated when the sample spreadsheet is activated, but now changes to other spreadsheets do not trigger this.

I thought others out there might have seen something similar. Also, this indicates that there may be a lot of code running which we do not realize. I only found this because it seemed to hijack my macro code; in actuality, the function was running in the background for any changes to any cell on any workbook whenever the workbook containing the function was open. Since it is a relatively small routine, the time delay associated with this was small. However, if someone has larger functions with the same "feature" it could add up.

If anyone has altenate ways of dealing with this, I would be interested in hearing them.

---GJ Case

Sorry, I see I have posted this to the Miscellaneous forum vs Programming. Can someone tell me how to move it?