Define VBA array from excel range

custno = Application.Transpose(Sheets("Affected Customers").Range("A1:A4").Value)

v = UBound(application.transpose([a1:a4].value),2)
'create 2-D array
?Ubound(v,1);ubound(v,2)
'returns 4   1
v = Application.Transpose(v)
?Ubound(v,1)
'returns 4
?Ubound(v,2)
'returns "Subscript out of range" ie no 2nd dimension

Private Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" ( _
    dest As Any, source As Any, ByVal bytes As Long)
 
Sub foo()
Dim varArr() As Variant
Let varArr = Range("A1:D1")
MsgBox ArrayDims(varArr)
Let varArr = WorksheetFunction.Transpose(varArr)
MsgBox ArrayDims(varArr)
Let varArr = WorksheetFunction.Transpose(varArr)
MsgBox ArrayDims(varArr)
End Sub
 
Function ArrayDims(arr As Variant) As Integer
    'http://www.vb2themax.com/Item.asp?PageID=TipBank&Cat=130&ID=27
    Dim ptr As Long
    Dim VType As Integer
 
    Const VT_BYREF = &H4000&
 
    ' get the real VarType of the argument
    ' this is similar to VarType(), but returns also the VT_BYREF bit
    CopyMemory VType, arr, 2
 
    ' exit if not an array
    If (VType And vbArray) = 0 Then Exit Function
 
    ' get the address of the SAFEARRAY descriptor
    ' this is stored in the second half of the
    ' Variant parameter that has received the array
    CopyMemory ptr, ByVal VarPtr(arr) + 8, 4
 
    ' see whether the routine was passed a Variant
    ' that contains an array, rather than directly an array
    ' in the former case ptr already points to the SA structure.
    ' Thanks to Monte Hansen for this fix
 
    If (VType And VT_BYREF) Then
        ' ptr is a pointer to a pointer
        CopyMemory ptr, ByVal ptr, 4
    End If
 
    ' get the address of the SAFEARRAY structure
    ' this is stored in the descriptor
 
    ' get the first word of the SAFEARRAY structure
    ' which holds the number of dimensions
    ' ...but first check that saAddr is non-zero, otherwise
    ' this routine bombs when the array is uninitialized
    ' (Thanks to VB2TheMax aficionado Thomas Eyde for
    '  suggesting this edit to the original routine.)
    If ptr Then
        CopyMemory ArrayDims, ByVal ptr, 2
    End If
End Function

Function TableToArray_StartCol3(wb As String, sht As Integer) As String()
'starts a col C1 and builds an array of single dimension that holds all strings 
 
ReDim A(1) As String
Dim rw As Long
rw = 0
Dim col As Long
col = 3
With Workbooks(wb).Sheets(sht)
    Dim c As Range
    For Each c In .Range("C1", FindLastCell(wb, sht)) 'counts left to right then next row down left to right etc.
         cnt = cnt + 1
         rw = rw + 1
         tmp = .Cells(rw, col).Value
 
         If tmp = "" Then 'check to see if cell was blank, if so reset row and col
            rw = 1
            col = col + 1
            tmp = .Cells(rw, col).Value 'get the value again
         End If
 
         If tmp <> "" Then 'if it was not blank store it
            ReDim Preserve A(cnt)
            A(cnt) = tmp
         End If
 
    Next
End With
 
TableToArray_StartCol3 = A
 
End Function 
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
 
FindLastCell(wb As String, sht As Integer) As Range
'found this online somewhere...can't remember
 
Dim theLastColumn As Integer
Dim theLastRow As Long
Dim theLastCell As Range
With Workbooks(wb).Sheets(sht)
    If WorksheetFunction.CountA(.Cells) > 0 Then
        'Search for any entry, by searching backwards by Rows.
        theLastRow = .Cells.Find(What:="*", after:=[a1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row
        'Search for any entry, by searching backwards by Columns.
                theLastColumn = .Cells.Find(What:="*", after:=[a1], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
                Set FindLastCell = .Range(Cells(theLastRow, theLastColumn).Address)
    End If
 
End With
End Function