1D Array Problem (Subscript Out Of Range)

[John]

Hi there,

I'm sure this is blindingly obviously but I have a UBound function returning
0 when I can see (in the locals window) that the array has been filled, so I
never get past the first loop of "For x.....". Here's the code:

Private Sub PopulateSheet(ByRef vFilesArray As Variant)

Dim iCol As Integer
Dim iStartRow As Integer
Dim wks As Worksheet

Set wks = Application.Worksheets(1)
iCol = 2
iStartRow = 5 'Starting row

For x = 0 To UBound(vFilesArray, 1)
wks.Cells(iStartRow + x, iCol).Value = vFilesArray(x)
Next x
End Sub

Can anyone tell me what I'm doing wrong please?

FYI - Locals window gives the following values:

- : vFilesArray(0) : : Variant/String(0 to 3)
: vFilesArray(0)(0) : "018.jpg" : String
: vFilesArray(0)(1) : "056.jpg" : String
: vFilesArray(0)(2) : "089.jpg" : String
: vFilesArray(0)(3) : "135.jpg" : String

Best regards

John

 

[witek]

So, is it one 1D or 2D array if you use vFilesArray(0)(0) ?

for me it looks like array with only one element: another array which
has three elements inside.

check exactly how you defines array which you pass as argument to
Populate sheet

 

[John]

Hi Witek,

Sorry not helpful without all the code. See below:

Thanks for your help

John


Public Sub GeneratePages()

Dim sImagesPath As String
Dim vImagesArray As Variant

'Get Images Array
sImagesPath = Application.Range("Image_Path").Value
vImagesArray = GetFileList(sImagesPath)
PopulateSheet (vImagesArray)

End Sub


Private Function GetFileList(ByRef sPath As String) As Variant

Dim fso As FileSystemObject
Dim fFile As File
Dim fldr As Folder
Dim sFileList As String

'Run through files in folder and return delimited array
Set fso = New FileSystemObject
Set fldr = fso.GetFolder(sPath)
For Each fFile In fldr.Files
Select Case Right(fFile.Name, 4)
Case ".jpg", ".jpe", "jpeg"
sFileList = sFileList & "," & fFile.Name
Case Else

End Select
Next fFile

Set fFile = Nothing
Set fso = Nothing

'Trim any leading commas
If Left(sFileList, 1) = "," Then
sFileList = Mid(sFileList, 2)
End If

GetFileList = Array(Split(sFileList, ","))

End Function


Private Sub PopulateSheet(ByRef vFilesArray As Variant)

Dim iCol As Integer
Dim iStartRow As Integer
Dim wks As Worksheet

Set wks = Application.Worksheets(1)
iCol = 2
iStartRow = 5 'Starting row

For x = 0 To UBound(vFilesArray, 1)
wks.Cells(iStartRow + x, iCol).Value = vFilesArray(x)
Next x
End Sub

 

[witek]

Change

GetFileList = Array(Split(sFileList, ","))

to

GetFileList = Split(sFileList, ",")


Split by itself returns array.
You did array of array like



{{"a", "b", "c" }}


so ubound ( {{"a", "b", "c" }}) is really 0.

There is only one element there: an array {"a", "b", "c" }

 

[John]

Perfect! Many thanks Witek. I ought to have spotted this from the locals
window as you did.

Thanks for your help.

Best regards

John