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 Configthrough 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 Keypassed 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 InitThisto 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 Getbut 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. 🙂