All,
Months ago I created a GUI within Excel to plot 2D scatterplots from various sorted datasheets based on constraints placed in the GUI. When I initially created the tool, the code worked properly without issues. All plots updated each time a constraint was changed, but I'm having issues now. More specifically, when I update the data to be plotted in each series (the length of the series changes) I run into a runtime error 1004 (unable to set the Values property of the series class) when I try to define the values of the first chart (I have roughly 350 charts). Right now, each chart has been created with the number of series I need (7 each). The series were defined by:
Sub CreateCharts()
Dim x As Integer, y As String, AddedPlots As Integer
Dim n As Integer
Application.ScreenUpdating = False
n = 28 'Total number of variables and responses
AddedPlots = n * (n - 1) / 2
For x = 1 To AddedPlots
y = x 'Because the concatenate function does not like integers
Charts.Add
ActiveChart.ChartType = xlXYScatter
ActiveChart.SetSourceData Source:=Sheets("TopDown").Range("S80")
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).XValues = "={-1000}"
ActiveChart.SeriesCollection(1).Values = "={-1000}"
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(2).XValues = "={-1000}"
ActiveChart.SeriesCollection(2).Values = "={-1000}"
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(3).XValues = "={-1000}"
ActiveChart.SeriesCollection(3).Values = "={-1000}"
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(4).XValues = "={-1000}"
ActiveChart.SeriesCollection(4).Values = "={-1000}"
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(5).XValues = "={-1000}"
ActiveChart.SeriesCollection(5).Values = "={-1000}"
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(6).XValues = "={-1000}"
ActiveChart.SeriesCollection(6).Values = "={-1000}"
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(7).XValues = "={-1000}"
ActiveChart.SeriesCollection(7).Values = "={-1000}"
ActiveChart.Location Where:=xlLocationAsObject, Name:="TopDown"
Next x
End Sub
I originally left out the .XValues and .Values part of the code so each series only had a ={1} in the .Values property. I believe my error is coming from the fact that there is a disagreement in the length of the x and y values in each series. I tried makign charts with only 1 series, then within the second code I would delete that series just before adding a new series with the right data, this did not work.
Any help would be much appreciated,
Tanner
Sub Format_Multivariate_Square(chart_num, i, j, x, y, x_type, y_type, plot_size)
Dim NumVars As Integer, NumResponses As Integer, TotalVars As Integer, ActiveCases As Integer
Dim NumCases As Integer, ActiveVars As Integer, ActiveMOEs As Integer, TotalActive As Integer
Dim TD_Sheet As String, DataTab As String, ConstraintCell As String
Dim w_i As Integer, w_j As Integer
NumVars = Sheets("Setup").Cells(3, 3).Value 'Number of input variables
NumResponses = Sheets("Setup").Cells(4, 3).Value 'Number of responses
DataTab = Sheets("Setup").Cells(7, 3).Value 'Name of tab where data is found
NumCases = Sheets("Setup").Cells(5, 3).Value 'Number of Cases
ActiveCases = Sheets("Stats").Cells(1, 2).Value
TotalVars = NumVars + NumResponses
TD_Sheet = "TopDown"
ActiveVars = Sheets(TD_Sheet).Cells(7, 43).Value
ActiveMOEs = Sheets(TD_Sheet).Cells(7, 17).Value
TotalActive = ActiveVars + ActiveMOEs
If NumResponses > NumVars Then
max_vars = NumResponses
Else
max_vars = NumVars
End If
Worksheets(TD_Sheet).ChartObjects("Chart " & chart_num).Select
'Format Chart
If plot_size < 6 Then
ActiveChart.ApplyCustomType ChartType:=xlUserDefined, TypeName:="Multivariate_1"
Else
ActiveChart.ApplyCustomType ChartType:=xlUserDefined, TypeName:="Multivariate_2"
End If
If x_type = "MOE" Then
w_i = i + NumVars
Else
w_i = i - NumResponses
End If
If y_type = "MOE" Then
w_j = j + NumVars
Else
w_j = j - NumResponses
End If
'Data
If ActiveCases = NumCases Then
ActiveChart.SeriesCollection(1).XValues = "={-1000}"
ActiveChart.SeriesCollection(1).Values = "={-1000}"
Else
'Original Code _ Error when running with increased vars/responses
ActiveChart.SeriesCollection(1).XValues = _
"=('Working Data'!R" & ActiveCases + 2 & "C" & w_i + 1 & _
":R" & NumCases + 1 & "C" & w_i + 1 & ")"
ActiveChart.SeriesCollection(1).Values = _
"={'Working Data'!R" & ActiveCases + 2 & "C" & w_j + 1 & _
":R" & NumCases + 1 & "C" & w_j + 1 & "}"
' ActiveChart.SeriesCollection(1).XValues = "=Sheet1!R7C2:R89C2"
' ActiveChart.SeriesCollection(1).Values = "=Sheet1!R7C3:R89C3"
End If
ActiveChart.SeriesCollection(2).XValues = _
"=('Working Data'!R2C" & w_i + 1 & _
":R" & ActiveCases + 1 & "C" & w_i + 1 & ")"
ActiveChart.SeriesCollection(2).Values = _
"=('Working Data'!R2C" & w_j + 1 & _
":R" & ActiveCases + 1 & "C" & w_j + 1 & ")"
If x_type = "MOE" Then
ActiveChart.SeriesCollection(3).XValues = "=('" & TD_Sheet & "'!R" & 2 * i + 6 & "C6,'" & TD_Sheet & "'!R" & 2 * i + 6 & "C6)"
ActiveChart.SeriesCollection(3).Values = "=('" & TD_Sheet & "'!R" & 2 * j + 6 & "C13,'" & TD_Sheet & "'!R" & 2 * j + 6 & "C15)"
Else
ActiveChart.SeriesCollection(3).XValues = "={1}"
ActiveChart.SeriesCollection(3).Values = "={1}"
End If
If y_type = "MOE" Then
ActiveChart.SeriesCollection(4).Values = "=('" & TD_Sheet & "'!R" & 2 * j + 6 & "C6,'" & TD_Sheet & "'!R" & 2 * j + 6 & "C6)"
If x_type = "MOE" Then
ActiveChart.SeriesCollection(4).XValues = "=('" & TD_Sheet & "'!R" & 2 * i + 6 & "C13,'" & TD_Sheet & "'!R" & 2 * i + 6 & "C15)"
ActiveChart.SeriesCollection(6).XValues = "=('TOPSIS'!R4C" & w_i + 1 & ")"
ActiveChart.SeriesCollection(6).Values = "=('TOPSIS'!R4C" & w_j + 1 & ")"
ActiveChart.SeriesCollection(7).XValues = "=('TOPSIS'!R5C" & w_i + 1 & ")"
ActiveChart.SeriesCollection(7).Values = "=('TOPSIS'!R5C" & w_j + 1 & ")"
Else
ActiveChart.SeriesCollection(4).XValues = "=('Stats'!R4C" & i - NumResponses + 1 & ",'Stats'!R5C" & i - NumResponses + 1 & ")"
End If
Else
ActiveChart.SeriesCollection(4).XValues = "={1}"
ActiveChart.SeriesCollection(4).Values = "={1}"
End If
ActiveChart.SeriesCollection(5).XValues = _
"=('TOPSIS'!R8C" & w_i + 1 & _
":R17C" & w_i + 1 & ")"
ActiveChart.SeriesCollection(5).Values = _
"=('TOPSIS'!R8C" & w_j + 1 & _
":R17C" & w_j + 1 & ")"
Worksheets(TD_Sheet).ChartObjects("Chart " & chart_num).Activate
ActiveChart.ChartArea.Select
Selection.Border.ColorIndex = 48
ActiveChart.HasAxis(xlCategory, xlPrimary) = True
ActiveChart.Axes(xlCategory).MinimumScale = Worksheets("Stats").Cells(4, w_i + 1).Value
ActiveChart.Axes(xlCategory).MaximumScale = Worksheets("Stats").Cells(5, w_i + 1).Value
ActiveChart.HasAxis(xlCategory, xlPrimary) = False
ActiveChart.HasAxis(xlValue, xlPrimary) = True
ActiveChart.Axes(xlValue).MinimumScale = Worksheets("Stats").Cells(4, w_j + 1).Value
ActiveChart.Axes(xlValue).MaximumScale = Worksheets("Stats").Cells(5, w_j + 1).Value
ActiveChart.HasAxis(xlValue, xlPrimary) = False
ActiveChart.ChartArea.AutoScaleFont = False
ActiveWindow.Visible = False
plot_height = plot_size * 21
plot_width = plot_size * 24
caption_offset = (plot_size - 3) / 2
'Set size
ActiveSheet.Shapes("Chart " & chart_num).Height = plot_height
ActiveSheet.Shapes("Chart " & chart_num).Width = plot_width
ActiveSheet.Shapes("Chart " & chart_num).Top = 230 + 25 * (max_vars + 1) + plot_height * (y - 1)
ActiveSheet.Shapes("Chart " & chart_num).Left = 96 + plot_width * (x - 2) + caption_offset * 24
Cells(1, 1).Select
End Sub
Runtime err 1004 when attempting to set data range for existing charts
sdoyle
Mr. Peltier,
Thank you for your comments, it's amazing how something so simple is usually the cause of so much irritation. Your comments worked like a chart, thank you as well for your comments focused at optimizing my code. With so many charts, this decreased runtime will help me enormously!
Thanks again - Tanner
LLIAMAH
Your main issue is that this line has improper puctuation:
ActiveChart.SeriesCollection(1).Values = _
"={'Working Data'!R" & ActiveCases + 2 & "C" & w_j + 1 & _
":R" & NumCases + 1 & "C" & w_j + 1 & "}"
Change the curly braces {} to parentheses (). Curly braces are for entering an array of values, like "={1,2,3}". The initial value you see, "={1}", is an array with one element.
Other comments:
y = x 'Because the concatenate function does not like integers
I don't understand this. If you want to use a string value of an integer in a concatenated string, make sure you use & as the concatenation operator (some folks use +, which doesn't always do what you expect), and for extra control use CStr(x) to convert it on the spot into a string. That way you know on inspection what it's doing, without having to remember that y = CStr(x).
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(7).XValues = "={-1000}"
ActiveChart.SeriesCollection(7).Values = "={-1000}"
I don't know why you go through all this, instead of creating the series when you populate them.
Worksheets(TD_Sheet).ChartObjects("Chart " & chart_num).Select
Save processing time and screen flicker by not selecting each chart. Instead, use With/Wnd With:
With Worksheets(TD_Sheet).ChartObjects("Chart " & chart_num).Chart
If plot_size < 6 Then
.ApplyCustomType ChartType:=xlUserDefined, TypeName:="Multivariate_1"
Else
.ApplyCustomType ChartType:=xlUserDefined, TypeName:="Multivariate_2"
End If
' etc.
End With
Also, it's generally best to wait until the chart is totally populated before applying your custom chart type. Otherwise not all your formatting may be applied.
One more thing: the formatting of the code in your post is both smaller and italicized, both of which decrease readability. If you need to set off the code (you really don't, we can figure it out), use Courier, as I did in the last code block above.
- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______