Clever Geek Handbook

How to create and pass array of strings to sub in Excel VBA?

VBA arrays are new to me, and it seems that there are several ways to create arrays of strings.

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

I know how many elements should be in the array by User range count (so maybe I don't need a dynamic array?). I am having trouble passing an array to another routine.

The thought process looks like this:

  • Iterating through a list of usernames
  • Create a sheet for everyone
  • Store each username in an array as I iterate
  • In another subroutine select all sheets I created and save them as PDF

Below is my code. I am getting runtime error 9 - index out of range (refers to an array object)

I appreciate any help! Thank!

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
+3


source to share


3 answers


The array parameter is not a problem, it was passed to the method correctly CreatePDF(...)

. The parameter type can be changed to SheetNames() As String

, but SheetNames As Variant

also works.

Then it Run-time error 9 - Subscript out of range

occurs here ThisWorkbook.Sheets(SheetNames).Select

because the array SheetNames

contains an invalid sheet name, this is the very first element. This element is an empty string and an empty string is not valid as a sheet name.

enter image description here



At the index, the loop For Next

starts at value 1

, but the array starts at 0

. This way the very first element of the array SheetNames

is left untouched and is finally an empty string. To solve this problem, set the lower border ReDim

explicitly to 1

. NTN

(Note: if you omit the lower border, it is used Option Base

, and if not specified Option Base

, then it is used 0

.)

'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
+2


source


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

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

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



Edited from dee's post: You can put an array of sheet names in .Sheets()

, but no blank lines. So, in your sub-CreateAllDashboards do the following:

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

and you can read about arrays in VBA.

0


source


I tested the following using a one page workbook with a range of Users and another FileDate file. He does what you asked.

The error Run-time error 9 - Subscript out of range

is caused by a reference to an array element. ThisWorkbook.Sheets(SheetNames).Select

will throw an error, but ThisWorkbook.Sheets(SheetNames(x)).Select

won't (as long as x is initialized and is within 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 example demonstrates how to parse an array to another under the requested request, but you can also easily integrate the CreatePDF code into the calling sub.

-1


source






More articles:


All Articles