Getting an array of strings from cells in excel - VBA

Question

I am working on a program that needs to read an array of values from cells in another worksheet in the same workbook. I am able to read a single value just fine, but when I try to read multiple, I cannot return an array.

Here is what I am trying to do:

    Dim list() As Variant
    list = ActiveWorkbook.Worksheets("Sheet2").Range("A2:C2").value
    Debug.Print TypeName(list)
    Debug.Print UBound(list)
    Debug.Print LBound(list)
    Debug.Print TypeName(list(UBound(list)))

For which the output is:

Variant()
 1 
 1 
Subscript out of range

However, If I try it where I expect a single string, instead of an array of strings

    Dim value As String
    Let value = ActiveWorkbook.Worksheets("Site IDs and CJONs").Range("A2").value
    Debug.Print TypeName(value)
    Debug.Print value

for which I get the output

String
Expected Value

According to this question I should be able to simply return an array from the range function (example from the answer below), but it doesn't seem to be working for me. What am I doing wrong?

    Dim DirArray As Variant
    DirArray = Range("a1:a5").Value

Note that a single cell is a special case, see http://www.cpearson.com/excel/ArraysAndRanges.aspx... and that these are 2D arrays. — BigBen, Mar 20 '20 at 16:58
`List` is a 2D array if there is more than one cell, so you need to refer to both. eg `debug.print list(1,1)` — Ron Rosenfeld, Mar 20 '20 at 16:59
This answer shows how you can use transpose to make it a 1d array. If your range is a row, you need to use transpose twice. https://stackoverflow.com/questions/37689847/creating-an-array-from-a-range-in-vba — Tom Sharpe, Mar 20 '20 at 17:16

Gary's Student · Answer 1 · 2020-03-20T17:31:51.320

Although it is not obvious, this:

Dim DirArray As Variant
DirArray = Range("a1:a5").Value

actually is like:

Dim DirArray(1 To 5, 1 To 1) As Variant

DirArray(1, 1) = Range("A1").Value
DirArray(2, 1) = Range("A2").Value
DirArray(3, 1) = Range("A3").Value
DirArray(4, 1) = Range("A4").Value
DirArray(5, 1) = Range("A5").Value

Pulling a set of cells into an array usually makes a 2-D array.

NOTE:

If you want to go from array to worksheet cells then, for example:

Sub ytrewq()
    Dim DirArray(1 To 5, 1 To 1) As Variant

    DirArray(1, 1) = "Larry"
    DirArray(2, 1) = "Moe"
    DirArray(3, 1) = "Curly"
    DirArray(4, 1) = "Shepp"
    DirArray(5, 1) = "James"

    Range("B9").Resize(5, 1) = DirArray
End Sub

score 1 · Answer 2 · answered Mar 20 '20 at 17:20

I might as well put my comment as an answer:

Option Explicit
Sub test()    
Dim list As Variant
    list = Application.Transpose(Application.Transpose(ActiveWorkbook.Worksheets("Sheet1").Range("A2:C2").Value))
    Debug.Print TypeName(list)
    Debug.Print UBound(list)
    Debug.Print LBound(list)
    'Debug.Print UBound(list, 2) Error
    'Debug.Print LBound(list, 2) Error
    Debug.Print TypeName(list(UBound(list)))
    Debug.Print list(UBound(list))
End Sub

Gives output:

Variant()
 3 
 1 
String
x

where C2 contains letter x.

Thank you! This helped my get it to my expected format – ambitiose_sed_ineptum Mar 20 '20 at 17:23 — ambitiose_sed_ineptum, Mar 20 '20 at 17:23

Getting an array of strings from cells in excel - VBA

2 Answers2