CODEWITHSUNDEEP
vbaexcel
ExcelCyclist
ExcelCyclist

Reputation: 115

VBA Clean use of many Constants

My Excel VBA takes ~300 XLS files and grabs 8 cells to deposit into their own row. (Office11) I have several subs and functions that use location constants for the sourceData and destination locations. Grand Total I have 23 constant locations with Column numbers, cell locations.

Question: Any suggestions on how to clean this up for readability and keeping constants all in one location? I was trying to avoid public variables but not sure of a better method. How do you do Arrays containing constant values?

partial example,
Public pstrQLocations(1 To 8) As String
pstrQLocations(1) = "B7"
pstrQLocations(2) = "B6"
pstrQLocations(3) = "B5"
pstrQLocations(4) = "B8"
pstrQLocations(5) = "A3"
pstrQLocations(6) = "C8"

Upvotes: 1

Views: 1372

Answers (1)

marg
marg

Reputation: 2827

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

Upvotes: 1

Related Questions