Subscript Out Of range when selecting an array I've created

Question

I'm trying to create a single PDF file containing a sheet for each tab listed from cell J2 in my Control sheet. I get

Subscript Out Of Range error.

When I record the action I see that it creates an array of sheet names which it then selects to export, so I have a For loop which goes through the list and creates an array which adds to itself until it reaches the end of the list. The aim is to create one long string which I then select as an array.

The variable PDFArray displays a string of the tab names in what appears to be the correct format but when I get to the line Worksheets(Array(PDFarray)).Select I get the error.

I made sure the sheet names contain no undesirable characters or spaces.

Sub B_PDFs()

Dim PDFarray As String, PDFName as String, sht As String

Sheets("Control").Select

PLFile = ActiveWorkbook.Name
PDFLoc = Application.ActiveWorkbook.Path & "\"
PDFName = Range("A20")
PDFSheetCount = Range("J1").Offset(Rows.Count - 1, 0).End(xlUp).Row

'Loop through column J and create a string with each tab name to be exported
For x = 2 To PDFSheetCount Step 1
    
    If x = PDFSheetCount Then   
        sht = """ " & "" & Cells(x, 10) & """ "
    Else
        sht = """" & "" & Cells(x, 10) & """" & ", "
    End If
    PDFarray = PDFarray & sht
    
Next x

'Create PDF from the array above
Worksheets(Array(PDFarray)).Select   ' this is where I get the error Subscript Out Of Range
Selection.ExportAsFixedFormat Type:=xlTypePDF, Filename:=PFDLoc & PDFName, Quality:= _
  xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

Workbooks(PLFile).Activate

End Sub

Have you tried debug.print PDFarray after the loop? what value do you get? — Daghan, Dec 09 '19 at 12:05
`Array(PDFArray)` will return an array with a single element containing your entire `PDFArray` string. You should also place `Option Explicit` at the start of your module and explicitly declare all variables. And there is no need in any of your code for `Select` and `Selection`. Declare and use your worksheet variables explicitly. — Ron Rosenfeld, Dec 09 '19 at 12:16

score 1 · Accepted Answer · answered Dec 09 '19 at 13:08

I don't understand why MS makes NOT requiring variable declaration the default. Select Tools/Options/Editor and check Require Variable Declaration. This will place Option Explicit at the start of any new module. To correct this module, enter it manually at the beginning.

Doing so would have enabled you to find and correct a typo in your code.

You should also be avoiding Select, Selection and Activate. They rarely serve any purpose at all, and can cause multiple problems because they lull into avoiding explicit declarations of which workbook, worksheet, etc. you need. See How to avoid using Select in Excel VBA

However in using the ExportAsFixedFormat method to export selected worksheets, it seems Selection and ActiveSheet are required for it to work.

Array(str_variable) returns an array with a single entry that contains the entire string variable. It does not interpret the string variable so as to split it into separate elements.

So, rewriting your code somewhat (I will leave it to you to clean up the PDF document):

Option Explicit
Sub B_PDFs()

Dim PDFarray As Variant, PDFName As String, PLFile As String, PDFLoc As String
Dim wsControl As Worksheet
Dim WB As Workbook

'Consider wheter you want to use ThisWorkbook or a specific workbook
Set WB = ThisWorkbook
With WB
    Set wsControl = .Worksheets("Control")
    PLFile = .Name
    PDFLoc = .Path & "\"
End With

With wsControl
    PDFName = .Range("A20")

    'create PDFarray
    'This will be a 1-based 2D array starting at J1
    'If you need to start at J2, alter the initial cell
    PDFarray = .Range(.Cells(1, 10), .Cells(.Rows.Count, 10).End(xlUp))
End With

'convert to a 1D array
PDFarray = WorksheetFunction.Transpose(PDFarray)


'Note the use of `Select` and `ActiveSheet` when using this `ExportAsFixedFormat` method
Worksheets(PDFarray).Select
'Create PDF from the array above

    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDFLoc & PDFName, Quality:= _
        xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False


End Sub

Thank you very much for the answer and the feedback. The PDFs are now created as I'd hoped and I'll take your advice on best practice, and will follow up on the article you suggested. — SteveJ, Dec 09 '19 at 14:38

score 0 · Answer 2 · answered Dec 09 '19 at 13:05

What @RonRosenfeld has suggested is correct about select and selection. The expression you are building is string whereas, Excel expects it to be real array.

So in principle an approach like below shall work for you which will create an array for processing and can be used as you want to utilise.

Dim shtNames As Variant
Dim pdfArray
shtNames = Range("J2:J" & Range("J1").Offset(Rows.Count - 1, 0).End(xlUp).Row).Value
pdfArray = Application.Transpose(shtNames)

Subscript Out Of range when selecting an array I've created

2 Answers2