CODEWITHSUNDEEP
vbaexcel
Joe Bujok
Joe Bujok

Reputation: 55

Storing user inputs for retrieving later

I have a spreadsheet where the user inputs various details on an inputs page and then presses a calculate button to get what they want. The inputs are strings, numbers and dates.

I want to save the inputs for each calculation for the user so that at a later date they could enter the calc id and not have to renter the inputs.

One simple way I thought of doing this was to copy the inputs when the calculation is run to another sheet with the inputs in a column with the calc id. Then just save future inputs in a separate column and lookup the correct column to retrieve the inputs at a later date.

I read this question - What are the benefits of using Classes in VBA? and thought it would be good to make a class called CalculationInputs that had all the details stored in one object. This may be overkill for what I need but i wanted to ask how other people would solve this simple task.

Upvotes: 2

Views: 2445

Answers (1)

David Zemens
David Zemens

Reputation: 53623

You can use Names to define variables within the scope of a workbook or worksheet. Typically these are used to define ranges, and more specifically dynamic ranges, but they can also be used to store static/constant values.

To create a Name manually, from the Formula ribbon, Names Manager:

enter image description here

Click on the "New" button, and then give it a meaningful name:

enter image description here

Make sure you put ="" in the "Refers To" field, if you leave it blank, the name will not be created.

Then when you press OK, or any time you go to the Names manager, you will see a list of all available Names in the workbook.

enter image description here

You can edit these through the Names manager, which is probably tedious, or you can easily use VBA and inputs to control them, for example:

Sub Test()
    ActiveWorkbook.Names("MyAddress").RefersTo = "734 Evergreen Terrace"
End Sub

You could do something like this to capture the value, our use other macros or user firm code to assign the value to the Name.

Activeworkbook.Names("MyAddress").RefersTo = _
    Application.Inputbox("please enter your address")

Etc.

If you run this, and then review the Names manager, you'll see the value has been updated:

enter image description here

In VBE, you can refer to the name like:

Debug.Print ActiveWorkbook.Names("MyAddress").Value '# Prints in the immediate pane
Range("A1") = ActiveWorkbook.Names("MyAddress").Value

These can also be accessed (read) from the worksheet, like:

enter image description here

Upvotes: 2

Related Questions