VBA code - Why is X axis label wrong

Rasm · Mar 7, 2011

I cannot understand why my X axis labels is the way it is on the 2nd chart. Below is the code to create two charts - including the arrays - so simply run the macro and two charts are generated - Hopefully making it very easy for you to visualize what I cannot get to work. The first chart is a Histogram - It has the X axis that I want. However I want the Histogram plotted as a curve - so the 2nd chart is the exact same data - exact same code - except for the '.ChartType' property. However the X axis is not the scale I would expect - I would like the X axis same as for my original Histogram.

I have posted this on other forums - but gotten no answers. I am using xl2007

Code:

Option Explicit
Public Sub AddHistochart()
    Dim NumCharts As Long
    Dim PlotValues() As Variant
    Dim i, ii As Long
    Dim frequency() As Variant
    Dim binlabel() As Variant
    Dim binsize As Variant
    Dim minimumX As Single
    Dim maximumX As Single
    Dim NumBins As Long
    Dim NumParameters As Long
    Dim HighFrequency As Long
    
    Application.StatusBar = "Making Histogram"
    NumBins = 10
    NumParameters = 1
    
   
    ReDim frequency(1 To NumBins)
    ReDim binlabel(1 To NumBins)
    
    Call GetArrayValue(PlotValues, minimumX, maximumX)
    binsize = (maximumX - minimumX) / NumBins
    For i = 1 To NumBins
        binlabel(i) = Format(minimumX + 0.5 * binsize + (i - 1) * binsize, "####0.00")
    Next i
    HighFrequency = 0
    For i = 1 To NumBins - 1
        For ii = 0 To UBound(PlotValues)
            If PlotValues(ii) >= binlabel(i) - 0.5 * binsize And PlotValues(ii) < binlabel(i + 1) - 0.5 * binsize Then
                frequency(i) = frequency(i) + 1
                If frequency(i) > HighFrequency Then HighFrequency = frequency(i)
            End If
        Next ii
    Next i
    '******* last bin
    For i = 0 To UBound(PlotValues)
        If PlotValues(i) >= binlabel(NumBins) - 0.5 * binsize And PlotValues(i) < maximumX Then
            frequency(NumBins) = frequency(NumBins) + 1
            If frequency(NumBins) > HighFrequency Then HighFrequency = frequency(i)
        End If
    Next i
    NumCharts = ActiveSheet.ChartObjects.Count
    If NumCharts > 0 Then
        For i = NumCharts To 1 Step -1
            ActiveSheet.ChartObjects(i).Delete
        Next i
    End If
    
StartMakingCharts:
    
    ActiveSheet.ChartObjects.Add Left:=50, Top:=50, Width:=600, Height:=300
    NumCharts = ActiveSheet.ChartObjects.Count
    If NumCharts > 1 Then
        ActiveSheet.ChartObjects(NumCharts).Left = ActiveSheet.ChartObjects(NumCharts - 1).Left
        ActiveSheet.ChartObjects(NumCharts).Top = ActiveSheet.ChartObjects(NumCharts - 1).Top + ActiveSheet.ChartObjects(NumCharts - 1).Height + 20
        ActiveSheet.ChartObjects(NumCharts).Height = ActiveSheet.ChartObjects(NumCharts - 1).Height
        ActiveSheet.ChartObjects(NumCharts).Width = ActiveSheet.ChartObjects(NumCharts - 1).Width
    End If
    NumCharts = ActiveSheet.ChartObjects.Count
    ActiveSheet.ChartObjects(NumCharts).Activate
    Dim Achart As ChartObject
    Set Achart = ActiveSheet.ChartObjects(NumCharts)
    
    With Achart.Chart     'Set chart properties
       If NumCharts = 1 Then
                .ChartType = xlColumnClustered
            Else
                .ChartType = xlXYScatterSmoothNoMarkers
        End If
        .SeriesCollection.NewSeries
        .HasLegend = False
        .Axes(xlCategory).MajorTickMark = xlTickMarkOutside
        .Axes(xlValue).MajorTickMark = xlTickMarkOutside
        .Axes(xlValue).MinimumScale = 0
        .Axes(xlValue).MaximumScale = HighFrequency + 1
        
        
        .SeriesCollection(NumParameters).Values = Array(frequency) ' creates bars
        .SeriesCollection(NumParameters).XValues = Array(binlabel)
       
      
        'Selection.Fill.OneColorGradient Style:=msoGradientHorizontal, Variant:=4, Degree:=0.231372549019608
        
        .Axes(xlCategory).HasTitle = True
        .Axes(xlCategory).AxisTitle.Characters.Text = "Predictions"
        .Axes(xlValue).HasTitle = True
        .Axes(xlValue).AxisTitle.Characters.Text = "Frequency"
    End With
    Set Achart = Nothing
    
    If NumCharts = 1 Then GoTo StartMakingCharts
    
    Application.StatusBar = "Completed ---- Making Histogram/Curve Histogram - Start new Task"
