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 cpearson.com/excel/ArraysAndRanges.aspx... and that these are 2D arrays. — BigBen
– BigBen, Commented Mar 20, 2020 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
– Ron Rosenfeld, Commented Mar 20, 2020 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. stackoverflow.com/questions/37689847/… — Tom Sharpe
– Tom Sharpe, Commented Mar 20, 2020 at 17:16

Gary's Student · Accepted Answer · 2020-03-20 17:31:51Z

3

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

edited Mar 20, 2020 at 17:31

answered Mar 20, 2020 at 17:12

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

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

Comments

Tom Sharpe · Accepted Answer · 2020-03-20 17:20:01Z

1

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.

answered Mar 20, 2020 at 17:20

Tom Sharpe

35.1k5 gold badges29 silver badges47 bronze badges

1 Comment

ambitiose_sed_ineptum Over a year ago

Thank you! This helped my get it to my expected format

Collectives™ on Stack Overflow

Getting an array of strings from cells in excel - VBA

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