VBA Excel - Object does not support this property or method

Question

Option Explicit
Dim wb1 As Workbook
Dim shtDisplay As Worksheet

Public Sub Main()
    Set wb1 = ActiveWorkbook
    Set shtDisplay = wb1.Worksheet("Sheet1")
    Call Main2
End Sub

Public Sub Main2()
    shtDisplay.Cells(1, 1).Value = "YES"
End Sub

I'm trying to declare aliases (variables) early in the module so I can use the same alias later in within the module without having to always declare in every sub/function.

As I understand scope, by the time the above code gets to Main2, shtDsplay would be set so I don't have to type out ActiveWorkbook.Worksheets("Sheet1").Cells(1,1).Value = Yes and yet before the program even gets that far Excel throws the Object does not support this property or method error at line Set shtDisplay = wb1.Worksheet("Sheet1")

What am I not understanding?

Thank you in advance for what is probably the most simple problem in the world and I can't get past it.

question from:https://stackoverflow.com/questions/65875124/vba-excel-object-does-not-support-this-property-or-method

Answer 1

You want the collection, not the object.

Worksheet (singular) is an object

Worksheets (plural) is a collection of objects

Simply add the s to make it plural.

wb1.Worksheets("Sheet1")