Avoiding Magic Numbers

When working with the following table in VBA, how would you handle it?

In programming, it is generally recommended to avoid the use of magic numbers.

While in languages other than VBA, you may rarely encounter magic numbers with a little bit of learning, in VBA, it is often difficult to avoid them due to the nature of the data being handled.

Personally, I believe there are several reasons for this:

• The majority of the data being handled is from sheets or CSV files.
• Memory management is complex, and there is often an inverse relationship between memory usage and readability.
• VBA behaves differently from mainstream languages, which can be a challenge for developers.

Due to the nature of the data being handled, if you code without thinking, magic numbers will naturally emerge. In this article, I would like to introduce the approach I frequently use to handle this issue, along with some code examples.

Approach

My Recommended Approach: Collection and Dictionary

My personal recommendation is to use a combination of the Collection object and the Dictionary object, which creates an associative array pattern. This method is familiar to those who have experience with languages like JavaScript, where handling JSON files is common.

------------------------------------------------
'
' Convert the target Range into an associative array.
'
' @param r The Range object to be converted.
' @return An associative array.
'
'------------------------------------------------
Public Function GetCollection(ByRef r As Range) As Collection

    Dim items As Collection, item As Object
    Set items = New Collection

    Dim row As Long, col As Long

    For row = 2 To r.Rows.Count

        Set item = CreateObject("Scripting.Dictionary")

        For col = 1 To r.Columns.Count

            item.Add r(1, col).Value, r(row, col).Value

        Next col

        items.Add item
    Next row

    Set GetCollection = items
End Function

While it may seem redundant to replace a Range object, which can already be treated as a two-dimensional array, with an associative array, I find this method to be the most preferable in terms of readability.

After applying the conversion, the Range object becomes similar to the following json file:

{
	"1": {
		"Number": "1",
		"Name": "Sato",
		"Address": "Hokkaido",
		"Phone Number": "090-0000-000",
		"Product": "Toy",
		"Option": "Option 1"
	},
	"2": {
		"Number": "2",
		"Name": "Tanaka",
		"Address": "Aomori",
		"Phone Number": "090-1111-111",
		"Product": "Snack",
		"Option": "Option 2"
	},
	"3": {...},
	"4": {...},
	"5": {...}
}

When extracting data, you can do it as follows:

Dim items as Collection

Set items = GetCollection(Range)

items(1)("Name") ' → "Sato"

Using an iterator (for each) makes it even more understandable.

Dim items as Collection, item as Object

Set items = GetCollection(Range)

For Each item in items
	item("Name") ' → "Sato", "Tanaka"...
next

There are several drawbacks to this approach. First is the speed. Simply looping the data twice (once for creating the array and once for the actual processing) will result in slower processing speed. Another drawback is that it assumes there are no duplicates in the header row. If there are duplicates or additional conditions to consider, the code becomes more complex and harder to follow. It would be better to use a different approach instead.

Majority is Enum

As a method to avoid magic numbers, the most popular approach is to use Enum. It is easier for beginners to understand compared to the methods using Collection and Dictionary that were introduced earlier, and it offers better maintainability, wouldn’t you agree?

Enum TableColumn
	Number = 1
	Name = 2
	Address = 3
	Phone Number = 4
	Product = 5
	Option = 6
End Enum

Since it’s just defining a group of constants, you can simply replace the numbers with Enums when calling them. It’s also easy to remove magic numbers from an already completed program.

Range(1, TableColumn.Name).value ' → "Sato"

If you want to loop through the data, the code would look like this:

Dim i as Long
For i = 1 to Range.Rows.Count
	Range(i, TableColumn.Name).value ' → "Sato", "Tanaka"...
Next

There are no particular drawbacks to this method. When in doubt, use this method.

The simplest and constant method

I hesitated to introduce this method because I thought everyone reading this article would already know it, but there is a method of defining each header column individually, which is the fastest in terms of processing speed.

Private Const TABLE_COLUMN_NUMBER As Long = 1
Private Const TABLE_COLUMN_NAME As Long = 2
Private Const TABLE_COLUMN_ADDRESS As Long = 3
Private Const TABLE_COLUMN_PHONE_NUMBER As Long = 4
Private Const TABLE_COLUMN_PRODUCT As Long = 5
Private Const TABLE_COLUMN_OPTION As Long = 6

To actively specify scope and type, this method is the fastest in terms of processing speed. However, I believe the heaviest part is the loop processing, so there is little difference compared to using Enum. Unless specified in coding rules or guidelines, using Enum should be sufficient.