Loading single value to VBA array

Question

I've been running a VBA code in Excel 2016 that loads a bunch of values into array from a single-column Excel table and then runs a FOR-loop on each value.

Sub Load_array()

Dim x As Long
Dim myArray() As Variant
Dim myTable As ListObject

Set myTable = Sheets("Sheet1").ListObjects("Table1")
TempArray = myTable.DataBodyRange.Columns(1)

myArray = Application.Transpose(TempArray)

For x = LBound(myArray) To UBound(myArray)

Range("A1").Value = myArray(x)

Next x

End Sub

Works fine up until I found a "bug". In case there is only a single row in that DataBodyRange, I'll get an error:

myArray = Application.Transpose(TempArray)

"Run time error '13': Type mismatch"

I could use a worksheet-based Excel COUNTA-formula and add IF-clause to re-route the code in case there is only a single value to be loaded, however perhaps there's a more elegant way to handle this? My experience on working with arrays is not extensive at all, so perhaps you guys could guide me a little?

Transposing a single-value array does not work like that, right?

There's no real shortcut here - you will need an If to take a separate route if there's only one cell. — Tim Williams
– Tim Williams, Commented Feb 3, 2020 at 1:27

Dy.Lee · Accepted Answer · 2020-02-03 02:27:59Z

1

Check if it's an array or exit.

Sub Load_array()

Dim x As Long
Dim myArray() As Variant
Dim TempArray
Dim myTable As ListObject

Set myTable = Sheets("Sheet2").ListObjects("Table1")
TempArray = myTable.DataBodyRange.Columns(1)
If Not IsArray(TempArray) Then '<~~ Check array
    Range("a1") = TempArray
    Exit Sub
End If

myArray = Application.Transpose(TempArray)

For x = LBound(myArray) To UBound(myArray)

    Range("A1").Value = myArray(x)

Next x

End Sub

answered Feb 3, 2020 at 2:27

Dy.Lee

7,5471 gold badge14 silver badges14 bronze badges

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

Comments

TechnoDabbler · Accepted Answer · 2020-02-03 04:07:28Z

1

Or force the single value into an array so it can be processed consistently ...

Option Explicit

Public Sub Load_array()

    Dim myRange As Range
    Dim myArray
    Dim x As Long

    Set myRange = Worksheets("Sheet1").ListObjects("Table1").ListColumns(1).DataBodyRange
    myArray = IIf(myRange.Rows.Count = 1, Array(myRange), Application.Transpose(myRange))

    For x = LBound(myArray) To UBound(myArray)
        Range("A1").Value = myArray(x)
    Next x
End Sub

ref: Force the creation and "looping" of single element arrays? (VBA)

answered Feb 3, 2020 at 4:07

TechnoDabbler

1,2751 gold badge7 silver badges12 bronze badges

1 Comment

dotsent12 Over a year ago

Although the previous answer solved my problem and felt easier to follow, I appreciate the input and will review this. Given I don't actually want to kill the Sub with single-value array but rather still process it, this one feels more efficient - once I'll get my head around it. Extra link surely helps too. Thanks!

Collectives™ on Stack Overflow

Loading single value to VBA array

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related