CODEWITHSUNDEEP
excelvbafunctionsubroutine
DBWeinstein
DBWeinstein

Reputation: 9549

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

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.

Upvotes: 0

Views: 2642

Answers (2)

Matt
Matt

Reputation: 46730

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

Upvotes: 3

Pankaj Jaju
Pankaj Jaju

Reputation: 5481

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

Upvotes: 1

Related Questions