Some (amazing) things you can do with the Application object in VBA
The Excel Application object represents the current Excel session that you have open. It contains all objects linked to this Excel session. All objects that you can access in VBA have this object as root, even if you can usually omit it without harming the execution. For example Application.Workbooks is identical to the object Workbooks. And also Application.Worksheets is identical to the Worksheets object...
You usually don’t use the Excel Application object a lot in simple programs, although it can be useful. It mostly provides some interesting hacks and ways to impress your boss with fancy features. But this is a very complex object, it is not recommended to modify its properties lightly as it can affect the whole behaviour of Excel. I won’t be exhaustive here, so I recommend that you explore Microsoft Excel’s VBA support website and the Application object in the VBA editor if you want to know more.
The Application object is the parent of many other objects
Application: “Luke, I am your father!”
The Application object represent the current session of Excel. The Application object is actually the implicit parent of the Workbooks objects that are open in this Excel session (i.e. the workbooks that are open in the current session of Excel).
It is interesting to understand how the Application object works, as a VBA macro typically runs within the Application object. What I mean is, even if you can have several Excel Application running at the same time (when you open several sessions of Excel), a VBA macro launched from one of these sessions won’t directly be able to affect the other sessions of Excel. This is because different sessions of Excel are designed to be independent. That is why you can run two macros at the same time on two different sessions of Excel, but you can’t run two macros at the same time on two workbooks opened in the same Excel session.
Interesting features of the Application object
Here are some Excel application properties and methods that I find rather useful (or simply fun):
Some methods of the application object
- Calculate the whole workbook: Application.Calculate: this calculates formulas in all workbooks open in Excel (don’t look surprised). Note that you can also calculate independently one worksheet. For example: Sheets(1).Calculate will do the calculations only in the first worksheet.
Also note that you can set the calculation mode in this Excel session by setting the properties as follows: Application.Calculation = xlCalculationAutomatic (automatic calculation) / xlCalculationManual (manual calculation: press F9 to compute the workbook, SHIFT + F9 to compute the current worksheet)
- Use the Excel functions in VBA: Application.WorksheetFunction.[functionName] ( [inputs] ): this one is an absolute classic. Most of you will have common functions of Excel in mind and it can be very interesting to use them in VBA code too. Min, Max, Average, Sum, Var, Match, Index, MMult, Transpose… They are fairly optimized so I recommend you use them whenever you can instead of coding a similar thing in VBA. Another advantage is that they can generally use arrays and ranges alike as inputs, which can same you some precious coding time.
- Make Excel talk: Application.Speech.Speak ("Hello there. Have Excel read aloud that text"): this will make Excel read aloud some text. Let’s admit it; it is a cool feature but it not extremely useful (except of course if you need that feature, that is primarily designed for people with visual impairment), and it will slow down the macro execution as it will wait for the voice to stop before it continues.
Note that you can also change the properties of Excel to read aloud any cell’s content when you are exiting the cell: Application.Speech.SpeakCellOnEnter = True will activate this functionality. Set “False” instead to stop it. This is a necessary option for visually impaired people, but it can also be extremely funny to activate this feature on your mean colleague’s Excel and drive him crazy with this constant robotic voice. And it is quite hard to stop without knowing the VBA code… ;)
Set Properties of the application object to modify Excel behaviour
- Show the progress in the execution in a status bar: Application.StatusBar = "Some text": this adds some text in the status bar, a space that typically shows the actions currently ongoing in the workbook. Once you have put some text there, you can change it easily by simply setting some other text instead: Application.StatusBar = "Some other text". Note that to delete that status bar once your macro has finished, you must add Application.StatusBar = FALSE
- Application.ScreenUpdating = False – Stop screen updating: This prevents the screen from refreshing during the macro: you won’t see the effect of the macro in real time. This will actually save a considerable amount of time and memory when running the macro, but on the other hand you don’t know exactly what the macro is doing. This is actually not very good practice, it is more a trick to speed-up huge (badly conceived) macros, for a bunch of reason:
- You are modifying the normal functioning of Excel in a somewhat unnatural way.
- If for some reason you don’t switch back on the screen updating (with Application.ScreenUpdating = True), you won’t be able to use Excel properly until you switch it back on: the screen will not update until then.
If screen updating is deactivated, it means you can’t move inside and interact with the Excel file, which is pretty bad. It can happen if the macro stops before intended and the ScreenUpdating stays switched off. In this case, type ALT + F11 (to open VBA without screen interactions), and run a sub containing the instruction Application.ScreenUpdating = True
- Application.DisplayAlerts = False – Disable warning popups when you save a workbook or delete a worksheet. This is very useful for example when you want to automate the saving or opening of a large number of files in Excel.
Do not forget to switch this back to True when the macro is finishing, so that you don’t alter the normal functioning of Excel.
Get information on the User via Application object properties
These are example of information that can be extracted from your PC very easily with VBA. It can have some ethical use, but essentially you should be aware of the existence of this data, and be aware that people can access it with potentially bad intentions. If they look harmless, they still represent a potential breach into your privacy.
- Application.RecentFiles - Access recently opened files: this provides the list of the 25 most recently used file names (and their paths). It can be useful in some situations. Still, it is better to avoid automation on the basis of the recent files list as its content can be rather unpredictable. Instead it is useful if you want to stalk the macro user and know what files he is using! Another (more ethical) purpose of this feature is to “save” a list of recently used files. Indeed, Excel only provides a list of 25 or so recently used files in the “Recent files” pane, which for certain people isn’t enough. Then saving recently used files every day can provide an archive of all the files you used, and make it easy to find that file you did a week ago but you can’t remember where you dropped it.
- Application.UserName – Get the user name
- Application.OrganizationName – Get the company name (if it is set in windows)
- Get the active printers: yet another not-so-useful feature. Actually I included it for the same reason as the recently opened file object: VBA can give very valuable information about your computer and network that could also be used with bad intentions. I guess it is worth repeating this even if you are already convinced: never open an Excel file when you don’t know where it comes from.