Constant With Dot Operator (VBA)

Question

I want to have a catalog of constant materials so I can use code that looks like the following:

Dim MyDensity, MySymbol
MyDensity = ALUMINUM.Density
MySymbol = ALUMINUM.Symbol

Obviously the density and symbol for aluminum are not expected to change so I want these to be constants but I like the dot notation for simplicity.

I see a few options but I don't like them.

1. Make constants for every property of every material. That seems like too many constants since I might have 20 materials each with 5 properties.

   Const ALUMINUM_DENSITY As Float = 169.34
   Const ALUMINUM_SYMBOL As String = "AL"
   

2. Define an enum with all the materials and make functions that return the properties. It's not as obvious that density is constant since its value is returned by a function.

   Public Enum Material
        MAT_ALUMINUM
        MAT_COPPER
   End Enum
   
   Public Function GetDensity(Mat As Material)
       Select Case Mat
           Case MAT_ALUMINUM
               GetDensity = 164.34
       End Select
   End Function
   

It doesn't seem like Const Structs or Const Objects going to solve this but maybe I'm wrong (they may not even be allowed). Is there a better way?

Answer 1

Make VBA's equivalent to a "static class". Regular modules can have properties, and nothing says that they can't be read-only. I'd also wrap the density and symbol up in a type:

'Materials.bas

Public Type Material
    Density As Double
    Symbol As String
End Type

Public Property Get Aluminum() As Material
    Dim output As Material
    output.Density = 169.34
    output.Symbol = "AL"
    Aluminum = output
End Property

Public Property Get Iron() As Material
    '... etc
End Property

This gets pretty close to your desired usage semantics:

Private Sub Example()
    Debug.Print Materials.Aluminum.Density
    Debug.Print Materials.Aluminum.Symbol
End Sub

If you're in the same project, you can even drop the explicit Materials qualifier (although I'd recommend making it explicit):

Private Sub Example()
    Debug.Print Aluminum.Density
    Debug.Print Aluminum.Symbol
End Sub

Answer 2

IMO @Comintern hit the nail on the head; this answer is just another possible alternative.

---

Make an interface for it. Add a class module, call it IMaterial; that interface will formalize the get-only properties a Material needs:

Option Explicit
Public Property Get Symbol() As String
End Property

Public Property Get Density() As Single
End Property

Now bring up Notepad and paste this class header:

VERSION 1.0 CLASS
BEGIN
  MultiUse = -1  'True
END
Attribute VB_Name = "StaticClass1"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Option Explicit

