Problem With Variant

I'm at a loss to explain how the following piece of code could result in what it is generating. If anyone could offer any comment, I would appreciate it.

Dim vTmp As Variant
Debug.Print " VarType(vRetLst): " & VarType(vRetLst)
Debug.Print " Bounds: " & LBound(vRetLst) & " -> " & UBound(vRetLst)
For Each vTmp In vRetLst
Debug.Print vTmp
Next vTmp

Result, in the Immediate Window:

VarType(vRetLst): 8204
Bounds: 0 -> 0
3DP type: 8
DRA type: 8
ENS type: 8
PAV type: 8
PRF type: 8
PRP type: 8
STR type: 8
SWM type: 8
TOP type: 8
UTL type: 8

I'm trying to populate a combobox with the strings being shown here, but they are not making it. While trying to check them out, I saw this and am wondering how the For Each could be looping that many times through a variant array containing only one item (according to the LBound and UBound).

vRetLst is the result of an ADO RecordSet.GetRows, for what that may be worth.

I got around this problem by using the For Each, but was hoping to understand this better. I won't understand why I used the For Each here, because I wouldn't normally do so with a variant array of strings.

 

Check the documentation for the .GetRows method for the returned type. Also that for the LBound and UBound functions for the optional second argument.

 

You have an array of variants, which is the data being returned [that's the meaning of the 8204 number].

Then you have a loop that says for every variant in that array, print it to debug window displaying that that variant's value is a string.
I'm at a loss to explain how the following piece of code could result in what it is generating. If anyone could offer any comment, I would appreciate it.

Dim vTmp As Variant
Debug.Print " VarType(vRetLst): " & VarType(vRetLst)
Debug.Print " Bounds: " & LBound(vRetLst) & " -> " & UBound(vRetLst)
For Each vTmp In vRetLst
Debug.Print vTmp
Next vTmp

Result, in the Immediate Window:

VarType(vRetLst): 8204
Bounds: 0 -> 0
3DP type: 8
DRA type: 8
ENS type: 8
PAV type: 8
PRF type: 8
PRP type: 8
STR type: 8
SWM type: 8
TOP type: 8
UTL type: 8

I'm trying to populate a combobox with the strings being shown here, but they are not making it. While trying to check them out, I saw this and am wondering how the For Each could be looping that many times through a variant array containing only one item (according to the LBound and UBound).

vRetLst is the result of an ADO RecordSet.GetRows, for what that may be worth.

I got around this problem by using the For Each, but was hoping to understand this better. I won't understand why I used the For Each here, because I wouldn't normally do so with a variant array of strings.

 

Between the answers from you and VBA, it has all become quite clear (and
obvious, in hindsight.)

Thank you, very much.

 

Between the answers from you and fantum, it has all become quite clear (and obvious, in hindsight.)

Thank you, very much.

You have an array of variants, which is the data being returned [that's the meaning of the 8204 number].

Then you have a loop that says for every variant in that array, print it to debug window displaying that that variant's value is a string.