vba - 在 VBA 中调用 Year() 函数时出现预期数组错误

Question

编辑 2:此问题已解决。我错误地声明了一个名为“Year”的变量...

我正在尝试让一个单元格包含我提供的一系列报告中的版权，并且由于版权随年份而变化，我想调用 Year 函数来给我当前的年份，所以我没有每年一月更新宏。我的相关代码如下所示:

With .Cells(5, 1)
            .Value = Chr(169) & " " & Year(Date) & " NCH Marketing Services, Inc"
            .Font.Bold = False
            .Font.Name = "Arial"
            .Font.Size = 11
        End With

当我执行此代码时，我收到错误消息“编译错误:预期数组”。奇怪的是，我尝试在三个不同的场合运行这个宏，第一次或第三次它没有编译，但第二次，它运行得很好。我没有对代码进行任何编辑，并且我在网上查找了 Year 函数的语法/用法，但我无法弄清楚为什么这有时会起作用。有没有一种方法可以以更可靠的方式实现这一点？

编辑:以下是完整代码

Option Explicit
Sub RGA_Format_Reports()

Application.ScreenUpdating = False

Dim Year As Integer
Dim Quarter As Integer

Dim FolderPath As String
Dim FileName As String
Dim WorkBk As Workbook

Dim iRows As Integer
Dim iCols As Integer

Dim shpTitle As Shape
Dim shpLogo As Shape


'Quarter = InputBox("Please enter the quarter number for which the reports are being run.")

'Year = InputBox("Please enter the year for which the reports are being run.")

'FolderPath = "G:\Analytical Services\Internal Client Requests\NRS\Scheduled\" & Year & "\Quarterly RGA Store Alert Reports\" & Year & " Q" & Quarter & "\"

'FolderPath = "G:\Analytical Services\General Team Folders\Kyle\Macro Tests\RGA Reports\"

'FileName = Dir(FolderPath & "*.xl*")

'DoWhile FileName <> ""

    'Set WorkBk = Workbooks.Open(FolderPath & FileName)
        'With WorkBk

