Initiating variables, of different types, in a way that I can iterate through them with a variable

Question

'Declare variables for cell values attained from APR spreadsheet
    Dim orgSheetvalues(0) As String 'Project Title
    Dim orgSheetvalues(1) As String 'Circuit Tag
    Dim orgSheetvalues(2) As String 'District
    Dim orgSheetvalues(3) As String 'State
    Dim orgSheetvalues(4) As Date 'Date recieved
    Dim orgSheetvalues(5) As Currency  'Planned Capital Cost
    Dim orgSheetvalues(6) As Currency  'Actual Capital Cost
    Dim orgSheetvalues(7) As Date  'Capital work completed date
    Dim orgSheetvalues(8) As Currency  'Planned O&M Cost
    Dim orgSheetvalues(9) As Currency  'Actual O&M Cost
    Dim orgSheetvalues(10) As Date  'O&M work completed date
    Dim orgSheetvalues(11) As String 'RWP File Path

Clearly, this does not work. Does anyone know of a way I can use the same variable name, for different variable types, in a way that can be iterated as the above would if it worked (eg. orgSheetvalue(iterating variable))?

Use an array of variants and initialize each member using the specific type? Why? — Tim Williams, Nov 17 '17 at 00:21
Possible duplicate of [How to create dynamic variable names VBA](https://stackoverflow.com/questions/38254337/how-to-create-dynamic-variable-names-vba) — Erik A, Nov 17 '17 at 09:44

Kostas K. · Answer 1 · 2017-11-17T09:41:36.057

While the Variant array will work just fine, it's prone to errors as you will need to remember the datatype each position holds.

A custom Type in my opinion would be better approach.

Public Type MyCustomType
    ProjectTitle As String
    CircuitTag As String
    District As String
    State As String
    DateRecieved As Date
    PlannedCapitalCost As Currency
   '...
End Type


Sub T()
    Dim o As MyCustomType
        o.ProjectTitle = "abc"
        o.CircuitTag = "..."
        o.DateRecieved = Date
End Sub

jsotola · Answer 2 · 2017-11-17T00:47:01.007

1

example

Option Explicit

Sub aaa()

    Dim bbb(10) As Variant

    bbb(0) = "test 123"          ' string
    Set bbb(1) = Range("a1")     ' object
    bbb(2) = 98765               ' numeric

    Debug.Print bbb(0); vbTab; bbb(1).Interior.Color; vbTab; bbb(2)

End Sub

edited Nov 17 '17 at 00:47

answered Nov 17 '17 at 00:23

jsotola

2,238
1
10
22

Initiating variables, of different types, in a way that I can iterate through them with a variable

2 Answers2