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

brettdj
  • 54,857
  • 16
  • 114
  • 177
mk117
  • 753
  • 2
  • 13
  • 26
  • @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. – brettdj Sep 06 '15 at 06:21

2 Answers2

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
Ron Rosenfeld
  • 53,870
  • 7
  • 28
  • 60
  • Thanks! That solved it! Since I'm still learning VBA, could you provide any info why ADD12 is a cell address? – mk117 Sep 05 '15 at 19:28
  • 2
    @mk117 It refers to the cell in row 12 column 784 in Excel's usual A1 notation – Ron Rosenfeld Sep 05 '15 at 19:29
  • Ok! I didn't realize that! The Columns are sequenced as alphabets, while rows as numbers! Thanks! – mk117 Sep 05 '15 at 19:31
  • ++ 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](http://spreadsheetsuperstar.com/vba-long-variable-data-type/) – Siddharth Rout Sep 05 '15 at 21:50
  • Another [Interesting Read](http://stackoverflow.com/questions/26409117/why-use-integer-instead-of-long) – Siddharth Rout Sep 05 '15 at 21:55
  • @SiddharthRout Your second `interesting read` seems to support using the Long data type in VBA, and refers to information I was vaguely recalling. I've never tested Long vs Integer in any practical way. But if VBA is converting integers to long anyway, it seems that saving that step should be of some value. – Ron Rosenfeld Sep 05 '15 at 22:03
  • Yes :) Even I use `Long` instead of `Integers` but like I said the difference in performance is negligible (unless you are using tons of Integer variables) – Siddharth Rout Sep 05 '15 at 22:05
  • There are actually 2 reasons why I prefer using `Long` instead of `Integers`. `(A)` To avoid Overflow errors `(B)` Long is shorter to type. I avoid using Intellisense as much as I can {Dare you laugh :D} – Siddharth Rout Sep 05 '15 at 22:11
  • @SiddharthRout I can smile :-). I don't use Integer. I use Long for counters. For decimal numbers, I'll use either Double or Decimal (the latter only when really needed as it uses more memory). – Ron Rosenfeld Sep 05 '15 at 22:16
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.

Excel Hero
  • 14,253
  • 4
  • 33
  • 40
  • 1
    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. – Ron Rosenfeld Sep 05 '15 at 19:27
  • Should one always use `Long` over `Integer`? Even if the number never approaches the integer's limit? – BruceWayne Sep 05 '15 at 20:27
  • 2
    @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. – Excel Hero Sep 05 '15 at 20:34