With ActiveWorkbook

        With .ActiveSheet

            iRows = .UsedRange.Rows.Count
            iCols = .UsedRange.Columns.Count

            .Rows.AutoFit
            .Columns.AutoFit

            .Columns(1).ColumnWidth = 30


            ActiveWindow.FreezePanes = False
            .Range("A9").Select
            ActiveWindow.FreezePanes = True

            With .Range(.Cells(iRows - 2, 1), .Cells(iRows, 1))
                .WrapText = False
                .Font.Name = "Arial"
                .Font.Size = 10
            End With


            With .PageSetup
                .TopMargin = Application.InchesToPoints(0.25)
                .BottomMargin = Application.InchesToPoints(0.25)
                .LeftMargin = Application.InchesToPoints(0.25)
                .RightMargin = Application.InchesToPoints(0.25)
                .CenterHorizontally = True
                .Zoom = False
                .Orientation = xlLandscape
                .FitToPagesWide = 1
                .FitToPagesTall = False
                .PrintTitleRows = "$1:$8"
            End With


            .Range(.Cells(9, 3), .Cells(iRows, 3)).HorizontalAlignment = xlRight
            .Range(.Cells(iRows - 4, 1), .Cells(iRows - 4, iCols)).Font.Bold = True
            .Range(.Cells(iRows - 4, 1), .Cells(iRows - 4, iCols)).Interior.Color = RGB(238, 236, 225)


            With .Range(.Cells(7, 9), .Cells(7, 12))
                .Merge
                .HorizontalAlignment = xlCenter
                .Font.Bold = True
                .Font.Name = "Arial"
                .Font.Size = 10
                .Interior.Color = RGB(238, 236, 225)

                With .Borders(xlEdgeTop)
                    .LineStyle = xlContinuous
                    .Weight = xlThick
                End With

                With .Borders(xlEdgeBottom)
                    .LineStyle = xlContinuous
                    .Weight = xlThick
                End With

                With .Borders(xlEdgeLeft)
                    .LineStyle = xlContinuous
                    .Weight = xlThick
                End With

                With .Borders(xlEdgeRight)
                    .LineStyle = xlContinuous
                    .Weight = xlThick
                End With

                .Value = "Total Billed"
            End With


            With .Range(.Cells(7, 13), .Cells(7, 19))
                .Merge
                .HorizontalAlignment = xlCenter
                .Font.Bold = True
                .Font.Name = "Arial"
                .Font.Size = 10
                .Interior.Color = RGB(238, 236, 225)

                With .Borders(xlEdgeTop)
                    .LineStyle = xlContinuous
                    .Weight = xlThick
                End With

                With .Borders(xlEdgeBottom)
                    .LineStyle = xlContinuous
                    .Weight = xlThick
                End With

                With .Borders(xlEdgeLeft)
                    .LineStyle = xlContinuous
                    .Weight = xlThick
                End With

                With .Borders(xlEdgeRight)
                    .LineStyle = xlContinuous
                    .Weight = xlThick
                End With

                .Value = "Total Adjustments"
            End With


            With .Range(.Cells(8, 1), .Cells(iRows - 4, 8))
                With .Borders(xlEdgeTop)
                    .LineStyle = xlContinuous
                    .Weight = xlThick
                End With

                With .Borders(xlEdgeBottom)
                    .LineStyle = xlContinuous
                    .Weight = xlThick
                End With

                With .Borders(xlEdgeLeft)
                    .LineStyle = xlContinuous
                    .Weight = xlThick
                End With

                With .Borders(xlEdgeRight)
                    .LineStyle = xlContinuous
                    .Weight = xlThick
                End With
            End With


            With .Range(.Cells(7, 9), .Cells(iRows - 4, 12))
                With .Borders(xlEdgeTop)
                    .LineStyle = xlContinuous
                    .Weight = xlThick
                End With

                With .Borders(xlEdgeBottom)
                    .LineStyle = xlContinuous
                    .Weight = xlThick
                End With

                With .Borders(xlEdgeLeft)
                    .LineStyle = xlContinuous
                    .Weight = xlThick
                End With

                With .Borders(xlEdgeRight)
                    .LineStyle = xlContinuous
                    .Weight = xlThick
                End With
            End With


            With .Range(.Cells(7, 13), .Cells(iRows - 4, 19))
                With .Borders(xlEdgeTop)
                    .LineStyle = xlContinuous
                    .Weight = xlThick
                End With

                With .Borders(xlEdgeBottom)
                    .LineStyle = xlContinuous
                    .Weight = xlThick
                End With

                With .Borders(xlEdgeLeft)
                    .LineStyle = xlContinuous
                    .Weight = xlThick
                End With

                With .Borders(xlEdgeRight)
                    .LineStyle = xlContinuous
                    .Weight = xlThick
                End With

            End With

            If .Cells(9, 4) = 0 Then
                Columns("D:E").Delete
            End If

            For iRows = 1 To 4
                .Cells(iRows, 1).Font.Bold = True
                .Cells(iRows, 1).Font.Name = "Arial"
                If iRows = 1 Then
                    .Cells(iRows, 1).Font.Size = 14
                Else
                    .Cells(iRows, 1).Font.Size = 12
                End If
            Next iRows

            With .Cells(5, 1)
                .Value = Chr(169) & " " & Year(Date) & " NCH Marketing Services, Inc"
                .Font.Bold = False
                .Font.Name = "Arial"
                .Font.Size = 11
            End With

            .Columns("A").ColumnWidth = 200

            .Range(Cells(1, 1), Cells(5, 1)).WrapText = False

            .Range(Cells(1, 1), Cells(5, 1)).Copy
            .Range("B1").Select
            Application.Wait (Now + TimeValue("00:00:01"))
            .Pictures.Paste.Select
            .Range(Cells(1, 1), Cells(5, 1)).ClearContents
            .Columns("A").AutoFit
            .Range("A1").Select

            Set shpTitle = .Shapes("Picture 1")
            With shpTitle
                .Name = "Title Picture"
                .Placement = xlFreeFloating
            End With

            Set shpLogo = .Shapes.AddPicture("G:\Analytical Services\AS Tools\AS Templates\NCH Logo.png", False, True, 1, 1, 60, 67)
            With shpLogo
                .Name = "Logo Picture"
                .Placement = xlFreeFloating
            End With

            With shpTitle
                .Left = 67
            End With


            .Columns("A").ColumnWidth = 30



        End With

End With


End Sub

Answer 1

@ShaiRado 指出我设置了一个名为“Year”的变量。我知道声明与函数同名的变量是一个很大的禁忌；我只是精神错乱了。更改变量声明解决了我的问题。谢谢@ShaiRado。