Weird automation error. Not testable, source unknow. (DDE + UDF + VB6 DLL)

[arbpairs]

I searched with no results and finally decided to post:

My Excel application runs with DDE links that update in real-time. Some of my cells have conditional formattings, that execute a UDF when a condition is TRUE. My UDF resides in VBA and calls a function in VB6 DLL library.

Everything works great, however sometimes I experience the following errors:

All of the sudden for no apparent reason or particular condition I get:

Run-time error '50290'
Method '~' of object '~' failed
[OK]

​

This is strange because my VB6 DLL function, (which is called by UDF inside VBA), has an error trap and this message should never be displayed.

So right now, my guess is somehow the link between Excel and DLL is being lost. But why and what can cause it?

Right after I click OK, next message is displayed that reads:

Run-time error '-2147418105' (80010007)

Automation error
The callee (server [not server application]) is not available and disappeared; all connections are invalid. The call may have executed.

[grayed out Continue] [End] [Debug] [Help]

If I click on Debug, it takes me to my UDF function in VBA and debugger highlights the line which makes the call to a function in VB6 DLL library.

By trying to execute it again, message #2 appears over and over again. My only options are to end execution and close Excel.

If I keep the same session of Excel opened, it's no longer possible to 'attach' or associate my DLL with this session of Excel. So my only resort is to save the workbook and close it. After re-opening the workbook everything works fine.. until this error (#1) happens again.

I will be extremely thankful for any ideas or insights on this issue and any suggestions on what could be done to overcome this error from happening or trap the source.

Thank you,
Alex

 

[arbpairs]

Anyone?

 

[Norie]

Alex

Are you sure the error isn't in the DLL?

If you do have an error trap what does it actually do?

Perhaps you could change it so that when there is an error it gives you some information about the error.

By the way, what do you mean that the UDF is executed by conditional formatting?

 

[arbpairs]

Re: Weird automation error. Not testable, source unknown. (DDE + UDF + VB6 DLL)

Hi Norie, thanks for your response!

I'm positive that this error is not inside the function, because this function has been tested thoroughly, it always executes normally with no errors and it has an error handler:

ErrHandler:
    MsgBox ("functionName() " & Err.Number & " - " & Err.Description & ", line: " & Erl())
    Resume Next
End Function

The error handler has function name, reference to what type of error it is and where the error has occurred. I've never seen this message box appear.

Could it be that during execution VB6 DLL (Activex) losses the "Excel.Application" object and can no longer read/write into Excel?

How can I find more info on error #2, it makes no sense:

The origin or the reason for first error is also not clear.

 

[Norie]

Re: Weird automation error. Not testable, source unknown. (DDE + UDF + VB6 DLL)

Alex

Eh, I thought you meant the error 'trap' was in the DLL.

The reason you are getting such an ambiguous error report when using your error trap could be because it's not a VBA error so the error code isn't recognised.

It could be something to do with the DLL losing 'contact' but that would be hard to tell without knowing more about the DLL and also how you are using it in VBA.

 

[NdNoviceHlp]

Re: Weird automation error. Not testable, source unknown. (DDE + UDF + VB6 DLL)

What does the task manager say when you get the error? Is the exel.exe process running? If it is, does ending the exel.exe process fix the error? Dave

 

[Colin Legg]

Please can you post all of the VB6 function code plus the VBA caller code? Let's see what it does...

 

[arbpairs]

Hi Norie,

The error trap I described in my previous post IS inside the DLL function. Before the function is executed, I see error #1 followed by #2, so the execution never really get's to the trap.

Dave, when these errors occur, the application isn't hanging so ending Excel.exe is not required. I can save & close the application. After re-opening, normal operation is resumed as if nothing happened.

Colin, my VBA UDF calling code consists of one line - a call to a function within VB6 DLL. The VB6 DLL function which is being called, checks for various conditions and it has been unit-tested as well as tested for integration with other modules. Also, since this function's error handler is never executed, I don't suspect function's code to be the problem.

I believe the error is happening elsewhere and Application object is getting lost.

If I try to re-associate DLL with Excel by sending "Application" object to the VB6 dll and try assigning it to Excel.Application variable, it executes the DLL function (which has an error trap) and the error I get is "#429 ActiveX component can't create object".

So this means that my link between ActiveX DLL (VB6) still exists, but I can no longer access the Excel.Application reference in order to read/write into Excel? Or Excel is no longer able to send Application object to my DLL? Could this be ActiveX permission issue? Or perhaps Application object getting corrupt somehow inside Excel? After all, if I start a fresh Excel session, everything works fine. If it was corrupt inside my DLL, I could re-associate the variable with new Application object, but that results in the #429 error.

Any thoughts?