CHART Formula Change VBA

[xluser2007]

Hi All,

I want to change the range references for 2 series of a chart using VBA.

The Chart name (after doing Shift + LeftClick) is "Chart 44".

It has 2 series:

• Actual

=SERIES('Section 4 - Case Management2'!$A$218,'Section 4 - Case Management2'!$B$216:$S$216,'Section 4 - Case Management2'!$B$218:$S$218,1)

and

• Forecast

=SERIES('Section 4 - Case Management2'!$A$219,'Section 4 - Case Management2'!$B$216:$S$216,'Section 4 - Case Management2'!$B$219:$S$219,2)

I want to change actual formula references from $218 to $252 and forecast from $219 to $259.

I have tried the following code (as adapted from Jon Peltier's helpful site):

[vba]Sub ChangeWorkbookChartFormula()

Dim chtObj As Excel.ChartObject
Dim wksht As Excel.Worksheet
Dim mySrs As Series

For Each chtObj In ThisWorkbook.Worksheets("Section 4 - Case Management2").ChartObjects

Debug.Print UCase(chtObj.Name)

If UCase(chtObj.Name) = "CHART 44" Then

For Each mySrs In chtObj.Chart.SeriesCollection

Select Case UCase(mySrs.Name)

Case "ACTUAL"

mySrs.Formula = WorksheetFunction.Substitute(mySrs.Formula, "$218", "$252")


Case "FORECAST"

mySrs.Formula = WorksheetFunction.Substitute(mySrs.Formula, "$219", "$259")

End Select

Next mySrs

End If

Next chtObj

End Sub[/vba]
there is no error and it is recognising teh charts and series as I Step through, but no change occurs in the series ranges!

Could anyone please help to correct for this?

I can't post the workbook up due to confidentiality.

[mdmackillop]

debug.print mySrs.Formula

Can you post the result?

[xluser2007]

[xluser2007]

Hi md,

I think my previous post was too large and got cut.

Basically, the code in my original post worked, just rebooted Excel and re-ran and it worked fine.

I have written code to do my required task, but it is far too large and could be made more maintable and elegant (as it repeats itself with different parameters).

I've attached my code in a TXT file for reference.

Any help to make this more elegant and maintanble is appreciated.

[xluser2007]

Can anyone help with this code.

Basically I just need to know how to clean up the repetitive structure by calling smaller macros.

Any help is appreciated.

[xluser2007]

Any ideas VBAXers, really keen to learn how to write this code better .

[Bob Phillips]

The first thing that I would do is to rename the charts to something more meaninful.

Then I would take all the Case actions that are more than 1 screen (or even close), and put them into separate functions.

Then I would check all of those extra functions to see which could be made into genaeric routines.

[xluser2007]

Hi Bob,

many thanks for your reply.

The first thing that I would do is to rename the charts to something more meaninful.

Agree with you, that would make it easier to read.

Then I would take all the Case actions that are more than 1 screen (or even close), and put them into separate functions.

Then I would check all of those extra functions to see which could be made into genaeric routines.

Again, fully agree, though I wasn't quite sure how to do this, as I was using 'With End With' operators and didn;t know how to pass the specific generic routines through.

For example there are 2 that need to be called, but needed some help writing them:

Routine 1:

[vba]Case "CHART 51"

With chtObj.Chart

.HasTitle = True
.ChartTitle.Text = "Number of Type1 Claims 1987 - 1998"

For Each mySrs In .SeriesCollection

With mySrs

Debug.Print UCase(.Name)

Select Case UCase(.Name)

Case "ACTUAL"

strTemp = WorksheetFunction.Substitute(.Formula, "'Section 4'!$B$218:$S$218", "'Section 4'!$B$238:$S$238")
.Formula = strTemp

Case "FORECAST"

strTemp = WorksheetFunction.Substitute(.Formula, "'Section 4'!$B$219:$S$219", "'Section 4'!$B$245:$S$245")
.Formula = strTemp

End Select

End With ' mySrs

Next mySrs

wksht.Activate
Range("A1").Select[/vba]

Routine 2:

[vba]'------------------------------------------
' Call the macro to update the chart scale
' Y-Axis scale to dsiplay the
' MINIMUM and MAXIMUM values
'------------------------------------------
ActiveSheet.ChartObjects(chtObj.Name).Activate
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.MinimumScaleIsAuto = True
.MaximumScaleIsAuto = True
.MinorUnitIsAuto = True
.MajorUnitIsAuto = True
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
wksht.Activate
Range("A1").Select



End With ' chtObj.Chart[/vba]

Any ideas how to make the bold code above generic Private Functions to call on repeatedly, for example?

Really keen to hear your thoughts .

[Bob Phillips]

On that first one, I don't think I would bother making that a separate routine, it seems too small to woryy about. I would replace Worksheetfunction.Substitute with the VBA Replace though.

On the second, would all of those properties need to be potentially amendable within that function?

[xluser2007]

On that first one, I don't think I would bother making that a separate routine, it seems too small to woryy about. I would replace Worksheetfunction.Substitute with the VBA Replace though.

Hi bob, I know its small, though I would really like to make it into a simple callable sub, if for no reason other than to shorten the code.

Basically the Sub that i was thinking of should be something like:

[vba]
Sub ChartModify(Chart_Workbook, Chartworksheet, Chartname, Chartseriestomodifyname, StringtoReplace, NewString)

' Need code here

End Sub[/vba]

That way I just call on one code, and all detaiuls for the chart to be modified are explicitly passed through - making it a very generalised routine, when looping through the worksheets.

Any ideas on how to write this.

The reason I ask this is because I had to go back in and modify this code for the chart references and scrolling through and ensureing I was adapting the right chart with the right range was very difficult - would be much easier to adapt a single line of a called Sub with relevant parameters, as above.

On the second, would all of those properties need to be potentially amendable within that function?

Not sure exactly what you mean here Bob. If you mean to have the function pass through Boolean values for each of the chart conditions (e.g. .MinimumScaleIsAuto, .MaximumScaleIsAuto), then yes that would be great to learn.

[xluser2007]

Hi Bob,

Did you get a chance to look into this by any chance?

Any other VBAX experts have any thoughts on generalising the routine (I understand that Bob may be very busy atm).

Any help appreciated.

[Bob Phillips]

It is not that I am so busy, but I have difficulty getting internet access at the moment, I only get about an hour a day at the end of the day.

On the first point, sometimes by creating functions it can actually make the code less manageable IMO, it makes it harder to see exactly what is going on. But ... as this seems tobe where you want to go, and that is your prerogative, I would return to my way of doing these things which is with a class module. I would have a class that manages all of the chart properties that I am interested in, and then just change ranges and so on as a property of the class.

On the second one, again I would think that would be nicely handled as properties of the class.

[xluser2007]

It is not that I am so busy, but I have difficulty getting internet access at the moment, I only get about an hour a day at the end of the day.

On the first point, sometimes by creating functions it can actually make the code less manageable IMO, it makes it harder to see exactly what is going on. But ... as this seems tobe where you want to go, and that is your prerogative, I would return to my way of doing these things which is with a class module. I would have a class that manages all of the chart properties that I am interested in, and then just change ranges and so on as a property of the class.

On the second one, again I would think that would be nicely handled as properties of the class.

Hi Bob,

Glad you are back. First off I understand completely. When I said you were probably busy, I just meant you may have limited time to look into these problems given your work Schedule, or in this case your internet access. I hope all is well with you.

With regards to the above, could you please given me an example of the class. I am really keen to learn. If you feel the class module is a better way to manage chart properties that then that would be a great thing to know. I am only really starting to appreciate the Object model and the power of classes, and realising that effectively whenever one refers to a workbook object, they are simply calling on an instance of that class. So our previous discussios on class modules are starting to sink in (albeit slowly ).

regards,

[Bob Phillips]

I think a class is a better way to manage all custom objects, and I am reading this that you want a custom object(s), albeit implementing a standard Excel object (a chart).

I will knock up a quick demo, not necessarily based on your post but of a chart manager class, and post that later, We can then dicuss from there, building it or whatever based upon our chat.

[Bob Phillips]

Here is a first simple shot for you to get your teeth into.

I have created a class that sets up very basic charts, and a demo of same. When you have got this clear in your head we can talk about adding extra properties.

The one thing to note at this juncture is how I use the chart type enumerated list as my data type for defining the chart. This means that when you go to set the charttype, you get intellisense, which is a big bonus IMO (you know that I love enumerated lists).

[xluser2007]

Here is a first simple shot for you to get your teeth into.

I have created a class that sets up very basic charts, and a demo of same. When you have got this clear in your head we can talk about adding extra properties.

The one thing to note at this juncture is how I use the chrta type enumerated list as my data type for defining the chart. This means that when you go to set the charttype, you get intellisense, which is a big bonus IMO (you know that I love enumeratred lists).

Hi Bob, you are too kind.

Thanks you for providing this example. As I am not that familiar with Classes, it may take me a couple of days to fully appreciate what you have done here, so bear with me as I can compile any questions.

Thanks once again for teaching me, appreciate your help .

[Bob Phillips]

No problems, I didn't add too much up-front so that you could assimilate the principles before we get down and dirty. I'll await your comments.

[Bob Phillips]

So are you making progress with this?