Run-time Error 9: Subscript out of range

  • Thread starter Thread starter Patrick
  • Start date Start date
P

Patrick

I am working on a new workstation with a Word macro that opens an Excel
spreadsheet and copies a chart into Word. The chart to be copied could
be one of two names -- and the code used for checking works on other
workstations but not mine for some reason.

--snip--
1: On Error Resume Next
2: Set GraphExists = xlApp.Charts("Units Graph")
3:
4: If Err = 0 Then
5: ChartExists = True
6: Else
7: ChartExists = False
8: End If
9:
10:
11:If ChartExists = True Then
12: xlApp.Charts("Units Graph").Select
13: chTitle = "Unit Production Forecast"
14: chNameTab = "Units Graph"
15: GoTo CopyGraph
16:Else
17: xlApp.Charts("Funding Graph").Select
18: chTitle = "Funding Production Forecast"
19: chNameTab = "Funding Graph"
20: GoTo CopyGraph
21:End If
22:
23:CopyGraph:
....
--snip--


The Excel spreadsheet being opened has a chart named Funding Graph. On
other workstations it seems to faily silently and go to the Else
statement at line 16, but on one specific one it fails with "Run-time
Error 9: Subscript out of range" (because "Units Graph" does not
exist).

My question is: is there a setting on the workstation/ in Office etc
that may cause this code to work ok in some situations and throw an
exception in others? As a temporary workaround I have begun to
reference the chart by number as opposed to name, but may not always be
the first chart in a spreadsheet so would appreciate any insight into
why this may be happening or a way to cleanly check for a graph
existing (have researched this but not seen an obvious method -- this
may be a stupid question).

Thanks in advance.
 
With your workbook the active project in the VBE, then
in the vbe, go to Tools=>Options and under the general tab make sure you have

Break on Unhandled errors
checked instead of
Break on All Errors.
 
Break on Unhandled errors seems to have already been checked.

Thanks for the quick response though.

1
 
You checked on the problematic workstation?

you can always loop throught the charts collection and check the names.
 
Back
Top