End Sub
 
 
Private Sub GetArrayValue(PlotValues, minimumX, maximumX)
Dim NumObvs As Long
NumObvs = 178
minimumX = 9.1
maximumX = 14
ReDim PlotValues(NumObvs)
PlotValues(0) = 10.406
PlotValues(1) = 10.496
PlotValues(2) = 10.3
PlotValues(3) = 10.469
PlotValues(4) = 10.381
PlotValues(5) = 10.148
PlotValues(6) = 10.306
PlotValues(7) = 10.457
PlotValues(8) = 9.893
PlotValues(9) = 9.881
PlotValues(10) = 9.44
PlotValues(11) = 9.276
PlotValues(12) = 9.162
PlotValues(13) = 9.721
PlotValues(14) = 9.61
PlotValues(15) = 9.804
PlotValues(16) = 9.794
PlotValues(17) = 10.27
PlotValues(18) = 10.416
PlotValues(19) = 10.571
PlotValues(20) = 10.793
PlotValues(21) = 11.061
PlotValues(22) = 11.28
PlotValues(23) = 11.472
PlotValues(24) = 11.527
PlotValues(25) = 11.809
PlotValues(26) = 11.962
PlotValues(27) = 12.141
PlotValues(28) = 12.328
PlotValues(29) = 12.49
PlotValues(30) = 12.677
PlotValues(31) = 12.871
PlotValues(32) = 12.951
PlotValues(33) = 13.15
PlotValues(34) = 13.305
PlotValues(35) = 13.272
PlotValues(36) = 13.168
PlotValues(37) = 13.457
PlotValues(38) = 13.411
PlotValues(39) = 13.613
PlotValues(40) = 13.651
PlotValues(41) = 13.91
PlotValues(42) = 11.053
PlotValues(43) = 11.176
PlotValues(44) = 11.153
PlotValues(45) = 11.902
PlotValues(46) = 11.111
PlotValues(47) = 11.316
PlotValues(48) = 11.839
PlotValues(49) = 11.633
PlotValues(50) = 11.752
PlotValues(51) = 11.709
PlotValues(52) = 11.868
PlotValues(53) = 12.061
PlotValues(54) = 12.223
PlotValues(55) = 12.164
PlotValues(56) = 12.308
PlotValues(57) = 12.541
PlotValues(58) = 12.389
PlotValues(59) = 12.697
PlotValues(60) = 12.722
PlotValues(61) = 12.458
PlotValues(62) = 12.888
PlotValues(63) = 12.999
PlotValues(64) = 12.072
PlotValues(65) = 12.953
PlotValues(66) = 11.03
PlotValues(67) = 11.192
PlotValues(68) = 11.086
PlotValues(69) = 11.197
PlotValues(70) = 11.236
PlotValues(71) = 11.324
PlotValues(72) = 11.696
PlotValues(73) = 11.732
PlotValues(74) = 11.943
PlotValues(75) = 12.113
PlotValues(76) = 12.17
PlotValues(77) = 12.475
PlotValues(78) = 12.349
PlotValues(79) = 13.006
PlotValues(80) = 12.688
PlotValues(81) = 12.655
PlotValues(82) = 12.869
PlotValues(83) = 13.098
PlotValues(84) = 13.127
PlotValues(85) = 13.146
PlotValues(86) = 13.493
PlotValues(87) = 13.746
PlotValues(88) = 13.729
PlotValues(89) = 13.833
PlotValues(90) = 14.026
PlotValues(91) = 13.958
PlotValues(92) = 11.208
PlotValues(93) = 11.098
PlotValues(94) = 11.4
PlotValues(95) = 11.221
PlotValues(96) = 11.22
PlotValues(97) = 11.191
PlotValues(98) = 10.946
PlotValues(99) = 11.353
PlotValues(100) = 11.274
PlotValues(101) = 11.361
PlotValues(102) = 11.173
PlotValues(103) = 11.034
PlotValues(104) = 10.986
PlotValues(105) = 11.025
PlotValues(106) = 10.88
PlotValues(107) = 10.862
PlotValues(108) = 10.852
PlotValues(109) = 11.185
PlotValues(110) = 10.71
PlotValues(111) = 10.83
PlotValues(112) = 10.961
PlotValues(113) = 10.71
PlotValues(114) = 10.895
PlotValues(115) = 10.66
PlotValues(116) = 10.712
PlotValues(117) = 10.86
PlotValues(118) = 10.777
PlotValues(119) = 10.779
PlotValues(120) = 10.596
PlotValues(121) = 10.754
PlotValues(122) = 10.488
PlotValues(123) = 10.829
PlotValues(124) = 10.667
PlotValues(125) = 10.527
PlotValues(126) = 10.378
PlotValues(127) = 10.188
PlotValues(128) = 10.566
PlotValues(129) = 10.468
PlotValues(130) = 10.488
PlotValues(131) = 10.389
PlotValues(132) = 10.188
PlotValues(133) = 10.29
PlotValues(134) = 10.313
PlotValues(135) = 10.289
PlotValues(136) = 10.214
PlotValues(137) = 10.194
PlotValues(138) = 10.126
PlotValues(139) = 10.125
PlotValues(140) = 10.071
PlotValues(141) = 10.248
PlotValues(142) = 10.157
PlotValues(143) = 10.242
PlotValues(144) = 10.128
PlotValues(145) = 10.028
PlotValues(146) = 10.304
PlotValues(147) = 10.092
PlotValues(148) = 10.038
PlotValues(149) = 9.995
PlotValues(150) = 10.132
PlotValues(151) = 10.131
PlotValues(152) = 9.857
PlotValues(153) = 10.22
PlotValues(154) = 9.977
PlotValues(155) = 10.135
PlotValues(156) = 10.181
PlotValues(157) = 10.042
PlotValues(158) = 9.988
PlotValues(159) = 10.151
PlotValues(160) = 10.108
PlotValues(161) = 10.16
PlotValues(162) = 10.088
PlotValues(163) = 10.224
PlotValues(164) = 10.078
PlotValues(165) = 10.05
PlotValues(166) = 9.901
PlotValues(167) = 9.915
PlotValues(168) = 9.988
PlotValues(169) = 9.999
PlotValues(170) = 10.005
PlotValues(171) = 9.962
PlotValues(172) = 9.971
PlotValues(173) = 10.075
PlotValues(174) = 9.989
PlotValues(175) = 10.015
PlotValues(176) = 10.086
PlotValues(177) = 10.172
 
End Sub

Rasm · Mar 7, 2011

ahhh - I found the answer

http://pubs.logicalexpressions.com/pub0009/LPMArticle.asp?ID=190

So no need to spend time on this anyone out there.

Quadophile · Mar 7, 2011

You helped others by posting and later answering your own question. LOL Welcome to PC Review!

Rasm · Mar 7, 2011

Well - I had posted on other forum - got the answer - so Figured - I would spare anybody here the time to find an answer. Everybody are busy - so normal curtesy I think. But thanks - I will follow this forum - it looks very high level. I am just learning.

Quadophile · Mar 7, 2011

Thank you for the feedback we appreciate that here very much. :nod:

If everyone was as courteous as you the web would become a much better place.

Look forward to bumping into you on the site more often.

VBA code - Why is X axis label wrong

Rasm

Rasm

Quadophile

Hon. Acoustical Engineer

Rasm

Quadophile

Hon. Acoustical Engineer