为什么按 Run Sub 时 Excel VBA 会提示我输入宏名称

Why does Excel VBA prompt me for a Macro name when I press Run Sub

提问by Jason Nichols

I have the following code:

我有以下代码：

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim RR As Range
    Dim TestArea As Range
    Dim foremenList As Range
    Dim workerList As Range
    Dim workers As Range
    Dim Foremen As Range
    Dim i As Integer
    Dim R As Range
    Dim EmplList() As Variant




    Set TestArea = Sheet90.Range("b4:q8", "b15:q19", "b26:q30")
    Set foremenList = Sheet90.Range("V24:V30")
    Set RR = Sheet90.Range("AA25:AA46")
    i = 0





    For Each R In RR.Cells
       If Len(R.Value) > 0 Then
           EmplList(i) = R.Value
           i = i + 1
        End If
    Next R

    Dim ValidStr As String
    Set ValidStr = Join(EmplList, ",")


    With Sheet90.Range("b26").Validation
        .Delete
        .Add xlValidateList, xlValidAlertStop, _
            xlBetween, "1,2,3"
    End With
    Sheet90.Range("b40").Value = "Test"
End Sub

But when I press run to test it, it prompts me for a macro name.

但是当我按下运行来测试它时，它会提示我输入一个宏名称。

Additionally, it does not trigger on Worksheet_Changeany more.

此外，它不再触发Worksheet_Change。

Is this an error (i.e. I forgot a semicolon or something) that consistently triggers Excel VBA to behave like this? If so, what should I look for in the future?

这是一个错误（即我忘记了分号或其他东西）始终触发 Excel VBA 表现得像这样吗？如果是这样，我将来应该寻找什么？

回答by techturtle

The reason you can't run this one with the Run Subbutton is because it requires a parameter. If you want to run this standalone, one possibility is to run it in the Immediate Windowso you can manually pass in the parameter. Since this one is expecting a more complex data type (range) you may want to create a small sub to call it so that you can properly create your range and pass that in. Then you can use the Run Subon this sub which will call your other one.

你不能用Run Sub按钮运行这个的原因是因为它需要一个参数。如果您想独立运行此程序，一种可能性是在立即窗口中运行它，以便您可以手动传入参数。由于这个期望更复杂的数据类型（范围），您可能需要创建一个小的子程序来调用它，以便您可以正确创建您的范围并将其传入。然后您可以在此子程序上使用Run Sub它将调用你的另一个。

As far is it not triggering on Worksheet_Change, I am not able to tell what is causing it just from what you posted. However, you do need to make sure that it is located on the code page for the worksheet you are trying to run it from. If you need the same one to run from multiple sheets, you should put it into a module and call it from each sheet's Worksheet_Change method.

就它没有在 Worksheet_Change 上触发而言，我无法仅从您发布的内容中判断是什么导致了它。但是，您确实需要确保它位于您试图从中运行它的工作表的代码页上。如果您需要同一个从多个工作表运行，您应该将它放入一个模块并从每个工作表的 Worksheet_Change 方法中调用它。

回答by Jason Nichols

The problem stems from two lines:

问题源于两行：

Set ValidStr = Join(EmplList, ",")

was not a valid use of the Setkeyword (It's a string and not an object), and

不是Set关键字的有效使用（它是一个字符串而不是一个对象），并且

Set TestArea = Sheet90.Range("b4:q8", "b15:q19", "b26:q30")

apparently has too many arguments.

显然有太多的论点。

According to Microsoft, it should be a single string argument like:

根据Microsoft，它应该是单个字符串参数，例如：

Set TestArea = Sheet90.Range("b4:q8, b15:q19, b26:q30")

Commenting both of these out made the code run fine both with the run sub button, and on the event.

注释掉这两个使代码在运行子按钮和事件上都能正常运行。

The "Name Macro" dialog is some kind of error indicator, but I still don't know what it means, other than Code Borked

“名称宏”对话框是某种错误指示器，但我仍然不知道它是什么意思，除了 Code Borked

回答by tigeravatar

You can't press F5 or the run button to run triggered code. You would have to make a change in the sheet where this code is located in order for the code to run. Also, if this code is not located in Sheet90, then you won't see anything happen because this code only makes changes to Sheet90. Lastly, to make sure events are enabled, you can run this bit of code:

您不能按 F5 或运行按钮来运行触发代码。您必须在此代码所在的工作表中进行更改才能运行代码。此外，如果此代码不在 Sheet90 中，那么您将看不到任何事情发生，因为此代码仅对 Sheet90 进行了更改。最后，为了确保事件已启用，您可以运行以下代码：

Sub ReEnable_Events()
    Application.EnableEvents = True
End Sub

Note that you will still have to enable macros.

请注意，您仍然必须启用宏。