Define a chart using VBA

  • Thread starter Thread starter Pim
  • Start date Start date
P

Pim

Hi folks,
I'm baffled- I'm writing a macro to pull a data table into an excel
sheet & create a bargraph of those results. The number of records in
the data table are variable (say between 2 and 50).

I'm trying to define a dynamic bar graph using VBA. The X values are
in col C and the text Y vlaues are in col A. (both start in row 3) The
values in column B are necessary, but have nothing to do with the
graph. I'm having trouble using variables to define the source data as
two, non adjacent ranges.

I've had problems just including the 50 cells in the data table as it
includes blank
spaces in the chart.

The code below builds the graph based on the X values starting in cell
C3. It just numbers them on the Y axis, not by the corresponding text
values....

Any ideas?
Thanks
pim

Dim sheetName as String 'name of the sheet where the data table
Dim NoRec As Double 'Number of records returned in query
Dim Graph As ChartObject 'Bar graph

NoRec = Application.WorksheetFunction.CountA(Columns("A:A"))
Set Graph = ActiveSheet.ChartObjects.Add _
(Left:=285, Width:=548, Top:=40, Height:=825)
Graph.Chart.SetSourceData
Source:=Sheets(sheetName).Range(Cells(3, 3), Cells(NoRec, 3))

Graph.Chart.ChartType = xlBarClustered
ActiveSheet.ChartObjects(1).Activate

ActiveChart.HasLegend = False
ActiveChart.HasTitle = False
With ActiveChart.ChartGroups(1)
..Overlap = 0
..GapWidth = 140
..HasSeriesLines = False
End With
With ActiveChart.ChartGroups(1)
..Overlap = 0
..GapWidth = 140
..HasSeriesLines = False
End With
'Y axis- text names format
With ActiveChart.Axes(xlCategory)
..CrossesAt = 1
..TickLabelSpacing = 1
..TickMarkSpacing = 1
..AxisBetweenCategories = True
..ReversePlotOrder = True
..MajorTickMark = xlOutside
..MinorTickMark = xlNone
..TickLabelPosition = xlLow
End With
'X axis-PI values Format
With ActiveChart.Axes(xlValue)
..TickLabelPosition = xlHigh
End With
ActiveChart.PlotArea.Select
With Selection.Border
..ColorIndex = 16
..Weight = xlThin
..LineStyle = xlContinuous
End With
 
Back
Top