Here we have two more functions, which have done me great service over the last years.

Instead of duplicating the code for switching on and off certain Excel application specific events, alerts and so on you can call these two functions before and after each function call reachable through an user interface.

Version & Remarks

2015-02-01

I've written a script to postprocess some data extracted from a database for one of my clients. Processing took quite some time and a collegue came up to me showing me on my keyboard how his wife typed in something wrong. Great! the script was only half way through and stopped, now showing my collegues input in a cell in one of the referenced Excel workbooks. Luckily I had a log file and could continue from there.

Lessons learned - prevent a user from interacting with Excel while you are processing something in VBA. Below a reference from the according Microsoft documentation for the Interactive property of the Excel Application object. Before I made this experience I always thought that this would happen automatically.

"If you set the this property to False, Microsoft Excel will block all input from the keyboard and mouse (except input to dialog boxes that are displayed by your code). Read/write Boolean."

However you will still be able to interrupt code exceution pressing the escaoe button.

Source Code

Make sure to place the function call of switchOnApp always after an error handling in the top calling function. Do not place calls to below functions in any function, which is called by other functions / subs. Else you would end up disabling and enabling application properties, when you actually don't want to enable or disable them.

'@Author - Alexander Bolte
'@ChangeDate - 2014-05-30
'@Description - Switches on all events, automated claculation and alerts in Excel. Also switches security to standard in Excel Security Center.
Public Sub switchOnApp()
    ' reset security settings
    Application.AutomationSecurity = msoAutomationSecurityByUI
    Application.EnableEvents = True
    Application.DisplayAlerts = True
    If Not (ActiveSheet Is Nothing) Then
        Application.Calculation = xlAutomatic
    End If
    Application.ScreenUpdating = True
    Application.EnableAnimations = True
    Application.Interactive = True
End Sub

'@Author - Alexander Bolte
'@ChangeDate - 2014-05-30
'@Description - Switches off all events, automated claculation and alerts in Excel. Also switches security to low in Excel Security Center in case macro enabled workbooks have to be processed.
Public Sub switchOffApp()
    ' Disable all alerts.
    Application.DisplayAlerts = False
    ' Disable all event handling.
    Application.EnableEvents = False
    ' Enable macros on workbooks opened through automation.
    Application.AutomationSecurity = msoAutomationSecurityLow
    ' Disable automated calculation.
    If Not (ActiveSheet Is Nothing) Then
        Application.Calculation = xlManual
    End If
    Application.EnableAnimations = False
    Application.ScreenUpdating = False
    Application.Interactive = False
End Sub

Referenced API

Important referenced APIs are listed below.

• https://msdn.microsoft.com/en-us/library/office/ff841248%28v=office.15%29.aspx (Interactive property)
• https://msdn.microsoft.com/en-us/library/office/ff837822%28v=office.15%29.aspx (AutomationSecurity property)

Last Updated: 01 February 2015

Hits: 4218

It may seem trivial, but even the smallest things have to be handled carefully when programming.

Therefore I have some standard functions, which handle the creation of new worksheets in my VBA projects. So far I never had trouble with them plus they emphazise NOT to use existing worksheets for your coding. Instead it is better to always create new worksheets. This way you have full control over your programm.

If you start coupling Excel files with source code, you start a war that you cannot win. If your source code relies on a certain status in Excel files, it will fail by time and you spend a lot of time handling errors, which can easily be avoided.

However, here the source code for creating a new worksheet safely.

' @Author - Alexander Bolte
' @ChangeDate - 2014-05-31
' @Description - checks, if a worksheet exists under given name in given workbook.
' @Param myBook - an initialized Excel Workbook, which should be searched for given sheet name.
' @Param sheetName - a String holding the sheets name, which should be searched in given workbook.
' @Returns true, if the sheet exists, else false.
Public Function worksheetExists(ByRef myBook As Workbook, ByVal sheetName As String) As Boolean
    Dim ret As Boolean
    Dim tmpS As Worksheet
    
On Error GoTo errHandler:
    Set tmpS = myBook.Worksheets(sheetName)
    ret = True
    Set tmpS = Nothing
    
errHandler:
    If Err.Number <> 0 Then
        ret = False
        Err.Clear
    End If
    
    worksheetExists = ret
End Function

' @Author - Alexander Bolte
' @ChangeDate - 2014-05-31
' @Description - creates a new worksheet with given name in given workbook. If a worksheet already exists under given name, it is deleted and replaced with an empty worksheet.
' @Param myBook - an initialized Excel Workbook, which should be searched for given sheet name.
' @Param sheetName - a String holding the sheets name, which should be created in given workbook.
' @Returns a Worksheet object referencing a newly created worksheet in given workbook.
Public Function createWorksheet(ByRef myBook As Workbook, ByVal sheetName As String) As Worksheet
    Dim tmpS As Worksheet
    
    ' ### delete the worksheet, if already existing ###
    If worksheetExists(myBook, sheetName) Then
        myBook.Worksheets(sheetName).Delete
    End If
    ' ### add a new worksheet ###
    Set tmpS = myBook.Worksheets.Add
    tmpS.Name = sheetName
    tmpS.Move myBook.Worksheets(1)
    
    Set createWorksheet = tmpS
End Function

If you do not want Excel to display warning Messages, you can switch them off using the DsiplayAlerts property of the Application object.

' Disable all alerts.
Application.DisplayAlerts = False

Last Updated: 01 June 2014

Hits: 4104

If you often have to convert values from Excel files into SQL filter lists for investigation purposes, this VBA function will save you a lot of time.

Thanks for this function go to Sascha Hombach a highly skilled IT Consultant, who initially developed it for HP ALM filter Strings. I only adjusted it for the use case described above.

Source Code

Last Updated: 06 January 2018

Hits: 4150