CODEWITHSUNDEEP
excelvbavariant-array
Mikey
Mikey

Reputation: 119

Displaying a Variant Array Value in the Immediate Window

I'm using a third party add-in, which seems to work ok. However, I'm not able to display the value of a variant array element in the immediate window. I have found ways around it, but I'd still like to know what the problem is and how to workaround it.

There is an object variable which contains data.

Dim odsDataSeries As DataSeries
Set odsDataSeries = odfData.GetSeries("ELY(high)")

In the Watch Window you can see the variant array called "Index" ...

enter image description here

In the immediate window I type...

?odsDataSeries.Index(1)

but it gives error message "Wrong number of arguments or invalid property assignment"

If I use join then I get the data...

?join(odsDataSeries.Index)
3/01/2020 2/01/2020 31/12/2019 etc...

In my code I was also able to write...

Dim v As Variant
v = odsDataSeries.Index
Stop

Then when I query an element in v in the Immediate Window, it works...

?v(1)
2/01/2020

So my question is...Why didn't my Immediate Window query "?odsDataSeries.Index(1)" work? For a while I thought Index wasn't public and I couldn't write a loop to process the array (and I still can't unless I put the array in another variable as shown above). Note that writing this in my code gives the same error message (on compiling).

I'm adding my whole sub for reference. Note that I used early binding. The excel add-in is a .xll file and I can't see the code inside (because I haven't got COM skills yet).

Dim av As New AlphaVantageExcelDataCOMFunctions

Public Sub PublicLoadData()
On Error GoTo 0
Dim odfData As DataFrame
    Set odfData = av.AVGetEquityTimeSeries("ELY", "Daily", True, "compact")
Dim odsDataSeries As DataSeries
    Set odsDataSeries = odfData.GetSeries("ELY(high)")
Dim v As Variant
    v = odsDataSeries.Index
    Debug.Print GetVariableType(odsDataSeries.Index)
    Stop
End Sub 'PublicLoadData

My GetVariableType() function returns "Array of Variant". Let me know if you want this code.

So the problem, again, is...In the immediate window I type...

?odsDataSeries.Index(1)

but it gives error message "Wrong number of arguments or invalid property assignment"

If I use join then I get the data...

?join(odsDataSeries.Index)
3/01/2020 2/01/2020 31/12/2019 etc...

When I query an element in v in the Immediate Window, it works...

?v(1)
2/01/2020

Here is a link to the DataSeries Documentation

Upvotes: 2

Views: 1397

Answers (1)

omegastripes
omegastripes

Reputation: 12612

Let's say there is an object someObject with a method (or property) getArray having no input arguments and returning an array.

You may retrieve an array by doing arr = someObject.getArray which is the same as arr = someObject.getArray() with no arguments passed explicitly. To retrieve a single element from that array by one line code you should do someObject.getArray()(0), where the first parentheses refer to the method call, and the second indicate the index of the element in the array.

Note, although the expression someObject.getArray()(i) is good for single element request, e. g. in Immediate window, but if used in loops, it might result in a lot of overhead to retrieve the array each iteration. So you should put the array into variable prior to the loop.

Here is the example showing some tricky behavior of commonly used Dictionary object. You can retrieve an element from array of keys returned by method simply by dict.Keys(i) with early bind:

Sub testEarlyBoundDict()

    ' Need to include a reference to "Microsoft Scripting Runtime"

    Dim earlyBoundDict As Dictionary
    Set earlyBoundDict = New Dictionary
    Set earlyBoundDict = CreateObject("Scripting.Dictionary")
    earlyBoundDict("myKey") = "myValue"
    Debug.Print earlyBoundDict.Keys(0)

End Sub

But if you use late bind then you have to write additional parentheses:

Sub testLateBoundDict()

    ' Need no references

    Dim lateBoundDict As Object
    Set lateBoundDict = CreateObject("Scripting.Dictionary")
    lateBoundDict("myKey") = "myValue"
    Debug.Print lateBoundDict.Keys()(0)
    Debug.Print lateBoundDict.Keys(0) ' fails

End Sub

To create fail-proof code I would suggest always use someObject.getArray()(i).

Upvotes: 2

Related Questions