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

Question

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.

If you will have `Option Explicit` on tot of the module where the code runs, it will warn you that `DisplayAlerts` variable is not defined... Otherwise, VBA neglects it and acts as it does not exist. — FaneDuru, Feb 03 '22 at 13:45
@FaneDuru: you seem to be right: VBA does not recognise `DisplayAlerts` as being a property of the `Application` object. However, I'm sure I've worked with a programming language where it was allowed to omit the `Application` object. Delphi, maybe? — Dominique, Feb 03 '22 at 14:18
@FaneDuru: you can add your comment as an answer, I'll accept it. — Dominique, Feb 03 '22 at 14:23
No, thanks... It should be more complex for an answer. In VBA, `Application` also exposes some properties/methods directly: `WorksheetFunction`, `ActiveSheet`, `ActiveCell`, `ActivePrinter` and much more, but not all of them... — FaneDuru, Feb 03 '22 at 14:29
@FaneDuru: too late, you've been beaten by [BigBen](https://stackoverflow.com/users/9245853/bigben) :-) — Dominique, Feb 03 '22 at 14:33
@ BigBen `Debug.Print ActivePrinter` correctly returns the active printer name, port included. At least, on 365... — FaneDuru, Feb 03 '22 at 14:35
In Delphi you only don't need to use the object instance variable when you use a ```with``` block! Besides that is your question absolutely not related to Delphi. — Delphi Coder, Feb 03 '22 at 14:35
There are some cased of methods which behaves differently when using them with `Application` or with `Application.WorksheetFunction`. For instance, declaring `Dim mtch` followed by `mtch = Application.WorksheetFunction.match(5, arr, 0)` will return a fatal error if 5 is not found in `arr`. But `mtch = Application.match(5, arr, 0)` does not. In this way you may check `If IsError(mtch) Then` and have the answer about its existence in the array or not... — FaneDuru, Feb 03 '22 at 14:39
@FaneDuru: keep out: you're talking about dropping the `WorksheetFunction` property, I was only talking about dropping the `Application` object reference. Those are complete different things. (But I do understand how confusing this appears to be :-) ) — Dominique, Feb 03 '22 at 14:48
I know what you asked for, but I only wanted to emphasize that there are `Application` methods, even not shown by intellisense, but exposed and which acts differently than its function (`WorksheetFunction`). Try writing `Application.mat` and see if `Match` is suggested... Of course, this was not required in your question. — FaneDuru, Feb 03 '22 at 14:54
I removed the Delphi tag as that was only a speculative but non valid connection. — Tom Brunberg, Feb 03 '22 at 14:54

BigBen · Accepted Answer · 2022-02-03T20:17:54.593

DisplayAlerts is an undeclared variable.

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

ActiveCell, ActiveSheet, ActiveWorkbook, ActiveWindow, Addins, 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.

FunThomas · Answer 2 · 2022-02-03T16:42:07.720

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.

Just wanted to check if someone is reading this ;-) - corrected. I know why I always preach to use `Option Explicit`... — FunThomas, Feb 03 '22 at 15:00
Though not covering exactly the question in OP, the [post](https://stackoverflow.com/questions/51897030/application-cells-vs-application-activesheet-cells/51898006?r=SearchResults&s=1|26.7330#51898006) describing how *member calls (Property Get) against a hidden, global-scope object cleverly named Global* work and might give further insight — T.M., Feb 03 '22 at 18:11

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

2 Answers2