Variable Error with macro in VBA

Question

I am new to VBA in excel and I ran into an error that I am unsure how to interpret. I was wondering if somebody could help. I am trying to create s table of contents for an excel file and I found a macro online. It is posted below:

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

The error which I received was this:

Compile Error: Variable TOS not defined.

I am really new to this and I am unsure about what this means. Could somebody provide a detailed explanation on what the problem is and how to go about fixing it.

Have you included "Option Explicit" at the top of your code? This forces you to define all your variables. This might help find the problem! — MiguelH
– MiguelH, Commented Aug 17, 2016 at 15:42
That is actually included in the file already it is highlight the TOS — Troll_Hunter
– Troll_Hunter, Commented Aug 17, 2016 at 15:48

Gary Evans · Accepted Answer · 2016-08-17 16:10:43Z

1

I believe the problem is in the quotation marks, there are angled/curly brackets being used and this is stopping the code being read properly.

Taking the first instance of the issue: -

.Worksheets(“TOC”).Delete

You receive the error:-

Compile Error:

Variable not defined

and “TOC” is highlighted. This is occurring because the wrong quotation marks are being so TOC is not being seen as a literal string but instead “TOC” is being presumed to be a variable, that is not defined (i.e. does not exist).

This can be fixed by going through your code and replacing all the below: -

Change “ (Angled open quotation) to " (straight quotation (shift+2)

Change ” (Angled close quotation) to " (straight quotation (shift+2)

Change ‘ (Angled apostrophe) to ' (straight single quotation (', shares the @ key)

answered Aug 17, 2016 at 16:10

Gary Evans

1,8904 gold badges18 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mathieu Guindon Over a year ago

Note that the "Shift+2" and "shares the @ key" parts are entirely dependent on your keyboard layout and locale. Shift+2 makes a "@" on my en-US keyboard, and I get a single quote using the '/" key. That part of your answer is probably better off left out.

Troll_Hunter Over a year ago

Got it- changing the quotation marks fixed a lot of it, but I'm still running into problems with SubAddress and TextToDisplay. Any idea how to solve those issues?

Collectives™ on Stack Overflow

Variable Error with macro in VBA

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related