Cfrgtkky

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.

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

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.

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