VBA function adding the values of two cells

Question

I am totally new to VBA and was asked to create a function that adds the numerical values of two cells.

I came up with the following, but I am given the message

Compile Error: Cannot assign to array, with Additionex

at the bottom marked as the problem…

Could anybody help out?

Function Additionex(num1 As Double, num2 As Double) As Double()
    Dim num3 As Double

    num3 = num1 + num2

    Additionex = num3
End Function

score 1 · Answer 1 · answered Dec 16 '18 at 15:13

Double() is an array of Double-type variables. You want as Double.

Function Additionex(num1 As Double, num2 As Double) As Double

    Dim num3 As Double

    num3 = num1 + num2

    Additionex = num3

End Function

QHarr · Answer 2 · 2018-12-16T15:43:03.763

1

You can also use Evaluate

Public Function Additionex(ByVal num1 As Double, ByVal num2 As Double) As Double
    Additionex = Evaluate(num1 + num2)
End Function

If you want something more generic that takes ranges (your two cells as arguments) and can handle errors consider something like:

Public Function Additionex(ByVal cell1 As Range, ByVal cell2 As Range) As Variant
    If IsNumeric(cell1.Value) And IsNumeric(cell2.Value) Then
        Additionex = Evaluate(num1 + num2)
    Else
       Additionex = CVErr(xlna)
    End If
End Function

edited Dec 16 '18 at 15:43

answered Dec 16 '18 at 15:31

QHarr

83,427
12
54
101

Could you shortly explain on this case the preference of ByVal. – VBasic2008 Dec 16 '18 at 15:44
What do I gain? – VBasic2008 Dec 16 '18 at 15:45
you are passing the actual value of an argument not a reference. The advantage of passing an argument ByVal is that it protects a variable from being changed by the procedure. – QHarr Dec 16 '18 at 15:47
https://learn.microsoft.com/en-us/dotnet/visual-basic/programming-guide/language-features/procedures/passing-arguments-by-value-and-by-reference Same principles apply to VBA. – QHarr Dec 16 '18 at 15:50
Could I say that a rule of thumb is ByRef for objects, ByVal for the rest? I meant do I gain something in speed of execution or whatever, or is it just correctness. I mean as I understand this, the default is ByRef and this case works anyhow. Thx, I'll check that out, sorry for bothering. – VBasic2008 Dec 16 '18 at 15:50
No you can't simply say that. I will get in trouble if I say the wrong thing and a rubber duck crew sees it. Worth popping by to see [them](https://chat.stackexchange.com/rooms/14929/vba-rubberducking) for a correct explanation of the intricacies. Basically, if I want to change a value with the passed to sub/function I use ByRef. – QHarr Dec 16 '18 at 15:52
3

@VBasic2008 The same rule applies for objects, but `ByVal` and `ByRef` apply to the *object reference itself*. If you pass an object `ByVal`, the called procedure can't alter the object pointer (set it to a new object or set it to `Nothing`). It can still alter properties of the object. – Comintern Dec 16 '18 at 16:13

score 0 · Answer 3 · answered Dec 16 '18 at 15:14

0

Don't make it an array:

Function Additionex(num1 As Double, num2 As Double) As Double
    Dim num3 As Double
    num3 = num1 + num2
    Additionex = num3
End Function

answered Dec 16 '18 at 15:14

Gary's Student

95,722
10
59
99

VBA function adding the values of two cells

3 Answers3