VBA defined function not accepting more than one variable

Question

My VBA Function is not accepting two variables for a basic function. It keeps giving a #VALUE! error.

Function myFunction(x As Integer, y As Integer) As Integer
    result = x + y
    myFunction = result
End Function

However, it works fine if I simply remove the y variable. For example, this works perfectly:

Function myFunction(x As Integer) As Integer

    result = x + x
    myFunction = result

End Function

I am using Windows 10 Pro on a Lenovo Yoga.

If you're using your function like this in VBA - myFunction(1,2) try putting call in front of it so it would be Call myFunction(1,2) - or you can remove the brackets so it'd be myFunction 1,3 — jamheadart
– jamheadart, Commented Jan 12, 2021 at 10:19
Your function should work as written (works here). What, exactly, is the value of y that you are feeding in. If it looks like a number, are there any other (non-printing) characters in the cell that would cause VBA to interpret it as text? (check by doing len(y)) — Ron Rosenfeld
– Ron Rosenfeld, Commented Jan 12, 2021 at 12:04
I have not defined y besides in the equation and even forgetting about y, I get the #VALUE! error even if I called my function as =myFunction(1,2) in order to make it sum the variable to get 3. The issue arises only when I add that second variable — Dylan
– Dylan, Commented Jan 12, 2021 at 12:49
OK. Is it possibly the case that your separator is the semicolon? If so, you would need to (on the worksheet), enter the formula as =myFunction(1;2) *By the way, to alert a commenter that you have posted a response to them, use @commenters_name in your comment) — Ron Rosenfeld
– Ron Rosenfeld, Commented Jan 12, 2021 at 17:20

Tarik · Accepted Answer · 2021-01-12 10:19:25Z

1

If you are using invoking your function with an Excel range with a formula such as =myFunction(A1:A2) an array will be sent as the first parameter and the second parameter will be missing and cause an error with the ultimate result you are getting.

Note: =myFunction(A1, A2) should work.

If you indeed are using a range, define myFunction as follows:

Function myFunction (x())

answered Jan 12, 2021 at 10:19

Tarik

11.2k2 gold badges31 silver badges48 bronze badges

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

2 Comments

Dylan Over a year ago

I am defining my function as =myFunction(A1,A2) and still getting #VALUE! It even does not work if I use =myFunction(1,2)

Tarik Over a year ago

@Dilan I do not have a Windows PC right now. Put a breakpoint on the line contains x + y. Check out the type of each parameter.

Collectives™ on Stack Overflow

VBA defined function not accepting more than one variable

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related