Help in making a simle column/line chart in excel using vb6

  • Thread starter Thread starter Sandeep
  • Start date Start date
S

Sandeep

Hi,

I want to make a simple column or line chart in excel
using vb6.
I have created instance of excel and in the sheet1, I am
able to populate some data. Now using that data, say the
range is A2-B6, I want to use that range and make a chart.

Pls help by giving me code, step by step.

Thx
Sandeep
 
Sandeep -

Step 1. In Excel, turn on the macro recorder while making the kind of
chart you want.

Step 2. Refine the recorded code.

Here is some recorded code, which creates an embedded column chart, adds
axis and chart titles, and does a little formatting:

'' START RECORDED CODE
Sub SimpleChartForVB()
'
' SimpleChartForVB Macro
' Macro recorded 8/11/2004 by Jon Peltier
'
Charts.Add
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A1:B6")
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
ActiveChart.PlotArea.Select
With Selection.Border
.ColorIndex = 1
.Weight = xlThin
.LineStyle = xlContinuous
End With
With Selection.Interior
.ColorIndex = 2
.PatternColorIndex = 1
.Pattern = xlSolid
End With
ActiveChart.Axes(xlValue).MajorGridlines.Select
With Selection.Border
.ColorIndex = 15
.Weight = xlThin
.LineStyle = xlContinuous
End With
ActiveChart.PlotArea.Select
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "My Chart Title"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = _
"Categories"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Values"
End With
ActiveChart.SeriesCollection(1).Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlAutomatic
End With
Selection.Shadow = False
Selection.InvertIfNegative = False
With Selection.Interior
.ColorIndex = 3
.Pattern = xlSolid
End With
ActiveChart.ChartArea.Select
End Sub
'' END RECORDED CODE

This procedure is cleaned up a little; I've removed some defaults and
streamlined the code a bit:

'' START ADJUSTED CODE
Sub SimpleChartForVB()
Charts.Add
With ActiveChart
.ChartType = xlColumnClustered
.SetSourceData Source:=Sheets("Sheet1").Range("A1:B6")
.Location Where:=xlLocationAsObject, Name:="Sheet1"
End With
With ActiveChart
With PlotArea.Border
'' black border
.ColorIndex = 1
End With
With PlotArea.Interior
'' white fill
.ColorIndex = 2
.PatternColorIndex = 1
End With
With .Axes(xlValue).MajorGridlines.Border
'' light gray lines
.ColorIndex = 15
.Weight = xlThin
.LineStyle = xlContinuous
End With
.HasTitle = True
.ChartTitle.Characters.Text = "My Chart Title"
With .Axes(xlCategory, xlPrimary)
.HasTitle = True
.AxisTitle.Characters.Text = "Categories"
End With
With .Axes(xlValue, xlPrimary)
.HasTitle = True
.AxisTitle.Characters.Text = "Values"
End With
With .SeriesCollection(1).Interior
'' red fill
.ColorIndex = 3
.Pattern = xlSolid
End With
End With
End Sub
'' END ADJUSTED CODE

For more about using VBA with charts, check out this web page:

http://peltiertech.com/Excel/ChartsHowTo/QuickChartVBA.html

You also have to keep in mind that you have to reference everything for
VB. This is easier if you use object variables. For example:

dim xlApp as Excel.Application
dim xlWB as Excel.Workbook
dim xlWS as Excel.Worksheet
dim xlCht as Excel.Chart
dim xlChOb as Excel.ChartObject

Set xlApp = <whatever>
set xlWB = <whatever>
set xlWS = <whatever>

set xlChOb = xlWS.ChartObjects.Add _
(<left, top, width, height in points>)
set xlCht = xlChOb.Chart

with xlCht
.SetSourceData Source:=xlWS.Range("A1:B6")
.ChartType = xlColumnClustered

' you don't need .Location
' .ChartObjects.Add puts it into xlWS

' do whatever formatting you need
end with

etc.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
Back
Top