Subscript Out of Range Error when printing from array

Nuke_It_Newport

New Member
Joined
Nov 17, 2020
Messages
13
Office Version
  1. 2013
Platform
  1. Windows
Hey everyone-
I have a workbook with a custom ribbon. When checkbox controls in the ribbon corresponding to weekdays are checked, the code is supposed to add the sheet names to an array, skipping the non selected sheets. When the Print button in the ribbon is clicked, It is then supposed to print the sheets from the array. It is giving me a "Subscript Out Of Range" error when Sub btns5_btn11_onAction is run. Here's the code.
Can anyone see anything obviously wrong with my code? Thanks!

VBA Code:
Dim shtArr(6) As Variant

'Callback for chkBoxMon onAction 'Monday Check Box
Sub chkBoxMon_onAction(control As IRibbonControl, MonSel As Boolean) 'Monday
If MonSel Then
    shtArr(0) = "Sheet1"
    Debug.Print (shtArr(0) & " Selected")
Else
    shtArr(0) = ""
     Debug.Print ("Sheet 1 Not Selected")
End If
End Sub

'Callback for chkBoxTue onAction 'Tuesday Check Box
Sub chkBoxTue_onAction(control As IRibbonControl, TueSel As Boolean) 'Tuesday
If TueSel Then
    shtArr(1) = "Sheet2"
    Debug.Print (shtArr(1) & " Selected")
Else
    shtArr(1) = ""
    Debug.Print ("Sheet 2 Not Selected")
End If
End Sub

'Callback for chkBoxWed onAction 'Wednesday Check Box
Sub chkBoxWed_onAction(control As IRibbonControl, WedSel As Boolean) 'Wednesday
  If WedSel Then
    shtArr(2) = "Sheet3"
    Debug.Print (shtArr(2) & " Selected")
Else
    shtArr(2) = ""
    Debug.Print ("Sheet 3 Not Selected")
End If

End Sub

'Callback for chkBoxThu onAction 'Thursday Check Box
Sub chkBoxThu_onAction(control As IRibbonControl, ThuSel As Boolean) 'Thursday
  If ThuSel Then
    shtArr(3) = "Sheet4"
    Debug.Print (shtArr(3) & " Selected")
Else
    shtArr(3) = ""
    Debug.Print ("Sheet 4 Not Selected")
End If
  
End Sub

'Callback for chkBoxFri onAction 'Friday Check Box
Sub chkBoxFri_onAction(control As IRibbonControl, FriSel As Boolean) 'Friday
  If FriSel Then
    shtArr(4) = "Sheet5"
    Debug.Print (shtArr(4) & " Selected")
Else
    shtArr(4) = ""
    Debug.Print ("Sheet 5 Not Selected")
End If
  
End Sub

'Callback for chkBoxSE onAction 'Standby Electricians Check Box
Sub chkBoxSE_onAction(control As IRibbonControl, SESel As Boolean) 'Standby Electricians
 If SESel Then
    shtArr(5) = "Sheet6"
    Debug.Print (shtArr(5) & " Selected")
Else
    shtArr(5) = ""
    Debug.Print ("Sheet 6 Not Selected")
End If

End Sub

'Callback for chkBoxSS onAction 'Standby Supervisor Check Box
Sub chkBoxSS_onAction(control As IRibbonControl, SSSel As Boolean) 'Standby Supervisors
  If SSSel Then
    shtArr(6) = "Sheet7"
    Debug.Print ("Sheet 7 Selected")
Else
    shtArr(6) = ""
    Debug.Print (shtArr(6) & " Not Selected")
End If
End Sub

Sub btns5_btn11_onAction(control As IRibbonControl) 'Print Button on Ribbon

ReDim newArr(LBound(shtArr) To UBound(shtArr))

For i = LBound(shtArr) To UBound(shtArr)

    If shtArr(i) <> "" Then
        J = J + 1
        newArr(J) = shtArr(i)
    End If
Next i
ReDim Preserve newArr(LBound(shtArr) To J)

Sheets(newArr).PrintOut Copies:=1, Preview:=True, Collate:=True

End Sub
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

kanadaaa

Active Member
Joined
Dec 29, 2019
Messages
343
Office Version
  1. 365
Platform
  1. Windows
No variable declaration for newArr?
 

diddi

Well-known Member
Joined
May 20, 2004
Messages
3,297
Office Version
  1. 2010
Platform
  1. Windows
hi and welcome to MrExcel.
it would be helpful to know which line the code stops at.
i suspect the error is in this line:
Sheets(newArr).PrintOut Copies:=1, Preview:=True, Collate:=True
because newArr should actually be newArr(i) or whatever loop variable you use. there is really little piont to creating newArr at all unless it is used elsewhere because you could have printed straight from the loop anyway

VBA Code:
For i = LBound(shtArr) To UBound(shtArr)
    If shtArr(i) <> "" Then
        Sheets(shtArr(i)).PrintOut Copies:=1, Preview:=True, Collate:=True
    End If
Next i
 

Nuke_It_Newport

New Member
Joined
Nov 17, 2020
Messages
13
Office Version
  1. 2013
Platform
  1. Windows
hi and welcome to MrExcel.
it would be helpful to know which line the code stops at.
i suspect the error is in this line:
Sheets(newArr).PrintOut Copies:=1, Preview:=True, Collate:=True
because newArr should actually be newArr(i) or whatever loop variable you use. there is really little piont to creating newArr at all unless it is used elsewhere because you could have printed straight from the loop anyway

