Add two cells in Custom Function without Parameters

Question

I'm trying to learn VBA for Excel. I was watching a tutorial about Custom Function / User Defined Functions in VBA. I tried to create a user defined function, but it displays a #REF! error in the cell where I've typed the function name. I'm trying to add (A Cell + B Cell), without passing any argument/parameter to the function. I'm using Offset to traverse 1,2 cells left of the Cell that has the function as a formula. Here's my code:

Option Explicit

Function ADD12()
Dim Number_1 As Integer
Dim Number_2 As Integer

Number_2 = ActiveCell.Offset(0, -2).Value
Number_1 = ActiveCell.Offset(0, -1).Value
ADD12 = Number_1 + Number_2
End Function

And screenshot of my worksheet:

Answer 1

The #REF error is because you cannot use a cell address as a function name.

But also note that ActiveCell will always be changing. You may want to look at Caller to get the cell where the function is located.

eg:

Option Explicit

Function ADD_12()
Dim Number_1 As Integer
Dim Number_2 As Integer
Dim R As Range

Set R = Application.Caller

Number_2 = R.Offset(0, -2).Value
Number_1 = R.Offset(0, -1).Value
ADD_12 = Number_1 + Number_2
End Function