How can I pass a cell range as an argument?

Question

How can I create a function in VBA so that I can pass a cell range as an argument?

For example, I want to call the function in the spreadsheet in a random cell by writing

=function_name(A2:A7)

My code gives me a ?NAME# error when I call it in the spreadsheet.

Public Function xyz ( ByRef x() As Variant, sizeOfSelection As Integer )
xyz=x(0);
End Function

It is coded in a Module and my Macros is on.

`ByVal x As Range`. Or https://stackoverflow.com/q/57159554/11683 if you want to get universal. — GSerg, Jun 19 '20 at 19:21
Your example code has different issues. First of all it has a syntax error, secondly you never user the parameter `sizeoOfSelection and thirdly your call has only one parameter. And a range is never an array what is your first parameter. — Storax, Jun 19 '20 at 19:48
@Storax, yes, also `sizeOfSelection` is not passed in the given example — S Meaden, Jun 20 '20 at 10:26

FaneDuru · Answer 1 · 2020-06-19T19:37:09.040

0

Try the next approach, please:

Public Function xyz (rng as Range) As String
    dim arr as variant
    arr = rng.value
    if rng.cells.count>1 then
        Debug.print arr(1, 1), arr(1, ubound(arr,2))
        xyz = rng.Address & " - " & arr(1, 1)
    Else
        xyz = rng.value
    End if
End Function

This is only an example? What would you like it to do/return?

edited Jun 19 '20 at 19:37

answered Jun 19 '20 at 19:21

FaneDuru

38,298
4
19
27

S Meaden · Answer 2 · 2020-06-20T14:14:08.430

0

In the following code we simply evaluate the argument which if a Range will call its default member which is .Value which will for a Range of more than one cell return a variant array. Conversely, wrapping a variant array in brackets does nothing so this it is unaffected.

Sub Test()
    Dim rng As Excel.Range
    Set rng = ActiveSheet.Range("FooBar")

    AllowBothRangeAndVariantArray rng
    AllowBothRangeAndVariantArray rng.Value2
End Sub

Function AllowBothRangeAndVariantArray(ByVal v As Variant)
    v = (v)  '* if v is a Range this will call the default member which is value
    'Debug.Print v(1, 1)
End Function

So this is a one-liner (if you omit the Debug.Print which is there for debugging)

I've also tested this from the worksheet.

edited Jun 20 '20 at 14:14

answered Jun 19 '20 at 20:20

S Meaden

8,050
3
34
65

Has the same problem https://stackoverflow.com/a/62477223/11683 originally had. Will fail if `Range("FooBar")` is a single cell. – GSerg Jun 20 '20 at 07:30
@GSerg That wasn't the point I was trying to make. The point i was trying to make is that use brackets to convert a Range to a Variant, whilst leaving a Variant unchanged. Works for single cells as well. Of course one then has to go on and check dimensions if your functions logic depends upon the shape.. – S Meaden Jun 20 '20 at 10:20
`v(1,1)` assumes a 2D array. Value of a single cell is not an array at all. – GSerg Jun 20 '20 at 10:33
Yeah, I know. Answer is to be adapted by the questioner. Text does say delete the Debug.Print in parenthesis. – S Meaden Jun 20 '20 at 14:02

score -1 · Answer 3 · answered Jun 19 '20 at 19:36

Range UDF

Try something like these:

Option Explicit

Function xyz(CountRange As Range) As Long
    xyz = Application.WorksheetFunction.Count(CountRange)
End Function

Function xyzSum(SumRange As Range) As Long
    xyzSum = Application.WorksheetFunction.Sum(SumRange)
End Function

Function xyzIf(CountRange As Range, Criteria as long) As Long
    xyzIf = Application.WorksheetFunction.CountIf(CountRange, Criteria)
End Function

How can I pass a cell range as an argument?

3 Answers3

Range UDF