VBA IDE has only partial Intellisense

AlHal2
AlHal2 used Ask the Experts™
on
I've programmed in vb6 and am trying to learn VBA for Word and Excel.  Intellisense doesen't seem to be working properly.
In the editor tab all the boxes are checked.
If I type Excel I get intellisense to type selection.  However if I put a dot after selection I get no intellisense.


I found this page useful, but it doesn't have what I want http://spreadsheetpage.com/index.php/tip/C32
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
ste5anSenior Developer

Commented:
Can you post a screenshot of this case?

Normally this means that this name doesn't point to an onject or class.

Author

Commented:
Is this any use?

Sub Test()
'
' Test Macro
'
' Keyboard Shortcut: Ctrl+p
'
    'Range("G5").Select
    ActiveCell.FormulaR1C1 = "This is the best excel tutorial"
    
    'Columns("G:G").Select
    'Columns("G:G").EntireColumn.AutoFit
    Selection.Columns.AutoFit
    With Selection.Font
        .Color = -16776961
        .TintAndShade = 0
    End With
    'Excel.Selection.Interior.
    
    With Excel.Selection.
    
    
        .Color = 240
        
    End With
End Sub

Open in new window

ste5anSenior Developer

Commented:
Ok. In this case: It's an syntax error. The dot at the end of line 19 is wrong.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
What about now?  I had to type Interior manually.

Sub Test()
    'Range("G5").Select
    ActiveCell.FormulaR1C1 = "This is the best excel tutorial"
    
    Selection.Columns.AutoFit
    With Selection.Font
        .Color = -16776961
        .TintAndShade = 0
    End With
    If Excel.Selection.Interior = 240 Then
    End If
    
End Sub

Open in new window

ste5anSenior Developer
Commented:
Yup. Cause Selection is of type Object and Intellisense uses static type lookup. So it cannot know the actual type.

Capture.PNG
The only way around is, when you know the correct type:

Untitled2.png
Fabrice LambertConsulting
Distinguished Expert 2017
Commented:
If I type Excel I get intellisense to type selection.  However if I put a dot after selection I get no intellisense.
Of course you have no intellisens for the Selection object, because it can be anything between:
A range, a worksheet, a chart, or a shape
The intellisens only work when the type is perfectly known.

Generally speaking when automating Excel, using object such as ActiveWorkbook, ActiveSheet, ActiveCell, ActiveChart, ActiveShape, Selection, the global Sheets, Range, Cells, Charts collection is a very bad idea.
Why you may ask ? Because these objects are tied to user interraction, and their content is subject to change anytime without notice, thus you can never garantee that its content is valid, neither it is what you expect.

Keep in mind that Office applications are mutli-document, and that users are free to do whatever they want while your application is processing.
so always be sure to explicitly set a reference on known objects not subject to user interractions.

Final note:
The Selection object is probably the worst to handle, as its exact type is unknown and it can even be nothing.
Plus it is slow as hell, update the GUI wich provide a bad user experience (unless your users like to see cursors blinking everywhere on their workbooks).
Mark EdwardsChief Technology Officer

Commented:
Some more notes:
- Intellisense doesn't work if the code won't compile - compile errors prevent intellisense from working.
- Intellisense only works if the object type is from a library such as:
Dim wb as Excel.Workbook
Dim ws as Object does not work.

Author

Commented:
Fabrice,

You say:
Generally speaking when automating Excel, using object such as ActiveWorkbook, ActiveSheet, ActiveCell, ActiveChart, ActiveShape, Selection, the global Sheets, Range, Cells, Charts collection is a very bad idea.

What is a good idea or are there any best practices to follow for VBA?  This question is for anyone.
Senior Developer
Commented:
See my sample again: Use explicit types. Furthermore, I recommend to take a step further and refactor it, so that the actual methods are always strongly typed. E.g.

Option Explicit

Public Sub Test()

  ActiveCell.FormulaR1C1 = "This is the best excel tutorial"
  AdjustAppearance Selection

End Sub

Private Sub AdjustAppearance(ARange As Excel.Range)

  ARange.Columns.AutoFit
  ARange.Font.Color = -16776961
  ARange.Font.TintAndShade = 0
  If ARange.Interior = 240 Then
  End If
    
End Sub

Open in new window

Author

Commented:
Thanks everyone.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial