Thread: Contour Graphs

Hello,

I have 4 contour graphs (see tab labeled 'Charts') that are updating based on the data from 4 dynamic data tables (see cells AE21:AJ51 on 'Mov_Avg_Chart' tab). The values in the dynamic data tables change based on the inputs in the optimisation table (see cells B22:B27 in 'Mov_Avg_Chart' tab). The automatic generation of the data tables and the 4 contour graphs has been implemented through a macro (named 'X'). Once you hit the update button next to the optimisation table, the data tables update, as do the 4 contour graphs. The graphs end up being loaded on top of each other, but if you move each one out of the way, you can see them. I am having 3 issues which I cannot seem to figure out:

1) The axes on the contour graphs are switched. That is, the values on the x-axis should be on the y-axis and vice versa. I am not sure how to switch these.

2) I would like each contour graph to have a title, based on the statistic that is being optimised in the top left cell in each data table. For example, chart 1 is an optimisation of the Annualized Return (see cell AE21, which is referencing cell AF7, which is the annualized return).

3) My calculation method is currently set to 'automatic except data tables.' However, whenever I hit the 'update' button next to the optimisation table, the calculation method switches to automatic. I'm guessing this may be due to the way the macro was written. Would someone be able to look at the macro and see if it is written in the way it should be?

I have been trying to go through these 3 issues all weekend, and would appreciate any assistance. I have attached a condensed version of my file.

Kind Regards,

Maani

1. If you change the layout of your data slightly, so the top left corner of the data matrix is empty you can use the Switch Row/Column button on the Design tab to switch Axis.
Note this will affect your update macro.

2. You can link the chart title to a cell.
http://www.andypope.info/tips/tip001.htm

3. Your code is setting calculation back to automatic at the end of the update routine.

Code:

With Application
    .EnableEvents = True
    .ScreenUpdating = True
    .DisplayAlerts = True
    .Calculation = xlCalculationAutomatic   ' Did you mean this??
End With

Hi Andy,

Thanks for your response and help.

1) If I change the layout as you suggested to leave the top left cell empty, where would I put the statistic that I seek to optimise (the cell that is currently highlighted). Also, how would the code need to be changed? I have tried to play around with this by switching the xlRows and xlColumns in the code but this doesn't work.

2) Thanks for the link on this. Is there a way to automate it? The ranges of the charts and values are always changing and thus I wanted something to be able to automate this by putting it into the macro. Is this possible?

3) When I changed the code to

Code:

With Application
    .EnableEvents = True
    .ScreenUpdating = True
    .DisplayAlerts = True
    .Calculation = xlCalculationSemiautomatic
End With

End Sub

the data table doesn't update. That is why it was set to xlCalculationAutomatic. is there a way to have it so that the calculation method remains what it is. That is, if it is set at 'automatic except for data tables' unless i hit the update button and after I hit update it goes back to 'automatic except for data tables'?

Thanks again,

Maani

Just move the topleft cell of the tables over 1 column or up 1.
Anything to help excel use the top row/column as axis labels.

I will try and look at your code tomorrow.

Using the macro recorder setting Calculation except for tables generates this code.

Code:

    Application.Calculation = xlSemiautomatic

Thanks for your response, Andy.

1) I kept the top-left cell of each data table empty, but the optimised statistic appears in the cell nonetheless once I hit update. Also, by moving the top-left value to a column up or across, it will affect the macro because it's been set to be a dynamic data table, so it will take values above and to the right of the data table. The reason I did this was so that the data table could adjust to an increase and decrease in values from the optimisation table (cells B22:B27).

3) I also tried to change the code to the one you mentioned below, but the data table didn't update when using this.

Thanks for taking the time to look at the code tomorrow. This has been something of a work in progress for quite awhile now, and as Stephen points out, there has been another thread on this same topic. Thanks to Stephen's help with the code, I have gotten to this point.

Thanks for your time,

Maani

Revised code

Code:

Sub x()

Dim nMinAP As Long, nMaxAP As Long, nStepAP As Long
Dim nMinAG As Long, nMaxAG As Long, nStepAG As Long
Dim i As Long, nLastcol As Long, nlastrow As Long
Dim rRow, rCol, rRef, j As Long
Dim rStart As Range, rData As Range
Dim lngCalcMode As XlCalculation
Dim rngChartOutput As Range

With Application
    .EnableEvents = False
    .ScreenUpdating = False
    .DisplayAlerts = False
    lngCalcMode = .Calculation
    .Calculation = xlCalculationManual
