Intellisense in VBE not fully functioning for objects

Question

I've seen in online tutorials Intellisense works the magic: while you key in the object name, it auto complete it for you. Why my VBE doesn't? It works on properties and methods, but not objects.

I'm using Office 2010, maybe the feature is not available in early versions?

Answer 1

The Intellisense feature has two requirements:

1. Compileable Code - Choose menu option Debug..Compile VBA Project to compile. If the project isn't compileable, Intellisense can get confused about when and where to present itself.

2. Strongly typed variables - Variables and Object Model members must be strongly typed in order for Intellisense to work.

   • None of these variables are strongly typed, so none of them will have Intellisense:

     Dim sht1, sht2 As Variant, sht3 As Object

     Whereas, Dim sht1 As Worksheet will get Intellisense

   • Some object model members, like Excel.ThisWorkbook.Worksheets(1) return a Variant (because the Worksheets method can return a single Worksheet or an array of Worksheets). In order to get Intellisense, you'll need to explicitly declare a strongly typed variable, and then work with that variable:

     'Explicitly declare a strongly typed variable
     Dim sht1 As Worksheet
     'Cast the Variant result to a Worksheet
     Set sht1 = Excel.ThisWorkbook.Worksheets(1)
     With sht1
         'Type a period to invoke Intellisense for the Worksheet object
         Debug.Print .Name
     End With
     

     If you're working with members that return types that are defined in another library, like Excel.Application.VBE, then you'll need to add a reference to that library (in this example, VBIDE) before you can declare an appropriate strongly typed member.

Answer 2

To force Intellisense feature in VBE, simply press Ctrl+Space.