VBA Clean use of many Constants

Answer 1

You can store your Constants in a Collection. The advantage is, that you can give your elements names.

Option Explicit
Dim pstrQLocations As Collection

Private Sub initializeConstants()
    Set pstrQLocations = New Collection

    pstrQLocations.Add "B7", "Title"
    pstrQLocations.Add "B6", "User"
End Sub

Private Sub showConstants()
    initializeConstants
    Debug.Print Me.Range(pstrQLocations("Title")).Value
    Debug.Print Me.Range(pstrQLocations("User")).Value
End Sub

3D Version:

Option Explicit
Dim pstrQLocations As Collection

Private Sub initializeConstants()
    Dim title As New Collection

    Set pstrQLocations = New Collection

    title.Add "B7", "source"
    title.Add "A6", "destination"

    pstrQLocations.Add title, "Title"
End Sub

Private Sub showConstants()
    Dim y As Collection
    initializeConstants

    Debug.Print pstrQLocations("Title")("source")
    Debug.Print pstrQLocations("Title")("destination")
End Sub