Excel passing a range into a function

Question

how do you go about passing a range of cells into a function so that you can then process the items separately. Therefore how do I pass a range of cells into a function.

I'm trying to do it so I can have a methods that uses the follow

Function processNumbers(Var as Range)

From this I'm not sure how I can get the number of items in the list and transverse the array to edit the contents. Is there a better way bring in the items than the above.

chris neilsen chris neilsen · Accepted Answer · 2011-12-29T02:13:13

Your function declaration as stated is the correct way to do it.

Function processNumbers(Var as Range) As Variant
    NumberOfCells = Var.Cells.Count
    NumberOfRows = Var.Rows.Count
    NumberOfColumns = Var.Columns.Count
    RangeAddress = Var.Address

    ' Iterate the range  (slow)
    For Each Cl in Var.Cells
        ' ...
    Next

    ' Get Values from range as an array
    Dim Dat as variant
    Dat = var

    ' Iterate array
    For rw = LBound(Dat,1) to UBound(Dat,1)
        For col = LBound(Dat,2) to UBound(Dat,2)
            ' reference Dat(rw,col)
        Next col
    Nest rw

    ' Put (modified) values back into range.  Note: won't work in a UDF
    Val = Dat
End Function

Excel passing a range into a function

1 Answers