Chart Size

[kritimehrotra]

Hi,

I have a sheet in Excel with about 8 small graphs. They are all "100% Stacked Bars with Cylindrical Shape". These charts are generated using VBA code, with each run of a program, since their data changes each time. What happens is, sometimes, the chart size of one or two of the charts just shrinks to about half its normal width. The chart area remains the same, and seemingly so does the plot area, but the actual chart itself reduces in size. Could anyone help figure out why that would happen?

Also, I have taken print-screen'ed bmps of the "good" scenario and the "bad" scenario, but am not sure how to attach them with my post.

Thank you very much,
Kriti

 

[Datsmart]

Is your Y axis scale format set to "Auto"? If not your chart will jump to sizes that may not be what you want.
You say your chart is generated using VBA code, if you post the code someone may be able to spot something there.

 

[kritimehrotra]

Hi,

Thanks so much for the quick reply. I just checked and the Y axis scale format is "Automatic", is that how it should be or should it be changed?

Here is the sub I use to generate the chart. For the charts I am referring to right now, the following are the arguments sent:
strLevel: "REGIONAL"
subNum: 2
strTitle: One of "A", "B", "C", "D" "E", "F", "G", "H"

Private Sub genChart(xlSht As Object, rngData As Range, strTitle As String, _
   strLevel As String, Optional subNum As Integer)
   
   Dim iLeft As Integer, iWidth As Integer, _
      iTop As Integer, iHeight As Integer, _
      iFontSize As Integer
   
   Select Case strLevel
   Case "NATIONAL"
      iLeft = 35
      iWidth = 535
      iTop = 100
      iHeight = 130
      iFontSize = 9
   Case "REGION"
      If subNum = 1 Then
         iLeft = 5
         iWidth = 590
         iTop = 100
         iHeight = 90
         iFontSize = 9
      Else
         If (InStr(strTitle, "A") <> 0) Then
            iLeft = 35
            iTop = 274
         ElseIf (InStr(strTitle, "B") <> 0) Then
            iLeft = 170
            iTop = 274
         ElseIf (InStr(strTitle, "C") <> 0) Then
            iLeft = 305
            iTop = 274
         ElseIf (InStr(strTitle, "D") <> 0) Then
            iLeft = 440
            iTop = 274
         ElseIf (InStr(strTitle, "E") <> 0) Then
            iLeft = 35
            iTop = 363
         ElseIf (InStr(strTitle, "F") <> 0) Then
            iLeft = 170
            iTop = 363
         ElseIf (InStr(strTitle, "G") <> 0) Then
            iLeft = 305
            iTop = 363
         ElseIf (InStr(strTitle, "H") <> 0) Then
            iLeft = 440
            iTop = 363
         End If
         iWidth = 125
         iHeight = 70
         iFontSize = 7
      End If
   Case "DIRECTOR"
      iLeft = 5 + (200 * ((subNum - 1) Mod 3))
      iWidth = 195
      If (CInt((subNum - 1) / 3) - ((subNum - 1) / 3)) <= 0 Then
         iTop = 340 + (80 * CInt((subNum - 1) / 3))
      Else
         iTop = 340 + (80 * (CInt((subNum - 1) / 3) - 1))
      End If
      iHeight = 75
      iFontSize = 7
   End Select
   
   With xlSht.ChartObjects.Add(Left:=iLeft, Width:=iWidth, Top:=iTop, Height:=iHeight).Chart
      .SetSourceData Source:=rngData, PlotBy:=xlColumns
      .ChartType = xlCylinderBarStacked100
      .HasTitle = True
      .ChartTitle.Text = strTitle
      .ChartTitle.Font.Size = iFontSize
      .Axes(xlCategory).HasTitle = False
      .Axes(xlSeries).HasTitle = False
      .Axes(xlValue).HasTitle = False
      .HasAxis(xlCategory) = False
      .HasAxis(xlSeries) = False
      .HasAxis(xlValue) = False
      .Axes(xlCategory).CategoryType = xlAutomatic
      .PlotArea.Top = .ChartTitle.Top + 15
      .PlotArea.Height = .ChartArea.Height - .ChartTitle.Top - 20
      .PlotArea.Left = 10
      If (strLevel = "NATIONAL") Or ((strLevel = "REGION") And (subNum = 1)) Then
         .ChartArea.Border.LineStyle = xlLineStyleNone
         .HasLegend = True
         With .Legend
            Dim i As Integer
            .Position = xlLegendPositionRight
            For i = 1 To .LegendEntries.count
               .LegendEntries(i).Font.Size = 8
            Next
         End With
         If strLevel = "REGION" Then
            .PlotArea.Top = 1
            .PlotArea.Height = .Parent.Height - 10
            .PlotArea.Left = 1
            .PlotArea.Width = .Legend.Left - 1
         End If
         .HasTitle = False
      Else
         .PlotArea.Width = .ChartArea.Width - 20
         .HasLegend = False
      End If
      If ((strLevel = "REGION") And (subNum = 2)) Then
         .HasTitle = False
         .PlotArea.Left = 1
         .PlotArea.Width = .Parent.Width - 10
         .PlotArea.Top = 10
         .PlotArea.Height = .Parent.Height - 20
      End If
      .Parent.Placement = xlFreeFloating
   End With
   
   xlSht.Select

End Sub

Thank you,
Kriti

 

[Datsmart]

Your code is full of sizing and chart positioning steps that I won't pretend to fully understand. If you created the code yourself then you need to walk through it and find where it is sizing your chart too small. If someone wrote it, then you can put some error trapping statements in it to see where it is causing your problems. Not having your data and chart available to work with, I am unable to help further. Anyone else able to help?

 

[kritimehrotra]

Hi Datsmart,

That's the reason I didn't post the code initially. The problem is not that one particular chart gets sized small each time. The problem is that it happens almost at random. The same chart in a different execution is fine. That's why I didn't think it was a problem with the code, but maybe just a general property of charts that I needed to make static or something.

Thanks,
Kriti