0

I have an Excel 2007 macro (Visual Basic 6.5) that throws Type Mismatch errors running under Excel 2010. The errors occur with reference to VB functions like Mid() or Instr().

Can anyone tell me the cause of these errors?

If VB.NET is not installed (or not current) on the Excel 2010 system, could this cause the errors?

This macro is used by a number of people on a variety of systems. Previously I have worked around these errors by writing my own functions.

Here is the relevant code, the error occurs in the middle line:

bdash = 0
bdash = InStr(Dfiles(ii, 1), "-")
bperiod = InStr(bdash, Mid(Dfiles(ii, 1), "."))
bname$ = Mid(Dfiles(ii, 1), 1 + bdash, bperiod - 2)
tprefix$ = Mid(TemplateFile$, 1, 12)
Kara
  • 6,115
  • 16
  • 50
  • 57
user2692903
  • 11
  • 1
  • show your code please –  Aug 19 '13 at 12:23
  • 2
    http://stackoverflow.com/questions/12476793/date-for-vba-not-working-in-excel-2011 – SeanC Aug 19 '13 at 13:10
  • @Sean, that's interesting. I don't completely understand what that is doing, but that sort of error across different platforms does fit what I am getting. I also have a report of a similar error on a MacBook, but I don't any details on it yet. Given that I can't control which platforms my macro may run on, I maybe stuff with writing all my own functions. Annoying, but doable. – user2692903 Aug 20 '13 at 02:56
  • either ensure all add-ins/references are loaded before running the code, or use late binding for your variables – SeanC Aug 20 '13 at 03:05

1 Answers1

0

As your error raised in:

bperiod = InStr(bdash, Mid(Dfiles(ii, 1), "."))

Your problem is right there in Mid(Dfiles(ii, 1), ".") part:

Public Shared Function Mid( _
   ByVal str As String, _
   ByVal Start As Integer, _
   Optional ByVal Length As Integer _
) As String

I think your code should be something like this:

bperiod = InStr(bdash, Mid(Dfiles(ii, 1), bdash), "."))
shA.t
  • 16,580
  • 5
  • 54
  • 111