Application-level Properties

On this page we'll go through and dissect a number of Application-level properties which can be set programmatically. Most importantly, these are functions which can be separated out and encapsulated into their own functions to keep them out of event handlers (where they usually reside).

I'll present two sets of procedures: one to return the current value of an Application property, and one to set the value. Most of the 'set'ting procedures have a default value, which you can change if you like. I've also included comments in some of the functions to explain what they do.

Note that this is not a full list of Application properties. Check the Object Browser (F2 in the VB IDE) for the complete listing.

Application.EnableCancelKey

To return the cancel key, we use a Variant type, so you can return either the Long variable that the EnableCancelKey property represents, or a String representation of the XlEnableCancelKey constant.

From Excel Help:
Controls how Microsoft Excel handles CTRL+BREAK (or ESC or COMMAND+PERIOD) user interruptions to the running procedure. Read/write XlEnableCancelKey.

Function GetCancelKey() As Variant

Dim cancelKey As String

GetCancelKey = Application.EnableCancelKey

' uncomment the following lines if you want a string instead of the number
' Select Case GetCancelKey
' Case 0
' cancelKey = "xlDisabled"
' Case 1
' cancelKey = "xlInterrupt"
' Case 2
' cancelKey = "xlErrorHandler"
' End Select
' GetCancelKey = cancelKey
End Function

Function SetCancelKey(cancelKey As XlEnableCancelKey)
Application.EnableCancelKey = cancelKey
End Function

Application.FeatureInstall

From Excel Help:
Returns or sets a value (constant) that specifies how Microsoft Excel handles calls to methods and properties that require features that aren’t yet installed. Can be one of the MsoFeatureInstall constants listed in the following table. Read/write MsoFeatureInstall.

Function GetInstallNewFeature() As Variant

Dim installNewFeature As String

GetInstallNewFeature = Application.FeatureInstall

' uncomment the following lines if you want a string instead of the number
' Select Case GetInstallNewFeature
' Case 0
' installNewFeature = "msoFeatureInstallNone"
' Case 1
' installNewFeature = "msoFeatureInstallOnDemand"
' Case 2
' installNewFeature = "msoFeatureInstallOnDemandWithUI"
' End Select
' GetInstallNewFeature = installNewFeature
End Function

Function SetInstallNewFeature(whatToDo As MsoFeatureInstall)
Application.FeatureInstall = whatToDo
End Function

Application.ShowChartTipNames

From Excel Help:
True if charts show chart tip names. The default value is True. Read/write Boolean.

Function ChartTipNamesShown() As Boolean
ChartTipNamesShown = Application.ShowChartTipNames
End Function

Function SetChartTipNames(Optional chartNames As Boolean = True)
Application.ShowChartTipNames = chartNames
End Function

Application.ShowChartTipValues

From Excel Help:
True if charts show chart tip values. The default value is True. Read/write Boolean.

Function ChartTipsShown() As Boolean
ChartTipsShown = Application.ShowChartTipValues
End Function

Function ShowChartTips(Optional chartTips As Boolean = True)
Application.ShowChartTipValues = chartTips
End Function

Application.ShowToolTips

From Excel Help:
True if ToolTips are turned on. Read/write Boolean.

Function ToolTipsShown() As Boolean
ToolTipsShown = Application.ShowToolTips
End Function

Function ToolTipsShow(Optional showTT As Boolean = True)
Application.ShowToolTips = showTT
End Function

Application.ShowWindowsInTaskbar

From Excel Help:
True if there’s a separate Windows taskbar button for each open workbook. The default value is True. Read/write Boolean.

Function GetShowWindows() As Boolean
GetShowWindows = Application.ShowWindowsInTaskbar
End Function

Function SetShowWindows(Optional showWnd As Boolean = False)
Application.ShowWindowsInTaskbar = showWnd
End Function

Application.ScreenUpdating

Toggle screen updating with these functions.

From Excel Help:
True if screen updating is turned on. Read/write Boolean.

Function IsScreenUpdating() As Boolean
IsScreenUpdating = Application.ScreenUpdating
End Function

Function UpdateScreen(Optional showUpdates As Boolean = True)
Application.ScreenUpdating = showUpdates
End Function

Application.MaxIterations

Set the number of iterations for a circular reference in your spreadsheet model.

From Excel Help:
Returns or sets the maximum number of iterations that
Microsoft Excel can use to resolve a circular reference. Read/write Long.

Function GetNumberOfIterations() As Long
GetNumberOfIterations = Application.MaxIterations
End Function

Function SetNumberOfIterations(num As Long)
Application.MaxIterations = num
End Function

Application.EnableEvents

Check if events are enabled, and if so, disable them.

From Excel Help:
True if events are enabled for the specified object. Read/write Boolean.

Function IsEventsDisabled() As Boolean
IsEventsDisabled = Not Application.EnableEvents
End Function

Function DisableEvents(Optional events As Boolean = True)
' toggle events, assume true (events disabled)
Application.EnableEvents = Not events
End Function

Edit in Cell

See Leveraging the Edit Directly in Cell Option for a good explanation of how this option works.

Function GetEditInCell() As Boolean
GetEditInCell = Application.EditDirectlyInCell
End Function

Function ToggleEditInCell(Optional ed As Boolean = False)
' toggle in-cell editing, assume false
Application.EditDirectlyInCell = ed
End Function

Showing the Status Bar

Function IsStatusBarDisplayed() As Boolean
IsStatusBarDisplayed = Application.DisplayStatusBar
End Function

