Adding or multiplying variants in VBA

Question

Suppose we are given two variants, X and Y, that may be numbers, ranges or arrays. Is there a simple way to add or multiply them like in worksheet formulas =X+Y and =X*Y?

One possibility i thought of would be to use the Evaluate operation, something like this:

Dim X, Y

Sub AddMult()
    Dim Add, Mult
    X = Array(Array(1, 3), Array(2, 4))
    Y = Array(1, 2)
    Add = [GetX()+GetY()]
    Mult = [GetX()*GetY()]
End Sub
Function GetX()
    GetX = X
End Function
Function GetY()
    GetY = Y
End Function

It seems a little awkward though. Any other ideas?

(Here is a related question: Multiplying arrays with scalars and adding in VBA.)

For the sake of argument - aren't there quite a few possible ways to interpret this? That question asks about multiplying values in equivalent positions in equal sized arrays. Are we assuming we only deal with arrays and ranges of equivalent dimensions and size? So you'd create functions with different steps to multiply var to var, var to range/array and range/array to range/array? — Acantud, Aug 29 '14 at 20:36
So you are looking for an approach that works regardless of whether the variables are numeric, ranges or variant arrays? — brettdj, Aug 30 '14 at 07:25
Yes, it should allow for arrays/ranges/numbers that are commensurate. For example using `Set X = Range("a1:b2")` and `Y = 2` returns a 2x2 array with each element incremented/multiplied by 2. — lori_m, Aug 30 '14 at 08:21

lori_m · Accepted Answer · 2014-09-01T19:35:35.067

After looking at various options, i've settled on a Worksheetfunction method. The most viable candidates for arithmetic calculations appear to be in the financial category. From Excel help on the PV function when rate = 0, the following relation exists among the arguments: pmt * nper + pv + fv = 0. This relation also applies to each of the other corresponding functions. Therefore one option would be:

Sub AddMult()
    Dim X, Y, Add, Mult
    X = Array(Array(1, 3), Array(2, 4))
    Y = Array(1, 2)
    With Application
        Add = .Pmt(, -1, X, Y)
        Mult = .PV(, 1, .PV(, X, Y))
    End With
End Sub

For other operations on variants, further WorksheetFunction methods are available:

.SLN(x,y,1)     'x-y
.SLN(x,,y)      'x/y
.Power(x,y)     'x^y 
.Quotient(x,y)  'x\y 
.Delta(x,y)     'x=y
.GeStep(x,y)    'x>=y

Note: Prefix by Application (not Worksheetfunction which doesn't allow for other data types.)

Axel Richter · Answer 2 · 2014-08-31T11:04:18.697

0

The provided code works in a Module. There the Functions GetX() and GetY() are UDFs and so [GetX()] and [GetY()] can evaluate them. But then your code possible is the best solution? Of course there are two UDFs and two variables in global scope. But another solution, which try to avoid this, would result in endless checking for possibilities what the Variants really contain.

Further tests have shown, that the functions in the Module can even be evaluated if they are declared private. Also the global variables must not to be public. So your solution is the best solution in my opinion.

Private X, Y

Sub AddMult()
    Dim Add, Mult
    X = [{4,5,6}]
    Set Y = Range("A1:C200")
    Add = [GetX() + GetY()]
    Mult = [GetX() * GetY()]
End Sub

Private Function GetX()
    GetX = X
End Function

Private Function GetY()
    GetY = Y
End Function

Greetings

Axel

edited Aug 31 '14 at 11:04

answered Aug 31 '14 at 07:07

Axel Richter

56,077
6
60
87

Thanks - test cases seem ok but this will only work on small arrays due to the string conversions which is too restrictive. The posted code may appear to be a kludge but, before jumping to conclusions, please try pasting into a new module and running - it worked ok in my tests. – lori_m Aug 31 '14 at 09:45
Yes, you are right. Sorry I had not tested your code in a Module. There the Functions SetX() and GetX() are UDFs and of course the Evaluate("GetX()") and [GetX()] can evaluate them then. But then your code possible is the best solution? Of course there are two UDFs and two variables in global scope. But another solution would result in endless checking for possibilities what the Variants really contain, like in my getAsLiteral(). – Axel Richter Aug 31 '14 at 10:24

Adding or multiplying variants in VBA

2 Answers2

Linked