Visual Basic > API and Miscellaneous

Reading the contents of an Excel Range

Reading the contents of an Excel Range

The following code can be used to quickly extact the values of an Excel Range into an array.

'Purpose     :  Reads the values of a range into an array (much quicker than looping through a range)
'Inputs      :  rngInput                The range to extract the values from.
'               avValues                See outputs.
'Outputs     :  Returns the True on success.
'               avValues                An 2d array containing the values in the range.
'Example     :  Call RangeToArray(Worksheets(1).Range("A1:K1000"), avValues)


Function RangeToArray(rngInput As Object, avValues As Variant) As Boolean
    On Error GoTo ErrFailed
    avValues = Empty
    avValues = rngInput.Value
    RangeToArray = True
    
    Exit Function

ErrFailed:
    'Failed
    Debug.Print "Error in RangeToArray: " & Err.Description
    Debug.Assert False
    RangeToArray = False
    On Error GoTo 0
End Function