VBA Code:
For i = LBound(shtArr) To UBound(shtArr)
    If shtArr(i) <> "" Then
        Sheets(shtArr(i)).PrintOut Copies:=1, Preview:=True, Collate:=True
    End If
Next i
Thanks. You are correct, the error is in the last line of the code. The "newArr" array is used to eliminate any unselected sheets in the array. I've tried changing the last code line to what you suggested, and I still get the error. I obviously don't understand how to use arrays as well as I need to. My understanding is that I print from an array, with code like "Sheets(Sheet1, Sheet2, Sheet3).PrintOut" I'm trying to loop through the sheets, and print the ones that are selected through the checkboxes in the custom ribbon controls. Am I overcomplicating things for myself?
 

Nuke_It_Newport

New Member
Joined
Nov 17, 2020
Messages
13
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

No variable declaration for newArr?
Good catch! I added Dim newArr() as Variant, but I still get the same error on the last line of the code.
 

Nuke_It_Newport

New Member
Joined
Nov 17, 2020
Messages
13
Office Version
  1. 2013
Platform
  1. Windows
I changed my code a little bit after doing some debugging. Here's the code, along with a screenshot of the output.
VBA Code:
'Callback for btns5_btn11 onAction

Sub btns5_btn11_onAction(control As IRibbonControl) 'Print
'   i is counter to run through check boxes
'   J  is array position for selected sheets

ReDim newArr(LBound(shtArr) To UBound(shtArr))

For i = LBound(shtArr) To UBound(shtArr) 'Number of available sheets

    If shtArr(i) <> "" Then 'Checkbox is checked, sheet is selected
        
        newArr(J) = (shtArr(i))
        Debug.Print ("newArr Position " & J & "=" & shtArr(i))
        J = J + 1 'Go to next newArr Position
    End If
Next i 'Get state of next checkbox
ReDim Preserve newArr(LBound(shtArr) To J)

Sheets(newArr).PrintOut Copies:=1, Preview:=True, Collate:=True

End Sub

Can anybody tell me what I'm doing wrong here? If you look at the screenshot, the code is removing the empty slots from array "shtArr". The array "newArr is the list of selected sheets with the empty slots removed. The yellow line in the screenshot is where the code is throwing an error. I hope this paints a more clear picture of what is going on. Thanks!
 

Attachments

  • Excel Screenshot.png
    Excel Screenshot.png
    44.8 KB · Views: 3

diddi

Well-known Member
Joined
May 20, 2004
Messages
3,297
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

i think you misunderstood my post...

this is the whole thing
VBA Code:
Sub btns5_btn11_onAction(control As IRibbonControl) 'Print
'   i is counter to run through check boxes
    For i = LBound(shtArr) To UBound(shtArr)
        If shtArr(i) <> "" Then
            Sheets(shtArr(i)).PrintOut Copies:=1, Preview:=True, Collate:=True
        End If
    Next i
End Sub
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,696
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
You are adding 1 to J after populating the item. That means that at the end, J is 1 more than the index you actually need, so you should actually redim to J - 1. (you had the opposite problem before, in that item 0 was always "")
 

Nuke_It_Newport

New Member
Joined
Nov 17, 2020
Messages
13
Office Version
  1. 2013
Platform
  1. Windows
i think you misunderstood my post...

this is the whole thing
VBA Code:
Sub btns5_btn11_onAction(control As IRibbonControl) 'Print
'   i is counter to run through check boxes
    For i = LBound(shtArr) To UBound(shtArr)
        If shtArr(i) <> "" Then
            Sheets(shtArr(i)).PrintOut Copies:=1, Preview:=True, Collate:=True
        End If
    Next i
End Sub
I sure did... Thanks for clarifying that. I replaced my code completely with yours, and it still errors out on the same step. See attached screenshot. Could there be anything else, such as how I Dim the array (Dim shtArr(6) As Variant)? Thanks for your help!
 

Attachments

  • Excel Screenshot 2.png
    Excel Screenshot 2.png
    105.8 KB · Views: 1

Nuke_It_Newport

New Member
Joined
Nov 17, 2020
Messages
13
Office Version
  1. 2013
Platform
  1. Windows
i think you misunderstood my post...

this is the whole thing
VBA Code:
Sub btns5_btn11_onAction(control As IRibbonControl) 'Print
'   i is counter to run through check boxes
    For i = LBound(shtArr) To UBound(shtArr)
        If shtArr(i) <> "" Then
            Sheets(shtArr(i)).PrintOut Copies:=1, Preview:=True, Collate:=True
        End If
    Next i
End Sub
I sure did... Thanks for clarifying that. I replaced my code completely with yours, and it still errors out on the same step. See attached screenshot. Could there be anything else, such as how I Dim the array (Dim shtArr(6) As Variant)? Thanks for your help!
You are adding 1 to J after populating the item. That means that at the end, J is 1 more than the index you actually need, so you should actually redim to J - 1. (you had the opposite problem before, in that item 0 was always "")
Thank you! This made the output what I would expect it to be, with the blanks removed from the array, but the code still errors on the same line. See screenshot. Any idea what else could be wrong? Thanks!
 

Attachments

  • Excel Screenshot 3.png
    Excel Screenshot 3.png
    99.6 KB · Views: 1

Watch MrExcel Video

Forum statistics

Threads
1,129,515
Messages
5,636,793
Members
416,941
Latest member
shazzaxyz

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top