Check If Office App Is Running
You may have a need in your application to check if a particular Office app is running. Here is some sample code that does just that. Paste the following code at the top of a standard module in any Office program that supports VBA.
' enums always start from zero, unless otherwise specified
' for demonstration purposes (on the worksheet), start from 1
Outlook = 1
PowerPoint = 2
Excel = 3
Word = 4
Publisher = 5
Access = 6
End Enum
Function IsAppRunning(appName As OfficeAppName) As Boolean
' check if a particular Office app is running
On Error GoTo NotRunning
Dim officeApp As Object
Dim appString As String
' assume true
IsAppRunning = True
' determine which app is being checked
Select Case appName
Case 1 ' Outlook
appString = "Outlook"
Case 2 ' PowerPoint
appString = "PowerPoint"
Case 3 ' Excel
appString = "Excel"
Case 4 ' Word
appString = "Word"
Case 5 ' Publisher
appString = "Publisher"
Case 6 ' Access
appString = "Access"
End Select
' try to hook into existing object
Set officeApp = GetObject(, appString & ".Application")
ExitProc:
Exit Function
NotRunning:
IsAppRunning = False
Resume ExitProc
End Function
Let's examine what the code above is doing. First we declare a set of enumerated constants as type OfficeAppName. The enums always start from zero, but we'll start from one to demonstrate a worksheet technique later on. For now it's only important to know that we're creating a new data type with values from one through six, represented by the friendly terms Outlook, PowerPoint, Excel, Word, Publisher, Access.
The reason we use the Enum section is so we can create our own Intellisense, and prevent invalid entries. If you type IsAppRunning( and then ignore the dropdown and enter something different, you'll get an "Argument not optional" error when you run the code.
Note that the IsAppRunning procedure is similar to the code I posted in Easy Application.Object References.
IsAppRunning takes one parameter: one of the custom data types we created, and it returns a boolean. If the specified application is running, it returns true, otherwise it returns false.
OfficeAppName must be translated back into the application name, so we use a Select Case statement to produce a string literal that we'll need in the GetObject call. Before that, though, we set IsAppRunning to true by default. I find it much easier to manage errors this way — by assuming the return value will be true, then setting it to false in the error handler. This way, you are only setting it false in one place (in the error handler, which is only reached if an error occurs!), instead of trying to use multiple error-checking if statements sprinkled all over your procedure, meshed with the logic of your program.
Sample usage:
MsgBox IsAppRunning(PowerPoint)
End Sub
But we're taking this example even further, with a worksheet example. Since we're working with a function, we can use it directly from the Excel worksheet, but with a twist.
First I took a blank workbook, displayed the Forms toolbar (View » Toolbars » Forms) and drew a listbox on the first worksheet. I right-clicked the control and selected "Format Control", then went to the Control tab and selected an Input range and Cell link. For Input range I chose A2:A7 and Cell link is A11.
The Input range will be what entries will be displayed in the listbox, while Cell link is the cell that will show which item in the listbox is selected.
The listbox is blank, so let's fill up the range with data. We'll want the end user to select from one of the Office apps specified in the Enum section in VBA, so we'll enter Office app names, in the same order and quantity as the Enum, into cells A2:A7.
| A |
—————-
2 | Outlook
3 | PowerPoint
4 | Excel
5 | Word
6 | Publisher
7 | Access
Once complete, you'll see the listbox populated with Office app names, in the same order as the worksheet (which we've also entered in the same order as in the VBA Enum).
The Cell link in A11 will show the position of the currently selected item in the listbox. Notice that as you click each one, the numbers cycle from one through six. This corresponds to the constant number we assigned in the Enum. So all we need to do is pass the value of the linked cell to the IsAppRunning function, and since we set up the Input range in the same order as the Enum, it will return true if the selected Office app is running, or false otherwise.
Now hopefully you understand why I started the enumeration from one instead of zero. The cell link is one-based, so if we started from zero, the formula in cell A15 (see below) would have to be =IsAppRunning(A11-1), which is awkward.
I went a small step further and also used conditional formatting to change the fill color of the cell if the value is True. Here's a screenshot of the final result:
If you want to download a sample workbook with the VBA code above, and the listbox and ranges, click on the button below.
