Why doesn't Intellisense appear after typing Worksheets("Sheet1").?

4
votes

I understand why it wouldn't pop up for ActiveSheet since it can return a worksheet or chart sheet so it wouldn't know which set of properties/methods to list, but if I'm explicitly referencing a worksheet within the Worksheets collection, why doesn't Intellisense get triggered?

I also want to extend this question to predefined constants. Sometimes a list of the predefined constants pops up for a property but sometimes it doesn't. Why is that? For example:

Application.Calculation= will trigger a list of the predefined constants.

Worksheets("Sheet1").PageSetup.Orientation= will NOT trigger a list of predefined constants.

Thanks for your answers, folks! :)

3
vbaexcel

3 Answers

2
votes

The Intellisense needs to know what is returned to give you the proper selection.

Have a look at the Object-Browser:

  • Application.Calulation returns XlCalculation. You get the selection by Intellisense
  • Worksheets("Sheet1") returns an unspecified Object, because it can be a Worksheet, but doesn't has to. That's why the Intellisense breaks at this level.
2
votes

the Intellisense works if Excel know exactly what you want.

If you do this in your Sub or Function it works.

Dim sh As Excel.Worksheet

Set sh = ThisWorkbook.Worksheets("DUMMY")

sh.PageSetup.Orientation = here comes the Intellisense 

Set sh = Nothing
1
votes

If you declare each object as a variable and clarify the kind of object to the program, Intellisense will work normally.

See examples below. Intellisense does not work for Charts (1), Worksheets ("Sheet1"), Cells (1, 1). But Ws, rng, myChart work.

Sub test()
    Dim Ws As Worksheet
    Dim rng As Range
    Dim myChart As Chart

    Set myChart = Charts(1)
    Set Ws = Worksheets("Sheet1")
    Set rng = Cells(1, 1)


End Sub