I currently have many rows of data which refer to months of the year. In all, I have 27 lines which the graph builds on a 3D graph. My problem is it makes the 27 lines one right after the other. I'd like to limit the Z axis or depth of the graph to only 12 points (like 12 months in a year) on the Z axis. Is there a way to do this? I can't seem to find an answer anywhere.
I want the data to be on top of each other after a certain series is finished. In other words, if I have 9 rows on each of the 3 sheets that graph data is pulled from, I want the next sheet's 9 rows to be within the same Z axis. I hope this isn't confusing the way I'm saying it.
I think you will need to upload your workbook for us to see what the problem is. You may need to post a couple of replys to this message before you can do that.
Be sure to remove any data or sheets that are not germane to the problem.
Be sure to change or remove any personal, private, or proprietary information that may be in the workbook.
I cannot upload the workbook, there is proprietary information throughout. However, I can post you what I'm passing to the graph.
[VBA]Sheets("Graph").Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.SetSourceData Source:=Sheets("Cmd1Vals").UsedRange
ActiveChart.ChartType = xl3DLine
ActiveChart.PlotBy = xlRows
For i = 1 To numRows
ActiveChart.SeriesCollection(i).Interior.ColorIndex = 3
ActiveChart.SeriesCollection(i).Name = i
Next
With ActiveChart.Axes(xlCategory)
.HasTitle = True
.AxisTitle.Caption = "3D Surface Graph"
End With
With ActiveChart.Axes(xlValue)
.HasTitle = True
.AxisTitle.Caption = "Volatility"
End With
With ActiveChart.Axes(xlSeries)
.HasTitle = True
.AxisTitle.Caption = "Months"
End With
Dim serNum2
ActiveChart.SeriesCollection.Add _
Source:=Sheets("Cmd2Vals").UsedRange
serNum2 = ActiveChart.SeriesCollection.Count
For i = (numRows) To serNum2
ActiveChart.SeriesCollection(i).Interior.ColorIndex = 5
ActiveChart.SeriesCollection(i).Name = i
Next
ActiveChart.SeriesCollection.Add _
Source:=Sheets("Cmd3Vals").UsedRange
Dim serNum3
serNum3 = ActiveChart.SeriesCollection.Count
serNum2 = serNum2 + 1
For i = serNum2 To serNum3
ActiveChart.SeriesCollection(i).Interior.ColorIndex = 8
ActiveChart.SeriesCollection(i).Name = i
Next[/VBA]
Last edited by Aussiebear; 03-18-2013 at 03:23 PM.
Reason: Added the correct tags to the supplied code
I am just putting VBA tags around your code to make it easier to understand. You can do the same by selecting the code in the edit box and clicking the VBA icon.
[VBA]Sheets("Graph").Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.SetSourceData Source:=Sheets("Cmd1Vals").UsedRange
ActiveChart.ChartType = xl3DLine
ActiveChart.PlotBy = xlRows
For i = 1 To numRows
ActiveChart.SeriesCollection(i).Interior.ColorIndex = 3
ActiveChart.SeriesCollection(i).Name = i
Next
With ActiveChart.Axes(xlCategory)
.HasTitle = True
.AxisTitle.Caption = "3D Surface Graph"
End With
With ActiveChart.Axes(xlValue)
.HasTitle = True
.AxisTitle.Caption = "Volatility"
End With
With ActiveChart.Axes(xlSeries)
.HasTitle = True
.AxisTitle.Caption = "Months"
End With
Dim serNum2
ActiveChart.SeriesCollection.Add _
Source:=Sheets("Cmd2Vals").UsedRange
serNum2 = ActiveChart.SeriesCollection.Count
For i = (numRows) To serNum2
ActiveChart.SeriesCollection(i).Interior.ColorIndex = 5
ActiveChart.SeriesCollection(i).Name = i
Next
ActiveChart.SeriesCollection.Add _
Source:=Sheets("Cmd3Vals").UsedRange
Dim serNum3
serNum3 = ActiveChart.SeriesCollection.Count
serNum2 = serNum2 + 1
For i = serNum2 To serNum3
ActiveChart.SeriesCollection(i).Interior.ColorIndex = 8
ActiveChart.SeriesCollection(i).Name = i
Next[/VBA]
Okay, thanks. Sorry for my unfamiliarity with the process of posting on the forum. This must be a good one because I'm getting replies back very quickly! I posted to Microsoft's and another and this was almost a week ago now, still no reply.
In order to help me understand your code, I cleaned it up and reformatted it. I think you may need to start by setting each Series object to xl3dLine. I am still looking at it though.
''''First set of Series
For i = 1 To numRows
With .SeriesCollection(i)
.Interior.ColorIndex = 3
.Name = i
End With
Next i
''''Set chart Axis Values
With .Axes(xlCategory)
.HasTitle = True
.AxisTitle.Caption = "3D Surface Graph"
End With
With .Axes(xlValue)
.HasTitle = True
.AxisTitle.Caption = "Volatility"
End With
With .Axes(xlSeries)
.HasTitle = True
.AxisTitle.Caption = "Months"
End With
''''Add Second set of Series
Dim serNum2 As Long
.SeriesCollection.Add Source:=Sheets("Cmd2Vals").UsedRange
serNum2 = .SeriesCollection.Count
For i = (numRows) To serNum2
With .SeriesCollection(i)
.Interior.ColorIndex = 5
.Name = i
End With
Next
''''Add Last Set Of Series
.SeriesCollection.Add Source:=Sheets("Cmd3Vals").UsedRange
Dim serNum3 As Long
serNum3 = .SeriesCollection.Count
serNum2 = serNum2 + 1
For i = serNum2 To serNum3
With .SeriesCollection(i)
.Interior.ColorIndex = 8
.Name = i
End With
Next
How about the 3D graph with the sample data. This will show your current situation. It may help someone get some additional insight what needs to be done.
snb: The months, yes, it needs to be 12, but I need more than 12 series of data plotted within the 12 month z-axis. I colored them differently to differentiate.
Jan Karel Pieterse: Yes, it is a duplication of a feature already used in another piece of software. However, in this other software, it does not give you the option to graph 3 different collections of data. So yes, it needs to be in 3D, just as the other one is. They need to see the surface of the graph.
I'm stubborn :-)
I would still advise to look for a different way to display this data. Chart multiples are probably a good bet.
3D charting is highly overrated.
Jan Karel: I think maybe you're not understanding what I'm saying. Think mathematically for a second. In the file I sent, there are 27 series of data being graphed, 27 lines. The X Axis is defined by Excel, the Y Axis is kind of gauged to the values, and the Z Axis is defined by how many series there are. There are 27 series, so Z Axis is 27 points long.
Now here's what I want. I want the Z Axis to be limited to 12 points, instead of being lengthened to the amount of data series I have. Then, I want all 27 series to be graphed WITHIN the 12 points. Do you see what I'm getting at? If not, look at my original upload of the workbook. If you notice, there are 3 different colored sets. That's because those are 3 separate entities I want graphed. I want them graphed all within 12 months (Z Axis).
Yeah, I should have clarified on that. So the reason they want it in 3D is because they need to see the surface of the data beside each other. With 2D, you can't really do that. They need to see the 3D Surface of it all and make decisions based on that.
They're using this in options trading on futures contracts. It's kind of like stock market trading, don't know if you're familiar with commodities markets.
Reply With Quote
