Transfer Excel Table into a VBA array

[Chris Macro]

I know you can do this fairly simply with a Named Range (set myArray = [insert named range]), however when trying to do this with a table I am running into some difficulty. Is there a simple way to do this? Thanks for your thoughts!

 

[pgc01]

Hi

Let's assume you have a table called MyTable in Sheet1.
To copy the table data into an array, for ex.:

Dim myArray As Variant

myArray = Worksheets("Sheet1").ListObjects("MyTable").DataBodyRange.Value

 

[Chris Macro]

Thanks for the response! I tried your code and I am getting a "Type Mismatch" error. Not sure how that could be since the variable is set to variant? Any thoughts?

<font face=Calibri><SPAN style="color:#00007F">Dim</SPAN> sht <SPAN style="color:#00007F">As</SPAN> Worksheet<br><SPAN style="color:#00007F">Dim</SPAN> cell <SPAN style="color:#00007F">As</SPAN> Range<br><SPAN style="color:#00007F">Dim</SPAN> EmailList <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN><br><br><SPAN style="color:#00007F">Set</SPAN> sht = ThisWorkbook.Sheets(Settings.Name)<br><br><SPAN style="color:#007F00">'Need to check if range is 0,1,or >1</SPAN><br>    <SPAN style="color:#007F00">'TO:</SPAN><br>        <SPAN style="color:#00007F">Set</SPAN> EmailList = sht.ListObjects("Email_To").DataBodyRange.Value</FONT>

 

[pgc01]

Hi

Your syntax is wrong, not how I posted.


Either

[FONT=Calibri]
        EmailList = sht.ListObjects("Email_To").DataBodyRange.Value


[/FONT]

... and you get an array with all the values,

or

[FONT=Calibri]        [COLOR=#00007F]Set[/COLOR] EmailList = sht.ListObjects("Email_To").DataBodyRange[/FONT][FONT=Calibri]
[/FONT]

... and you get the range where the values are.

Please try.

 

[Chris Macro]

Gah, that was my problem all along! I don't know what I was thinking, silly mistake. Thanks for clearing that up for me

 

[pgc01]

I'm glad it helped. Thanks for the feedback.

 

[Chris Macro]

Hey PGC just a quick follow-up.. if my table only has one row in it, the variable turns into a string instead of an array. Is there anyway to check if the variable is a string or an array (without using Error Handlers)?

In my example if there is only one row in the table, my code errors out on the "elseif" line because the variable is a string and doesn't have an upper bound:

<font face=Calibri>    <SPAN style="color:#007F00">'TO:</SPAN><br>        EmailList = sht.ListObjects("Email_To").DataBodyRange.Value<br>        <br>        <SPAN style="color:#00007F">If</SPAN> IsEmpty(EmailList) <SPAN style="color:#00007F">Then</SPAN><br>            MsgBox "Please enter in an email address to send to."<br>            <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>        <SPAN style="color:#00007F">ElseIf</SPAN> <SPAN style="color:#00007F">UBound</SPAN>(EmailList) = 1 <SPAN style="color:#00007F">Then</SPAN><br>            CompiledListTo = EmailList(1, 1)<br>        <SPAN style="color:#00007F">Else</SPAN><br>            CompiledListTo = Join(Application.Transpose(EmailList), "; ")<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN></FONT>

 

[pgc01]

Hi

You can test if it is an array, like:

       If IsEmpty(EmailList) Then
            MsgBox "Please enter in an email address to send to."
            Exit Sub
        ElseIf Not IsArray(EmailList) Then
            CompiledListTo = EmailList
        Else
            CompiledListTo = Join(Application.Transpose(EmailList), "; ")
        End If

 

[Chris Macro]

Worked perfectly. Thanks for all your help!!!

 

[pgc01]

Glad it's working OK. Cheers!