CODEWITHSUNDEEP
arraysvbaexcel
Barrett Kuethen
Barrett Kuethen

Reputation: 1914

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

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

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

Upvotes: 3

Views: 14963

Answers (3)

gembird
gembird

Reputation: 14053

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

Upvotes: 2

Mark Fitzgerald
Mark Fitzgerald

Reputation: 3068

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.

Upvotes: -1

simpLE MAn
simpLE MAn

Reputation: 1622

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.

Upvotes: 0

Related Questions