Constant With Dot Operator (VBA)

  • A+

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?


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 


:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: