Contour Graphs

**maani** · 08-24-2009, 09:36 AM

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

**Andy Pope** · 08-24-2009, 11:26 AM

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.

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

**maani** · 08-24-2009, 11:41 AM

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

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

**Andy Pope** · 08-24-2009, 11:45 AM

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.

    Application.Calculation = xlSemiautomatic

**maani** · 08-24-2009, 12:01 PM

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

**StephenR** · 08-24-2009, 12:45 PM

In case it's of use, here is the original thread:
http://www.excelforum.com/excel-prog...a-table-4.html

**maani** · 08-26-2009, 05:37 AM

Hi Andy,

I just noticed that another macro I have in my workbook looks like it has been affected by the existance of the data table and the contour graphs. That is, when I hit the 'update' button on the 'BBG Raw Data' tab, calculation mode switches from 'automatic except data tables' to 'automatic.' This particular macro is not supposed to update data tables, but for some reason it is; it is simply trying to update data from one sheet to another. I tried to store the current calculation setting in a variable, lngCalcMode, as you had done prior to setting the calculation mode to Manual, but it still is updating the data tables when I hit update. The code is as follows:

Sub Dynamic_Table()
    
Dim RowC, Colc As Integer
Dim CRow, CCol As Integer
Dim i As Integer
Dim SupName As String

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

'Delete the Data from "Data for Pivot"

With Sheets("Data for Pivot")
    .Range("A3", .Range("A3").End(xlDown)).Clear
    .Range("A3", .Range("A3").End(xlDown)).NumberFormat = "dd/mm/yyyy hh:mm"
End With

' Dynamically count Data in BBG Raw Data sheet

With Sheets("BBG Raw Data")
    .Range("A4", .Range("A4").End(xlDown)).Copy
    Sheets("Data for Pivot").Range("A3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
End With

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

' Also update the chart

X_Axis_Scale

End Sub

How would this particular macro need to change in order to run so that when I hit 'update', it doesn't update the data tables?

Thanks,

Maani

**Andy Pope** · 08-26-2009, 07:36 AM

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.

**maani** · 08-26-2009, 07:49 AM

Would this be correct?

Sub Dynamic_Table()
    
Dim RowC, Colc As Integer
Dim CRow, CCol As Integer
Dim i As Integer
Dim lngCalcMode As XlCalculation
Dim SupName As String

Application.ScreenUpdating = False
lngCalcMode = Application.Calculation
Application.Calculation = xlCalculationManual

'Delete the Data from "Data for Pivot"

With Sheets("Data for Pivot")
    .Range("A3", .Range("A3").End(xlDown)).Clear
    .Range("A3", .Range("A3").End(xlDown)).NumberFormat = "dd/mm/yyyy hh:mm"
End With

' Dynamically count Data in BBG Raw Data sheet

With Sheets("BBG Raw Data")
    .Range("A4", .Range("A4").End(xlDown)).Copy
    Sheets("Data for Pivot").Range("A3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
End With

Application.ScreenUpdating = True
Application.Calculation = lngCalcMode

' Also update the chart

X_Axis_Scale

End Sub

**Andy Pope** · 08-26-2009, 07:52 AM

Correct in the sense of the code yes.

What state is calculation in when the routine has finished?
Perhaps the X_Axis_Scale routine is changing things as well.

**maani** · 08-26-2009, 10:18 AM

It's in automatic except data tables. It seems to be working. The only issue is that my original worksheet (this is a condensed version) is very large and thus it takes quite a bit of time everytime the data tables recalculate. is there a way to make the recalculation of the data tables go faster? Or to have the data tables not recalculate upon saving the worksheet?

Contour Graphs

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Contour Graphs

re: Contour Graphs

re: Contour Graphs

re: Contour Graphs

re: Contour Graphs

re: Contour Graphs

re: Contour Graphs

re: Contour Graphs

re: Contour Graphs

re: Contour Graphs

re: Contour Graphs

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions