Numeric value mysteriously appearing in the VBA editor immediate window.

[rlv]

So, a colleague sent me a workbook for beta testing and code review. While testing I noticed that I was getting a numeric value appearing in the immediate window in the vba editor, as if from a debug.print statement. I removed all debug.print statements from the project code and still got the value. It seemed to be event-based, triggering when I changed values. I reviewed every piece of event based code in the project and found nothing. I turned off events (Application.EnableEvents = False), but still observed the numeric value being generated when I changed cell values. The value *seems* to be a an Excel Time value, as if the time of my cell changes are being logged. I Deleted all the code modules, no change. Then I deleted all worksheets but one I created myself. At this point, the value stopped appearing and an error message popped up with something to the effect of "Compile Error in Hidden Module". Hidden module? I then started looking at add-ins and the project's references (Tools-References) and there are no add-ins I did not install myself, and no non-Microsoft references in the project references. The only other thing I noticed was some weirdness with the CTRL-Z (undo) behavior which started after I ran one of the macros, but nothing in that macro's code is unusual or making any external calls. I was thinking key-logger? But why would it write to the VBA immediate window? It's almost like there a .DLL somewhere with hidden functions, but I can't see anything.


Any ideas about what might be going on? I can't post the WB since it is work related.

[Kenneth Hobs]

I guess you looked at code in ThisWorkbook object and all the sheet objects?

[rlv]

Yes. Each one. Nothing stands out. I'm going to continue trying selective deletions to at least narrow it down to which object.

[Paul_Hossler]

Are any worksheets "Very Hidden"?

You can't delete them or unhide them from the worksheet side, only the VBA side

The WB might be corrupted -- try Code Cleaner

http://www.appspro.com/Utilities/CodeCleaner.htm

[rlv]

There were about 4 sheets set to xlSheetVeryHidden, but I unhid them all without any change in behavior. Code cleaner is a good idea. I have it installed but I did not think to try it. The 'hidden module' message threw me and I got to wondering if there was a way to hide an excel module I was not aware of. No external links or connections I can see either.

[Paul_Hossler]

Maybe delete/replace the sensitive data post it here?

[rlv]

I've tried doing that, but so far the simplified versions don't exhibit the problem. One last question: Can you think of any mechanism that would

1)Allow writing values to the "Immediate" window in the VBA IDE when all debug.print statements have been purged from the VBA project
2)Behave like a Worksheet_Change event, but one which still works when EnableEvents have been set to false.

Thx.

[Paul_Hossler]

Don't simplify so much -- can you leave enough in to show the error, and then sanitize the data?


Are there any addins loaded? Or any XLSTART hiddent workbooks?

No, but sometimes EnableEnvents is turned back on by another sub

[Kenneth Hobs]

Have you tried a Ctrl+F in the VBE and looked for Debug.Print with the "Current Project" search option from a code window in that workbook?

Otherwise, it might be the Personal workbook or other hidden addon workbooks. I would suspect your problem file though.

[rlv]

Thanks everyone for all the suggestions. Some random comments:

- I used 'current project' as my scope for search/replace to comment out all debug.print statements.
- There's nothing in the WB code that calls anything in my personal macro workbook, and there is no macro in there that produces that kind of immediate window value.
- I coded a throwaway macro that allowed me to quickly check to see if events were somehow getting back on turned on. No evidence that this happened. When I disabled events, they stayed off until I re-enabled them.
- No add-in I was not expecting to see. All installed by me and have been in use for a long time.
- I had not thought to check, XLSTART, but I have now and there's nothing there except my personal macro workbook

[Paul_Hossler]

Only think left that I can think of is to step through the macro(s) with F8 until you see what is putting the numbers out

You can skip over called subs with Shift-F8 which might speed things up

If you do skip a called sub and the number comes out, then it's drill down time