Get one cell from passed column/row/range VBA Excel

Question

i'm writing a user defined function for excel in VBA.

User may pass a whole column/row into the function instead of one cell. How do you get cell that is in the same row (for column case) and in the same column (for row case), where the function is.

For example, when you are writing in Excel in cell, say, C3 the formula "=A:A*B:B" it calculates A3*B3 in fact. I want to have the same behaiviour in my UDF.

Let's assume function that returns passed argument for simplicity reasons. This code does not work (returns #VALUE! for columns/rows/ranges):

Public Function MyTestFunction(ByVal arg1) As Variant
    MyTestFunction = arg1
End Function

My option is as follows, but I am concerned about performance and the fact that user may want to pass a value to the formula instead of Range.

Public Function MyTestFunction2(ByVal arg1 As Range) As Variant

    If arg1.Count = 1 Then
        MyTestFunction2 = arg1.Value
    Else
        ' Vertical range
        If arg1.Columns.Count = 1 Then
            MyTestFunction2 = arg1.Columns(1).Cells(Application.Caller.Row, 1).Value
            Exit Function
        End If

        ' Horizontal range
        If arg1.Rows.Count = 1 Then
            MyTestFunction2 = arg1.Rows(1).Cells(1, Application.Caller.Column).Value
            Exit Function
        End If

        ' Return #REF! error to user
        MyTestFunction2 = CVErr(xlErrRef)
    End If

End Function

How do you solve this problem?

---

Thanks to valuable comments code has been slightly updated and now can be used in other formulas to filter input values.

Public Function MyTestFunction2(ByVal arg1) As Variant

    If Not TypeName(arg1) = "Range" Then
        MyTestFunction2 = arg1
        Exit Function
    End If


    If arg1.Count = 1 Then
        MyTestFunction2 = arg1.Value
    Else
        ' Vertical range
        If arg1.Columns.Count = 1 Then
            ' check for range match current cell
            If arg1.Cells(1, 1).Row > Application.Caller.Row Or _
                arg1.Cells(1, 1).Row + arg1.Rows.Count - 1 < Application.Caller.Row Then
                ' Return #REF! error to user
                MyTestFunction2 = CVErr(xlErrRef)
                Exit Function
            End If

            ' return value from cell matching cell with function
            MyTestFunction2 = arg1.Worksheet.Columns(1).Cells(Application.Caller.Row, arg1.Column).Value
            Exit Function
        End If

        ' Horizontal range
        If arg1.Rows.Count = 1 Then
            ' check for range match current cell
            If arg1.Cells(1, 1).Column > Application.Caller.Column Or _
                arg1.Cells(1, 1).Column + arg1.Columns.Count - 1 < Application.Caller.Column Then
                ' Return #REF! error to user
                MyTestFunction2 = CVErr(xlErrRef)
                Exit Function
            End If

            ' return value from cell matching cell with function
            MyTestFunction2 = arg1.Worksheet.Rows(1).Cells(arg1.Row, Application.Caller.Column).Value
            Exit Function
        End If

        ' Return #REF! error to user
        MyTestFunction2 = CVErr(xlErrRef)
    End If

End Function

Answer 1

I think you need to use Application.ThisCell property to do it. According to MSDN:

Application.ThisCell- Returns the cell in which the user-defined function is being called from as a Range object.

Let me present how to use it on simple example. Imagine we have data as presented below in column A:B and we want to achieve results which comes from =A*B for each row separately.

In such situation you need the function below and put it next in C column in this way: =MyTestFunction(A:A,B:B)

Function MyTestFunction(rngA As Range, rngB As Range)

    Dim funRow As Long
        funRow = Application.ThisCell.Row
    MyTestFunction = rngA(funRow) * rngB(funRow)

End Function

Please keep in mind that Application.ThisCell will not work if you call your function from other VBA procedure.

Answer 2

In the first code snippet change MyTestFunction = arg1 to Set MyTestFunction = arg1. Also add a small mechanism that recognizes the TypeName() of the arg1 and make sure that the function is receiving a Range type object.

Public Function MyTestFunction(ByVal arg1) As Variant
    Set MyTestFunction = arg1
End Function

example

---

Then, if you get to your spreadsheet and type in =MyTestFunction(A:A) on any row and you'll receive the equivalent value from the column you're passing to the function that sits on the same row.

And your second idea about getting a similar behaviour as =A:A*B:B you can achieve with

Public Function MyTestFunction2(ParamArray arr() As Variant) 
    MyTestFunction2 = arr(0)
End Function

example