PDA

View Full Version : Histograms in excel 2013



werafa
07-10-2013, 04:05 PM
Hi all
I have either a problem or an excel bug.
This is not realy a VBA question - but once sorted I will code the VBA, so a vba only answer is fine (and will make me look super good too :cloud9:)

I used to be able to turn a column chart into a histogram by setting the gap width in a column chart to 0 (and have the columns touch each other.

I cant do this in excel 2013 - it always has a gap of at least 1 column width or more. Am I missing something, or has microsoft been too clever again?

Thanks
Tim

werafa
07-11-2013, 12:08 AM
This is getting stranger. the graph is generated using vba, and has the problem described. If I make the chart without VBA - I can set the gap to zero.

The code that generates the chart is:
Sub InsertChart(mySheet As Worksheet)
'ID data table range
'Insert chart
'Format Chart

Dim lastrow As Long
Dim myChart As Chart
Dim myObject As Object

mySheet.Range("B7").Select
lastrow = 30
Do Until mySheet.Range("B" & lastrow).Value = ""
lastrow = lastrow + 1
Loop

ActiveSheet.Shapes.AddChart.Select
Set myChart = ActiveChart
With myChart.Parent 'set size and position
.Left = 100
.Top = 70
.Width = 800
.Height = 290
End With
With myChart
.SetSourceData Source:=Range(mySheet.Range("J30"), mySheet.Range("J" & lastrow))
.ChartType = xlAreaStacked
.Axes(xlValue, xlPrimary).TickLabels.NumberFormat = "$0"
With .SeriesCollection(1)
.XValues = Range(mySheet.Range("C31"), mySheet.Range("C" & lastrow))
With .Format.Shadow
.Visible = msoTrue
.Blur = 10
.Transparency = 0.6
.OffsetX = 6
.OffsetY = 6
End With
With .Format.ThreeD
.Visible = msoTrue
.BevelTopType = msoBevelCircle
.BevelTopDepth = 3
.BevelTopInset = 3
End With
End With
'add series 2
.SeriesCollection.NewSeries.Values = Range(mySheet.Range("M31"), mySheet.Range("M" & lastrow))
With .SeriesCollection(2)
.XValues = Range(mySheet.Range("C31"), mySheet.Range("C" & lastrow))
.Name = mySheet.Range("M30")
With .Format.Shadow
.Visible = msoTrue
.Blur = 10
.Transparency = 0.6
.OffsetX = 6
.OffsetY = 6
End With
With .Format.ThreeD
.Visible = msoTrue
.BevelTopType = msoBevelCircle
.BevelTopDepth = 3
.BevelTopInset = 3
End With
End With

With .SeriesCollection(1)
.ChartType = xlLineMarkers
.AxisGroup = 2
End With
.Axes(xlValue, xlSecondary).TickLabels.NumberFormat = "0 ""kWh"""
.HasTitle = True
Set myObject = .ChartTitle
With myObject
.Text = mySheet.Range("A1").Value
End With
End With
mySheet.Range("A1").Select
Set myObject = Nothing
Set mySheet = Nothing
End Sub

I am trying to manually convert the area to a histogram - and coming unstuck