Save it as StaticClass1.cls and keep it in your "frequently needed VBA code files" folder (make one if you don't have one!).

Now add a prototype implementation to the text file:

VERSION 1.0 CLASS
BEGIN
  MultiUse = -1  'True
END
Attribute VB_Name = "Material"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Option Explicit    
Implements IMaterial

Private Const mSymbol As String = ""
Private Const mDensity As Single = 0

Private Property Get IMaterial_Symbol() As String
    IMaterial_Symbol = Symbol
End Property

Private Property Get IMaterial_Density() As Single
    IMaterial_Density = Density
End Property

Public Property Get Symbol() As String
    Symbol = mSymbol
End Property

Public Property Get Density() As Single
    Density = mDensity
End Property

Save that text file as Material.cls.

Now import this Material class into your project; rename it to AluminiumMaterial, and fill in the blanks:

Private Const mSymbol As String = "AL"
Private Const mDensity As Single = 169.34

Import the Material class again, rename it to AnotherMaterial, fill in the blanks:

Private Const mSymbol As String = "XYZ"
Private Const mDensity As Single = 123.45

Rinse & repeat for every material: you only need to supply each value once per material.

If you're using Rubberduck, add a folder annotation to the template file:

'@Folder("Materials")

And then the Code Explorer will cleanly regroup all the IMaterial classes under a Materials folder.

Having "many modules" is only a problem in VBA because the VBE's Project Explorer makes it rather inconvenient (by stuffing every single class under a single "classes" folder). Rubberduck's Code Explorer won't make VBA have namespaces, but lets you organize your VBA project in a structured way regardless.

Usage-wise, you can now have polymorphic code written against the IMaterial interface:

Public Sub DoSomething(ByVal material As IMaterial)
    Debug.Print material.Symbol, material.Density
End Sub

Or you can access the get-only properties from the exposed default instance (that you get from the modules' VB_PredeclaredId = True attribute):

Public Sub DoSomething()
    Debug.Print AluminumMaterial.Symbol, AluminumMaterial.Density
End Sub

And you can pass the default instances around into any method that needs to work with an IMaterial:

Public Sub DoSomething()
    PrintToDebugPane AluminumMaterial
End Sub

Private Sub PrintToDebugPane(ByVal material As IMaterial)
    Debug.Print material.Symbol, material.Density
End Sub

Upsides, you get compile-time validation for everything; the types are impossible to misuse.

Downsides, you need many modules (classes), and if the interface needs to change that makes a lot of classes to update to keep the code compilable.

Answer 3

You can create a Module called "ALUMINUM" and put the following inside it:

Public Const Density As Double = 169.34
Public Const Symbol As String = "AL"

Now in another module you can call into these like this:

Sub test()
    Debug.Print ALUMINUM.Density
    Debug.Print ALUMINUM.Symbol
End Sub

Answer 4

You could create a Class module -- let's call it Material, and define the properties a material has as public members (variables), like Density, Symbol:

Public Density As Float
Public Symbol As String

Then in a standard module create the materials:

Public Aluminium As New Material
Aluminium.Density = 169.34
Aluminium.Symbol = "AL"

Public Copper As New Material
' ... etc

Adding behaviour

The nice thing about classes is that you can define functions in it (methods) which you can also call with the dot notation on any instance. For example, if could define in the class:

Public Function AsString() 
    AsString = Symbol & "(" & Density & ")"
End Function

...then with your instance Aluminium (see earlier) you can do:

MsgBox Aluminium.AsString() ' =>   "AL(169.34)"

And whenever you have a new feature/behaviour to implement that must be available for all materials, you only have to implement it in the class.

Another example. Define in the class:

Public Function CalculateWeight(Volume As Float) As Float
    CalculateWeight = Volume * Density
End Function 

...and you can now do:

Weight = Aluminium.CalculateWeight(50.6)

Making the properties read-only

If you want to be sure that your code does not assign a new value to the Density and Symbol properties, then you need a bit more code. In the class you would define those properties with getters and setters (using Get and Set syntax). For example, Symbol would be defined as follows:

Private privSymbol as String

Property Get Symbol() As String
    Symbol = privSymbol
End Property

Property Set Symbol(value As String)
    If privSymbol = "" Then privSymbol = value
End Property

The above code will only allow to set the Symbol property if it is different from the empty string. Once set to "AL" it cannot be changed any more. You might even want to raise an error if such an attempt is made.

Answer 5

I like a hybrid approach. This is pseudo code because I don't quite have the time to fully work the example.

Create a MaterialsDataClass - see Mathieu Guindon's knowledge about setting this up as a static class

Private ArrayOfSymbols() as String
Private ArrayOfDensity() as Double
Private ArrayOfName() as String
' ....

    ArrayOfSymbols = Split("H|He|AL|O|...","|")
    ArrayOfDensity = '....
    ArrayOfName = '....

    Property Get GetMaterialBySymbol(value as Variant) as Material
    Dim Index as Long
    Dim NewMaterial as Material
        'Find value in the Symbol array, get the Index
        New Material = SetNewMaterial(ArrayOfSymbols(Index), ArrayofName(Index), ArrayofDensity(Index))
        GetMaterialBySymbol = NewMaterial
    End Property

Property Get GetMaterialByName(value as string) ' etc.

Material itself is similar to other answers. I have used a Type below, but I prefer Classes over Types because they allow more functionality, and they also can be used in 'For Each' loops.

Public Type Material
    Density As Double
    Symbol As String
    Name as String
End Type

In your usage:

Public MaterialsData as New MaterialsDataClass
Dim MyMaterial as Material
    Set MyMaterial = MaterialsDataClass.GetMaterialByName("Aluminium")
    Debug.print MyMaterial.Density