2

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.

Community
  • 1
  • 1
Michaelb88
  • 151
  • 2
  • 6
  • 14

1 Answers1

2

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
chris neilsen
  • 52,446
  • 10
  • 84
  • 123
  • Thanks, but what do you mean by 'won't work in a UDF'. When I do processNumbers = Dat instead of Val = Dat I only get one cell back instead of a full amended list. I.e. I'm passing in a1:e1 and enter the formula in A3, I don't get back values in A3:E3 but only in A3. – Michaelb88 Dec 29 '11 at 13:28
  • When a Function is called from a formula in a cell its called a User Defined Function or UDF. When called like this it cannot change cells in the sheet, only return a value. – chris neilsen Dec 29 '11 at 13:31
  • To return values to a range like `A3:E3` enter the function as an array formula: select the range `A3:E3`, type in your function, and press Ctrl-Shift-Enter (rather than just Enter) The result should be the formula displayed in the formula bar should look like `{=processNumbers(...)}` – chris neilsen Dec 29 '11 at 13:35