VBA Editor not autocompleting

[eduzs]

Why is my VBA Excel editor not autocompleting while I type the code?
For example: if I type "Selection". I'll usually expect to see the list of things I can do with it.
Maybe I disabled any setting?

Thanks

 

[shg]

Can you show how an activesheet is not a worksheet?

If it's a chart sheet.

 

[DanteAmor]

If it's a chart sheet.

Okay, but the OP did not mention that it is working with a chart sheet. And my example is focused on a sheet.

 

[footoo]

Okay, but the OP did not mention that it is working with a chart sheet. And my example is focused on a sheet.

The OP asked about intellisense re Selection, not ActiveSheet. I was merely commenting upon the example you posted.

 

[DanteAmor]

The OP asked about intellisense re Selection, not ActiveSheet. I was merely commenting upon the example you posted.

Thanks for the observation.

Everything is about intellisense, it is to show another example of that and to correctly declare the variables.

 

[eduzs]

Thanks all.
Intellisense does not seem to work with "Selection.", "Activesheet.", "Sheets(1).", "Cells(1,1).".
I suppose that in Word the minimum selection (if you don't selected anything else) is the character right to the cursor position, in Excel is the Activecell, so I think that the VBA can know what is selected.
In conclusion, since sometimes I need the intellisense assistance for programming macros and there's this easy workaround, I will correctly declare variable instead of direct using "Sheets(x).", "Selection." or "Cells(x,y).", like:

Sub MyNewMacro

Dim, oRng as Range, oSheet as Worksheet

Set oRng=Selection.range ' or Cells(1,1) for example
Set oSheet=Sheets(1)

Debug.print oRng.cells.count
Debug.print oSheet.name

End Sub

 

[RoryA]

I suppose that in Word the minimum selection (if you don't selected anything else) is the character right to the cursor position, in Excel is the Activecell, so I think that the VBA can know what is selected.

No, in Excel the selection could be a chart, or a shape/control for example. Activecell will give intellisense because it's always a Range. Selection could be a number of things that are not ranges, which is why it returns an Object, and that is why you don't get Intellisense. Equally, as has been said, Activesheet - or Sheets - is not necessarily a worksheet, so again it returns an Object and you don't get Intellisense. Cells is the one anomaly there as far as I can see.

 

[footoo]

Re intellisense and Cells() : https://social.msdn.microsoft.com/F...vailable-for-some-type-objects?forum=exceldev

 

[shg]

Sid's posts in that thread are rife with errors.

It returns a handle to that Range.

For example

Sub Sample()
  Dim rng As Range
  
  Set rng = Range.Cells(1, 1)
  
  MsgBox rng.Address '<~~ This will give you the address of the range
  MsgBox rng.Value '<~~ This will give you the value of the range
End Sub

That doesn't compile.

IntelliSense is not available for generic object types and .Cell() is a generic object type. Same goes for ActiveSheet. You will not get IntelliSense for Activesheet but will get it for Sheets("Sheet1")

There is no ".Cell", and Cells always returns a range, and you don't get Intellisense for Sheets() (because it can return various objects) or Worksheets() (because it can return a worksheet object or a Worksheets collection, which have different properties and methods). Nothing in that sentence is correct.

 

[Fluff]

There's an article here https://colinlegg.wordpress.com/2012/01/24/making-sense-of-cells-intellisense-part-1/ explaining about Cells & intellisense.

 

[shg]

Interesting, but doesn't Colin's post suggest that Cells(whatever) could return something other than an Range object? What might that be, and how?