VBA function with array inputs

Question

I am trying to create a more user friendly version of the function sumproduct but I don't understand why the function is returning #VALUE!. Please advise:

Below is my code both array inputs are of the same length:

Function sp(x As Variant, y As Variant) As Double

psum = 0


For i = LBound(x) To UBound(x)
    qsum = x(i) * y(i)
    psum = psum + qsum
Next

sp = psum

End Function

Have you debugged the code and observed how qsum and psum changes? — FunThomas
– FunThomas, Commented Jul 25, 2017 at 13:42
The issue is with your data and not the code. check that. You probably have text instead of numbers in the arrays you are passing. — Scott Craner
– Scott Craner, Commented Jul 25, 2017 at 14:00

TJYen · Accepted Answer · 2017-07-25 13:56:35Z

1

You need to state what qsum and psum are. Also make sure the arrays being called in are also of the same type variant.:

Function sp(x As Variant, y As Variant) As Double
Dim psum, qsum As Double
psum = 0


For i = LBound(x) To UBound(x)
    qsum = x(i) * y(i)
    psum = psum + qsum
Next

sp = psum

End Function

Test sub

  Sub test()
    Dim ex, wy As Variant
    ex = Array(1, 2, 3, 4, 5)
    wy = Array(2, 3, 4, 5, 6)
    Call sp(ex, wy)

    End Sub

This worked for me without the Value Error

answered Jul 25, 2017 at 13:56

TJYen

3835 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

VBA function with array inputs

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related