Weird Error

[guiltyberto]

I keep getting this weird error: "Method 'Values' of object 'Series' failed" I don't know what is causing it, but here is my code:

Private Sub initChart(filename)

Dim monthName As String
Dim monthRange As Range


monthName = getMonthName(filename)
Set monthRange = getMonthRange(filename)
getLabels (filename)


Set foreChart = Charts.Add


With foreChart
    .ChartType = xlColumnClustered
    .Location where:=xlLocationAsNewSheet, Name:="Forecast Chart"
    .SeriesCollection.NewSeries
    .SeriesCollection(1).Name = monthName
    'MsgBox ("Got here 1")
    .SeriesCollection(1).Values = monthRange      'error here
    .SeriesCollection(1).XValues = monthLabels
    .HasTitle = True
    .ChartTitle.Text = "forecast chart"
End With
End Sub

Thanks in advance!

I also occasionally get the error "Automation error, the object invoked has disconnected from it's clients" if someone could explain what that means/show me what's causing it that would be pretty awesome too.

 

[NeonRedSharpie]

Well, your month range is probably not a real range. The range needs to be a grouping of cells. monthRange, currently, is the exact same value as monthName.

 

[guiltyberto]

I think my monthRange is a real range, how could it be the same value as monthName?

This is what my getMonthRange function looks like:

Private Function getMonthRange(filename) As Range

Dim wb As Workbook


Application.ScreenUpdating = False
Application.DisplayAlerts = False


Set wb = Workbooks.Open(filename, True, True)
Set getMonthRange = wb.Worksheets("Summary-Region").Range("D25:F25,H25:J25,L25:N25,P25:R25")


wb.Close False ' close the source workbook without saving any changes
Set wb = Nothing ' free memory


Application.ScreenUpdating = True
Application.DisplayAlerts = True


End Function

 

[joeu2004]

I keep getting this weird error: "Method 'Values' of object 'Series' failed" I don't know what is causing it, but here is my code:

Private Sub initChart(filename)
Dim monthName As String
Dim monthRange As Range
monthName = getMonthName(filename)
Set monthRange = getMonthRange(filename)
getLabels (filename)
Set foreChart = Charts.Add
With foreChart
    .ChartType = xlColumnClustered
    .Location where:=xlLocationAsNewSheet, Name:="Forecast Chart"
    .SeriesCollection.NewSeries
    .SeriesCollection(1).Name = monthName
    'MsgBox ("Got here 1")
    .SeriesCollection(1).Values = monthRange      'error here
    .SeriesCollection(1).XValues = monthLabels
    .HasTitle = True
    .ChartTitle.Text = "forecast chart"
End With
End Sub

Private Function getMonthRange(filename) As Range
Dim wb As Workbook
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set wb = Workbooks.Open(filename, True, True)
Set getMonthRange = wb.Worksheets("Summary-Region").Range("D25:F25,H25:J25,L25:N25,P25:R25")
wb.Close False ' close the source workbook without saving any changes
Set wb = Nothing ' free memory
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Function

First, I wonder if your perception is wrong about where the error occurs. Did you single-step using F8?

I don't see where the variable monthLabels is declared, much less assigned to. On the other hand, if you do not have Option Explicit set (you should!), monthLabels might be an empty type Variant. So that might not be a VBA error, although it might not be what you intended.

Second, what happens if you comment out wb.Close in getMonthRange; i.e. do not close the workbook. I wonder if Chart.SeriesCollection must refer to an open workbook.

In fact, I wonder if you need ....=monthRange.Address(external:=True). Usually, we assign a string to Chart.SeriesCollection.

And I'm not sure that would work with an external range in any case. TBD.

 

[guiltyberto]

monthLabels is a global variable:

 Dim monthLabels as Range

declared at the beginning of my program.

Can you explain Option Explicit and =monthRange.Address(external:=True)? Also, I will try not closing the workbook.

Thanks for your help!

 

[joeu2004]

Can you explain Option Explicit

A statement that you put at the top of any (all) modules.

It requires a global or local declaration for all variables used in the procedure. So it helps to avoid referencing uninitialized variables due to mistyping their name.

Alternatively, in VBA, click on Tools, Options, Editor tab, and select "Require variable declaration".

That causes VBA to create the Option Explicit statement each time you create a new module. But it will not put the statement into pre-existing modules.

Can you explain [...] =monthRange.Address(external:=True)?

A misdirection. Sorry. Ostensibly, your syntax should work, namely:

.SeriesCollection(1).Values = monthRange
.SeriesCollection(1).XValues = monthLabels

where monthRange and monthLabels are type Range and properly set.

Also, I will try not closing the workbook.

That does seem to make a difference in my experiments.

 

[shg]

So it helps to avoid referencing uninitialized variables ...

It prevents you from referencing undeclared variables, or declared variables not having scope within the procedure in which they appear.

 

[guiltyberto]

So would this work:

Private Function getMonthRange(filename) As Range

Dim wb As Workbook


Application.ScreenUpdating = False
Application.DisplayAlerts = False


Set wb = Workbooks.Open(filename, True, True)
getMonthRange = wb.Worksheets("Summary-Region").Range("D25:F25,H25:J25,L25:N25,P25:R25")


wb.Close False ' close the source workbook without saving any changes
Set wb = Nothing ' free memory


Application.ScreenUpdating = True
Application.DisplayAlerts = True


End Function

Or do I need "Set" in front of "getMonthRange = wb.Worksheets("Summary-Region").Range("D25:F25,H25:J25,L25:N25,P25:R25")"?

 

[joeu2004]

So would this work:

[....]
getMonthRange = wb.Worksheets("Summary-Region").Range("D25:F25,H25:J25,L25:N25,P25:R25")

wb.Close False ' close the source workbook without saving any changes
[....]
End Function

Or do I need "Set" in front of "getMonthRange = wb.Worksheets("Summary-Region").Range("D25:F25,H25:J25,L25:N25,P25:R25")"?

You need "Set getMonthRange = ..." as you wrote in your original version.

Remove the wb.Close statement in the getMonthRange procedure.

You might want to close the workbook somewhere else. But you have not provided sufficient context for us to offer guidance with that.

 

[guiltyberto]

I am still getting the same error, honestly I don't know what could be going wrong :/