Returning a value from a Function back to a sub

[gailb]

I'm going to use this Function in a few different places within my code, so I want to call the Function from a Sub and return the string of myPC.

This sub goes to the Function, but it does not return the string myPC. What piece have I missed?

Code: Select all

Function WhichTest() As String

    Dim myInputBox As String
    Dim myPC As String
    
    myInputBox = Application.InputBox(Prompt:="Which Progress check is this for?" _
        & vbCrLf & vbCrLf & "1 = Progress Check - Workload Data " _
        & vbCrLf & vbCrLf & "2 = Progress Check - Data Analysis " _
        & vbCrLf & vbCrLf & "3 = Progress Check - Statistics " _
        & vbCrLf & vbCrLf & "4 = Progress Check - Minimum Manning ", _
        Title:="Paste Worksheet/s", Default:=1, Type:=1)

    Select Case myInputBox
        Case 1: myPC = "Workload Data"
        Case 2: myPC = "Data Analysis"
        Case 3: myPC = "Statistics"
        Case 4: myPC = "Minimum Manning"
    End Select

End Function

Code: Select all

Sub FindTheTest()
    Dim myPC As String
    myPC = WhichTest
    Debug.Print myPC
End Sub

[HansV]

You make a function return a value by assigning a value to the function name:

Code: Select all

Function WhichTest() As String
    Dim myInputBox As String
    Dim myPC As String
    
    myInputBox = Application.InputBox(Prompt:="Which Progress check is this for?" _
        & vbCrLf & vbCrLf & "1 = Progress Check - Workload Data " _
        & vbCrLf & vbCrLf & "2 = Progress Check - Data Analysis " _
        & vbCrLf & vbCrLf & "3 = Progress Check - Statistics " _
        & vbCrLf & vbCrLf & "4 = Progress Check - Minimum Manning ", _
        Title:="Paste Worksheet/s", Default:=1, Type:=1)

    Select Case myInputBox
        Case 1: myPC = "Workload Data"
        Case 2: myPC = "Data Analysis"
        Case 3: myPC = "Statistics"
        Case 4: myPC = "Minimum Manning"
    End Select

    ' *** The following line makes the function return a value ***
    WhichTest = myPC
End Function

[gailb]

Thanks Hans. That makes perfect sense now and works great.