CODEWITHSUNDEEP
arraysexcelvba
Mikey
Mikey

Reputation: 119

Create an array in VBA from the output of a dynamic array function

I just subscribed to ExcelPriceFeed.com and got access to their dynamic array functions.

One of them is EPF.Yahoo.OptionsChain.ExpiryDates

It works when used in an excel cell... EPF.Yahoo.OptionsChain.ExpiryDates in a cell

Now I want to call this dynamic array function within a VBA sub and put that list of dates into an array (after which I will loop through the array and create a new tab for each date). Is this possible? My code that I've written is...

Dim wsUser As Worksheet
Set wsUser = ActiveWorkbook.ActiveSheet
Dim sgTicker As String
'sgTicker = InputBox( _
'        "Please input a ticker symbol to extract dates for..." _
'        , "Input a Ticker Symbol" _
'        , "META" _
'        )
sgTicker = "META"
Dim a As Variant
a = wsUser.Evaluate("EPF.Yahoo.OptionsChain.ExpiryDates(""" & sgTicker & """)")

After running the code, variable a contains the error 2042.

Does anyone know how to make it work?

Otherwise, I'll have to write some code that creates a temporary worksheet and loop through the results in the worksheet.
The cells get a defined name of "A1#". How would I loop through this range?

Upvotes: 1

Views: 132

Answers (1)

Andy Sinclair
Andy Sinclair

Reputation: 2303

The problem you are experiencing is due to the synchronous nature of VBA/Excel and the asynchronous nature of the function that you are using.

You can try the synchronous version of the function as this will wait for a result to be returned.

Replace the last line of your code with this:

a = wsUser.Evaluate("EPF.Yahoo.VBA.OptionsChain.ExpiryDates(""" & sgTicker & """)")

The software you are using, Excel Price Feed, has a bunch of synchronous functions that you can use from VBA, more details here: https://www.excelpricefeed.com/userguide/excel-vba-functions-yahoo-finance

I hope this helps.

Upvotes: 0

Related Questions