View Full Version : 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
nst1107
09-03-2009, 10:22 AM
Check out the ReDim statement.
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?
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
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
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.