Macro to create large 2d surface charts.

Y

y

Is there a macro to easily create 2D surface charts?
Where I may find it out?

I'm going to build it on my own, but if someone did it I'll actually appreciate his/her work! ;)
Any hints for the last questions?

Thanks, Alex.
 
Y

y

Hi Tom,
I did it and tried to go over but not all things go fine...

This is the code:

Sub Crea2DChart()
Dim sArea As Range
Dim i As Integer
Dim Nseries As Integer
Set sArea = Application.InputBox(prompt:="Select range:", Type:=8)

Charts.Add
ActiveChart.ChartType = xlSurface
ActiveChart.SeriesCollection(1).XValues = sArea.Offset(sArea.Columns.Count, 2).Resize(1,
sArea.Columns.Count - 1)
For i = 1 To Nseries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(i).Values = ActiveSheet.sArea.Offset(i + 1, 2).Resize(1,
sArea.Columns.Count)
ActiveChart.SeriesCollection(i).Name = ActiveSheet.sArea.Offset(i + 1, 1).Resize(1, 1)
Next i
ActiveChart.Location Where:=xlLocationAsNewSheet
End Sub

Thanks, Alex
 
Y

y

Hi Tom,
I did it and tried to go over but not all things go fine...

This is the code:

Sub Crea2DChart()
Dim sArea As Range
Dim i As Integer
Dim Nseries As Integer
Set sArea = Application.InputBox(prompt:="Select range:", Type:=8)
Nseries = sArea.Rows.count - 1
Charts.Add

ActiveChart.SeriesCollection(1).XValues = sArea.Offset(sArea.Columns.Count, 2).Resize(1,
sArea.Columns.Count - 1)
For i = 1 To Nseries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(i).Values = ActiveSheet.sArea.Offset(i + 1, 2).Resize(1,
sArea.Columns.Count)
ActiveChart.SeriesCollection(i).Name = ActiveSheet.sArea.Offset(i + 1, 1).Resize(1, 1)
Next i
ActiveChart.Location Where:=xlLocationAsNewSheet
End Sub

Thanks, Alex
 
Y

y

Hi Tom,
I did it and tried to go over but not all things go fine...

This is the code:

Sub Crea2DChart()
Dim sArea As Range
Dim i As Integer
Dim Nseries As Integer
Set sArea = Application.InputBox(prompt:="Select range:", Type:=8)
Nseries = sArea.Rows.count - 1
Charts.Add
Do I need to activate it? And How?


ActiveChart.SeriesCollection(1).XValues = sArea.Offset(sArea.Columns.Count, 2).Resize(1,
sArea.Columns.Count - 1)
For i = 1 To Nseries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(i).Values = ActiveSheet.sArea.Offset(i + 1, 2).Resize(1,
sArea.Columns.Count)
ActiveChart.SeriesCollection(i).Name = ActiveSheet.sArea.Offset(i + 1, 1).Resize(1, 1)
Next i
ActiveChart.Location Where:=xlLocationAsNewSheet
End Sub

Thanks, Alex
 
W

Wei-Dong XU [MSFT]

Hi Alex,

I modify some of your code below with commentary. Please read!

'----------------------
Sub Crea2DChart()
Dim sArea As Range
Dim i As Integer
Dim Nseries As Integer
'Add the definition for the Chart object and SeriesCollection
Dim oChart As Excel.Chart
Dim oSC As Excel.SeriesCollection

Set sArea = Application.InputBox(prompt:="Select range:", Type:=8)

Nseries = sArea.Rows.Count - 1
'change the codes from
' Charts.Add
'to the line below which set the new created object to oChart variable
Set oChart = Charts.Add

'ActiveChart.ChartType = xlSurface
oChart.ChartType = xl3DArea
'select this chart object
oChart.Select

'at this time, there is empty in the series, you will need to
'add one range object or array to create series so that you can
' set value to the chart serie.
'See my code below
Set oSC = oChart.SeriesCollection
Call oSC.Add(sArea)

'then using oChart instead of ActiveChart in the later codes
'Please note: before using the chart serie, please ensure it exists in the chart
'...
'----------------------

Furthermore, "Quick Chart VBA Examples" from Jon will help you a lot on the chart development.
http://peltiertech.com/Excel/ChartsHowTo/QuickChartVBA.html#VBAcharts

Please feel free to let me know if you have any further questions.

Best Regards,
Wei-Dong Xu
Microsoft Product Support Services
Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.
---
This response contains a reference to a third party World Wide Web site. Microsoft is providing this information as a convenience to you. Microsoft
does not control these sites and has not tested any software or information found on these sites; therefore, Microsoft cannot make any
representations regarding the quality, safety, or suitability of any software or information found there. There are inherent dangers in the use of
any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any
software from the Internet.
 
Top