I understand that VBA functions can only alter the contents of the cell that calls them. That said, I can't figure out why this code doesn't work:
Function Test() As Double
ActiveCell.Offset(0, -3).Activate
Test = ActiveCell.Value
End Function
So my question is, once I use a function's procedure to find the right cell to activate, how do I then get the function to return the value of that cell to the cell that called the function?
I wouldn't use active cell - the active cell could easily return an incorrect value.
Try this test:
In cell D1 enter your function =Test()
In cell A1 enter this formula =D3
In cell A3 enter any value.
In cell D3 enter a value that's different from the one in A3.
The formula returns the value three to the left of the active cell which is D3, so returns the value you've entered in cell A3 rather than the value in A1 (which is a duplicate of what you entered in D3).
Application.Caller is a reference to the cell calling the function so:
Function Test() As Double
Test = Application.Caller.Offset(, -3).Value
End Function
"The cell that called the function" is the ActiveCell
You're setting the ActiveCell to be something else (Offset(0,-3)) within the function (with ActiveCell.Offset(0, -3).Activate).
Simply, don't call the Activate command, and it should work;
Function Test() As Double
Test = ActiveCell.Offset(0, -3).Value
End Function
Rand with Test or use Application.Volatile inside your function as I do belowcode
Function Test()
Application.Volatile
Dim rng1 As Range
On Error Resume Next
Set rng1 = ActiveCell.Offset(0, -3)
On Error GoTo 0
If Not rng1 Is Nothing Then
Test = rng1.Value
Else
Test = "Invalid range"
End If
End Function
