vba 处理取消输入框以选择范围

Handle cancellation of InputBox to select range

提问by Jeremy Newton

I have the following piece of code:

我有以下一段代码：

dim selectRange as Range
Set selectRange = Application.InputBox("Select your range", "Hello", , , , , , 8)

When a user chooses Cancel the InputBox prompt, it returns error of Object not set.

当用户选择取消 InputBox 提示时，它返回error of Object not set。

I have tried to use a Variant variable type but I can't handle it. In case of cancelling, it returns False, meanwhile in case of selecting a range, it returns Range of InputBox.

我曾尝试使用 Variant 变量类型，但无法处理。取消时返回False，选择范围时返回输入框的范围。

How can I avoid this error?

我怎样才能避免这个错误？

采纳答案by Netloh

This is a problem when selection a range with an inputbox. Excel returns an error before the range is returned, and it carries this error on when you press cancel.

当使用输入框选择范围时，这是一个问题。Excel 在返回范围之前返回一个错误，当您按取消时它会继续此错误。

You should therefore actively handle this error. If you don't want anything to happen when you press cancel, you can just use the code like this:

因此，您应该主动处理此错误。如果您不想在按下取消键时发生任何事情，您可以使用如下代码：

Sub SetRange()
    Dim selectRange As Range

    On Error Resume Next
        Set selectRange = Application.InputBox("Select your range", "Hello", , , , , , 8)
    Err.Clear
    On Error GoTo 0
End Sub 

回答by Dirk Reichel

While this question is a bit older I still want to show the properway to do it without errors. You can do it either to it via function or with a sub.

虽然这个问题有点老，但我仍然想展示正确的方法来做到这一点而不会出错。您可以通过函数或 sub 来完成它。

Your main procedure is something like this:

你的主要程序是这样的：

Sub test()
  Dim MyRange As Range

  testSub Application.InputBox("dada", , , , , , , 8), MyRange 'doing via Sub
  Set MyRange = testFunc(Application.InputBox("dada", , , , , , , 8)) ' doing via function

  If MyRange Is Nothing Then
    Debug.Print "The InputBox has been canceled."
  Else
    Debug.Print "The range " & MyRange.Address & " was selected."
  End If
End Sub

the Sub-way (funny) would be:

该子往来港澳（搞笑）将是：

Sub testSub(ByVal a As Variant, ByRef b As Range)
  If TypeOf a Is Range Then Set b = a
End Sub

And the functionwould look like:

该函数将如下所示：

Function testFunc(ByVal a As Variant) As Range
  If TypeOf a Is Range Then Set testFunc = a
End Function

Now simply use the way you like and delete the unused line.

现在只需使用您喜欢的方式并删除未使用的行。

If calling a sub or a function you do not need to Setthe parameter. That said, it doesn't matter if the InputBoxreturns an object or not. All you need to do, is to check if the parameter is the object you want or not and then act accordingly to it.

如果调用 sub 或函数，则不需要Set参数。也就是说，它是否InputBox返回一个对象并不重要。您需要做的就是检查参数是否是您想要的对象，然后根据它采取行动。

EDIT

编辑

Another smart way is using the same behavior with a collection like this:

另一种聪明的方法是对这样的集合使用相同的行为：

Sub test()
  Dim MyRange As Range
  Dim MyCol As New Collection

  MyCol.Add Application.InputBox("dada", , , , , , , 8)
  If TypeOf MyCol(1) Is Range Then Set MyRange = MyCol(1)
  Set MyCol = New Collection

  If MyRange Is Nothing Then
    Debug.Print "The inputbox has been canceled"
  Else
    Debug.Print "the range " & MyRange.Address & " was selected"
  End If
End Sub

If you still have any questions, just ask ;)

如果您还有任何问题，请提问;)

回答by Raystafarian

I'm late to the party here but this was the only place I could find that explained whyI was having trouble just checking my variable for nothing. As explained in the accepted answer, the vbCancelon a range object isn't handled the same way as a string object. The error mustbe caught with an error handler.

我在这里参加聚会迟到了，但这是我能找到的唯一可以解释为什么我在检查变量时遇到麻烦的地方。正如接受的答案中所解释的，vbCancel范围对象上的处理方式与字符串对象不同。错误必须有错误处理程序捕获。

I hateerror handlers. So I segregated it to its own function

我讨厌错误处理程序。所以我把它隔离成它自己的功能

