ExcelBanter - 1D Array Problem (Subscript Out Of Range)

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - 1D Array Problem (Subscript Out Of Range) (https://www.excelbanter.com/excel-programming/365972-1d-array-problem-subscript-out-range.html)

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

1D Array Problem (Subscript Out Of Range)

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

1D Array Problem (Subscript Out Of Range)

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" wrote in message
...
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

1D Array Problem (Subscript Out Of Range)

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 the 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" wrote in message
...

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

1D Array Problem (Subscript Out Of Range)

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" wrote in message
...
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 the 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" wrote in message
...

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