2

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.

Community
  • 1
  • 1
Joe Bujok
  • 55
  • 5
  • 2
    In light of your first idea (separate sheet) you can also 'hide' this sheet so it's not visible to the end user. – Automate This Oct 06 '13 at 20:21
  • 2
    +1 for @PortlandRunner's comment. You can even make the sheet `xlVeryHidden` so that the user can't right-click and unhide the sheet. The syntax for that is `Sheets("SheetName").Visible = xlVeryHidden`. – ARich Oct 06 '13 at 22:54
  • Used `Names`. I'll give you some examples :) – David Zemens Oct 07 '13 at 00:44
  • "at a later date" -- if you mean within the same instance of Excel (ie not having it closed/reopened) I would just use a Userform and use `.Hide` - the form saves all your data and everything remains the same value the next time you `.Show` it. but if you want to persist data through closing/opening then you have to put it into a sheet of some sorts – enderland Oct 07 '13 at 00:45

1 Answers1

2

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

David Zemens
  • 53,033
  • 11
  • 81
  • 130