array in vba

[sw21]

Hi,

Is it possible to fill an array (variant type) without knowing its size ?
for instance, in c++ you have push_back() from stl, is there a solution in vba ?

thanks in advance

[nst1107]

Check out the ReDim statement.

[sw21]

Check out the ReDim statement.

I have seen, but I will not resize each time I had a string .. isnt it dirty ?

[nst1107]

That's the only way I know to fill an array of undetermined size.

[nst1107]

Alternatively, you can use the collection or dictionary objects.

[p45cal]

Where's the data coming from?

[sw21]

Im listing the files in a folder to an array

[Benzadeus]

Well, do you have Excel 2003?

[VBA]Option Explicit
Option Base 1
Sub ListFolders()
Dim n As Long
Dim arFolders() As String

With Application.FileSearch
.LookIn = "C:\Documents and Settings\felipe.gualberto\Desktop\Felipe\VBA"
.FileType = msoFileTypeAllFiles
.Execute
ReDim arFolders(.FoundFiles.Count)

For n = 1 To .FoundFiles.Count
arFolders(n) = .FoundFiles(n)
Next n

End With
Debug.Print UBound(arFolders)
End Sub[/VBA]

[sw21]

Well, do you have Excel 2003?

[vba]Option Explicit
Option Base 1
Sub ListFolders()
Dim n As Long
Dim arFolders() As String

With Application.FileSearch
.LookIn = "C:\Documents and Settings\felipe.gualberto\Desktop\Felipe\VBA"
.FileType = msoFileTypeAllFiles
.Execute
ReDim arFolders(.FoundFiles.Count)

For n = 1 To .FoundFiles.Count
arFolders(n) = .FoundFiles(n)
Next n

End With
Debug.Print UBound(arFolders)
End Sub[/vba]

I can not test now, but the vba code should work for every version of excel

[Benzadeus]

No, the object FileSearch does not work in Excel 2007+.

[rbrhodes]

but good old Dir does...

[mdmackillop]

Do you really need an array?
[vba]
Sub ListFolders()
Dim arFolders As Object, a, i As Long

With Application.FileSearch
.LookIn = "C:\AAA"
.FileType = msoFileTypeAllFiles
.Execute
Set arFolders = .FoundFiles
End With

For Each a In arFolders
Debug.Print a
Next
'or
For i = 1 To arFolders.Count
Debug.Print arFolders(i)
Next
End Sub

[/vba]

[sw21]

this is in a function that returns the list of the file of a folder, so yes I think.

[Benzadeus]

MD,

[VBA] Set arFolders = .FoundFiles [/VBA]

I didn't think that =)

Is it possible to change
[VBA] For Each a In arFolders
Debug.Print a
Next [/VBA]

For something like
[VBA] Debug.Print Join(arFolders, vbCrLf)[/VBA]?

[Paul_Hossler]

A common technique I've seen uses Redim Preserve to keep resizing the array as you add elements

[VBA]
Sub Demo()
Dim a() As Variant
Dim n As Long, iPretendThisIsaFile As Long

n = 0

For iPretendThisIsaFile = 1 To 10
n = n + 1
ReDim Preserve a(1 To n)

a(iPretendThisIsaFile) = "This is File #" & iPretendThisIsaFile

Next iPretendThisIsaFile

For n = LBound(a) To UBound(a)
Debug.Print a(n)
Next n
End Sub
[/VBA]

Paul