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
Subfor each hardcoded value would not be ideal - this is to prevent my class from becoming too cluttered
- ie. something like an additional
- 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
- Get around this by using a load of
- Although I get Intellisense for
.Topand.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
Longin the project I'm currently working on) - Can be accessed without the need of typing an initialisation portion each time (such as a
functionorenumname)- Of course a
Withblock or function equivalent would be fine, as that only requires the one instance of specifying the enum/data collection name
- Of course a