Private Function GetUserInputRange() As Range
    'This is segregated because of how excel handles cancelling a range input
    Dim userAnswer As Range
    On Error GoTo inputerror
    Set userAnswer = Application.InputBox("Please select a single column to parse", "Column Parser", Type:=8)
    Set GetUserInputRange = userAnswer
    Exit Function
inputerror:
    Set GetUserInputRange = Nothing
End Function

Now in my main sub I can

现在在我的主子中我可以

dim someRange as range
set someRange = GetUserInputRange
if someRange is Nothing Then Exit Sub

Anyhow this is not the same as the accepted answer because it allows the user to only handle this error with a specific error handler and not need to resume nextor have the rest of the procedure handled the same way. In case anyone ends up here like I did.

无论如何，这与接受的答案不同，因为它允许用户仅使用特定的错误处理程序处理此错误，而无需resume next或以相同的方式处理其余过程。万一有人像我一样最终来到这里。

回答by Ken Love

I have found that checking for the "Object required" error that you mentioned is one way of handling a cancel.

我发现检查您提到的“需要对象”错误是处理取消的一种方法。

On Error Resume Next
dim selectRange as Range
' InputBox will prevent invalid ranges from being submitted when set to Type:=8.
Set selectRange = Application.InputBox("Select your range", "Hello", , , , , , 8)
' Check for cancel: "Object required".
If Err.Number = 424 Then
    ' Cancel.
    Exit Sub
End If
On Error GoTo 0

回答by mwahlgreen

If I use Dirks second answer inside a for loop and I want to exit my sub, it is not enough to execute an Exit Sub inside his IF statement
I found that if I use Exit Sub standalone inside a for loop, I will not exit my sub in all cases, however, in most cases only exit the for loop.

如果我在 for 循环中使用 Dirks 的第二个答案并且我想退出我的 sub，在他的 IF 语句中执行 Exit Sub 是不够的
我发现如果我在 for 循环中独立使用 Exit Sub，我将不会退出我的sub 在所有情况下，然而，在大多数情况下，只退出 f​​or 循环。

Here you have Dirks code

这里有 Dirks 代码

EDIT

Another smart way is using the same behavior with a collection like this:

 Sub test()
  Dim MyRange As Range
  Dim MyCol As New Collection

  MyCol.Add Application.InputBox("dada", , , , , , , 8)
  If TypeOf MyCol(1) Is Range Then Set MyRange = MyCol(1)
  Set MyCol = New Collection

  If MyRange Is Nothing Then
    Debug.Print "The input box has been canceled"
  Else
    Debug.Print "the range " & MyRange.Address & " was selected"
  End If
 End Sub

If you still have any questions, just ask ;)

编辑

另一种聪明的方法是对这样的集合使用相同的行为：

 Sub test()
  Dim MyRange As Range
  Dim MyCol As New Collection

  MyCol.Add Application.InputBox("dada", , , , , , , 8)
  If TypeOf MyCol(1) Is Range Then Set MyRange = MyCol(1)
  Set MyCol = New Collection

  If MyRange Is Nothing Then
    Debug.Print "The input box has been canceled"
  Else
    Debug.Print "the range " & MyRange.Address & " was selected"
  End If
 End Sub

如果您还有任何问题，请提问;)

Here is what I made to work as a example:

以下是我所做的工作作为示例：

Sub test()
  Dim i as Integer
  Dim boolExit as Boolean
  Dim MyRange As Range
  Dim MyCol As New Collection
  boolExit = False

  For i = 1 To 5 Then
    MyCol.Add Application.InputBox("dada", , , , , , , 8)
    If TypeOf MyCol(1) Is Range Then Set MyRange = MyCol(1)
    Set MyCol = New Collection

    If MyRange Is Nothing Then
      Debug.Print "The inputbox has been canceled"
      boolExit = True
      Exit Sub
    Else
      Debug.Print "the range " & MyRange.Address & " was selected"
    End If
  Next i

  If boolExit = True Then Exit Sub 'Checks if Sub should be exited

  Debug.Print "Program completed"      
End Sub

If you press cancel at anytime in the five runs, the Sub is shutdown with the above code and you will never see Program completedprinted.

如果您在五次运行中的任何时候按取消，Sub 将使用上述代码关闭，您将永远不会看到Program completed打印出来。

However if you remove boolExit from the above, the code after the For loop is still being run if you press cancel in any of the 1+ runs and you will see Program completedeven when that is not true.

但是，如果您从上面删除 boolExit，如果您在任何 1+ 次运行中按取消键，For 循环之后的代码仍在运行，即使这不是真的，您也会看到程序已完成。