How do I create and pass string array to a sub in Excel VBA?

Question

VBA arrays are new to me and it seems like there are multiple ways to create string arrays.

• I believe I need to create a dynamic array
• But I can't find any examples how to pass dynamic arrays to a subroutine

I know how many items there need to be in the array by the count of the User range (so maybe I don't need a dynamic array??). I'm having trouble passing the array through to another Subroutine.

The thought process is as follows:

1. Iterate through a list of user names
2. Create a sheet for each
3. Save each user name in an array as I iterate through
4. In another Subroutine, select all the sheets I created and save as a PDF

Below is my code. I'm getting Run-time error 9 - Subscript out of range (Referring to the array object)

I appreciate any help! Thank you!

Sub CreateAllDashboards(StartDate As Date, EndDate As Date)
'Used to iterate through the list of users and call the Sub to create Dashboards

Dim UserNameRangeStart As Range
Set UserNameRangeStart = Range("UserName")
Dim SheetNames() As String

'Cyle through users
For i = 1 To GetUserNameRange().CounT
    'Some code
    ReDim Preserve SheetNames(i)
    SheetNames(i) = UserNameRangeStart.Offset(i, 0).Value
Next i

Call CreatePDF(EndDate, SheetNames) 'Also tried SheetNames()

End Sub

---

Sub CreatePDF(FileDate As Date, ByRef SheetNames As Variant)

Dim FilePath As String, FileName As String

FilePath = Application.ActiveWorkbook.Path
FileName = "Production Dashboards - " & Format(FileDate, "mmddyy") & ".pdf"


ThisWorkbook.Sheets(SheetNames).Select

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:= _
    FileName, Quality:=xlQualityStandard, IncludeDocProperties:=True, _
     IgnorePrintAreas:=False, OpenAfterPublish:=True

End Sub

Solution

The array parameter is not a problem it is passed correctly to method CreatePDF(...). The parameter type can be changed to SheetNames() As String but SheetNames As Variant works as well.

Then the Run-time error 9 - Subscript out of range is raised here ThisWorkbook.Sheets(SheetNames).Select because the array SheetNames contains invalid sheet name, which is the very first item. This item is an empty string and empty string is not valid as a sheet name.

In the For Next loop index starts with value 1 but the array starts with 0. So the very first item of the array SheetNames remains untouched and is finally an empty string. To solve it set the lower bound in ReDim explicitly to 1. HTH

(Note: if you omit lower bound then Option Base is used and if no Option Base is specified then 0 is used.)

'Cyle through users
For i = 1 To GetUserNameRange().Count
    'Some code
    ReDim Preserve SheetNames(1 To i)
    SheetNames(i) = UserNameRangeStart.Offset(i, 0).value
Next i

Comments

[Daniel Dušek]

Sheets(Array("Sheet4", "Sheet5")) i valid, have a look e.g. here: msdn.microsoft.com/en-us/library/office/…

[Daniel Dušek]

Exactly, that is my case as well :-)

[simpLE MAn]

I started my answer the same way but @dee pointed me to this to show that you can, surprisingly I must say, reference an array within .Sheets(). The Run-time error 9 is thrown by the empty line in SheetNames array.

[Barrett Kuethen]

Thanks @dee. That's what I needed!

[Daniel Dušek]

@Barrett Kuethen i am glad it helped!