How do I pass a variable from a function to a sub?

Question

I want to use a function to select a random number between 1 and 31, and based on the selection, assign a "box" value. Results 1-16 are Box 1, 17-24 are Box 2, etc.

Within the Sub, I want to detect whether the selected box exists within the data. Here's the important part: If the selected box doesn't exist within the data, select a different box.

To select a different box, I want to refer to the function rather than having to loop using "goto", because looping gets very messy very quickly.

Similar questions seem to be use cases different from mine. The values I'm trying to generate are not determined by variables defined in the Sub, as is a common use of functions.

This is on Windows 10, Excel 365. I've used looping (GoTo... #) because I'm not experienced enough with functions.

Sub cmdClickityClack
SelectABox
MsgBox (SelectABox(Box)) 'I know, this is completely wrong
End Sub

Function SelectABox(Box As Long)
Dim BoxFind As Long
BoxFind = RndBetween(1, 31)

Select Case BoxFind
    Case 0 To 16
    Box = "1"

    Case 17 To 24
    Box = "2"

    Case 25 To 28
    Box = "3"

    Case 29 To 30
    Box = "4"

    Case 31
    Box = "5"
End Select
End Function

Ideally, I'd just use the variable Box, which was defined by the function, within the sub. The sub would know the value of Box after the function is called.

Scott Craner · Accepted Answer · 2019-06-20T21:35:25.543

You do not call the function then do something with it, you simply call the function and treat it like a variable to be used at that point.

Sub cmdClickityClack()
    MsgBox SelectABox
End Sub

Or you can declare a variable and store the output then use the variable:

Sub cmdClickityClack()
    Dim str as String
    str = SelectABox

    MsgBox str 
End Sub

You are not using Box so eliminate the need to pass it. And return the value by using the function name.

Function SelectABox()
    Dim BoxFind As Long
    BoxFind = Application.RandBetween(1, 31)

    Select Case BoxFind
        Case 0 To 16
        SelectABox = "1"

        Case 17 To 24
        SelectABox = "2"

        Case 25 To 28
        SelectABox = "3"

        Case 29 To 30
        SelectABox = "4"

        Case 31
        SelectABox = "5"
    End Select
End Function

If you want to pass a variable to be used in the function:

Sub cmdClickityClack()
    Dim BoxFind As Long
    BoxFind = Application.RandBetween(1, 31)

    MsgBox SelectABox(BoxFind)
End Sub

Function SelectABox(box As Long)

    Select Case box
        Case 0 To 16
        SelectABox = "1"

        Case 17 To 24
        SelectABox = "2"

        Case 25 To 28
        SelectABox = "3"

        Case 29 To 30
        SelectABox = "4"

        Case 31
        SelectABox = "5"
    End Select
End Function

But pass a variable to a function that is to be used in the function and use the function name itself to return the value.

Perfect solution. Even helped me simplify my code significantly. — bloodymurderlive, Jun 20 '19 at 21:36
So in my simple mindedness, it seems like the Function is substituting for a standard variable... or it's like the function is acting like a very dynamic type of variable. — bloodymurderlive, Jun 20 '19 at 21:37
More or less. I am not as eloquent on the particulars of why and how as others. I am more of throw it at the wall and see if it sticks type of coder. — Scott Craner, Jun 20 '19 at 21:40

How do I pass a variable from a function to a sub?

1 Answers1