Compile error: Variable not defined??

[Unicode]

Dear All, I run the following code script, then I get an error: "Variable not Defined. What could be the issue, is there an explanation to what is the correct variable not set in the script code?




[CODE:]




Sub ListSheetNamesInNewWorkbook()
Dim wb As Workbook: Set wb = ThisWorkbook
Dim ws As Worksheet: Set ws = wb.ActiveSheet


Set wb = Excel.Application.Workbooks.Add
Set wb = wb.Sheets(21)


For i = 1 To ThisWorkbook.Sheet.Count
wb.Cells(i, 1) = i
wb.Cells(i, 2) = ThisWorkbook.Sheets(i).Name
Next i


With objNewWorksheet
.Rows(1).Insert
.Cells(4, 2) = "Section"
.Cells(4, 2).Font.Bold = True
.Cells(2, 2) = "Table of Contents"
.Cells(2, 2).Font.Bold = True
.Cells(4, 6) = "Page"
.Cells(4, 6).Font.Bold = True
.Columns("A:B").AutoFit
End With
End Sub

 

[Joe4]

You have a lot of some curious things going on here.

First, why are you setting wb multiple times?

[COLOR=#333333]Dim wb As Workbook: [/COLOR][COLOR=#ff0000]Set wb[/COLOR][COLOR=#333333] = ThisWorkbook[/COLOR]
[COLOR=#333333]Dim ws As Worksheet: Set ws = wb.ActiveSheet[/COLOR]


[COLOR=#ff0000]Set wb[/COLOR][COLOR=#333333] = Excel.Application.Workbooks.Add[/COLOR]
[COLOR=#ff0000]Set wb[/COLOR][COLOR=#333333] = wb.Sheets(21)[/COLOR]

Second, this line looks incorrect:

Set wb[COLOR=#333333] = wb.Sheets(21)[/COLOR]

You are trying to set a worksheet equal to a Workbook variable.
I think you mean:

Set ws[COLOR=#333333] = wb.Sheets(21)[/COLOR]

and then the wb references below should be ws

[COLOR=#333333]For i = 1 To ThisWorkbook.Sheet.Count[/COLOR]
[COLOR=#ff0000]wb[/COLOR][COLOR=#333333].Cells(i, 1) = i[/COLOR]
[COLOR=#ff0000]wb[/COLOR][COLOR=#333333].Cells(i, 2) = ThisWorkbook.Sheets(i).Name[/COLOR]
[COLOR=#333333]Next i[/COLOR]

and then you are trying to reference an object that you haven't defined anywhere:

[COLOR=#333333]With [/COLOR][COLOR=#ff0000]objNewWorksheet[/COLOR][COLOR=#333333][/COLOR]

You need to set objNewWorksheet equal to something before you reference it!

I highly recommend that you turn on "Option Explicit", which will force you to declare all variables and objects before using them. This will help you identify errors due to typos or trying to use objects/variables that you haven't defined.
See: https://www.excel-easy.com/vba/examples/option-explicit.html

 

[Unicode]

Why would this error on the following code line: For i = 1

You have a lot of some curious things going on here.

First, why are you setting wb multiple times?

[COLOR=#333333]Dim wb As Workbook: [/COLOR][COLOR=#ff0000]Set wb[/COLOR][COLOR=#333333] = ThisWorkbook[/COLOR]
[COLOR=#333333]Dim ws As Worksheet: Set ws = wb.ActiveSheet[/COLOR]


[COLOR=#ff0000]Set wb[/COLOR][COLOR=#333333] = Excel.Application.Workbooks.Add[/COLOR]
[COLOR=#ff0000]Set wb[/COLOR][COLOR=#333333] = wb.Sheets(21)[/COLOR]

Second, this line looks incorrect:

Set wb[COLOR=#333333] = wb.Sheets(21)[/COLOR]

You are trying to set a worksheet equal to a Workbook variable.
I think you mean:

Set ws[COLOR=#333333] = wb.Sheets(21)[/COLOR]

and then the wb references below should be ws

[COLOR=#333333]For i = 1 To ThisWorkbook.Sheet.Count[/COLOR]
[COLOR=#ff0000]wb[/COLOR][COLOR=#333333].Cells(i, 1) = i[/COLOR]
[COLOR=#ff0000]wb[/COLOR][COLOR=#333333].Cells(i, 2) = ThisWorkbook.Sheets(i).Name[/COLOR]
[COLOR=#333333]Next i[/COLOR]

and then you are trying to reference an object that you haven't defined anywhere:

[COLOR=#333333]With [/COLOR][COLOR=#ff0000]objNewWorksheet[/COLOR]

You need to set objNewWorksheet equal to something before you reference it!

I highly recommend that you turn on "Option Explicit", which will force you to declare all variables and objects before using them. This will help you identify errors due to typos or trying to use objects/variables that you haven't defined.
See: https://www.excel-easy.com/vba/examples/option-explicit.html

 

[Joe4]

Because you haven't defined i.

If you have Option Explicit on, you MUST declare all variables before using them.
You declared wb and ws, but not i.

Take a look at that link I provided you for more information.