CODEWITHSUNDEEP
excelvba
faucet37
faucet37

Reputation: 1

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

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!

Upvotes: 0

Views: 91

Answers (1)

jonadv
jonadv

Reputation: 492

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.

Upvotes: 2

Related Questions