Type Mismatch error when there is no type mismatch

[Chris_W_Amey]

Hi,

Code in 2 separate macros that have been working for months (run once a week) has suddenly started producing a type mismatch error when there is no type mismatch. I searched but I cannot find anyone with the same problem. I am aware that recently at least 1 Office 365 update caused Access problems that cause Error 13: Type Mismatch but not in Excel. The code in the first macro is:


Worksheets(mysheets(m)).Activate
Dim ws as Worksheet
Set ws = Worksheets(mysheets(m))
(I did not write the above code, by the way. I am aware that it is bad, I just do not have time to re-write old code, I am just fire-fighting to get the code to run ASAP to get our weekly reports out)
and the second is

Dim wbSource as Workbook
Set wbSource = Workbooks.Open((sPath_Source & "\" & sFilename_Source)
In both the above code samples, the error is on the line that Sets the variable. In both cases, I first checked the contents of the simple variables and they populated correctly and I even put a
Debug.Print Typename(Worksheets(mysheets(m))) & vbTab & Worksheets(mysheets(m)).Name which gave the answer Worksheet and the correct worksheet name, and in the second example, the I put a Debug.Print Typename around the Workbooks.Open statement and that returned Workbook, and opened the workbook. I cannot see what the problem is. I fixed both problems by simply declaring the variables in question "As Object", but that is just a Band-Aid masking the real problem. We have a large Excel VBA codebase and I am worried that this is going to break other code that just has not been run yet.

Unfortunately, I cannot get hold of our IT Dept due to COVID-19-related staffing issues. to find out if there has been any Windows or Office updates run over the weekend. (The second piece of code was in a workbook that I tested on Friday in both Live and Test environments and everything was fine then.

Any help would be appreciated.

Thanks, Chris

[Chris_W_Amey]

I have discovered something really weird going on here. I was just messing around putting debug.print statements after each of the Dim statements and I noticed that the intellisense was bringing up the properties for the wrong objects. After

Dim ws as Worksheet
Intellisense was bringing up the properties for a chart sheet. Why? Who knows, so I changed it to:

Dim ws as Excel.Worksheet
and the code worked fine. Then came the really weird bit. Just to see if I could reproduce the error, I removed the "Excel." in front or Worksheet and the code worked fine! So all I did was add "Excel." and compiled it, then removed "Excel." and compiled it again and it worked fine. At first I thought it must that maybe the file somehow got corrupted, but the odds of 3 different Excel files in different file locations getting corrupted at the same time in exactly the same why is almost infinitely unlikely. So I have found a fix that works (I am leaving the "Excel." just to be safe, but have no idea why it happened.
By the way, the Dim wbSource as Workbook was creating a Worksheet variable, so again I just added the "Excel." in front of Workbook and hey presto, it worked.

The only worry is that other objects are going to get mixed up in the same way these have, and I have no explanation to offer, which is a situation I do not like being in.

Does anyone know under what circumstances this sort of thing would happen?

[Bob Phillips]

Just a thought, but go into Tools>References in the VBIDE and see if there are any reference errors there.