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 from being asked 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