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:

Iterate through a list of user names
Create a sheet for each
Save each user name in an array as I iterate through
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

gembird · Accepted Answer · 2014-12-31 09:30:42Z

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.

enter image description here

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

simpLE MAn · Accepted Answer · 2014-12-31 02:11:20Z

0

I would change this:
Sub CreatePDF(FileDate As Date, ByRef SheetNames As Variant)

To this:
Sub CreatePDF(FileDate As Date, SheetNames() As String)

But your problem is at this line:
ThisWorkbook.Sheets(SheetNames).Select

Edited from dee's comment: You can put an array of sheet names in .Sheets() but without empty rows. So in your sub "CreateAllDashboards" do this:

ReDim Preserve SheetNames(i - 1)
SheetNames(i - 1) = UserNameRangeStart.Offset(i, 0).Value

and you could read that about arrays in VBA.

edited Dec 31, 2014 at 2:11

answered Dec 31, 2014 at 1:58

simpLE MAn

1,62214 silver badges23 bronze badges

2 Comments

gembird Over a year ago

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

gembird Over a year ago

Exactly, that is my case as well :-)

Mark Fitzgerald · Accepted Answer · 2014-12-31 02:57:19Z

I've tested the following using a single sheet workbook with a range named Users and another named FileDate. It does what you asked.

The reason for the Run-time error 9 - Subscript out of range error is that you have to reference the array element. ThisWorkbook.Sheets(SheetNames).Select will throw an error but ThisWorkbook.Sheets(SheetNames(x)).Select won't (as long as x is initialised and within the bounds of the array)

Sub PopulateArray()
Dim user As Range
Dim SheetNames As Variant

    ReDim SheetNames(1 To 1) 'Initialise the array
    For Each user In [Users]
        ThisWorkbook.Sheets.Add After:=Worksheets(Worksheets.Count)
        With Worksheets(Worksheets.Count)
            .Name = user.Value2
            .[A1] = user.Value2 'you can't print a blank sheet!
        End With
        SheetNames(UBound(SheetNames)) = user.Value2
        ReDim Preserve SheetNames(1 To UBound(SheetNames) + 1)
    Next user
    ReDim Preserve SheetNames(1 To UBound(SheetNames) - 1) 'Delete the last element
    Call CreatePDF([FileDate], SheetNames)
End Sub

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

Dim FilePath As String, FileName As String
Dim x As Long

    FilePath = Application.ActiveWorkbook.Path & "\" 'Note backslash added to path.
    FileName = "Amtec Production Dashboards - " & Format(FileDate, "mmddyy")

    For x = 1 To UBound(SheetNames)
        ThisWorkbook.Sheets(SheetNames(x)).ExportAsFixedFormat Type:=xlTypePDF, FileName:= _
            FileName & SheetNames(x) & ".pdf", Quality:=xlQualityStandard, IncludeDocProperties:=True, _
             IgnorePrintAreas:=False, OpenAfterPublish:=True
    Next x
End Sub

The above demonstrates how to parse an array to another sub as requested but you could integrate the CreatePDF code into the calling sub fairly easily too.

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.

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related