Problem with formatting an excel chart

[silverbird]

Hi,

I am rather new on all this so please tolerate my bad coding practices and tell me about them when you spot them (So I won't make the same mistakes again...).

I was making a simple macro for plotting 5 graphs on a single worksheet for me in excel. Initially, I started the programming in 07 and everything worked fine. However, I get a couple problem when I tested the code in 03.

Problem 1:
I have set font.size, .top and . left for all of the axis titles. I have also set the .height and .width of the .plotarea. However, when I run the code, the graph produced seem to have automatically scaled down the whole plot area and the size of the axis title text. I am wondering what would have caused that and how I can fix the problem.

Problem 2:
I also noticed a strange thing happening with the .top settings for the x axis title. The 07 version put it at the place I wanted it to go but the 03 version somehow minus the Top value for 100 pts.

Problem 3:
Even stranger, I noticed that if I put breakpoints into the "formatting setting" portion of the code and go step by step, the first 3 graphs would work correctly as I had intended (the 07 version result) but it reverts to the wrong version (With both problems 1 and 2) after the breakpoints are gone.... ?! I really have no idea why this would have happen so any suggestions would be helpful.

-------------------------------------------------------
Please find attached the code. It is a part of a bigger thing, so there might be a couple things that might be confusing. GenSettings and Search are two "custom" classes. strPlotRange is a module function that returns a =Sheet!.... string. GetGraphH or GetGraphL are functions returning the desired dimensions of the chart object.

Thanks!!

Public Sub SetGraph(ByRef sys As GenSettings, ByRef sch As Search)
    On Error GoTo ErrHandler:
    
    Dim Cht As Chart
    Dim ChtLabel As String
    Dim counter As Integer
    Dim Buffer1 As Variant
    Dim FirstRun As Boolean
    Dim Mode As Integer
    
    FirstRun = True
    
    Do While sch.GetChartTracker <= sch.GetTotGraphs()
        Set Cht = Charts.Add
        Set Cht = Cht.Location(Where:=xlLocationAsObject, Name:=sch.GetSheetName)
        
        With Cht
            .ChartType = xlXYScatterLines
            .HasLegend = False
            
            With .Parent
                Buffer1 = Range("A8").Top + (sch.GetGraphH() + 5) * (sch.GetChartTracker() - 1)
                Buffer1 = Buffer1 + 7 * Int(CInt(sch.GetChartTracker - 1) \ CInt(sch.GetNGraphs))
                .Top = Buffer1
                .Left = Range("A8").Left + 5
                .Width = sch.GetGraphL()
                .Height = sch.GetGraphH()
            End With
            
            Buffer1 = sch.GetPlotInlet()
            
            If sch.GetChartTracker() = Buffer1 Then
                ChtLabel = "Inlet Pressure (" & Worksheets(1).Cells(14, 2).Value & ")"
                Mode = 1
            Else
                Buffer1 = Buffer1 + sch.GetPlotOutlet()
                If sch.GetChartTracker() = Buffer1 Then
                    ChtLabel = "Outlet Pressure (" & Worksheets(1).Cells(14, 3).Value & ")"
                    Mode = 2
                Else
                    Buffer1 = Buffer1 + sch.GetPlotInter()
                    If sch.GetChartTracker() = Buffer1 Then
                        ChtLabel = "Intermediate Pressure (" & Worksheets(1).Cells(14, 4).Value & ")"
                        Mode = 3
                    Else
                        Buffer1 = Buffer1 + sch.GetPlotTemp()
                        If sch.GetChartTracker() = Buffer1 Then
                            ChtLabel = "Case Temperature (" & Worksheets(1).Cells(14, 5).Value & ")"
                            Mode = 4
                        Else
                            Buffer1 = Buffer1 + sch.GetPlotBatt()
                            If sch.GetChartTracker() = Buffer1 Then
                                ChtLabel = "Battery (" & Worksheets(1).Cells(14, 6).Value & ")"
                                Mode = 5
                            Else: GoTo ErrHandler:
                            End If
                        End If
                    End If
                End If
            End If
            
            ' Delete all series and ready the chart for data insertion
            Do Until .SeriesCollection.Count = 0
                .SeriesCollection(1).Delete
            Loop
            
            ' Insert new series
            With .SeriesCollection.NewSeries
                .Name = ChtLabel
                .Values = strPlotRange(Mode, sys, sch)
                .XValues = strPlotRange(0, sys, sch)
                .MarkerStyle = xlNone
                .Smooth = False
                .Shadow = False
                With .Border
                    .ColorIndex = 3
                    .Weight = xlThin
                    .LineStyle = xlContinuous
                End With
            End With
        End With
           
        FormatChart Cht, ChtLabel, 1, sch
        
        ' Housekeeping stuff
        Set Cht = Nothing
        sch.RecChartAdded
        FirstRun = False
    Loop
    
    PageSettings sch.oWS, sch.GetPLn(), sch.GetPHt()
    
    Exit Sub
    
ErrHandler:
    MsgBox "Unable to generate graphs! " & err.Description
    Exit Sub
End Sub

' Format the chart
Private Sub FormatChart(Cht As Object, ChtLabel As String, Mode As Integer, ByRef sch As Search)
    ' Chart title formatting settings
    With Cht
        .HasTitle = True
        With .ChartTitle
            .Characters.Text = "Plot of " & ChtLabel & " against time"
            .Font.Name = "Calibri"
            .Font.Size = 10
            .Font.Bold = True
            .Top = 2
        End With
            
        ' X axis label formatting settings
        With .Axes(xlCategory, xlPrimary)
            .HasTitle = True
            .HasMajorGridlines = True
            .HasMinorGridlines = True
            .MinimumScale = ThisWorkbook.Worksheets(1).Cells(sch.GetStartIndex, 1).Value
            .MaximumScale = ThisWorkbook.Worksheets(1).Cells(sch.GetEndIndex, 1).Value
            
            .TickLabels.Font.Size = 8
            
            With .MajorGridlines.Border
                .ColorIndex = 15
                .Weight = xlHairline
            End With
            
            With .AxisTitle
                .Characters.Text = "Time"
                .Font.Size = 8
                .Top = sch.GetGraphH() - 15
            End With
        End With
        
        ' Y axis label formatting settings
        With .Axes(xlValue, xlPrimary)
            .HasTitle = True
            .HasMajorGridlines = True
            
            .TickLabels.Font.Size = 8
            
            With .MajorGridlines.Border
                .ColorIndex = 15
                .Weight = xlHairline
                .LineStyle = xlContinuous
            End With
            
            With .AxisTitle
                .Characters.Text = ChtLabel
                .Font.Size = 8
                .Left = 3
            End With
        End With
        
        ' Plot area formatting
        With .PlotArea
            .Left = 20
            .Top = 25
            .Height = sch.GetGraphH() - 25 - 10
            .Width = sch.GetGraphL() - 25
            .Interior.ColorIndex = xlNone
            .Border.LineStyle = xlNone
        End With
    End With
End Sub

 

[pbornemeier]

Welcome to the Board!

1. Be sure to uncheck the Auto scale checkbox in the font tab for each of these, or in code .AutoScaling = False before manually setting any of the other font values.

2. Can't help - I don't have 2007, but when a similar thing occurred between version 2000 and 2003, some of my code while my company was fielding both versions used something like:

        Select Case Application.Version
    Case Is = 9 ' Excel 2000
    
    Case Is = 10 'Excel XP
    
    Case Is = 11 'Excel 2003
    
    Case Is = 12 'Excel 2007
    
    Case Else
    
    End Select

3. Ibid

 

[pbornemeier]

If your .Plotarea.Height was at its maximum and the .Plotarea.Top value was less than 25 when you try to set .Plotarea.Top to 25, it will not change, because the existing .Height setting will not "compress". This type of interaction may be the cause of your inability to set the top/height relationship you want. For any chart, I believe the plotarea.height+ plotarea.top will be less than chartarea.height-chartarea.top. Which ever of the .top or .height you set second will not be able to exceed the relationship in the above formulas.