Check out some similar questions!

colbtech · Sep 14, 2009, 03:00 AM

How do I return values from a subroutine in Excel VBA? It appears that I have to "temporarily" store a value elsewhere on the spreadsheet and recover when I return to the main part of the program.

Simply the program runs and asks the user to enter data on several forms, but this data has to be "saved" so it can be used later. Is this a "feature" and my "work-around" is the correct way to achieve this?

e.g.

SelectWhat.Show ' Display Form
WhatToDo = Sheet2.Cells(5, 252) 'recover info
.
.
.
.
.
Private Sub CommandButton1_Click()

End Sub

Private Sub SelectOrder_Click()

Sheet2.Cells(5, 252).Value = "Order" 'Save data for later
Unload Me
End Sub

Private Sub SelectQuote_Click()

Sheet2.Cells(5, 252).Value = "Quote"
Unload Me
End Sub

Private Sub SelectInvoice_Click()

Sheet2.Cells(5, 252).Value = "Invoice"
Unload Me
End Sub

Private Sub SelectView_Click()

Sheet2.Cells(5, 252).Value = "View"
Unload Me
End Sub

Private Sub UserForm_Click()

End Sub

JBeaucaire · Sep 14, 2009, 11:11 AM

I don't believe storing values collected from forms on your sheet is a "workaround". Forms are intended for this very sort of thing. If you don't store them somewhere, they go away, yes?

The most common scenario is as form data is collected it is added permanently to a growing database of information. That information is later used to cull reports and charts, etc.

When the data collected is temporary, using a garbage sheet to store the values and/or garbage cells is perfectly normal.

colbtech · Sep 15, 2009, 12:10 AM

Thanks for the reply. I (foolishly) presumed that variables calculated in a subroutine would still be available after the return. Still all is good. Program works.