PDA

View Full Version : array in vba



sw21
09-03-2009, 09:59 AM
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
09-03-2009, 10:22 AM
Check out the ReDim statement.

sw21
09-03-2009, 10:50 AM
Check out the ReDim statement.

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

nst1107
09-03-2009, 11:27 AM
That's the only way I know to fill an array of undetermined size.

nst1107
09-03-2009, 11:28 AM
Alternatively, you can use the collection or dictionary objects.

p45cal
09-03-2009, 12:09 PM
Where's the data coming from?

sw21
09-03-2009, 12:41 PM
Im listing the files in a folder to an array

Benzadeus
09-03-2009, 01:08 PM
Well, do you have Excel 2003?

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

sw21
09-03-2009, 01:13 PM
Well, do you have Excel 2003?

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

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

Benzadeus
09-03-2009, 03:24 PM
No, the object FileSearch does not work in Excel 2007+.

rbrhodes
09-03-2009, 06:43 PM
but good old Dir does...

mdmackillop
09-04-2009, 06:12 AM
Do you really need an array?

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

sw21
09-04-2009, 06:38 AM
this is in a function that returns the list of the file of a folder, so yes I think.

Benzadeus
09-04-2009, 07:30 AM
MD,

Set arFolders = .FoundFiles

I didn't think that =)

Is it possible to change
For Each a In arFolders
Debug.Print a
Next

For something like
Debug.Print Join(arFolders, vbCrLf)?

Paul_Hossler
09-04-2009, 02:20 PM
A common technique I've seen uses Redim Preserve to keep resizing the array as you add elements


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


Paul