Short Description
Create an Excel Add-in that allows you to add Application Settings functionality on any Workbook. Store and retrieve setting names and values on a centralised location and accessible anywhere from your VBA modules and classes.
Ingredients
- Microsoft Excel
The Need
Sometimes when you are creating an Excel tool or application, you need a centralised place where you can save and retrieve settings form.
As you express the business logic of your application in the form of code on the Visual Basic for Applications (VBA) environment, you’ll realise that it’s handy to have a place where you could store and retrieve key-value pairs from any part of your application.
These settings can be used to store a number of elements of information about the application such as user preferences, file versions, application configuration settings, etc.
I use these Application Settings technique in many of the Workbooks I create. The image below shows an example of one of these files and the type of information I store in them.
The Recipe
The Application Settings is not a User Interface (UI) element and as such is not particularly visual or interesting to look at. However, as simple as it is, you will find that it has many uses when you’re developing non-trivial Excel applications.
As you could see on the image above, the feature is nothing more than a table with two columns that store key-value pairs. One column stores the setting name and the other its value.
The way that I implemented the idea of an Application Settings functionality is through the creation of an Add-in. If you don’t know what an Excel Add-in is you can have a look here.
By encapsulating this functionality in an Add-in file the feature can be easily implemented in any Workbook.
1.- Create the Add-in
Open Excel. Create a new document and Save As an Excel Add-in file (.xlam) and give it an appropriate name. A good name would be ApplicationSettings.xlam.
2.- Create a new Module
Open the VBA editor (Alt + F11) and create a new Module. Give the module the name modAppSettings.
3.- Create a file header
I have the practice of starting all my VBA files with a somewhat standardised header. In this case create a header that looks like the one below.
'************************************************************** ' Description: Module for managing application settings. ' ' Authors: code-recipes.com ' ' Options: Option Explicit '************************************************************** '************************************************************** ' MODULE CONFIGURATION CONSTANTS '************************************************************** Private Const p_strMODULE_NAME As String = "modAppSettings" Private Const p_strSETTINGS_SHEET_NAME As String = "shtSettings" Private Const p_strSETTINGS_TABLE_NAME As String = "tblSettings" Private Const p_strSETTINGS_NAME_COLUMN As String = "Name" Private Const p_strSETTINGS_VALUE_COLUMN As String = "Value" '************************************************************** ' MODULE LEVEL VARIABLES '************************************************************** Private wkbOperatingWorkbook As Excel.Workbook
These files headers help you to keep code better organised and a single place in the file to go to for things like Module level variables and constants.
In this header we’re declaring a few important elements we’ll use throughout the module. Other than the module’s name, there are four constants defined here. Their names should be fairly self-descriptive, but in any case, their meanings are:
- p_strSETTINGS_SHEET_NAME is the name of the Worksheet where the settings table will be stored. As it will become clear later, this Worksheet’s creation is managed automatically by this Module.
- p_strSETTINGS_TABLE_NAME is the name that will be given to the Table (ListObject in VBA) that will hold all the settings.
- p_strSETTINGS_NAME_COLUMN is the name or header of the column that will hold the name of the setting, in other words, the key of the key-value pair.
- p_strSETTINGS_VALUE_COLUMN is the name or header of the column that will hold the value of the setting.
- wkbOperatingWorkbook will be used to hold a reference of the Workbook upon which the Add-in will be operating on.
4.- Create the functions
This Module will expose to its user three main functions.
- GetSetting
- AssignSetting
- RemoveSetting
While these are the functions publicly exposed by the Module, we will also define a number of Private functions to encapsulate some of the aspects of this Module.
The design philosophy of this feature is that the Add-in will have the responsibility to manage the Worksheet where the settings are stored. The Workbook that uses the Add-in will call on this functions and shouldn’t have to worry about the implementation details of how the settings are stored and retrieved.
As such, the Add-in will have to take care of checking if the settings Worksheet exists and if it doesn’t create a new one, creating the table, adding and removing rows from this table, etc.
We will start by creating all the Private auxiliary functions and then we’ll create the final three Public functions which make use of them.
4.1.- Check if the Settings Worksheet exists
Since we can add this functionality to any Workbook, we have to be able to add a new Worksheet to that Workbook if required. The first function we’ll create is one that checks if that Worksheet exists. To keep things simple, we’ll write all our code in the modAppSettings Module of the Add-in. Create the following function in it.
' --------------------------------------------------------------------------- ' Purpose: ' Determines if the settings worksheet exists in the Active Workbook ' ' Arguments: ' NIL ' ' Returns: ' Boolean = TRUE if the settings worksheet exists ' ' Date Developer Comment ' --------------------------------------------------------------------------- ' 24/Jan/2016 code-recipes.com Initial version Private Function CheckIfSettingsSheetExists() As Boolean On Error GoTo ErrHandler CheckIfSettingsSheetExists = False Dim i As Integer If wkbOperatingWorkbook Is Nothing Then Exit Function With wkbOperatingWorkbook For i = 1 To Sheets.Count If Sheets(i).Name = p_strSETTINGS_SHEET_NAME Then CheckIfSettingsSheetExists = True Exit For End If Next i End With Exit_ErrHandler: Exit Function ErrHandler: CheckIfSettingsSheetExists = False Resume Exit_ErrHandler End Function
4.2.- Create a function that returns the settings Worksheet
We can now use the function created above to make a function that checks whether the settings Worksheet exists in the Workbook we’re working with and create the proper worksheet if it doesn’t. Bare in mind that when the settings Worksheet is created it is hidden to remove distractions to the end user. Add the code below to your Module.
' ---------------------------------------------------------------------------
' Purpose:
' Retrieves the settings worksheet. If it doesn't exist yet in the Active
' Workbook, it creates a new Worksheet and then returns that reference
'
' Arguments:
' NIL
'
' Returns:
' Excel.Worksheet = The settings worksheet
'
' Date Developer Comment
' ---------------------------------------------------------------------------
' 24/Jan/2016 code-recipes.com Initial version
Private Function GetOrCreateSettingsSheet() As Excel.Worksheet
If CheckIfSettingsSheetExists = False Then
Dim shtSettings As Excel.Worksheet
Dim tblSettings As Excel.ListObject
Application.ScreenUpdating = False
Set shtSettings = wkbOperatingWorkbook.Sheets.Add()
shtSettings.Name = p_strSETTINGS_SHEET_NAME
shtSettings.Range("A1").Value = p_strSETTINGS_NAME_COLUMN
shtSettings.Range("B1").Value = p_strSETTINGS_VALUE_COLUMN
Set tblSettings = shtSettings.ListObjects.Add(xlSrcRange, shtSettings.Range("A1:B1"), , xlYes)
tblSettings.Name = p_strSETTINGS_TABLE_NAME
shtSettings.Visible = xlSheetHidden
Application.ScreenUpdating = True
End If
Set GetOrCreateSettingsSheet = wkbOperatingWorkbook.Sheets(p_strSETTINGS_SHEET_NAME)
End Function
4.3.- Create a function that returns the settings table
We can now use the above function to return a reference to the table holding the settings. Create the function below.
' --------------------------------------------------------------------------- ' Purpose: ' Retrieves the settings table ' ' Arguments: ' NIL ' ' Returns: ' Excel.ListObject = The settings table object ' ' Date Developer Comment ' --------------------------------------------------------------------------- ' 24/Jan/2016 code-recipes.com Initial version Private Function GetSettingsTable() As Excel.ListObject Dim shtSettings As Excel.Worksheet Set shtSettings = GetOrCreateSettingsSheet Set GetSettingsTable = shtSettings.ListObjects(p_strSETTINGS_TABLE_NAME) End Function
4.4.- Create a function to delete the settings Worksheet
We finally create a function to delete the settings Worksheet is necessary. Add the following to your code.
' --------------------------------------------------------------------------- ' Purpose: ' Deletes the settings sheet if it exists ' ' Arguments: ' NIL ' ' Returns: ' NIL ' ' Date Developer Comment ' --------------------------------------------------------------------------- ' 24/Jan/2016 code-recipes.com Initial version Private Sub DeleteSettingsSheet() If CheckIfSettingsSheetExists = True Then Application.DisplayAlerts = False wkbOperatingWorkbook.Sheets(p_strSETTINGS_SHEET_NAME).Delete Application.DisplayAlerts = True End If End Sub
4.5.- Create the three Public functions
We are now ready to create the three main Public functions of this Add-in. Add the following code to your Module.
' --------------------------------------------------------------------------- ' Purpose: ' Takes the name of a setting and returns it's value ' ' Arguments: ' strSettingName = The name of the setting to be retrieved ' ' Returns: ' Variant = The value of the setting retrieved, returns #N/A ' if the Setting could not be found ' ' Date Developer Comment ' --------------------------------------------------------------------------- ' 24/Jan/2016 code-recipes.com Initial version Public Function GetSetting(wbkWorkbook As Excel.Workbook, strSettingName As String) As Variant On Error GoTo ErrHandler Dim tblSettings As Excel.ListObject Set wkbOperatingWorkbook = wbkWorkbook Set tblSettings = GetSettingsTable GetSetting = Excel.WorksheetFunction.VLookup(Trim(strSettingName), _ tblSettings.DataBodyRange, tblSettings.ListColumns(p_strSETTINGS_VALUE_COLUMN).Index, 0) Exit_ErrHandler: Set tblSettings = Nothing Exit Function ErrHandler: GetSetting = CVErr(xlErrNA) Resume Exit_ErrHandler End Function
' --------------------------------------------------------------------------- ' Purpose: ' Takes the name of a setting and its value and returns true if setting is ' set correctly ' ' Arguments: ' strSettingName = The name of the setting to be assigned ' varValue = The value that will be assigned to this setting ' bolCreateIfNotExists = An optional flag that determines if the setting ' should be created if it doesn't exist already ' Defaults to FALSE ' ' Returns: ' Boolean = Returns TRUE if the setting was assigned successfuly ' ' Date Developer Comment ' --------------------------------------------------------------------------- ' 24/Jan/2016 code-recipes.com Initial version Public Function AssignSetting(wbkWorkbook As Excel.Workbook, strSettingName As String, varValue As Variant, _ Optional bolCreateIfNotExists As Boolean = False) As Boolean On Error GoTo ErrHandler Dim intRow As Integer Dim tblSettings As Excel.ListObject Dim SettingDoesNotExist As Boolean Set wkbOperatingWorkbook = wbkWorkbook Set tblSettings = GetSettingsTable SettingDoesNotExist = Application.WorksheetFunction.IsNA(GetSetting(wkbOperatingWorkbook, Trim(strSettingName))) If SettingDoesNotExist = True Then If bolCreateIfNotExists = True Then tblSettings.ListRows.Add intRow = tblSettings.DataBodyRange.Rows.Count tblSettings.ListColumns(p_strSETTINGS_NAME_COLUMN).DataBodyRange.Cells(intRow, 1).Value2 = strSettingName Else AssignSetting = False Exit Function End If End If With tblSettings 'Find row intRow = Application.WorksheetFunction.Match(Trim(strSettingName), .ListColumns(p_strSETTINGS_NAME_COLUMN).DataBodyRange, 0) 'Assign value to value column .ListColumns(p_strSETTINGS_VALUE_COLUMN).DataBodyRange.Cells(intRow, 1).Value2 = varValue End With AssignSetting = True Exit_ErrHandler: Set tblSettings = Nothing Exit Function ErrHandler: AssignSetting = False Resume Exit_ErrHandler End Function
' --------------------------------------------------------------------------- ' Purpose: ' Removes the setting from the table if it exists. ' ' Arguments: ' strSettingName = The name of the setting to be removed ' ' Returns: ' Boolean = Returns TRUE if the value was succesfuly removed ' ' Date Developer Comment ' --------------------------------------------------------------------------- ' 24/Jan/2016 code-recipes.com Initial version Public Function RemoveSetting(wbkWorkbook As Excel.Workbook, strSettingName As String) As Boolean On Error GoTo ErrHandler If Not Application.WorksheetFunction.IsNA(GetSetting(wkbOperatingWorkbook, Trim(strSettingName))) Then Dim intRow As Integer Dim tblSettings As Excel.ListObject Set wkbOperatingWorkbook = wbkWorkbook Set tblSettings = GetSettingsTable With tblSettings 'Find row intRow = Application.WorksheetFunction.Match(Trim(strSettingName), .ListColumns(p_strSETTINGS_NAME_COLUMN).DataBodyRange, 0) 'Delete row .ListRows(intRow).Delete End With RemoveSetting = True End If Exit_ErrHandler: Set tblSettings = Nothing Exit Function ErrHandler: RemoveSetting = False Resume Exit_ErrHandler End Function
5.- How to use this
Now for the useful part. Create a test file and create a new module to test your new Add-in. Make sure that the Add-in has been added to the references of the new Workbook.
When any of the main functions is used, a reference to the Workbook where settings are to be retrieved, saved or deleted from needs to be passed. Think of it like a sort of Dependency Injection. The calling Workbook passes a reference to itself using the ThisWorkbook object to the Add-in Module.
The following code shows a series of tests that exemplify the usage of this Add-in.
Private Const strSETTING As String = "My Setting" Private Const intVALUE As Integer = 123 Private Sub CanCreateAndAssignNewSetting() Debug.Assert modAppSettings.AssignSetting(ThisWorkbook, strSETTING, intVALUE, True) = True Debug.Print "CanCreateAndAssignNewSetting Passes" End Sub Private Sub CanChangeSettingValue() Debug.Assert modAppSettings.AssignSetting(ThisWorkbook, strSETTING, intVALUE + 100) = True Debug.Print "CanChangeSettingValue Passes" End Sub Private Sub CanReadSetting() Debug.Assert modAppSettings.GetSetting(ThisWorkbook, strSETTING) = 223 Debug.Print "CanReadSetting Passes" End Sub Private Sub CanDeleteSetting() Debug.Assert modAppSettings.RemoveSetting(ThisWorkbook, strSETTING) Debug.Print "CanDeleteSetting Passes" End Sub Public Sub RunTests() CanCreateAndAssignNewSetting CanChangeSettingValue CanReadSetting CanDeleteSetting Debug.Print "Tests Completed" End Sub
That’s it!. We hope you found this recipe useful. If you did, please share this post in the social network of your choosing by clicking one of the buttons below.