UDF Function - VBA code getting data online smarter

[Rijnsent]

Hi all,

I'm currently working on a series of UDFs (user defined functions) in VBA.
What the code basically does is e.g. pick up a daily stock rate from Yahoo Finance.
So I created a function named DAILY_STOCK_RATE. It works like this: DAILY_STOCK_RATE(stockcode,date,"OPEN"), which will pull in the info from Yahoo Finance with a web query. The issue is that my workbook is getting rather slow when I have more than a handful of that function in my sheet. If I have over 10 times that function on a sheet and try to refresh, several give back a zero value (randomly).

Has anyone dealt with this before? What would be a smarter way to get these functions working?

Thanks for any ideas,

Koen


My pseudocode looks like:

Function DAILY_STOCK_RATE(stockcode as String, RateDate as Date, Optional RateType As String)

'Several Dims
Application.Volatile

'Check input variables
'Send out GET command 
Set objHTTP = CreateObject("WinHttp.WinHttpRequest.5.1")
On Error Resume Next
objHTTP.Open "GET", Url
objHTTP.Send

'Process return value
DAILY_STOCK_RATE = returned_processed_value

End Function