Function ToggleShowStatusBar(Optional showStatusBar As Boolean = False)
' toggle status bar, assumes false
Application.DisplayStatusBar = showStatusBar
End Function

The MRU list

From Excel Help:
True if the list of recently used files is displayed on the File menu. Read/write Boolean.

Function IsRecentFilesListShown() As Boolean
IsRecentFilesListShown = Application.DisplayRecentFiles
End Function

Function ToggleRecentFiles(Optional showFiles As Boolean = True)
' toggle Display Recent Files property, assume true
Application.DisplayRecentFiles = showFiles
End Function

Toggle the Formula Bar

From Excel Help:
True if the formula bar is displayed. Read/write Boolean.

Function IsFormulaBarDisplayed() As Boolean
IsFormulaBarDisplayed = Application.DisplayFormulaBar
End Function

Function ToggleFormulaBar(Optional showformBar As Boolean = False)
' toggle formula bar, assumes false
Application.DisplayFormulaBar = showformBar
End Function

Showing or hiding comments

From Excel Help:
Returns or sets the way cells display comments and indicators. Can be one of the following xlCommentDisplayMode constants: xlNoIndicator, xlCommentIndicatorOnly, or xlCommentAndIndicator. Read/write Long.

Function GetCommentIndicator() As Variant

Dim commentIndicator As String

GetCommentIndicator = Application.DisplayCommentIndicator

' uncomment the following lines if you want a string instead of the number
' Select Case GetCommentIndicator
' Case 1
' commentIndicator = "xlCommentAndIndicator"
' Case -1
' commentIndicator = "xlCommentIndicatorOnly"
' Case 0
' commentIndicator = "xlNoIndicator"
' End Select
' GetCommentIndicator = commentIndicator
End Function

Function SetCommentIndicator(commentType As XlCommentDisplayMode)
' toggles comment indicators on worksheet
Application.DisplayCommentIndicator = commentType
End Function

Don't allow the clipboard window

From Excel Help:
Returns True if the Microsoft Office Clipboard can be displayed. Read/write Boolean.

Function GetDisplayClipboardWindow() As Boolean
GetDisplayClipboardWindow = Application.DisplayClipboardWindow
End Function

Function SetDisplayClipboardWindow(Optional showWindow As Boolean = False)
' toggle display of the clipboard window, assumes false
Application.DisplayClipboardWindow = showWindow
End Function

Displaying Alerts

One of the most important (along with ScreenUpdating and EnableEvents).

From Excel Help:
True if Microsoft Excel displays certain alerts and messages while a macro is running. Read/write Boolean.

Function GetDisplayAlerts() As Boolean
GetDisplayAlerts = Application.displayAlerts
End Function

Function SetDisplayAlerts(Optional showAlerts As Boolean = False)
' sets the Display Alerts property, assumes false
Application.displayAlerts = showAlerts
End Function

Interrupting Calculations

From Excel Help:
Sets or returns an XlCalculationInterruptKey constant that specifies the key that can interrupt Microsoft Excel when performing calculations. Read/write.

Function GetCalcInterruptKey() As Variant

Dim calcInterruptKey As String

GetCalcInterruptKey = Application.CalculationInterruptKey

' uncomment the following lines if you want a string instead of the number
' Select Case GetCalcInterruptKey
' Case 0
' calcInterruptKey = "xlNoKey"
' Case 1
' calcInterruptKey = "xlEscKey"
' Case 2
' calcInterruptKey = "xlAnyKey"
' End Select
' GetCalcInterruptKey = calcInterruptKey
End Function

Function SetCalcInterruptKey(key As Excel.XlCalculationInterruptKey)
' sets the calculation interruption key
Application.CalculationInterruptKey = key
End Function

Toggle Calculation mode

From Excel Help:
Returns or sets the calculation mode. Read/write XlCalculation.

Function GetCalcMode() As Variant

Dim calcMode As String

GetCalcMode = Application.Calculation

' uncomment the following lines if you want a string instead of the number
' Select Case GetCalcMode
' Case -4105
' calcMode = "Automatic"
' Case -4135
' calcMode = "Manual"
' Case 2
' calcMode = "Semiautomatic"
' End Select
' GetCalcMode = calcMode
End Function

Function SetCalcMode(Optional calc As XlCalculation = xlCalculationAutomatic)
' change calculation mode
' assumes automatic calculation
' see http://tinyurl.com/yjefjf5 for reasons
Application.Calculation = calc
End Function

Toggle Calculation Before Save

From Excel Help:
True if workbooks are calculated before they're saved to disk (if the Calculation property is set to xlManual). This property is preserved even if you change the Calculation property. Read/write Boolean.

Function GetCalcBeforeSave() As Boolean
GetCalcBeforeSave = Application.CalculateBeforeSave
End Function

Function SetCalcBeforeSave(Optional calc As Boolean = True)
' toggle CalculateBeforeSave
Application.CalculateBeforeSave = calc
End Function

Updating Links

One of the most annoying properties. If your workbook has links, you can stop them from asking if you want the links updated.

From Excel Help:
True if Microsoft Excel asks the user to update links when opening files with links. False if links are automatically updated with no dialog box. Read/write Boolean.

Function GetUpdateLinks() As Boolean
GetUpdateLinks = Application.AskToUpdateLinks
End Function

Function SetUpdateLinks(Optional toUpdate As Boolean = True)
' toggle AskToUpdateLinks property, defaults to True
Application.AskToUpdateLinks = toUpdate
End Function

Code For Excel And Outlook