1D Array Problem (Subscript Out Of Range)

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

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 wrote:
> 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
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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 wrote:
>> 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

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 wrote:
> 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" <[email protected]> wrote in message
> news:[email protected]...
>
>>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 wrote:
>>
>>>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
>
>
>

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

Thanks for your help.

Best regards

John

"witek" <[email protected]> wrote in message
news:[email protected]...
> 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 wrote:
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>>
>>>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 wrote:
>>>
>>>>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
>>
>>