title: How do I create and pass string array to a sub in Excel VBA?
body: <p>VBA arrays are new to me and it seems like there are multiple ways to create string arrays.</p>
  
  <ul>
  <li>I believe I need to create a <a href="http://patorjk.com/programming/tutorials/vbarrays.htm" rel="nofollow noreferrer">dynamic array</a></li>
  <li>But I can't find any examples how to pass <a href="http://www.cpearson.com/excel/PassingAndReturningArrays.htm" rel="nofollow noreferrer">dynamic arrays</a> to a subroutine</li>
  </ul>
  
  <p>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.</p>
  
  <p>The thought process is as follows:</p>
  
  <ol>
  <li>Iterate through a list of user names</li>
  <li>Create a sheet for each</li>
  <li>Save each user name in an array as I iterate through</li>
  <li>In another Subroutine, select all the sheets I created and save as a PDF</li>
  </ol>
  
  <p>Below is my code. I'm getting Run-time error 9 - Subscript out of range (Referring to the array object)</p>
  
  <p>I appreciate any help! Thank you!</p>
  
  <pre><code>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
  </code></pre>
  
  <hr>
  
  <pre><code>Sub CreatePDF(FileDate As Date, ByRef SheetNames As Variant)
  
  Dim FilePath As String, FileName As String
  
  FilePath = Application.ActiveWorkbook.Path
  FileName = "Production Dashboards - " &amp; Format(FileDate, "mmddyy") &amp; ".pdf"
  
  
  ThisWorkbook.Sheets(SheetNames).Select
  
  ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:= _
      FileName, Quality:=xlQualityStandard, IncludeDocProperties:=True, _
       IgnorePrintAreas:=False, OpenAfterPublish:=True
  
  End Sub
  </code></pre>