vba 将输入整数写入单元格

Writing an input integer into a cell

提问by David Max

I am writing a quick application myself - first project, however I am trying to find the VBA code for writing the result of an input string to a named cell in Excel.

我自己正在编写一个快速应用程序 - 第一个项目，但是我试图找到用于将输入字符串的结果写入 Excel 中命名单元格的 VBA 代码。

For example, a input box asks the question "Which job number would you like to add to the list?"... the user would then enter a reference number such as "FX1234356". The macro then needs to write that information into a cell, which I can then use to finish the macro (basically a search in some data).

例如，输入框询问“您想将哪个工作编号添加到列表中？”的问题……然后用户将输入参考编号，例如“FX1234356”。然后宏需要将该信息写入一个单元格，然后我可以使用它来完成宏（基本上是在某些数据中搜索）。

回答by Bill the Lizard

You can use the Range object in VBA to set the value of a named cell, just like any other cell.

您可以使用 VBA 中的 Range 对象来设置命名单元格的值，就像任何其他单元格一样。

Range("C1").Value = Inputbox("Which job number would you like to add to the list?)

Where "C1" is the name of the cell you want to update.

其中“C1”是您要更新的单元格的名称。

My Excel VBA is a little bit old and crusty, so there may be a better way to do this in newer versions of Excel.

我的 Excel VBA 有点老旧，所以在较新版本的 Excel 中可能有更好的方法来做到这一点。

回答by dbb

I recommend always using a named range (as you have suggested you are doing) because if any columns or rows are added or deleted, the name reference will update, whereas if you hard code the cell reference (eg "H1" as suggested in one of the responses) in VBA, then it will not update and will point to the wrong cell.

我建议始终使用命名范围（如您所建议的那样），因为如果添加或删除任何列或行，名称引用将更新，而如果您对单元格引用进行硬编码（例如“H1”的响应）在 VBA 中，则它不会更新并指向错误的单元格。

So

所以

Range("RefNo") = InputBox("....") 

is safer than

比

Range("H1") = InputBox("....") 

You can set the value of several cells, too.

您也可以设置多个单元格的值。

Range("Results").Resize(10,3) = arrResults()

where arrResults is an array of at least 10 rows & 3 columns (and can be any type). If you use this, put this

其中 arrResults 是至少 10 行和 3 列的数组（可以是任何类型）。如果你使用这个，把这个

Option Base 1

at the top of the VBA module, otherwise VBA will assume the array starts at 0 and put a blank first row and column in the sheet. This line makes all arrays start at 1 as a default (which may be abnormal in most languages but works well with spreadsheets).

在 VBA 模块的顶部，否则 VBA 将假定数组从 0 开始，并在工作表中放置一个空白的第一行和第一列。该行使所有数组从 1 开始作为默认值（这在大多数语言中可能不正常，但适用于电子表格）。

回答by dbb

When asking a user for a response to put into a cell using the InputBox method, there are usually three things that can happen1.

当要求用户使用InputBox 方法将响应放入单元格时，通常会发生三种情况1。

1. The user types something in and clicks OK. This is what you expect to happen and you will receive input back that can be returned directly to a cell or a declared variable.
2. The user clicks Cancel, presses Escor clicks ×(Close). The return value is a boolean False. This should be accounted for.
3. The user does nottype anything in but clicks OKregardless. The return value is a zero-length string.

1. 用户输入内容并点击OK。这是您期望发生的事情，您将收到可以直接返回到单元格或声明变量的输入。
2. 用户单击Cancel、按下Esc或单击×（关闭）。返回值是一个布尔值False。应该考虑到这一点。
3. 用户也没有输入，但点击任何东西OK不管。返回值是一个零长度字符串。

If you are putting the return value into a cell, your own logic stream will dictate what you want to do about the latter two scenarios. You may want to clear the cell or you may want to leave the cell contents alone. Here is how to handle the various outcomes with a variant type variable and a Select Case statement.

如果您将返回值放入一个单元格中，您自己的逻辑流将决定您要对后两种情况做什么。您可能想要清除单元格，或者您可能想要单独保留单元格内容。以下是如何使用变体类型变量和Select Case 语句处理各种结果。

    Dim returnVal As Variant

    returnVal = InputBox(Prompt:="Type a value:", Title:="Test Data")

    'if the user clicked Cancel, Close or Esc the False
    'is translated to the variant as a vbNullString
    Select Case True
        Case Len(returnVal) = 0
            'no value but user clicked OK - clear the target cell
            Range("A2").ClearContents
        Case Else
            'returned a value with OK, save it
            Range("A2") = returnVal
    End Select

---

---

1 _{There is a fourth scenario when a specific type of InputBox methodis used. An InputBox can return a formula, cell range error or array. Those are special cases and requires using very specific syntax options. See the supplied link for more.}

1_{当使用特定类型的InputBox 方法时，还有第四种情况。InputBox 可以返回公式、单元格范围错误或数组。这些是特殊情况，需要使用非常具体的语法选项。有关更多信息，请参阅提供的链接。}

回答by nzpcmad

I've done this kind of thing with a form that contains a TextBox.

我已经用一个包含 TextBox 的表单完成了这种事情。

So if you wanted to put this in say cell H1, then use:

因此，如果您想将其放入 say cell 中H1，请使用：

ActiveSheet.Range("H1").Value = txtBoxName.Text

ActiveSheet.Range("H1").Value = txtBoxName.Text