CODEWITHSUNDEEP
vbaexcel
RWA4ARC
RWA4ARC

Reputation: 109

VBA Explicitly Request Variant Array As Opposed to String Value

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

Upvotes: 0

Views: 367

Answers (1)

Sgdva
Sgdva

Reputation: 2800

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 "()" enter image description here

After "()" was taken enter image description here


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.

Upvotes: 2

Related Questions