Set a reference to an Enum

Question

I have an application that consists of multiple sheets, each containing similar data in a grid layout.

Enum eSht1
    FName = 1
    LName = 2
    Data1 = 3
    Data2 = 4
End Enum

The issue is to extract data at the intersection of a row/column. To get the column, a macro searches row 1 for the desired number corresponding the desired item. Eg, for LName the macro would search for 2 and return column B.

These numbers are subject to change, both in value & position – I have no control over this, the client is the “interface” designer, my job is to make the VBA code work. So to facilitate any changes, these numbers are contained in an Enum. So in this example, the macro would search for eSht1.LName.

This has worked well in the past, with one data sheet per workbook, but now we’re rolling all these sheets into a single workbook. As all these tables have similar data, it would be nice to use the same procedures throughout.

Enum eSht2
    Index = 10
    FName = 20
    LName = 30
    Data2 = 40
End Enum

The problem is to reference the various Enums from a single macro. So for instance, to extract LName from sheet1, the macro would search with eSht1.LName. To get LName from sheet2, the same macro would search with eSht2.LName.

This is a dummied down example, there are 20+ sheets, and a substantial number of macros scattered throughout the project that reference these Enums. Is this possible, or do I need to have separate macros for each sheet? Or try a totally different approach?

Answer 1

Siddharth Rout brings up valid points in the comments, you might be better off sticking with just the names instead of keeping track of two different values.

---

However, if you are feeling adventurous, you could create a Class Module to help deal with these value pairs. This is helpful if the goal is to be able to use something similar to CallByName on Enum.

As a result you could do something along the lines of:

eSht1.Properties("FName") ' Returns 1

How is that any better than writing eSht1.FName? With a class you can access the values with a variable. This makes iterating over multiple properties or objects very simple.

---

For example, consider having the following CEnum Class Module modified from this answer:

Option Explicit

Private pProperties As Object

Public Property Get Properties() As Object
  Set Properties = pProperties
End Property

Public Property Let Properties(p As Object)
  Set pProperties = p
End Property

Sub Class_Initialize()
  Set pProperties = CreateObject("Scripting.Dictionary")

  'Add/instantiate your properties here
  pProperties("Index") = 0
  pProperties("FName") = 0
  pProperties("LName") = 0
  pProperties("Data1") = 0
  pProperties("Data2") = 0
End Sub

With this class we can then get properties from any or all CEnum objects rather easily.

Option Explicit

Sub TestCEnums()

  ' You can set a CEnum to a variable and instantiate the "properties"
  Dim e1 As New CEnum
  With e1
    .Properties("Index") = 1
    .Properties("Data1") = 10
  End With

  ' If you'd prefer to have all of the CEnums in a collection, it might be
  ' better to add them via a function
  Dim enums As New Collection
  AddEnum "e2", enums, 11, 12, 13, 14, 15
  AddEnum "e3", enums, 22, 23, 24, 25, 26
  AddEnum "e4", enums, 99, 88, 77, 66, 55

  ' Having everything under the dictionary object makes it trivial to get all
  ' properties from any (or every) CEnum object
  Debug.Print "All properties from e1:"
  Dim p As Variant
  For Each p In e1.Properties.Keys()
    Debug.Print p, e1.Properties(p)
  Next

  enums.Add e1, "e1", "e2" ' Add e1 to the collection

  ' You can also get only a specific property from all CEnums in a collection
  Debug.Print vbCrLf & "The Index property from all CEnums:"
  Dim e As CEnum
  For Each e In enums
    Debug.Print e.Properties("Index")
  Next

End Sub

This produces the following output:

All properties from e1:
Index          1 
FName          0 
LName          0 
Data1          10 
Data2          0 

The Index property from all CEnums:
 1 
 11 
 22 
 99 

---

Here's the AddEnum function for this example:

' Adds a new CEnum to a collection
Private Function AddEnum( _
                          key As String, _
                          enums As Collection, _
                          myIndex As Long, _
                          myFName As Long, _
                          myLName As Long, _
                          myData1 As Long, _
                          myData2 As Long _
                        )

  Dim tempEnum As New CEnum
  With tempEnum
    .Properties("Index") = myIndex
    .Properties("FName") = myFName
    .Properties("LName") = myLName
    .Properties("Data1") = myData1
    .Properties("Data2") = myData2
  End With

  enums.Add tempEnum, key
  Set tempEnum = Nothing

End Function

---

EDIT

You aren't limited to just using the hack "properties" in the actual Properties property object. (Not at all confusing!) If each CEnum is linked to a specific worksheet, for example, you could add a Sheet property with its own Get and Let block. This would let you add conditional checks to your loops.

  ' SNIP
  For Each e In enums
    If e.Sheet = someSheet Then
      ' Do something
    End If
  Next