VBA - Dynamically create Public Type variables?

Quick question, I've got all of these statically set Public Type variables in my macro code. These are supposed to represent the values of an INI file. The w开发者_运维百科ay I would like it to be is that the code is all dynamic, based on what's in the INI file. So I don't need to manually update both the INI file and the code behind.

This is an outtake of the code the way it is now. This is inside it's own module:

Public Type Fields
     Firstname as String
     Lastname as String
     Username as String
End Type

I was thinking of reading the entire section of the INI file using ReadIniSection, but it seems as though it's not possible to do this within a Public Type. Am I correct? Could it be possible to get around this somehow?

Use a Scripting.Dictionary object (set a reference to the Scripting.Runtime library).

To store:

oDict.Add keyName, keyValue

To read back:

oDict(keyName)

That's assuming you have unique key names with single values.

http://msdn.microsoft.com/en-us/library/x4k5wbx4%28v=vs.85%29.aspx

Tim

It is also possible (but maybe not advisable) to add code to a module programatically. Since VBA does not support reflection, this is the only type of "dynamic" coding there is in the language. This is useful in a pinch.

See the snippet below. The addCode sub takes a standard module's name, a Type's name, and an array containing the definition of the fields.

It first tries to delete the existing Type with the same name, and then adds the new type definition in.

Sub TestAdd()
    Dim FieldArray()

    FieldArray = Array( _
     "Firstname As String", _
     "Lastname As String", _
     "Username As String" _
    )


    AddCode "Module2", "Fields", FieldArray
End Sub

Sub AddCode(ModuleName As String, TypeName As String, FieldArray())
    Dim StartLine As Long, EndLine As Long, StartColumn As Long, EndColumn As Long, _
        CodeToInsert As String

    StartLine = 1: StartColumn = -1
    EndLine = -1: EndColumn = -1

    'Find the old type definition and remove it.
    With Application.VBE.ActiveVBProject.VBComponents(ModuleName).CodeModule

        'Search for the start of the type definition
        If .Find("Public Type " & TypeName, StartLine, StartColumn, EndLine, EndColumn, True) Then
            EndLine = EndLine + 1: StartColumn = -1: EndLine = -1: EndColumn = -1

            'Found the start, now find the end of the type definition
            If .Find("End Type", EndLine, StartColumn, EndLine, EndColumn, True) Then
                .DeleteLines StartLine, (EndLine - StartLine) + 1
            End If
        End If

        CodeToInsert = _
            "Public Type " & TypeName & vbCrLf & _
            Join(FieldArray, vbCrLf) & vbCrLf & _
            "End Type"

        .InsertLines StartLine, CodeToInsert
    End With

End Sub