CODEWITHSUNDEEP
excelvbaexcel-formulaerror-handling
rastawolf
rastawolf

Reputation: 338

Function returns differently when called from sub vs excel

I have a simple function here that performs a calculation on a range of values. The range will always have 5 values. The range should also be one dimensional. In an attempt to cover an out of range case, I have tried to add error handling that will prompt the user of the functions limitation.

When I test a range with an x value > 1 from a subroutine, I only get the error msg once as intended. However, when I attempt the same range of cells from a worksheet, I get the error msg many times.

I don't understand why I would get different results from a worksheet vs. a subroutine.

Any help is greatly appreciated.

Public Function RIAJ_SCORE_RANGE(Range_FROM_million_TO_gold As Range) As Double

On Error GoTo eh

Dim millionTOgold As Variant

millionTOgold = Range_FROM_million_TO_gold.Value

Dim i As Long
Dim x As Integer

x = 1

If x <> 1 Then GoTo er1

For i = LBound(millionTOgold) To UBound(millionTOgold)
If i = 1 Then
RIAJ_SCORE_RANGE = millionTOgold(i, 1) * 10
ElseIf i = 2 Then RIAJ_SCORE_RANGE = RIAJ_SCORE_RANGE + millionTOgold(i, x) * 7.5
ElseIf i = 3 Then RIAJ_SCORE_RANGE = RIAJ_SCORE_RANGE + millionTOgold(i, x) * 5
ElseIf i = 4 Then RIAJ_SCORE_RANGE = RIAJ_SCORE_RANGE + millionTOgold(i, x) * 2.5
ElseIf i = 5 Then RIAJ_SCORE_RANGE = RIAJ_SCORE_RANGE + millionTOgold(i, x)
Else: GoTo eh

End If
Next i

er1:
    x = 1
    MsgBox ("Function requires range from a single column")
    GoTo eh
    
eh:
    Exit Function

End Function

This sub runs as intended, returning error msg and exiting the function. From Excel, passing the same range prompts the error msg 32 times.

Sub testRange()

RIAJ_SCORE_RANGE (ActiveSheet.Range("E86:F91"))

End Sub

Upvotes: 0

Views: 61

Answers (1)

Mathieu Guindon
Mathieu Guindon

Reputation: 71217

RIAJ_SCORE_RANGE (ActiveSheet.Range("E86:F91"))

Drop the parentheses, they're let-coercing your Range into a value through an implicit default member call (Range.[_Default], a hidden member that "branches" to other members depending on how it's parameterized) that ultimately attempts to pass a 2D Variant array (i.e. exactly what you would be getting with ActiveSheet.Range("E86:F91").Value) to a procedure that wants to work with a Range object, hence the type mismatch error.

This would be passing the Range object without let-coercing it into an array:

RIAJ_SCORE_RANGE ActiveSheet.Range("E86:F91")

That said, invoking a function without capturing its return value is (should be) useless, because a function (especially one intended to run from a worksheet cell) should not have any side-effects: it takes inputs, computes a result based on these inputs, and outputs a result. Looks like that's exactly what you have here, so.. good job!

You do need the parentheses when you are capturing a function's return value:

MsgBox RIAJ_SCORE_RANGE(ActiveSheet.Range("E86:F91"))

Notice the missing space between the function name and the ( character: that's the VBE telling you that the (...) parentheses are delimiting an argument list. When there's a space (the VBE will force that space, you can't fight it), the parentheses are let-coercing the first argument in the argument list - MsgBox is also a function, and we're not capturing its return value here, so no parentheses are needed.

We would have to add parentheses to do that:

Debug.Print MsgBox(RIAJ_SCORE_RANGE(ActiveSheet.Range("E86:F91")), vbInformation)

And this would be illegal:

MsgBox (RIAJ_SCORE_RANGE(ActiveSheet.Range("E86:F91")), vbInformation)

...because (RIAJ_SCORE_RANGE(ActiveSheet.Range("E86:F91")), vbInformation) makes no sense as a value expression!

Upvotes: 2

Related Questions