How to save all hard-coded values ​​in a class

advertisements

I have created a class in VBA which I would like to have some pre-set values associated with it. I am new to classes, and am wondering what is the best (/a good) way of structuring my VBA code within the class object, so that I can access these default values easily as I type. An answer should preferably:

  • Require relatively few additional lines of code over and above the lines which I assume will be required for the actual hard-coding of values

    • ie. something like an additional Sub for each hardcoded value would not be ideal
    • this is to prevent my class from becoming too cluttered
  • Allow me to use intellisense in some way to access these hard coded values

It's worth noting that my main use of these hard coded values is in setting default values to variables of my class (by looping in the initialize event), but I may also want to access them in other portions of code

What I've tried:

Declaring an Enum to save my hard coded values

'Declarations
Private Enum startingVals
    Top = 10
    Column_Count = 4
    Left = 15
    ...
End Enum
Private topVal As Long 'variables which I assign default values to
Private colCnt As Long
Private leftVal As Long

Private Sub Class_Initialize()
topVal = startingVals.Top
colCnt = startingVals.Column_Count
'etc.
End Sub

This has 2 limitations;

  • Enums can only store Longs

    • Get around this by using a load of Consts instead, but then you have to remember every constant's name, plus it looks cluttered in code
  • Although I get Intellisense for .Top and .Column_Count, I still have to type startingVals out in full
    • That's significantly better than having to remember all the hardcoded constant names though

Ideally I would be able to do this

Private Sub Class_Initialize()
With startingVals 'or Dim v As startingVals, With v
    topVal = .Top
    colCnt = .Column_Count
    'etc.
End With
End Sub

But I can't


Another approach would be to use a function to save the values,that way you could declare different types to just long.

'Declarations
Private Enum startingVals
    Top = 1
    Column_Count = 2
    Left = 3
    ...
End Enum
Private topVal As Long 'variables which I assign default values to
Private colCnt As Long
Private leftVal As Long

Private Sub Class_Initialize()
topVal = getval(Top)
colCnt = getval(Column_Count)
'etc.
End Sub

Then to access the hard coded data, you have a function which takes an enum input (allowing for intellisense)

Private Function getval(dataType As startVals) As String
Const savedData As String = "1,2,1.17171717,hey,me,you" 'save the return values for the index specified by dataType
getval = Split(savedData, ",")(dataType) 'use datatype as a direct index of the array
End Function

or another way of saving the values

Private Function getval(dataType As startVals) As String
Const colV As Long = 10 'index 1
Const topV As String = "This is the top" 'index 2
'...
If dataType = ColumnCount Then getval = colV 'use dataType to check what to return
If dataType = Top Then getval = colV 'could use a select case too
'etc
End Function

  • But either way we still can't access the constants unless we type the function name out.
  • Also this approach requires me to update both the enum declaration at my class declarations portion, and the const declaration within the function itself, making the code harder to maintain.

TL;DR

What's the best method to save hardcoded values in a class object, where best is defined as

  • Uses VBA intellisense (autofill) so I can quickly select the value I want as I type
  • Is neat, self contained and concise within my class module, to avoid clutter
  • Can preferably hold any kind (data type) of hardcoded value (although I am only using Long in the project I'm currently working on)
  • Can be accessed without the need of typing an initialisation portion each time (such as a function or enum name)
    • Of course a With block or function equivalent would be fine, as that only requires the one instance of specifying the enum/data collection name

...to prevent my class from becoming too cluttered

I would separate the class from its initialization process adding another class lets call it Initializer. Initializer will know how to initialize my objects, will contain the default values and will fill my object with this defaults. But in the initializer you will have to write the assignments, no magic intellisense, but just simply write m_ and select from the list. HTH

Class Foo

Option Explicit

'variables which I assign default values to
Private m_topVal As Long
Private m_colCnt As Long
'Private m_leftVal As Long

Private Sub Class_Initialize()
    Dim initializer As FooInitializer
    Set initializer = New FooInitializer
    initializer.Initialize Me
End Sub

Public Property Get TopVal() As Long
    TopVal = m_topVal
End Property

Public Property Let TopVal(ByVal vNewValue As Long)
    m_topVal = vNewValue
End Property

Public Property Get ColCnt() As Long
    ColCnt = m_colCnt
End Property

Public Property Let ColCnt(ByVal vNewValue As Long)
    m_colCnt = vNewValue
End Property

' Add Get/Let(Set) for other member variables as well

Class FooInitializer

Option Explicit

' Default startingVals values
Private m_topValDefault As Integer
Private m_columnCountDefault As Integer
'etc.

Public Sub Initialize(ByRef fooInstance As Foo)
    fooInstance.TopVal = m_topValDefault
    fooInstance.ColCnt = m_columnCountDefault
    'etc.
End Sub

Private Sub Class_Initialize()
    m_topValDefault = 10
    m_columnCountDefault = 4
    'etc.
End Sub

Standard module

Option Explicit

Sub test()
    Dim f As Foo
    Set f = New Foo
    ' f is now initizlized via initializer with default values
    Debug.Print f.TopVal
    Debug.Print f.ColCnt
End Sub