Automation error (440)

[erik.smans]

I'm currently facing a big problem and I was hoping you can help me.

I created a workbook containing the following code:

Function Leeg(x) As Boolean
If IsNull(x) Then Leeg = True Else Leeg = Trim(x) = ""
End Function

The code is stored in a workbook module, and the function is used in the sheets and in other procedures.

Problem:

When the sheet is openend for the first time, it gives an 'automation error' (440).


To solve this I do the following:

1. Open with macro's disabled

2. Remove the 'Leeg' function

3. Store the workbook and reopen with the macro's enabled

4. Put the function back and press F9 to recalculate

5. Store the workbook


From then on the error is gone. Unfortunately this file is used daily and send by e-mail many times per week with different names. It makes the file very user-unfriendly, specially because it has a protection code.

 

[Hermanito]

Are you sure this function is the real cause? Because I tested here, and it works without error... I would at least change it to:

Function Leeg(x As Variant) As Boolean
    If IsNull(x) Then Leeg = True Else Leeg = Trim(x) = ""
End Function

though. That way there is no undefined variable (it could be that it defaults to Variant, but personally I never trust defaults, they tend to change and mess things up )
And what do you expect from Leeg = Trim(x) = "" ?
Isn't Leeg always evaluating to "", it seems the Trim(x) is doing nothing?

EDIT: after some searching, I understand the last part now...
Leeg is set to the result of the boolean expression Trim(x)=""

 

[erik.smans]

I think the problem is not in de code itself.
it could even be: Leeg= trim(" " & x)=""

When the error occurs, and I look in my project explorer I see "VBAProject", without the filename. I then remove the password and see it gives the error on the first lane (Function Leeg.....)

When I edit this lane by making it leeg2, the procedure stops. message:
"This action will reset your project, proceed anyway?"
I click 'YES' and the filename appears in the project explorer.
Changing it back to 'Leeg' and it is OK

 

[Hermanito]

Tested it again here, now with password on the VBA project, to match your setup, and still no problems...
It must be something else then, but I can't imagine what...