Simple way to load a worksheet table into a VBA array?

[JenniferMurphy]

I need to import a worksheet table into a VBA array. I found several solutions, but they all seemed unnecessarily complicated. One even involved about 20 lines of code.

Isn't there a simple expression do do this? I have assigned a name in the worksheet to the table.

Thanks

 

[JenniferMurphy]

Try this...

YourVariable = Join(Application.Index(Range("C5:G5").Value, 1, 0))

Thanks

 

[JenniferMurphy]

Since you have 365, this also works:

YourVariable = Evaluate("TEXTJOIN("" "",0,C5:G5)")

or use WorksheetFunction.TextJoin.

Thanks, I'll give that a try, also.

 

[JenniferMurphy]

If it's not an actual Excel table, you just assigned a name to a (contiguous) range of cells:

Arr = Range("MyData").Value

Does it matter whether I declare Arr as an array?

Dim Arr()
Arr = Range("MyData").Value

Or as a scalar?

Dim Arr
Arr = Range("MyData").Value

It seems to work both ways.

 

[JenniferMurphy]

One more question. It appears that data read in this way creates a 2-dimensional array even if the data is a single row or column. Is that correct? Or did I do something wrong?

These statements are in the module outside any code:

Const nrAlbumNums As String = "AlbumNums"
Private AlbumNums() As Variant

This code uses these variables:

AlbumNums = Range(nrAlbumNums).Value

For row = LBound(AlbumNums) To UBound(AlbumNums)
  temp = AlbumNums(row, 1)
      . . .
Next row

I first tried it with a single subscript, but got an error.

I'm OK with this. It probably makes sense. Just curious.

Thanks

 

[Rick Rothstein]

VB will automatically create a two-dimensional array from any multi-cell range assigned to (I always use) a Variant variable. You can force it to return one-dimensional arrays from single rows or single columns like this...

SingleRowArray = Application.Index(Range("C5:X5").Value, 1, 0)

SingleColumnArray = Application.Transpose(Range("C5:C26").Value)

Note: These arrays will all start with index 1 (not 0).

 

[JenniferMurphy]

VB will automatically create a two-dimensional array from any multi-cell range assigned to (I always use) a Variant variable. You can force it to return one-dimensional arrays from single rows or single columns like this...

SingleRowArray = Application.Index(Range("C5:X5").Value, 1, 0)

SingleColumnArray = Application.Transpose(Range("C5:C26").Value)

Note: These arrays will all start with index 1 (not 0).

Thanks