CODEWITHSUNDEEP
vbaexcel
HHH
HHH

Reputation: 23

How to get VBA function in Excel to return value of active cell?

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?

Upvotes: 2

Views: 6757

Answers (3)

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19782

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

Upvotes: 2

brettdj
brettdj

Reputation: 55682

  1. You should be using errorhandling as the cell range may be invalid
  2. So that the function updates, you will need to make the function volatile by either adding a function such as Rand with Test or use Application.Volatile inside your function as I do below

code

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

Upvotes: 0

Uri Goren
Uri Goren

Reputation: 13690

"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

Upvotes: 0

Related Questions