Calling a sub, with passed variables, from within a function

Question

I have a function that is called and requires 2 passed variables

here is the first line: Function dimErr(rngStart As Long, rngEnd As Long)

Now in the middle of this function I call a sub: Call highlightLegitRows

Then the function carries on as intended.

Now, my problem is I now have optional variables associated with this sub:

Sub highlightLegitRows(Optional ByVal rngStart As Long = 0, Optional ByVal rngEnd As Long = 0)

When calling this sub, with the same values that have already been passed through, like so: Call highlightLegitRows(rngStart, rngEnd) My function appears to simply end at this line.

For example, this:

Call highlightLegitRows(rngStart, rngEnd)
MsgBox "hello"

would NOT trigger the message box. However this would:

Call highlightLegitRows
MsgBox "hello"

The only difference is the addition of these optional passed variables in the sub. Any idea where I am going wrong?

I haven't posted the whole function and sub as they are lengthy and complex, but both were working as intended before the above change.

Is your "function" just a normal function, or are you using it as a UDF (i.e. calling it as part of a formula in Excel)? — YowE3K
– YowE3K, Commented Nov 24, 2017 at 19:17
It's not being called as part of a formula, the result of the function is boolean and used as part of another sub. — Aurelius
– Aurelius, Commented Nov 24, 2017 at 19:22
Hmmm - UDFs appear to just stop when they hit an error, which is why I suspected that as the cause. Does your code have any On Error GoTo statements? That could also cause your code to jump out of the subroutine with execution passed to the error-handler. — YowE3K
– YowE3K, Commented Nov 24, 2017 at 19:26
We can suggest a lot of things, but without seeing the code, we are blind hitting. — user8753746
– user8753746, Commented Nov 24, 2017 at 20:28
I guess the other thing to ask is ... does it still not reach the MsgSub when rngStart and rngEnd are both explicitly 0? i.e. is the problem caused by the existence of the parameters, or because of the values in the parameters? — YowE3K
– YowE3K, Commented Nov 24, 2017 at 20:36

Siddharth Rout · Accepted Answer · 2017-11-25 04:53:08Z

Your code under normal circumstances will work. For example

Sub Sample()
    Debug.Print dimErr(1, 2)
End Sub

Function dimErr(rngStart As Long, rngEnd As Long)
    Call highlightLegitRows(rngStart, rngEnd)
    MsgBox "hello"
End Function

Sub highlightLegitRows(Optional ByVal rngStart As Long = 0, _
                       Optional ByVal rngEnd As Long = 0)
    Rows(rngStart & ":" & rngEnd).Interior.ColorIndex = 3
End Sub

But will not in the below scenario.

Sub Sample()
    On Error GoTo Whoa

    Debug.Print dimErr(0, 0)        
Whoa:
End Sub

Function dimErr(rngStart As Long, rngEnd As Long)
    Call highlightLegitRows(rngStart, rngEnd)
    MsgBox "hello"
End Function

Sub highlightLegitRows(Optional ByVal rngStart As Long = 0, _
                       Optional ByVal rngEnd As Long = 0)
    Rows(rngStart & ":" & rngEnd).Interior.ColorIndex = 3
End Sub

or even this

Sub Sample()
    Debug.Print dimErr(0, 0)
End Sub

Function dimErr(rngStart As Long, rngEnd As Long)
    On Error GoTo Whoa
    Call highlightLegitRows(rngStart, rngEnd)
    MsgBox "hello"
Whoa:
End Function

Sub highlightLegitRows(Optional ByVal rngStart As Long = 0, _
                       Optional ByVal rngEnd As Long = 0)
    Rows(rngStart & ":" & rngEnd).Interior.ColorIndex = 3
End Sub

Please check your code for error handling. Is there something that is stopping (exiting the sub/function) on error?

Collectives™ on Stack Overflow

Calling a sub, with passed variables, from within a function

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