A different kind of 'ambigious' error

[Jippy]

Hi everyone, thank you for letting me into your forum

I keep getting a strange error when I launch my spreadsheet. Not so much because the error itself is strange, but because the typical cause of this error is not there and then if I just hit "Ok" and then go to Debug --> Compile VBA Project, it works fine that second time and the code from then functions exactly as intended with no issues.

If I change the name of the sub that is highlighted, it just picks another sub that is in the modules with the same error on next start-up. Multiple computers are doing the same thing. Here is what I have checked based on advice on forums such as this:

- There is no duplicate sub names
- There are no duplicate public variables or declared variables in subs that have the same name as public variables

The document does have some instructions in the Private Sub Workbook_Open() routine in the ThisWorkbook object It is very strange that the code seems to work perfectly happily after just hitting OK to the error and then going to the Open sub and hitting F5, or recompiling so I am at a loss to explain why this is happening only on start up and no other time.

Has anyone else encountered this before? I can upload a dummy-data copy of the files (there are two of them and both need to be there and in the same folder for everything to work as intended as my application has two trackers for two offices that do talk to each other) if needed.

 

[Joe4]

Welcome to the Board!

You could get some strange errors if you have used a "reserved" word for the name of a procedure, function, or a variable in your VBA code.
For example, if you chose the word "Date" as the name of a variable, since it is a reserved word (represents the "Date" function), if can cause ambiguity and unexpected errors (i.e. if you have a Variable of this name, when you go to use it in your code, Excel cannot tell whether you mean the Date function or the Date Variable).

Uploading dummy data probably won't be much help, as those errors most likely indicate an issue with the VBA code, not the data. You would be better off to post your VBA code, if you cannot locate the error.

 

[Jippy]

Hi Joe

I thought about this but I ruled it out. I changed the name of the routine being flagged and instead the same error moved to a different routine that had been used in this code for some time with no issues. We may have a solution though, where I have added a DoEvents command before another command where we think that the code was running while Excel was still booting and before it should have, hence why the issue only came up on start-up, not after re-compiling the project. I'm trialling that fix now.