CODEWITHSUNDEEP
excelms-accessvba
CodeCamper
CodeCamper

Reputation: 6984

VBA auto-complete / suggestion

Typing 'Activesheet.' will not bring up a list of suggestions whereas other classes will. How do I bring up this autosuggestion screen in the coding screen as I am typing?

Upvotes: 2

Views: 5225

Answers (1)

pteranodon
pteranodon

Reputation: 2059

In Excel, typing ActiveSheet is invoking a property of the default object, Excel.Application . If you are working in Access (based on your tags), the default object is Access.Application, which doesn't have an ActiveSheet property. Instead, Access, will see ActiveSheet as an undimensioned variant variable. To get the Intellisense that you seek, you must:

  • Have a reference to the Excel library
  • Declare a variable of type Excel.Application
  • Type a dot after that variable's name and you'll see the ActiveSheet in the Intellisense

or (from @dee)

Dim someSheet As Worksheet  'As Excel.Worksheet in Access
Set someSheet = ActiveSheet 'As Excel.ActiveSheet in Access
'use someSheet, there you have intellisense

If you type Option Explicit at the top of your module, you'll get a helpful compile error when you refer to something that doesn't actually exist or is mis-spelled, instead of accidentally declaring a new variable.

Upvotes: 3

Related Questions