当我按Run Sub时，为什么Excel VBA提示我输入宏名称 [英] Why does Excel VBA prompt me for a Macro name when I press Run Sub

问题描述

我有以下代码:

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.

此外，它不会再在 Worksheet_Change 上触发.

Additionally, it does not trigger on Worksheet_Changeany more.

这是否是始终触发Excel VBA行为的错误(即我忘记了分号或其他内容)?如果是这样，将来我应该寻找什么?

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?

推荐答案

问题源于两行:

Set ValidStr = Join(EmplList, ",")

不是有效地使用了 Set 关键字(它是一个字符串，而不是一个对象)，并且

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

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

显然有太多的论点.

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

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

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

对这两种方法进行注释，使代码可以通过run sub按钮以及事件正常运行.

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

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

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

这篇关于当我按Run Sub时，为什么Excel VBA提示我输入宏名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！