Whenever you have to interact with users there is information you would like to store outsie the lifetime of an application in order for it to be available at next startup.

The below class can assist in writing and reading property files into hidden temp directories like AppData in Windows systems. This way you can for example store user settings without having to deal with protecting and hiding Excel sheets. I think it is bad style to store information that only rarely is touched by a user in a workbook, which is used to process data. Unless Excel is not used as an interface to configure a lot of different parameters in an application, I use property files.

However the main advantage is that you can use a Scripting.Dictionary internally in the below class in order to ease getting and setting properties by name. This includes the following advantages compared to storing information in Excel files.

1. No duplicates possible.
2. No handling of indices in Excel sheets necessary.
3. Property files are more reliable because Excel sheets are more likely to be modified by users.

Source Code

Last Updated: 19 August 2015

Hits: 4795

Whenever you have a list of unique key values identifying one file in a directory, but the file name contains other text as well it is possible to use wildcards in order to open the file you want.

Below code snippet will do the trick for you.

Source Code

The function Dir() returns an empty String (""), in case a file could not be identified. Therefore you will need extra handling for this case.

In case more than one file was identified by Dir(), only the first file (whichever the first may be) is returned.

Dim sFound As String
 
sFound = Dir(ActiveWorkbook.Path & "\302113*.xlsm")    'the first one found
If sFound <> "" Then
    Workbooks.Open filename:= ActiveWorkbook.Path & "\" & sFound
End If

Last Updated: 25 July 2015

Hits: 4539

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: 4603