Any way I can get around with the error message? "aumation e


New Member
Jul 30, 2002

I'm a novice for VBA. My quesiton is that I'm writing some UDFs which can do some calculation dynamically on stock prices. To achieve that, I hav my spreadsheet link to stock quote system, which can pull dynamic data continuously.

Unfortunately, wherever open my model, I got the "automation error" message. I think the reason is mainly because my UDFs can not have valid reference as the stock quote systerm has a time lag before retrive any data.

Therefore, I really appreciate if someone can tell me how I can delay my UDFs to start to calculate.(wait till stock price is retrived) In excel formula, I can use "if(iserror(a1), 0, "do calculation"), but do I have the similar method in VBA?

It is time sensitive and I appreicate your help.


Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Thanks for the instruction.

It doesn't work, unfortunately. In my spreadsheet. I wrote UDF to calculate the value of call option, which is a function of : CallOption(stock, exercise, time, interest, volatility).

As you can see from the input above, I need to extract those data from quote system. I've done this many times. The only difference is this time the scale is bigger (require 8 UDF calculation at the same time vs. two UDF before), is it matter?

I follow your instruction, using: if iserror(stock) or iserror(exe) or...
then exit function.

Please help.

Upvote 0
Please Help!

I'd like to know how I can postpone UDF to execute.

I've tried the iserror function but it just doesn't work.( I have more than one variable, so I juse if (iserror("a1")) or (iserror("a2")) or....


Upvote 0

You don´t say which version of XL You run but I guess 2000 or later.

The Automation-errormessage indicate that you use a property that does not apply to the object. If this is not the case perhaps You use an unregistered Active-X component.

The best way is to step through Your code via F8-button.

Do You have any errorhandling in Your code?

Upvote 0
Dennis and Juan,

Thanks for you reply.

I use Excel 2000 and my OS is Win XP.

I did try "On error resume next" statement but it doesn't work. Also, I tried both iserror(stk) and iserror("stk") since I don't know if I include the quotation marks. However, it...doesn't work.

As for F8, I can't not even step through my UDF as the error message appears right after I open the file. This is the code:

Function CallISD1(stk, exe, tme, r, optprice)
'stk, exe, tme, r, optprice are dynamic data pulled from quote system'' Is it the reason why?'
On Error Resume Next
If IsError(optprice) Or IsError(stk) Or IsError(tme) Or IsError(r) Or IsError(exe) Then
Exit Function
H = 2
L = 0
Do While (H - L) > 0.0001
If CallOptn(stk, exe, tme, r, (H + L) / 2) > optprice Then
H = (H + L) / 2
Else: L = (H + L) / 2
End If
CallISD = (H + L) / 2
End If
End Function

Besides, whenever I got this automation error, I can only change the name of my UDF to interrupt the calculation. After I did that, the strange thing happened:

In VBE, I will automatically have some extra new worksheets but those are not found in EXCEL, nor I can delete them.

Thank you very very much

Juan, again my apology to bother you but this problem is such a pain....

Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back