Strange Add-In Problem (Hard to Duplicate)

[blackscholes]

I have a very strange problem and I can't seem to diagnose it because it's hard to duplicate/replicate.

I have an Excel 2003 spreadsheet with VBA code that performs various functions including calling an Add-In that pulls data from servers. The spreadsheet has been working for a long time and the VBA code has not been touched. Lately, everytime I run the code, it first calls the Add-in and as it begins processing, I get "Application-defined or object defined error". I keep pressing OKAY and the problem goes away and continues operating.

I can't seem to figure out why the problem is occuring. I tried repairing my Excel and even asked IT to delete temp files. This behavior is erratic and I don't think it has anything to do with my VBA code, except one line of code that calls the Add-in which I have no control over since it is from a different vendor.

I've searched the Internet far and wide and most issues has to with poorly written VBA code but I don't think its my VBA code that's causing the issue. I think it's the Add-in but I'm not sure because it always produces this error when it attempts to call the Add-In. However, sometimes I never get this error so I'm really confused.

Any help is appreciated?

[Norie]

Can you post the line of code the error occurs on?

[blackscholes]

I can guess where the error is occuring. See the arrow below

VENDORaddin.GetLicenseStatus "EMAIL", "PASSWORD", freeLicCount, usedLicCount, diagnostic

If (freeLicCount < 1 Or diagnostic <> "") Then
MsgBox "Not enough license"
Exit Sub
End If

Vendoraddin.Login "EMAIL ", "PASSWORD", errcode, diagnostic, 1

If errcode <> 0 Then
MsgBox "Error loggin in"
Exit Sub
End If

VENDORaddin.ClearCache
VENDORaddin.calcactivesheet <-----Error occurs here

[blackscholes]

Bump..anyone? LOL

[MrShorty]

That statement appears to be calling a procedure in Vendoraddin. We would almost certainly need to see the code for that procedure to try to understand what that procedure is doing.

Unless, as with the Login procedure, the programmer passes back some kind of error code that could be used to determine what error is occuring with calcactivesheet. You would have to check the documentation that came with vendoraddin to see if there is any discussion of error codes and what they mean (if that procedure even uses them).

I don't know if we can help much without knowing what vendoraddin.calcactivesheet is trying to do.

[blackscholes]

So I'm still having the same issue. It's really hard to figure out what's going on when the Add IN is called and I dont think I can access the code. Does anyone think that it may be something to do with my Excel spreadsheets? Anything I can do to possibly clear temp files, registries, etc... or to check my settings?

[blackscholes]

Okay so I debugged it further and have determined the following :

1) When I copy and paste the problematic sheet to a new workbook and run it, the problem disappears. The error goes away
2) The problem continues to occur in the workbook that I've been using which contains other sheets.

Is there something going on when I copy over to a new workbook?

[mc84excel]

Is there something going on when I copy over to a new workbook?

My guess: There is something in the original WB that is conflicting with the sheet. Copying the sheet to a new WB removes this conflict causing it to run as expected.

[blackscholes]

I am getting closer!!!!!

I noticed that the Excel spreadsheet status bar shows Calculating 0% and runs up to 100% then Ready and then repeats. The weird thing is that, it's not really calculating anything. This is possibly related to an WorkSheet_Changed event that keeps getting called and I'm not sure why. But what's happening is when the ADD-IN is called and performs various calculations, this Worksheet_Changed event is also triggered which may conflict.

I disabled the event before the ADD-IN is called and re-enabled it after and the problem disappeared.

Does this make sense?