Avatar of New_Alex
New_Alex
Flag for Cyprus asked on

VBA, Store Variables from a text file into an Array

[ColorGroup]
2=3
4=4
3=1
6=5
5=1
1=0
0=0

[FontGroup]
IndexFont=Times New Roman
IndexSize=34
IndexUnderline=True
IndexItalic=True
IndexBold=True
IndexCellColour=2
Box1=False


[MiscGroup]
Rapids=2
Cats=4
Docs=3
6=5
5=5
1=1
0=0

Open in new window


Lets say I have the above format in a settings.txt file (also attached). I am trying to find a way to read the file into a 2 dimensional Array of the Format:

MyArray(Group, Variable)

For example if I:

Msgbox MyArray("ColorGroup", "6") it will output "5".
Msgbox MyArray("FontGroup", "IndexFont") it will output "Times New Roman".

Thanks.
(Full Points will be given to the first valid answer instantly within 30minutes of post.)


settings.txt
Microsoft ExcelMicrosoft OfficeVB Script

Avatar of undefined
Last Comment
New_Alex

8/22/2022 - Mon
Robert Schutt

Your extra limitations may put people off...

I don't think you can use strings as indices of an array in VBA normally. You could use a double layer of Scripting.Dictionary objects, but maybe you're better off using a standard function to read ini files. A good example can be found on: http://www.bygsoftware.com/Excel/VBA/ini_files.htm 
New_Alex

ASKER
My Problem is that I need to put all variables of the .ini file into an array and not getting in one by one which is resource consuming.

Any ideas?
Robert Schutt

sure how about this:

Option Explicit

Dim g_MyArray As New Scripting.Dictionary

Private Sub Workbook_Open()
    ReadIniFile "settings.txt"
    
    MsgBox MyArray("ColorGroup", "6")
End Sub

Function MyArray(g, k)
    MyArray = g_MyArray.Item(g & "/" & k)
End Function

Sub ReadIniFile(fn)

    g_MyArray.RemoveAll

    Dim fso As New Scripting.FileSystemObject
    Dim f As TextStream
    Set f = fso.OpenTextFile(ActiveWorkbook.Path & "/" & fn)
    Set fso = Nothing

    Dim txt, group, iPos, sKey, sVal
    While Not f.AtEndOfStream
        txt = Trim(f.ReadLine)
        If txt = "" Then
            group = ""
        ElseIf Left(txt, 1) = "[" And Right(txt, 1) = "]" Then
            group = Trim(Mid(txt, 2, Len(txt) - 2))
        ElseIf group <> "" Then
            iPos = InStr(txt, "=")
            If iPos > 1 Then
                sKey = Trim(Left(txt, iPos - 1))
                sVal = Trim(Mid(txt, iPos + 1))
                If sKey <> "" Then ' And sVal <> "" Then
                    g_MyArray.Add group & "/" & sKey, sVal
                End If
            End If
        End If
    Wend
    f.Close
    Set f = Nothing
End Sub

Open in new window

Your help has saved me hundreds of hours of internet surfing.
fblack61
New_Alex

ASKER
Man thanks but,

Is it possible to avoid this "Dim g_MyArray As New Scripting.Dictionary"?

I wouldn't lilke to define new objects in my code...

Thanks...
ASKER CERTIFIED SOLUTION
Robert Schutt

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
New_Alex

ASKER
Working !

As promised !