Array of 600+ Strings in excel VBA

Question

I am doing a loop for each string in an array such that

 filename = Array(file1,file2.....file600)

However VBA gets a compile error that is due to the array taking up 8 lines. As far as I am aware it only allows 1 line

(error says expected list or separator)

I am new to VBA sorry

are `file1` , `file2` ... defined as `String` or they are just text ? — Shai Rado, Jan 01 '17 at 13:46
escape the new lines with `_` or store the filenames in a hidden worksheet and read them in as an array? http://stackoverflow.com/questions/8303821/if-statement-with-multiple-lines — Orphid, Jan 01 '17 at 13:53
file1, file2 are just text. So essentially on my VBA editor I have like 8 lines of text with brackets around them Oh and it is filename = Array("file1", "file2",..."file600") — Lucas, Jan 01 '17 at 13:55
are those the _real_ strings? I mean are the strings actually defined as "file1", "file2" and so on? — user3598756, Jan 01 '17 at 13:57
If all of the files are gathered in one directory and all of the files have a certain pattern to their name (e.g. all files with a given extension), there wouldn't be any need to assemble the names as a list of strings. A fairly common VBA programming task is to iterate through all files in a directory which satisfy a given property, processing them in a loop. As a special case, this includes processing all of the files in a directory. — John Coleman, Jan 01 '17 at 14:28

score 3 · Answer 1 · edited May 23 '17 at 12:33

3

You can escape new lines in VBA with _.

so your solution might look like

filename = Array("file1", _
    "file2", _
    "file3")

See How to break long string to multiple lines and If Statement With Multiple Lines

If you have 100's of names, however, you might be better off storing them in a worksheet and reading them in, rather than hard-coding them.

edited May 23 '17 at 12:33

Community

1
1

answered Jan 01 '17 at 13:57

Orphid

2,722
2
27
41

Although the solution of Orphid may work for you, maybe you should take a closer look to your design, requiring an array initialization with 600 elements in one statement ... – nabuchodonossor Jan 01 '17 at 14:05
@nabuchodonossor is right. Lucas show an example of the actual content of your 600 strings – user3598756 Jan 01 '17 at 14:07
@orphid, I can enter 24 lines with `_`, not more. – Timo Aug 26 '20 at 15:43

score 0 · Answer 2 · answered Jan 01 '17 at 14:23

Should you strings in the array be actually "buildable" following a pattern (like per your examples: "file1", "file2", ...,"file600") then you could have a Function define them for you, like follows:

Function GetFileNames(nFiles As Long) As String()            
    Dim iFile As Long
    ReDim filenames(1 To nFiles) As String

    For iFile = 1 To nFiles
        filenames(iFile) = "file" & iFile
    Next
    GetFileNames = filenames
End Function

which you'd call in your "main" code as follows

Sub main()
    Dim filenames() As String

    filenames = GetFileNames(600) '<--| this way'filenames' array gets filled with 600 hundred values like "file1", "file2" and so on 
End Sub

This is a potentially good approach (depending on OP's situation), although if the filenames are so buildable there is unlikely to be much reason to put them in an array. More likely than not any such array will be used in a loop which opens and processes the files in turn. If that is the case, you might as well cut the middle man out and create the filenames in the same loop that processes them — John Coleman, Jan 01 '17 at 14:40

score 0 · Answer 3 · answered Jan 01 '17 at 19:50

The amount of code that can be loaded into a form, class, or standard module is limited to 65,534 lines. A single line of code can consist of up to 1023 bytes. Up to 256 blank spaces can precede the actual text on a single line, and no more than twenty-four line-continuation characters ( _) can be included in a single logical line.

From VB6's Help.

Patrick Lepelletier · Answer 4 · 2017-01-01T20:34:05.653

when programming, you don't build an array this big mannually, never.

either you store each multiline-string inside a Cell, and at the end you buid the array like this :

option Explicit

Sub ArrayBuild ()
Dim Filenames() 'as variant , and yes i presume when using multi files, the variable name should have an "s"
With Thisworkbook.sheets("temp") 'or other name of sheet
    Max = .cells(.rows.count,1).end(xlup).row '=max number of rows in column 1
    Filenames = .range( .cells(1,1) , .cells(Max,1)).value2 ' this example uses a one column range from a1 to a[max] , but you could also use a multi column by changing the second .cells to `.cells(Max, ColMax)`
end with

'do stuff

erase Filenames 'free memory
End Sub

An other way is to build an array like you build a house by adding one brick at a time, like this :

Dim Filenames() as string 'this time you can declare as string, but not in the previous example
Dim i& 'counter
For i=1 to Max 'same max as in previous example, adapt code plz...
    redim Preserve Filenames (1 to ubound(filenames)+1) 'this is an example for unknown size array wich grows, but in your case you know the size (here Max, so you could declare it `Dim Filenames (1 to Max)` from the start, just wanted to show every option here.
    Filenames(i) = Cells (i,1).value2 'for example, but can be anything else. Also i'm beeing lazy and did not reference the cell to its sheet, wich i'd never do in actual coding...
 next i

EDIT i did re-read your Question, and it is even easier (basically because you ommited the bracets in your post and corrected it as comment...), use user3598756 's code plz. I thought File1 is a variable, when it should be written as "File1" .

EDIT 2 why bother build and array where Filename(x)="Filex" anyway? you know the result beforehand

Array of 600+ Strings in excel VBA

4 Answers4