Consulting

Results 1 to 3 of 3

Thread: Type Mismatch error when there is no type mismatch

  1. #1

    Type Mismatch error when there is no type mismatch

    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

  2. #2

    Solution of sorts

    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?

  3. #3
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Just a thought, but go into Tools>References in the VBIDE and see if there are any reference errors there.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •