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.

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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.

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

For chart titles add these lines to the routine

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

Make the change after the chart has data in it.

Please Login or Register  to view this content.

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.

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.