Below is my code. 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. 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.

I would change this: Sub CreatePDF FileDate As Date, ByRef SheetNames As Variant. Sub CreatePDF FileDate As Date, SheetNames As String. But your problem is at this line: You can put an array of sheet names in.

Excel VBA: Passing arrays to Functions not working properly. : excel

Sheets but without empty rows. So in your sub "CreateAllDashboards" do this:. 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. Select will throw an error but ThisWorkbook.

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. By posting your answer, you agree to the privacy policy and terms of service. Stack Overflow Questions Explore Jobs Documentation beta Tags Users. Sign up or log in to customize your list.

Tour Start here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us. Log In Sign Up. Join the Stack Overflow Community. Stack Overflow is a community of 7.

Excel: Passing Array to User Defined Function (VBA) - Stack Overflow

Join them; it only takes a minute: 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. 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.

Barrett Kuethen 4 12 Barrett Kuethen i am glad it helped!

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: So in your sub "CreateAllDashboards" do this: Value and you could read that about arrays in VBA. We learn everyday, thanks dee: Exactly, that is my case as well: Count With Worksheets Worksheets.

How to: Pass an Array to a Procedure or Property (Visual Basic)

Mark Fitzgerald 2, 3 12 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. The Run-time error 9 is thrown by the empty line in SheetNames array. Sign up or log in StackExchange. Sign up using Facebook.

Sign up using Email and Password. Post as a guest Name.

Developers Who Use Spaces Make More Money Than Those Who Use Tabs. Stack Overflow works best with JavaScript enabled. Sheets Array "Sheet4", "Sheet5" i valid, have a look e. MathOverflow Mathematics Cross Validated stats Theoretical Computer Science Physics Chemistry Biology Computer Science Philosophy more 3. Meta Stack Exchange Stack Apps Area 51 Stack Overflow Talent.