为什么我的VBA代码会抛出“无效的外部过程”错误？

For the life of me I cannot figure out why the following code is throwing a compile error with the message "Invalid outside procedure". It is highlighting the error on the starred line below.

对于我的生活,我无法弄清楚为什么以下代码抛出编译错误消息“无效的外部过程”。它突出显示下面的星号线上的错误。

Option Explicit

Dim shtThisSheet As Worksheets

**Set shtThisSheet = Application.Workbook("Formatting2.xlsm").Worksheets("Sheet1")**

Sub Formatting()

    With shtThisSheet

        With Range("A1")
            .Font.Bold = True
            .Font.Size = 14
            .HorizontalAlignment = xlLeft
        End With

        With Range("A3:A6")
            .Font.Bold = True
            .Font.Italic = True
            .Font.ColorIndex = 5
            .InsertIndent 1
        End With

        With Range("B2:D2")
            .Font.Bold = True
            .Font.Italic = True
            .Font.ColorIndex = 5
            .HorizontalAlignment = xlRight
        End With

        With Range("B3:D6")
            .Font.ColorIndex = 3
            .NumberFormat = "$#,##0"
        End With

    End With

End Sub

2 个解决方案

#1

Set statements aren't allowed outside procedures. Move the Set statement into the Formatting procedure:

外部程序不允许使用Set语句。将Set语句移动到格式化过程:

Sub Formatting()
    Set shtThisSheet = Application.Workbook("Formatting2.xlsm").Worksheets("Sheet1")
    ...

(I'd move the Dim statement into the procedure as well. I prefer to avoid global variables when possible.)

(我也将Dim语句移到了程序中。我希望尽可能避免使用全局变量。)

#2

You can declare variables as global, but you cannot set them outside of a procedure such as a sub or function.

您可以将变量声明为全局变量,但不能在子过程或函数等过程之外设置它们。

If you need this variable as a global, then it's best to set it on. Workbook_Open()

如果您需要将此变量作为全局变量,则最好将其设置为开。 Workbook_Open()

If you do not need it as a global, then move both the declaration and the set statement into your procedure

如果您不需要它作为全局,那么将声明和set语句移动到您的过程中

#1