Compile error on Excel 2010, but not on Excel 2003

0
votes

I can't explain what is wrong with this simple code. It works perfectly on Excel 2003, but in Excel 2010 it displays:

Compile error: Sub of Function not defined.

Can anyone explain why is there an error on Excel 2010?

Sub Test_File_Opened()
    If IsFileOpen("D:\Test.xls") Then
        MsgBox "File is open!"
    Else
        MsgBox "File is closed!"
    End If
End Sub
2
excelvba
Post the IsFileOpen function as well. It should be included when you transfer your code from 2003 to 2010. - L42
This link might clarify it for you. IsFileOpen is not an inbuilt VBA function. - rusk
@rusk it doesn't explain why the UDF worked in xl 2003. MJ54, pls post your function. - brettdj
There is no function below the code. - MJ54
@brettdj: Could be any of a million things. It's in another module the OP didn't copy. It's in an add-in. etc. etc. Bottom line is, the code is probably the one on this Microsoft page and just has to be made available in the new Excel 2010 workbook e.g. pasted into a module, or the add-in loaded etc. Not enough info here to diagnose properly, but the remedy is the same regardless. - Jean-François Corbett

2 Answers

1
votes

The IsFileOpen function is not present in your code, this is the only possible explanation. You must declare it somewhere in your VBA module.

Here are a couple of things to check:

  • Did you misspell the procedure name?
  • Try to call a procedure from another project without explicitly adding a reference to that project in the References dialog box.
  • Specify a procedure that is not visible to the calling procedure.
  • Declare a Windows dynamic-link library (DLL) routine or Macintosh code-resource routine that is not in the specified library or code resource.
0
votes

IsFileOpen is not a VBA built-in function. It probably comes from this Microsoft page (as pointed out by rusk in a comment) and the code just has to be made available in your new Excel 2010 workbook e.g. pasted into a module.

Why was it available in your Excel 2003 workbook but in your 2010 workbook? Could be any of a range of things. It's in another module the you didn't copy over to the new workbook. It's in an add-in not made available in your current installation of Excel 2010. Etc. Etc. You don't give enough information to diagnose the problem properly, but the remedy is the same regardless, as indicated above.