UDF Function - VBA code getting data online smarter

Rijnsent

Well-known Member
Joined
Oct 17, 2005
Messages
1,382
Office Version
  1. 365
Platform
  1. Windows
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:
Code:
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
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Forum statistics

Threads
1,213,490
Messages
6,113,957
Members
448,535
Latest member
alrossman

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 MrExcel.com.
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 "mrexcel.com".
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
Back
Top