VBA: Copy range to 2D Array

[blothian]

As a continuation of a current macro I'm writing ( read 1st post here http://www.mrexcel.com/forum/excel-...ect-invoked-has-disconnected-its-clients.html for brief explanation).

Now that I can access the content in the workbook's worksheets, I have written a simple function to calculate the last row in a worksheet:

Public Function CountChainageRows(ws As Excel.Worksheet) As Long
' ---------------------------------------------------------------------
'   Find the number of the last row in the chainage column
' ---------------------------------------------------------------------
    CountChainageRows = 0


    CountChainageRows = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    FrmPWayLevel.TB_Chainage.Value = CountChainageRows
End Function

I use this in a function which has a purpose of copy data from certain columns into a 2D array like so:

Public Function ReadWorkSheet(ByRef data() As String)
' ---------------------------------------------------------------------
'   Copy cells from selected worksheet to 2D array
' ---------------------------------------------------------------------
    Dim R                                    As Long
    Dim C                                    As Long
    
    ReDim data(0)
    data = OCurrentWs.Range("C3:C & CountChainageRows(OCurrentWs)")
    For R = 1 To UBound(data, 1)    ' First array dimension is rows.
        For C = 1 To UBound(data, 2)    ' Second array dimension is columns.
            Debug.Print data(R, C)
        Next C
    Next R
End Function

OCurrentWs is a public variable and its value is set each time a listbox item is clicked and in turn ReadWorkSheet would then be called. However, the line data = OCurrentWs.Range("C3:C & CountChainageRows(OCurrentWs)") is giving the error Method 'Range' of object '_worksheet' failed. I've put a break in the line before it errors and can see OCurrentWs has a value assigned to it so I'm unsure what else is wrong.

 

[shg]

You need to read the range into a Variant:

Public Function ReadWorkSheet(ByRef [COLOR="#FF0000"]data As Variant[/COLOR])
' ---------------------------------------------------------------------
'   Copy cells from selected worksheet to 2D array
' ---------------------------------------------------------------------
    Dim R                                    As Long
    Dim C                                    As Long
    
    data = OCurrentWs.Range("C3:C & CountChainageRows(OCurrentWs)").Value2

 

[dmt32]

Hi,
Try changing the line as follows

data = OCurrentWs.Range("C3:C" & CountChainageRows(OCurrentWs)).Value

Data parameter should be a variant

Dave

 

[blothian]

Thanks for the replies and spotting the quotation mark position error.
Is there a specific reason why an array of strings doesn't work? I've used variant arrays in other macro's but can't recall why I did at the time.

 

[shg]

Because the Value (or Value2) property of a multi-cell range returns a variant array, and that can only be assigned to a Variant.

WorksheetFunction.MMult returns the products of two matrices, which would always be an array of Doubles, but you still can't assign it to a dynamic array or type Double, just because the function returns the array as a Variant.

 

[shg]

array or type Double

array of type Double