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

[JJ59]

Hi,

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.

JJ

 

[Juan Pablo González]

The IsError function also exists in VBA.

If IsError(MyVariable) then Exit Function

 

[JJ59]

Juan,

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.

JJ

 

[JJ59]

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....

TIA

JJ

 

[XL-Dennis]

JJ59,

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?

HTH,
Dennis

 

[JJ59]

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
Else
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
Loop
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....

JJ