End With

On Error Resume Next
Sheets("Charts").Delete
On Error GoTo 0
Sheets.Add(After:=Sheets(Sheets.Count)).Name = "Charts"

Set rngChartOutput = Worksheets("Charts").Range("B2:H14")

Sheets("Mov_Avg_Chart").Activate
nMinAP = Range("B22").Value
nMaxAP = Range("B23").Value
nStepAP = Range("B24").Value
nMinAG = Range("B25").Value
nMaxAG = Range("B26").Value
nStepAG = Range("B27").Value
Set rRow = Range("C8")
Set rCol = Range("C6")
rRef = Array("AF7", "AF9", "AF10", "AF11")

nlastrow = Cells.Find(What:="*", After:=Cells(1, 1), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
nLastcol = Cells.Find(What:="*", After:=Cells(1, 1), SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
If nLastcol < 9 Then
    Range(Cells(21, "AE"), Cells(nlastrow, "AE")).Clear
Else
    Range(Cells(21, "AE"), Cells(nlastrow, nLastcol)).Clear
End If

Set rStart = Range("AE21")

For i = LBound(rRef) To UBound(rRef)
    With rStart
        .Offset(1).Value = nMinAP
        .Offset(1).DataSeries Rowcol:=xlColumns, Type:=xlLinear, Step:=nStepAP, Stop:=nMaxAP
        .Offset(, 1).Value = nMinAG
        .Offset(, 1).DataSeries Rowcol:=xlRows, Type:=xlLinear, Step:=nStepAG, Stop:=nMaxAG
        With .CurrentRegion
            .Rows(1).Font.Bold = True
            .Columns(1).Font.Bold = True
        End With
    End With
    Set rData = rStart.Offset(1, 1).CurrentRegion
    With rData
        .NumberFormat = "0.00"
        .Table RowInput:=rRow, ColumnInput:=rCol
        .Select
        ActiveSheet.Shapes.AddChart.Select
        ActiveChart.ChartType = xlSurfaceTopView
        ActiveChart.Location xlLocationAsObject, "Charts"
        With ActiveSheet.ChartObjects(ActiveSheet.ChartObjects.Count)
            .Left = rngChartOutput.Left
            .Top = rngChartOutput.Top
            .Width = rngChartOutput.Width
            .Height = rngChartOutput.Height
        End With
        If i Mod 2 = 0 Then
            ' move to right
            Set rngChartOutput = rngChartOutput.Offset(, rngChartOutput.Columns.Count + 2)
        Else
            ' move down and left
            Set rngChartOutput = rngChartOutput.Offset(rngChartOutput.Rows.Count + 2, -rngChartOutput.Columns.Count - 2)
        End If

        Sheets("Mov_Avg_Chart").Activate
    End With
    With rStart
        .Formula = "=" & rRef(i)
        .Interior.ColorIndex = 17
    End With
    Set rStart = rStart.Offset(rStart.CurrentRegion.Rows.Count + 1)
Next i

With Application
    .EnableEvents = True
    .ScreenUpdating = True
    .DisplayAlerts = True
    .Calculate
    .Calculation = lngCalcMode
End With

End Sub

For chart titles add these lines to the routine

Code:

        ActiveSheet.Shapes.AddChart.Select
        ActiveChart.ChartType = xlSurfaceTopView
        ActiveChart.HasTitle = True
        ActiveChart.ChartTitle.Text = "='" & rStart.Parent.Name & "'!" & rStart.Address(, , xlR1C1)
        ActiveChart.Location xlLocationAsObject, "Charts"

At the start of the routine I store the current calculation setting in a variable, lngCalcMode, just before setting calculation to Manual.

Then at the end I restore calculation mode to the setting.

I did not have to switch the axes as that is the default for that matrix of data. If the matrix dimensions change then you may need to force a plot by rows

Code:

activechart.PlotBy=xlRows

Make the change after the chart has data in it.

Code:

        ActiveChart.ChartType = xlSurfaceTopView
        ActiveChart.PlotBy = xlRows
        ActiveChart.HasTitle = True
        ActiveChart.ChartTitle.Text = "='" & rStart.Parent.Name & "'!" & rStart.Parent.Range(rRef(i)).Offset(0, -1).Address(, , xlR1C1)
        ActiveChart.Location xlLocationAsObject, "Charts"

That code sets calculation back to automatic.

Use the same approach as before. Store the value before setting to manual.
Restore to content of variable rather than explicitly back to automatic.