New array in For Next loop every iteration VBS

Question

I have a section of code which opens each text file in a folder and I want to not only put the file name into an array but also split the text inside the file into an array, something like this:

i = 0
n = 1
For Each File In Folder
    i = i + 1
    Dim UserArray & i()
    Set openedFile = fso.OpenTextFile(File)
    Do Until openedFile.AtEndOfStream 
        Line = openedFile.ReadLine
        ReDim Preserve UserArray & i(n)
        UserArray & i(n) = Line
        n = n + 1
    Loop
    n = 0
Loop

The idea being that each line will later be strComp to another array of lines from a different text file. So each file needs to create a unique array name for its text contents and the number of files in any given folder varies.

The above does not work,any ideas?

Gurmanjot Singh · Accepted Answer · 2017-07-31 07:17:06Z

2

There are syntax errors in your code(Line 5,9,10 - fixed by using execute statements which allows you to declare variables dynamically during run time with different names) along with variable spelling mistakes(Line 8).

P.S. I am not making any changes to the logic applied here. Just trying to correct the mistakes.

i = 0
n = 0                                                   'initialised to 0
For Each File In Folder
    i = i + 1
    Execute "Dim UserArray"&i&"()"                      'used execute statement to declare arrays with new names based on the value of i for each file
    Set openedFile = fso.OpenTextFile(File)
    Do Until openedFile.AtEndOfStream 
        Line = openedFile.ReadLine                      'corrected the spelling mistake here
        Execute "ReDim Preserve UserArray"&i&"("&n&")"
        Execute "UserArray"&i&"("&n&")="&Line
        n = n + 1
    Loop
    n = 0
Loop

After this code, you should have UserArray1 for 1st file, UserArray2 for 2nd file and so on...

edited Jul 31, 2017 at 7:17

answered Jul 31, 2017 at 7:07

Gurmanjot Singh

10.4k2 gold badges22 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

BertB Over a year ago

Worked a treat! For line10 of the snippet, I had to add a chr(34) before and after the line or it would fault. Execute "UserArray" & i & "(" & n & ") = " & chr(34) & Line & chr(34) For lines in the text file that already have quotation marks, I included a Replace line to prevent failure: Line = Replace(Line,chr(34)," ' ") How does the "Execute" work? Is there any documentation on its use?

Gurmanjot Singh Over a year ago

@BertB Glad that worked. You can find some information on the execute command here: ss64.com/vb/execute.html

Erin Halbmaier · Accepted Answer · 2017-07-31 20:50:44Z

0

You could try an array of arrays. Like so:

Dim Userarray() As Variant
Dim subArray() As String

i = 0
n = 1

For Row = 0 To 4
    i = i + 1
    ReDim subArray(i)
    For Each cell In ActiveSheet.Range(Cells(i, 1), Cells(i, 5))
        ReDim Preserve subArray(n)
        subArray(n) = cell.Value
        n = n + 1
    Next
    ReDim Preserve Userarray(i)
    Userarray(i) = subArray
    n = 0
Next

(I didn't have files, so I was just using a range in Excel). I assume VBA is similar enough to VBScript for this to work...Results in the first row of data as an array in Userarray(1), the second row of data as an array in Userarray(2), etc.

edited Jul 31, 2017 at 20:50

answered Jul 31, 2017 at 20:29

Erin Halbmaier

3545 silver badges9 bronze badges

3 Comments

BertB Over a year ago

That is quite an interesting approach, I will try using this soon. How do you address each element in the second array? EG addressing UserArray(1) should give you subArray(1~5), right? So how would you address say UserArray(1) --> subArray(3) ?

Erin Halbmaier Over a year ago

I believe Userarray (1,3). I'm not ultra-fond of arrays in VBA, though, so while I love them in Javascript and PHP, I avoid them like the plague in Excel and have thus not actually used this in a project. :-D Note: the multidimensional array will likely use more memory than the way you were doing it (if that works - I didn't realize VBA allowed for that!). Seems to me like an easier way to loop through them all, though. Ah, and I believe you would get upperbound of the subArray with UBound(Userarray, 2) for second dimension - it might make the subs all the same size.

Erin Halbmaier Over a year ago

Another option might be to make Userarray a Collection instead of an array and Userarray.Add subArray at the end of the loop.

Collectives™ on Stack Overflow

New array in For Next loop every iteration VBS

2 Answers 2

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related