VB: Type Mismatch Error, Array Dimensions 1x1

Question

I am trying to use a Variant datatype to store an array of strings. This is an example of the code I am using.

Sub Selecto()

Dim numRows As Integer    
Dim Badger As Variant

numRows = InputBox("How many rows?", "Rows",0)
Badger = Range("C21:C"& 21 -1 + numRows).Value    

For i = 1 To UBound(Badger)
    MsgBox (Badger(i, 1))
Next

End Sub

My goal is to make it so that Badger is an array whose length can be defined by the user running the macro via the InputBox. The code works just fine, except when the value of numRows entered is 1 it spits out a "type mismatch error".

The error causes the line For i=1 to UBound(Badger) to be highlighted and the error message box says that there is a "type mismatch".

I'm guessing that for a 1-by-1 array, the data is simply stored as a string rather than as an array and that array functions like Ubound() and array index syntax like Badger(i,1) don't work. Is that the case? Is there a way to force it to recognize that variable as an array?

Answer 1

If only one value is read from the range then the variant doesn't create an array, which is why you get a type mismatch when trying to index it. Try checking if Badger is an array with IsArray(Badger). The code below works when I use it:

Sub Selecto()
    Dim numRows As Integer
    Dim Badger As Variant

    numRows = InputBox("How many rows?", "Rows", 0)

    Badger = Range("C21:C" & 21 - 1 + numRows).Value

    If IsArray(Badger) Then
        For i = 1 To UBound(Badger)
            MsgBox (Badger(i, 1))
        Next
    Else
        MsgBox Badger
    End If
End Sub