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

 

[Rick Rothstein]

Assuming the variable Arr is declared as a Variant and that you are loading a table named "Table1" located on Sheet1 into it, then this should do it...

Arr = Sheets("Sheet1").ListObjects("Table1").DataBodyRange

 

[Eric W]

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

Arr = Range("MyData").Value

 

[JenniferMurphy]

Assuming the variable Arr is declared as a Variant and that you are loading a table named "Table1" located on Sheet1 into it, then this should do it...

Arr = Sheets("Sheet1").ListObjects("Table1").DataBodyRange

If data is coming from the calling sheet, can I eliminate the Sheet("Sheet1")? That way I can call it from other sheets in the same workbook.

 

[JenniferMurphy]

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

Arr = Range("MyData").Value

Amazing. I thought I tried that, but I must have done something wrong.

Thank you.

PS: Yes, it's just a named contiguous range, not an actual table. I've got to be more careful with the terminology.

 

[Eric W]

Glad we could help!

 

[JenniferMurphy]

I thought maybe my error was that I declared Arr as a dynamic array.

Dim Arr()

But I just tried it and it works that way as well. Is there any difference depending on whether I declare it as an array or not?

 

[JenniferMurphy]

PS: On a (marginally) related question, is there a way to import a row of text values into a space delimited scalar variable?

Suppose I have assigned the name "Initials" to the range (C5:G5) containing the values "BC, JC, WC, BS, & PS". Can I import those values into a variable as a space delimited string ("BC JC WC BS PS") with a single VBA statement?

 

[Rick Rothstein]

Try this...

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

 

[Eric W]

Since you have 365, this also works:

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

or use WorksheetFunction.TextJoin.