VBA subscript out of range error when using entire array but not when using specific elements from the array

Question

This is supposed to be a relatively simple function - an excel workbook with a selection of sheets to be filled out. If the user chooses to omit some of the sheets they will not be included in the final function.

It also hides a couple of columns in specific sheets which contain information not required on the exported format.

Sub Export()


Dim i As Integer, c As Integer, f As Integer, g As Integer, h As Integer, numberofsheets As Integer

' i is for counting total number of build sheets
' c is used in the while loop to track the current sheet the code is running on
' f is used to denote the row on the excel sheet currently look at
' g tracks the current sheet out of number of sheets marked for build
' h tracks consecutive sheets so the code doesnt run on the same one multiple times
' numberofsheets is the number of sheets required for final build

'printsheets is an array to denote which final sheets are to be exported

Dim sheetname As String             'name of current sheet

numberofsheets = Range("AC2")
ReDim printsheets(numberofsheets) As Variant

h = 1
i = Range("AC3").Value
For g = 1 To numberofsheets                 ' for loop run to create an array equal to number of sheets denoted for build
    c = h
    While (c < i)                           ' while loop to track which sheet is currently looked at
        f = (4 + c)
        sheetname = Range("AC" & f)
            If Range("AB" & f) = "Yes" Then     ' determines whether sheet is used for build
            printsheets(g - 1) = sheetname      ' adds sheet to array if used for build
                If Not sheetname = ("Quality Final") Then           'hides the button columns for basic build sheets, not quality final
                Worksheets(sheetname).Columns("L").EntireColumn.Hidden = True
                h = h + 1
                c = i
                Else: End If
            Else:
            h = h + 1
            c = c + 1
            End If
    Wend
Next g



ThisWorkbook.Sheets(printsheets()).Select


            ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, filename:= _
            "C:\Build Sheets\MW\CT\PDF\" & ThisWorkbook.Name & ".pdf" _
            , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
            :=False, OpenAfterPublish:=True



Sheets("Main Menu").Select
End Sub

the top part functions perfectly, it hides columns, and through testing I can see the array fills in properly. The problem comes on the select line.

If I specify a specific element of the array e.g. printsheets(3) then the function exports fine, but if I try and select multiple, or the entire array with printsheets() I get runtime error 9, subscript out of range.

I can't figure out where the problem is because I can select part of the array, but never the whole lot, which defeats the point of exporting everything into 1 sheet.

Pretty new to VBA so sorry if i missed something obvious!

Double check that the sheet names in the array are spelled correctly - no typos, trailing spaces, etc. — BigBen
– BigBen, Commented Jan 30, 2020 at 15:23
The only way I can reproduce your error is if you've got one of the sheet names wrong. — BigBen
– BigBen, Commented Jan 30, 2020 at 15:28

jonadv · Accepted Answer · 2020-01-30 15:20:57Z

2

When the code runs into an error, choose debug and find the reference that is trying to be found within the array.

For example in the code put the cursor on 'numberOfsheet' if that is the array the code is getting stuck on), right click mouse and choose Add whatch. By holding the mouse over 'g' (the itemnumber in the array) it should show the number it is currently running. If its not, write in the debug window (ctrl + g) "Debug.Pring g" and hit enter to find out at which item of the array the code is running exactly. With that number you can find at which numberOfsheet (via add whatch) the code is stuck.

Likely this way you will find that the number (i, c, f, g, h or i) is larger then the array it is trying to find a reference in.

edited Jan 30, 2020 at 15:20

answered Jan 30, 2020 at 15:14

jonadv

5027 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

19 Comments

BigBen Over a year ago

Except the problematic line is ThisWorkbook.Sheets(printsheets()).Select - which has no index.

jonadv Over a year ago

So thats the problem.

BigBen Over a year ago

Yes, very possible: stackoverflow.com/questions/14404650/…. This is an instance where you want to use ActiveSheet.

jonadv Over a year ago

printsheets(g - 1) = sheetname --> is it possible it is adding all the sheets or empty sheet names to the array?

BigBen Over a year ago

@faucet37 - add a new loop and just Debug.Print all the sheet names in the array. Most likely you've got something in the array that shouldn't be there, or is misspelled.

|

Collectives™ on Stack Overflow

VBA subscript out of range error when using entire array but not when using specific elements from the array

1 Answer 1

19 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

19 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related