Chris_W_Amey
10-12-2020, 05:21 AM
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
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