excel - VBA中宏的可变错误

Question

我是excel中VBA的新手，遇到一个我不确定如何解释的错误。我想知道是否有人可以提供帮助。我正在尝试为excel文件创建目录，但我在网上找到了一个宏。它张贴在下面:

Sub Create_TOC()
Dim wbBook As Workbook
Dim wsActive As Worksheet
Dim wsSheet As Worksheet
Dim lnRow As Long
Dim lnPages As Long
Dim lnCount As Long
Set wbBook = ActiveWorkbook
With Application
    .DisplayAlerts = False
    .ScreenUpdating = False
End With
    'If the TOC sheet already exist delete it and add a new
    'worksheet.
On Error Resume Next
With wbBook
    .Worksheets(“TOC”).Delete
    .Worksheets.Add Before:=.Worksheets(1)
End With
On Error GoTo 0
Set wsActive = wbBook.ActiveSheet
With wsActive
    .Name = “TOC”
    With .Range(“A1:B1”)
        .Value = VBA.Array(“Table of Contents”, “Sheet # – # of Pages”)
        .Font.Bold = True
    End With
End With
lnRow = 2
lnCount = 1
‘Iterate through the worksheets in the workbook and create
‘sheetnames, add hyperlink and count & write the running number
‘of pages to be printed for each sheet on the TOC sheet.
For Each wsSheet In wbBook.Worksheets
    If wsSheet.Name <> wsActive.Name Then
        wsSheet.Activate
        With wsActive
            .Hyperlinks.Add .Cells(lnRow, 1), “”, _
            SubAddress:=”‘” & wsSheet.Name & “‘!A1”, _
            TextToDisplay:=wsSheet.Name
             lnPages = wsSheet.PageSetup.Pages().Count
            .Cells(lnRow, 2).Value = “‘” & lnCount & “ - ” & lnPages
        End With
        lnRow = lnRow + 1
        lnCount = lnCount + 1
    End If
Next wsSheet
wsActive.Activate
wsActive.Columns(“A:B”).EntireColumn.AutoFit
With Application
    .DisplayAlerts = True
    .ScreenUpdating = True
End With
End Sub

我收到的错误是这样的:

Compile Error: Variable TOS not defined.

我真的很陌生，我不确定这意味着什么。有人可以详细说明问题是什么以及如何解决它。

Answer 1

我相信问题出在引号中，使用了尖括号/弯括号，这阻止了代码的正确读取。

首先考虑问题:-

.Worksheets(“TOC”).Delete

您收到错误:

Compile Error:

Variable not defined

并且 “TOC”突出显示。发生这种情况是因为引号使用了错误，因此 TOC未被视为文字字符串，而是 “TOC”被假定为 undefined variable (即不存在)。

可以通过遍历代码并替换以下所有内容来解决此问题:-

将 “(斜角引号)更改为 "(直引号(shift + 2))

将 ”(斜角引号)更改为 "(直引号(shift + 2))

将 ‘(斜撇号)更改为 '(单引号('，共享@键)