在VBA中调用年份()函数时的预期数组错误

Question

编辑2:这已经解决了。我错误地声明了一个名为“年份”的变量.

我试图让一个单元格在我发布的一系列报告中包含版权，由于版权随着年份的变化，我想调用年份函数来给我当前的年份，这样我就不必每年一月更新宏了。我的代码如下所示：

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

当我执行这段代码时，我会得到错误消息“编译错误:预期数组”。奇怪的是，我曾三次尝试运行这个宏，它没有编译第一次或第三次，但第二次运行得很好。我没有对代码进行任何编辑，我在网上查找了年份函数的语法/用法，我不知道为什么有时会起作用。有什么方法可以让我以更可靠的方式实现这一点？

编辑:下面是完整的代码

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。