存储用户输入以供以后检索 [英] Storing user inputs for retrieving later

问题描述

我有一个电子表格，用户在输入页面输入各种细节，然后按一个计算按钮来获得所需的内容。输入是字符串，数字和日期。

我想为用户的每个计算保存输入，以便稍后他们可以输入calc id而不是必须收回投入。

我想到这样做的一个简单的方法是将计算运行到另一个工作表时，将输入复制到带有calc id的列中。然后只需将未来的输入保存在单独的列中，并查找正确的列即可在以后检索输入。

我读了这个问题 - 在VBA中使用Classes有什么好处？，并认为这是很好的创建一个名为CalculationInputs的类，将所有的细节存储在一个对象中。这可能是我需要的，但我想问其他人如何解决这个简单的任务。

解决方案

你可以使用名称定义工作簿或工作表范围内的变量。通常，这些用于定义范围，更具体地说是动态范围，但它们也可用于存储静态/常量值。

创建一个名称手动从公式功能区，名称管理器：

点击新建按钮，然后给它一个有意义的名称：

如果您将其留空，请确保在参考字段中放置 =。不会创建名称。

然后当您按确定，或任何时候您到名称管理器，您将看到所有可用的名称列表工作簿。

您可以通过名称管理器编辑这些，这是prob或者您可以轻松使用VBA和输入来控制它们，例如：

  Sub Test（）
 ActiveWorkbook.Names（MyAddress）。RefersTo =734 Evergreen Terrace
 End Sub 
  

您可以执行此操作来捕获该值，我们使用其他宏或用户公司代码将值分配给 Name 。

  Activeworkbook.Names（MyAddress）。RefersTo = _ 
 Application.Inputbox（请输入您的地址）
  

等等。

如果你运行这个，然后查看名称管理器，您将看到该值已更新：

在VBE中，您可以参考以下名称：

 code> Debug.Print ActiveWorkbook.Names（MyAddress）。值'＃在立即窗格中打印
范围（A1）= ActiveWorkbook.Names（MyAddress）。 
  

这些也可以从工作表中访问（读取），如：

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.

解决方案

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:

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

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.

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:

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:

这篇关于存储用户输入以供以后检索的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！