VBA Explicitly Request Variant Array As Opposed to String Value

Question

I have a combobox I am trying to populate using a range in an excel sheet. I extract the last row of the column, and then add all values in that range to the combo box. The issue is, if only one entry is there, Excel will extract just the single value as a string from the array, as opposed to a variant array.

This results in a type mismatch, because it is trying to assign the value as a string to a variant array. Is there a way to explicitly request a variant array as opposed to a the string value?

Here is the original code:

Dim hvVals() As Variant
lastRow = getLastRowInCol(Sheets("MySheet"), "B")
hvVals = Sheets("MySheet").Range("B2:B" & CStr(lastRow)).value
If Not isEmpty(hvVals) And lastRow <> 1 Then
Me.CECB.list = hvVals

Here is my attempt at a solution, but this does slow down my code a little and I don't know if it is very robust: (Also, the first row in the sheet is technically 2, which is why it's in the if statement)

Dim hvVals() As Variant
lastRow = getLastRowInCol(Sheets("MySheet"), "B")
If lastRow = 2 Then
    ReDim hvVals(1) As Variant
    hvVals(0) = Sheets("MySheet").Range(hvCol & "2:" & hvCol & CStr(lastRow)).value
Else
    hvVals = Sheets("MySheet").Range(hvCol & "2:" & hvCol & CStr(lastRow)).value
End If
If Not isEmpty(hvVals) And lastRow <> 1 Then
    Me.CECB.list = hvVals

When you pick up the Value of a (multi-cell) range you get a 2D variant array (1 to #rows, 1 to #columns): if you want to mimic that for a single cell you also need a 2D array (1 to 1, 1 to 1) — Tim Williams
– Tim Williams, Commented Aug 18, 2016 at 16:28

Sgdva · Accepted Answer · 2016-08-18 15:27:36Z

2

Try this approach
1. Take out the "()" from the variant declaration -if it's going to be an array your code logic should handle it-.
2. You don't need to convert to string the number in this line Sheets("MySheet").Range("B2:B" & CStr(lastRow)).value,I wouldn't risk it to cause a random error, change it for Sheets("MySheet").Range("B2:B" & lastRow).value

Prior "()"

After "()" was taken

3. My advise -if you really need it variable- would be to check for the Ubound of the variable and, if it's greater than 0, it means more than one value was found and do your handling accordingly.
4.Why not to make an array of ranges instead? You can play easier with that since you can get the ".value" property that way.

edited Aug 18, 2016 at 15:27

answered Aug 18, 2016 at 15:21

Sgdva

2,8103 gold badges19 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

RWA4ARC Over a year ago

Trying 1 seems to work. I also think I will try 4 if I run into any more issues. I think I originally chose a variant because I wasn't sure if combobox.list would handle taking in a range.value type. But I do normally work with ranges. Thanks for the help!

Collectives™ on Stack Overflow

VBA Explicitly Request Variant Array As Opposed to String Value

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related