how to get a subroutine in excel vba to return something?

Question

I have the following code in excel vba:

Sub main()
    Call Get_Rows_Generic("usersFullOutput.csv", 1)
End Sub

in another module i have:

Sub Get_Rows_Generic(work_sheet As String, column_num As Integer) 

    Dim wsUsers As Worksheet:             Set wsUsers = Worksheets(work_sheet)
    Dim userRows As Integer:              userRows = wsUsers.Cells(Rows.Count, column_num).End(xlUp).Row

    MsgBox userRows
End Sub

How do i access the results of the sub Get_Rows_Generic (what is shown in the MsgBox) outside of the sub? i.e. feed the result into another sub or cell...etc.

If you want a `Sub` to return something make it a `Function` — Matt, Mar 14 '15 at 20:53

score 3 · Accepted Answer · edited May 23 '17 at 11:51

3

Why not just make it a function then?

Function Get_Rows_Generic(work_sheet As String, column_num As Integer) 

    Dim wsUsers As Worksheet:             Set wsUsers = Worksheets(work_sheet)
    Dim userRows As Integer:              userRows = wsUsers.Cells(Rows.Count, column_num).End(xlUp).Row

    Get_Rows_Generic = userRows
End Sub

Then your call could be

Sub main()
    Dim result as integer
    result = Get_Rows_Generic("usersFullOutput.csv", 1)
End Sub

If you wanted the result to be a global variable then I would refer you to this question: How do I declare a global variable in VBA?

Public result as integer

edited May 23 '17 at 11:51

Community

1
1

answered Mar 14 '15 at 20:55

Matt

45,022
8
78
119

Thanks! last line of fuction should be `Get_Rows_Generic = userRows`, correct? – DBWeinstein Mar 14 '15 at 21:17
1

@dwstein Thanks. Yes, you are correct. There should be an equal sign there. – Matt Mar 14 '15 at 21:19

score 1 · Answer 2 · answered Mar 14 '15 at 21:04

Assuming that you know that you can convert your Sub into a Function, there is a crude way to achieve what you are trying to do. As VBA/VBScript by default passes the parameters as ByRef, you might use this to "return" a value.

Sub main()
    Dim userRows as Integer
    userRows = 0
    Call Get_Rows_Generic("usersFullOutput.csv", 1, userRows)
    Msgbox userRows
End Sub

Sub Get_Rows_Generic(work_sheet As String, column_num As Integer, ByRef userRows as Integer) 

    Dim wsUsers As Worksheet:             Set wsUsers = Worksheets(work_sheet)
    Dim userRows As Integer:              userRows = wsUsers.Cells(Rows.Count, column_num).End(xlUp).Row

End Sub

how to get a subroutine in excel vba to return something?

2 Answers2