ForEach On Error GoTo Resume Next不工作

I'm trying to follow this tutorial. I've created a button with the following click handler:

我正在尝试按照本教程。我用以下点击处理程序创建了一个按钮:

Private Sub SqrtButton_Click()
    Dim rng As Range, cell As Range
    Set rng = Selection
    For Each cell In rng
        On Error GoTo InvalidValue:
        cell.Value = Sqr(cell.Value)
    Next cell
    Exit Sub

InvalidValue:
    MsgBox Err.Number & " " & Err.Description & " at cell " & cell.Address
    Resume Next
End Sub

According to the tutorial, the program should display two error messages for cells B4 and B5 and replace values in the remaining cells. But, when I run the code, only the first cell value is replaced (B2) and no error messages are displayed.

根据教程,程序应显示单元格B4和B5的两条错误消息,并替换其余单元格中的值。但是,当我运行代码时,只替换第一个单元格值(B2)并且不显示任何错误消息。

The example is available to download and it doesn't work either.

该示例可供下载,但也不起作用。

What's wrong with this code?

这段代码出了什么问题?

2 个解决方案

#1

Your range is based off of the selection (highlighted cells), select all 5 cells (B2:B6) and then click the button. The code seemed to work fine for me when I did this.

您的范围基于选择(突出显示的单元格),选择所有5个单元格(B2:B6),然后单击按钮。当我这样做时,代码对我来说似乎很好。

Be kind and leave feedback! :)

善待并留下反馈! :)

#2

I know this has been answered but it really bothers me that the On Error GoTo syntax could be considered good practice. Always avoid that if you can. Here is the same code, but rewritten to avoid the On Error GoTo statement:

我知道这已经得到了解答,但是On Error GoTo语法可能被认为是一种很好的做法。如果可以的话,一定要避免。这是相同的代码,但重写以避免On Error GoTo语句:

Private Sub SqrtButton_Click()
    Dim rng As Range, cell As Range
    Set rng = Selection
    For Each cell In rng.Cells
        If IsNumeric(cell.Value) And cell.Value >= 0 Then
            cell.Value = Sqr(cell.Value)
        Else
            MsgBox "Invalid Value [" & cell.Value & "] at cell " & cell.Address(0, 0)
        End If
    Next cell
End Sub

#1