How to store Config in Excel

I often use configuration when writing VBA macros in Excel. Sometimes it is needed in forms to save settings, sometimes to save some data received during the macro execution.
For a long time I was looking for information on how to work with config in VBA.
There are many different options, from storing the configuration in code, to putting it in a separate file.
Today I would like to talk step by step about how I store and process the macro configuration in an Excel workbook.
Create a ConfigSheet
I had Excel 2010 handy, but in this case the version doesn’t matter.
First, let’s create a separate sheet. I called him config, but this is not essential. What is really important is CodeName
sheet:
If you suddenly didn’t know, Excel document sheets in VBA are nothing more than an object class Worksheet
. We turn to the help and see the object Worksheet
necessary property (you can overwrite it programmatically, despite Read-only, but more on that another time):
To make it easier to refer to our Config sheet, change the field value to it (Name) in properties (if you don’t have them, click F4 or View -> Properties Windowand if you don’t have a structure with a project, click ctrl+r or View -> Project Explorer).
Thus, we additionally protect ourselves from an error in case of a change in the usual sheet name, and also get an autocomplete.
By the way, since the leaf is an objectwe can also refer to its methods, fields and properties through a dot, as usual:
We will use this, but more on that later.
Create a ListObject
So, as you may have guessed, we will store all the information in a table, namely in an object ListObject
.
To do this, on our sheet we create an empty table with two columns Key
and Value
:
Now it remains to give our table a name in the Constructor, and the basis for storage is ready:
Get the table object
Let’s move on to the most interesting. We will write the code in the sheet module ConfigSheet
.
First, let’s create the necessary getters:
Public Property Get Table() As ListObject
' Свойство Read-Only для объекта таблицы.
Set Table = Me.ListObjects("configTable")
End Property
Public Property Get Keys() As Range
' Свойство Read-Only для столбца ключей.
Set Keys = Me.Table.ListColumns(KeyColumn).DataBodyRange
End Property
Public Property Get Values() As Range
' Свойство Read-Only для столбца значений.
Set Values = Me.Table.ListColumns(ValueColumn).DataBodyRange
End Property
To property Table
put our table in Keys
– a column of keys, in Values
– column of values.
For the convenience of referring to columns (and not to hardcode), write Enum
at the module level:
Private Enum EConfigColumns
KeyColumn = 1
ValueColumn
End Enum
Said “don’t hardcode” and zahardkodil table name 😀. Correcting:
Option Explicit
Private Enum EConfigColumns
KeyColumn = 1
ValueColumn
End Enum
Private Const ConfigTable As String = "configTable"
We register the Get Config property
It remains to create a property Config
through which we will write, receive and update values in the table.
First, let’s get the value by the key:
Public Property Get Config(ByVal Key As Variant) As Variant
Dim i As Long
For i = 1 To Me.Keys.Rows.Count
If Key <> Me.Keys(i).Value Then GoTo NextKey
Config = Me.Values(i).Value: Exit Property
NextKey:
Next
End Property
Everything is quite simple here – we loop through the keys and compare them with the parameter Key
passed by the user. As soon as we find the desired key, we return the corresponding value.
Since we work directly with the object Range
, this already somewhat slows down the macro. Plus, during the loop, the macro will constantly call properties Keys
and Values
to get their values, which will further slow down the execution speed.
To speed up the process a little and avoid constant access, you can create variables and pass properties into them:
Public Property Get Config(ByVal Key As Variant) As Variant
' Переменные, для хранения свойств.
Dim Keys As Range: Set Keys = Me.Keys
Dim Values As Range: Set Values = Me.Values
Dim i As Long
For i = 1 To Me.Keys.Rows.Count
If Key <> Me.Keys(i).Value Then GoTo NextKey
Config = Me.Values(i).Value: Exit Property
NextKey:
Next
End Property
But this somewhat clutters up the code (and we will still have the property Let
), so we use UDT
(user defined type) and a procedure that will initialize it.
Create a type TConfig
in which we place all our previously created properties (except, in fact, Config
) and also create private variable This
at the module level:
Option Explicit
Private Enum EConfigColumns
KeyColumn = 1
ValueColumn
End Enum
Private Const ConfigTable As String = "configTable"
Private Type TConfig
Table As ListObject
Keys As Range
Values As Range
End Type
Private This As TConfig
It is very important that and Type TConfig
and variable This
were private, otherwise a compile-time error will occur.
Next, we write a small procedure InitThis
to assign values to our type:
Public Sub InitThis()
Set This.Table = Me.Table
Set This.Keys = Me.Keys
Set This.Values = Me.Values
End Sub
Now let’s fix the property Config
:
Public Property Get Config(ByVal Key As Variant) As Variant
Me.InitThis
Dim i As Long
For i = 1 To This.Keys.Rows.Count
If Key <> This.Keys(i).Value Then GoTo NextKey
Config = This.Values(i).Value: Exit Property
NextKey:
Next
End Property
Laconic, isn’t it?
We register the property Let Config
With setting values a little differently:
Public Property Let Config(ByVal Key As Variant, ByVal RHS As Variant)
Me.InitThis
If This.Keys Is Nothing Then This.Table.ListRows.Add: Me.InitThis
Dim i As Long
Do Until Key = This.Keys(i).Value
i = i + 1
If i > This.Keys.Rows.Count Then This.Table.ListRows.Add: Exit Do
Loop
This.Keys(i).Value = Key
This.Values(i).Value = RHS
End Property
We accept as parameters Key
and RHS
(right hand side – on the right hand), in order to be able to prescribe such a construction:
ConfigSheet.Config("Key") = "Value"
At the very beginning we check This.Keys
on the Nothing
, because if there are no values in the table yet, an error will pop up when trying to loop through the columns.
To avoid this, after checking, add an empty string to the table and re-initialize This
. Only after that it will be possible to freely cycle through the columns.
We add a similar check to Get
but instead of adding a row, we simply return the message “No data in the configuration table”:
Public Property Get Config(ByVal Key As Variant) As Variant
Me.InitThis
If This.Keys Is Nothing Then Config = "Нет данных в таблице конфигурации": Exit Property
Dim i As Long
For i = 1 To This.Keys.Rows.Count
If Key <> This.Keys(i).Value Then GoTo NextKey
Config = This.Values(i).Value: Exit Property
NextKey:
Next
End Property
Further, as in Get
parts, cycle, only now Do Until
, run through the config keys. When the maximum index is reached, we add a new line at the end and exit the loop. At the end, we assign the key and value to the corresponding cells.
Removing empty lines
The first time we access the table, we will have an empty row. To remove it (and in general, just in case, check every time if there are empty rows in the table), we write a small procedure:
Public Sub DeleteEmptyRows()
Me.InitThis
Dim i As Long
For i = This.Keys.Count To 1 Step -1
If (IsEmpty(This.Keys(i).Value) And IsEmpty(This.Values(i).Value)) _
Or (This.Keys(i).Value = vbNullString And This.Values(i).Value = vbNullString) Then This.Table.ListRows(i).Delete
Next
End Sub
and add it to the already written Let
part:
Public Property Let Config(ByVal Key As Variant, ByVal RHS As Variant)
Me.InitThis
If This.Keys Is Nothing Then This.Table.ListRows.Add: Me.InitThis
Dim i As Long
Do Until Key = This.Keys(i).Value
i = i + 1
If i > This.Keys.Rows.Count Then This.Table.ListRows.Add: Exit Do
Loop
This.Keys(i).Value = Key
This.Values(i).Value = RHS
Me.DeleteEmptyRows ' Проверяем на пустые строки.
End Property
Final code
Option Explicit
Private Enum EConfigColumns
KeyColumn = 1
ValueColumn
End Enum
Private Const ConfigTable As String = "configTable"
Private Type TConfig
Table As ListObject
Keys As Range
Values As Range
End Type
Private This As TConfig
Public Sub InitThis()
Set This.Table = Me.Table
Set This.Keys = Me.Keys
Set This.Values = Me.Values
End Sub
Public Property Get Table() As ListObject
' Свойство Read-Only для объекта таблицы.
Set Table = Me.ListObjects(ConfigTable)
End Property
Public Property Get Keys() As Range
' Свойство Read-Only для столбца ключей.
Set Keys = Me.Table.ListColumns(KeyColumn).DataBodyRange
End Property
Public Property Get Values() As Range
' Свойство Read-Only для столбца значений.
Set Values = Me.Table.ListColumns(ValueColumn).DataBodyRange
End Property
Public Property Get Config(ByVal Key As Variant) As Variant
Me.InitThis
If This.Keys Is Nothing Then Config = "Нет данных в таблице конфигурации": Exit Property
Dim i As Long
For i = 1 To This.Keys.Rows.Count
If Key <> This.Keys(i).Value Then GoTo NextKey
Config = This.Values(i).Value: Exit Property
NextKey:
Next
End Property
Public Property Let Config(ByVal Key As Variant, ByVal RHS As Variant)
Me.InitThis
If This.Keys Is Nothing Then This.Table.ListRows.Add: Me.InitThis
Dim i As Long
Do Until Key = This.Keys(i).Value
i = i + 1
If i > This.Keys.Rows.Count Then This.Table.ListRows.Add: Exit Do
Loop
This.Keys(i).Value = Key
This.Values(i).Value = RHS
Me.DeleteEmptyRows ' Проверяем на пустые строки.
End Property
Public Sub DeleteEmptyRows()
Me.InitThis
Dim i As Long
For i = This.Keys.Count To 1 Step -1
If (IsEmpty(This.Keys(i).Value) And IsEmpty(This.Values(i).Value)) _
Or (This.Keys(i).Value = vbNullString And This.Values(i).Value = vbNullString) Then This.Table.ListRows(i).Delete
Next
End Sub
Checking the result
And finally, we check the result.
Write the value to the config:
Sub Test()
' Значение "Дневник VBAшника" записано в таблицу с ключом "ChanelName"
ConfigSheet.Config("ChanelName") = "Дневник VBAшника"
End Sub
Reading the value:
Sub Test()
' Распечатает: "Дневник VBAшника"
Debug.Print ConfigSheet.Config("ChanelName")
End Sub
Change and read again:
Sub Test()
ConfigSheet.Config("ChanelName") = "https://t.me/VBAn_Diary"
' Распечатает: "https://t.me/VBAn_Diary"
Debug.Print ConfigSheet.Config("ChanelName")
End Sub
Well, then this sheet can be hidden from the eyes of users, protect the book and structure, and work calmly with the config.
I am sure that you can change the approach and write the code differently. Perhaps you have suggestions or additions to the article. I will be glad to any feedback. 🙂