array in vba

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

Check out the ReDim statement.

Quote:

---

Originally Posted by nst1107

Check out the ReDim statement.

---

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

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

Alternatively, you can use the collection or dictionary objects.

Where's the data coming from?

Im listing the files in a folder to an array

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]

Quote:

---

Originally Posted by 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]

---

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

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

but good old Dir does...

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]

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

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]?

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