2

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:

Worksheet

Improve this question
1
  • @pnuts has a consensus been agreed to or put forward for tags? As an aside seems to be a huge increase in the number of poor questions in recent times. Commented Sep 6, 2015 at 6:21

2 Answers 2

Reset to default
6

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
Improve this answer
Sign up to request clarification or add additional context in comments.

9 Comments

Thanks! That solved it! Since I'm still learning VBA, could you provide any info why ADD12 is a cell address?
@mk117 It refers to the cell in row 12 column 784 in Excel's usual A1 notation
Ok! I didn't realize that! The Columns are sequenced as alphabets, while rows as numbers! Thanks!
++ Nice :) BTW In case where numbers are fixed, you can use Integer in lieu of Long for example For i = 1 to 5. Here, if you Dim i as Integer, it should not be a problem. However in the above case the numbers in the excel sheet can change and hence using Long is advisable. As far as "efficiency" is concerned, it actually negligible :) Interesting Read
Another Interesting Read
|
4

The problem you are facing is that the name you have chosen for your UDF is illegal.

ADD12 is the address of a cell on the worksheet. This guarantees a reference error and your UDF never gets called.

Change the name to something that is not a cell address, like ADD12_.

Caller is the correct way to do what you are after, unless you really want it to calculate from the selected cell. Either of these ways guarantees that your function is nonvolatile, which means it will not automatically update the result if you change one of the dependent values.

You should use Long variable types instead of Integers.

Improve this answer

3 Comments

255 has nothing to do with anything here. The Integer data type is valid for numbers in the range -32,768 to 32,767. But I agree that the Long data type should generally be used as in modern versions of Excel, I am told it is more efficient.
Should one always use Long over Integer? Even if the number never approaches the integer's limit?
@BruceWayne Yes. VBA is optimized for Longs. It processes them more efficiently and hence, faster. Obviously, they use twice as much memory but that is not an issue anymore, like it was 25+ years ago.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.