What about "Application" as default object in Excel VBA?-CodePudding

I have just written this easy macro in Excel VBA for merging a group of selected cells:

Sub Macro_Merge()
Dim Temp As String
Dim S As Variant

Temp = ""
For Each S In Selection
  If Temp = "" Then
    Temp = CStr(S.Value)
  Else:
    Temp = Temp   ","   CStr(S.Value)
  End If
Next

Selection.Merge
Selection.Value = Temp
Selection.VerticalAlignment = xlTop
  
End Sub

This works fine, but I always see that annoying dialog box, warning me about loosing data while merging (which is exactly what I'm trying to avoid in my macro).

I can get rid of that dialog box, configuration the Application's DisplayAlerts property:

Application.DisplayAlerts = False
Selection.Merge
Selection.Value = Temp
Application.DisplayAlerts = True

This is working fine.
So, as Application is the default object, I tried to clean up my code, as follows:

DisplayAlerts = False
Selection.Merge
Selection.Value = Temp
DisplayAlerts = True

As you see, I simply omit mentioning the Application object. This is something which is allowed and I've done in the past. (If not in VBA, then Delphi, maybe?)

... but to my surprise, the dialog box appears again (although pressing F1 brings me to the official "Application.DisplayAlerts" documentation).
This leaves me with a simple question:

If a simple DisplayAlerts = ... does not equal Application.DisplayAlerts = ... anymore, what does it mean and how can I use it?

For your information, I'm working with Excel-365.

CodePudding user response：

DisplayAlerts is an undeclared variable.

Certain Application properties and methods can (effectively) have the Application omitted:

ActiveCell, ActiveSheet, ActiveWorkbook, ActiveWindow, Addins, Cells, Charts, Selection, etc.
Calculate, Evaluate, Intersect, Run, Union, etc.

(but see this answer why/how this works):

A boolean property such as DisplayAlerts (EnableEvents, ScreenUpdating, etc) doesn't fall into the above category.

A golden rule in order not to fall into such a trap is the usage of Option Explicit while writing macros.

CodePudding user response：

Just to add some information to the answer of @BigBen. If you write something like Workbooks or ActiveSheet in your code, VBA is not looking into the Application-object - it is looking into a (rather well hidden) object named Global.

The global object is exposing some (but not all) properties and methods of the Application-object, so ActiveSheet is referring to Application.ActiveSheet - but not because the Application has a member with this name but because the Global object defines that ActiveSheet means Application.ActiveSheet. In fact even the Application-object is accessed via the Global object.

There is hardly any information about this Global object or its concept. I found a page from Microsoft describing the Global object of MS Word, but the only explanation there is "Contains top-level properties and methods that don't need to be preceded by the Application property.". For Excel, I found this page on O'Reilly.

From time to time you get strange error messages like "Excel VBA Method 'Range' of object'_global' failed" - this is a pointer to the Global object. I would be glad to learn more about the concepts and mechanics of this object, but I am afraid that there are only very few people around that know more (except of course Mathieu Guindon AKA Mr. Rubberduck...). In daily life, we take it for granted that things like ActiveSheet simply works.