Add/remove month button for chart. [Solved/Closed]

TrowaD 2438 Posts Sunday September 12, 2010Registration dateContributorStatus October 16, 2018 Last seen - Apr 5, 2011 at 10:20 AM - Latest reply:  RayH
- Apr 26, 2011 at 03:31 PM
Hello,

My chart receives data from columns. Each column is a month.
Now I want to create two buttons:
First button to add a month and another to subtract a month.

I used the macro recorder to get me started:
    ActiveChart.SeriesCollection(1).XValues = "=Data!R3C3:R3C29" 
    ActiveChart.SeriesCollection(1).Values = "=Data!R19C3:R19C29"

Now when I hit the button "Add month" I want to change it to look like:
    ActiveChart.SeriesCollection(1).XValues = "=Data!R3C3:R3C30" 
    ActiveChart.SeriesCollection(1).Values = "=Data!R19C3:R19C30"

When I hit the button again the 30 becomes 31 and so forth.

I tried this with no succes:
    ActiveChart.SeriesCollection(1).XValues = "=Data!R3C3:R3C" & x 
    x = Value + 1

Kindly help me out with this query.
Thanks in advance for the time taken.

Best regards,
Trowa
See more 

18 replies

Best answer
1
Thank you
Dim x As Integer

Sub AddMonth()
ActiveChart.PlotArea.Select

    ActiveChart.SeriesCollection(1).XValues = "=Data!R3C3:R3C" & x
    ActiveChart.SeriesCollection(1).Values = "=Data!R19C3:R19C" & x
    x = x + 1
   
End Sub

Sub setx()
Let x = 3
End Sub


The macro setx is just to give the variable x an initial value. it could be set from a cell value.

Thank you, RayH 1

Something to say? Add comment

CCM has helped 1295 users this month

Best answer
1
Thank you
As you have already discovered, a BYTE can contain 256 values (0 to 255), and an INTEGER consists of 2 BYTES.
So, an INTEGER can contain 256*256=65536 WHOLE NUMBER values.
If you do not assign the variable type Excel automatically assigns the VARIANT type.
I would just say that defining your variables as certain types is just good practice.
Others may want to step in with their own points of view.
Maybe this topic is not the place for this discussion and another could be created.

Back to the charts....
Excel will need to know the starting point, that is, the date that the first column number.
From there a simple formula could be constructed to determine the current month value.

e.g.
Starting Month=Jan 2010
Current Month=April 2011
Current Month-Starting Month=difference in months = 15

Therefore x= Chart column offset =15

To do this in VBA...

DiffMonths = DateDiff("M", startmonth, currentmonth)

Thank you, RayH 1

Something to say? Add comment

CCM has helped 1295 users this month

TrowaD 2438 Posts Sunday September 12, 2010Registration dateContributorStatus October 16, 2018 Last seen - Apr 26, 2011 at 09:32 AM
Thanks RayH,

The DiffMonths part works very well.

Can't help myself to ask you one more question about "Dim as":
Why did you use Integer instead of Byte when the x value won't exceed the 255?
Just to be safe?

Anyway thanks for your assistence.

Best regards,
Trowa
Glad that worked ok for you.
Regarding the integer vs Byte. Just a habit. Plus you never know one day you might need more values.
Don't think 1 byte will make a difference to the amount of RAM required to store it, don't have a ZX81 anymore ;)
aquarelle 7178 Posts Saturday April 7, 2007Registration dateModeratorStatus May 25, 2018 Last seen - Apr 5, 2011 at 10:56 AM
0
Thank you
Hi Trowa,

Happy to see you :)

Could you post an workbook example, please ?

Best regards
TrowaD 2438 Posts Sunday September 12, 2010Registration dateContributorStatus October 16, 2018 Last seen - Apr 7, 2011 at 10:31 AM
Hi Aquarelle,

Good to see you too,

Here is my workbook:
http://www.speedyshare.com/files/27833629/Post-Omzet_Groei_Kioskea.xls

The reason for the button's (which aren't created yet) is so that I can see what an extra (or one less) month of data will do to the trendline.
Don't know if the code has a different structure if there are more lines involved. If that is the case then please look at the chart on sheet "Prijs per poststuk", which contains a chart with 4 lines (and is also the prettiest one).

I'm looking forward to your reply and thank you for helping me out once again!

Best regards,
Trowa
aquarelle 7178 Posts Saturday April 7, 2007Registration dateModeratorStatus May 25, 2018 Last seen - Apr 7, 2011 at 03:05 PM
Hi Trowa,

I'm not sure that you need to use a macro because if I understood your need, it seems that you need a sort of dynamical update of your chart and then, when you add a new month and a new column data, you want the graph shifts a month.
Is it right?

Best regards
TrowaD 2438 Posts Sunday September 12, 2010Registration dateContributorStatus October 16, 2018 Last seen - Apr 8, 2011 at 08:13 AM
Not exactly Aquarelle.

The point is that when the graph is updated automatically, I can't see what is does to the trendline.

So I want to be able to see how the trend is developing. If months are removed from the graph, by clicking a button multiple times, is the trend increasing or decreasing? I can also find out when the trend started to increase or decrease.

A dynamic update of the graph is also nice and welcome, but only in addition to the ability to add and remove months at will.

Best regards,
Trowa
aquarelle 7178 Posts Saturday April 7, 2007Registration dateModeratorStatus May 25, 2018 Last seen - Apr 14, 2011 at 01:54 PM
Hi Trowa,

Don't worry, I think of you. I made some search but I haven't much time this week. I'm trying to do something as soon as possible.

Best regards :)
TrowaD 2438 Posts Sunday September 12, 2010Registration dateContributorStatus October 16, 2018 Last seen - Apr 15, 2011 at 09:24 AM
Hi Aquarelle,

Just glad to see you are still thinking about me ;)

Hope to hear from you soon.
0
Thank you
Sub AddMonth()
ActiveChart.PlotArea.Select

ActiveChart.SeriesCollection(1).XValues = "=Data!R3C3:R3C" & x
ActiveChart.SeriesCollection(1).Values = "=Data!R19C3:R19C" & x
x = x + 1

End Sub

The variable 'x' needs to be defined before this will work. I DIMmed a global variable "DIM x as integer" and ran this macro before the AddMonth one.
Sub setx()
Let x = 3
End Sub

Seems to work ok.
TrowaD 2438 Posts Sunday September 12, 2010Registration dateContributorStatus October 16, 2018 Last seen - Apr 15, 2011 at 09:16 AM
Thanks for your reply RayH.

Unfortunately I can't get it to work.

Also when x is set to 3 and I want to get 29, I will have to run the macro 26 times.
Then I want to get to 30, x is still set to 3, so to get to 30 I will need to run the macro 27 times just to add a single month!

I will need something that will recognise what the graph is displaying and then run the macro a single time to add a single month.

I haven't seen anybody use "Let" in a code before. Excel help says something about it being a style. Can you explain something about the use of Let?

Best regards,
Trowa
I just set it to 3 as an initial value. you can set it to what ever you want. The initial value could be in a cell somewhere and assigned accordingly.
As it stands I just ran the add_month routine many times and each time the graph changed as I imagine you want it to.

How is it not working? Do you get an error? What version of Excel are you using, it may be having an effect. I'm using 2007.

Regarding the use of 'Let', I guess its just habit from my BASIC days. Doesn't appear to make any difference.
TrowaD 2438 Posts Sunday September 12, 2010Registration dateContributorStatus October 16, 2018 Last seen - Apr 15, 2011 at 10:37 AM
0
Thank you
Hi RayH,

I'm using excel 2003.
When I ran the code:
- My graphline vanished, only trendline is still there.
- The x-axis text vanished except for "jan 2009".
- No error messages were shown.
Running the code multiple times didn't make any difference.

I know your 3 was just an example, as was my 26 times.
To change the initial value in a cell means I have to calculate which number to input according to the months I want to show. That is not preferable since I might not use the file for a month to built up data and I might be out of it and besides that it should be easy to use for other users as well.

Best regards,
Trowa
not sure whats going on then.
The sheet show TWO graphs the only one that is changing is the one between rows 1and 3.
This graph shows four graphlines.
so for me:
I run the setx sub ONCE to set the inital value, then run the Add_Month sub many times.
The x-axis does not change.
The blue graph line add a month for each time the add_month sub is run.
The other 3 graphlines disappear I assume as they are not being modified. Not sure on this.
TrowaD 2438 Posts Sunday September 12, 2010Registration dateContributorStatus October 16, 2018 Last seen - Apr 18, 2011 at 08:38 AM
Sorry Rayh, I didn't use the line "Dim x As Integer". Never completely understood the use of Dim As. I always thought the Dim As was just to make codes to run smoother, guess I was wrong.

So does "Dim x As Integer" means that Excel remembers the x value for use in different codes?

And I still would really like Excel to see/know at which month the graph is currently at.

Best regards and thanks for the effort,
Trowa
Sorry for the late reponse, I've been tied up with work,
The 'dim x as integer' statement merely define the variable x as an integer type. The statement alone does not means it remembers the value. Its placement inside a sub means that only the sub knows about it, placed outside the rest of the sub defines it as a global variable and means that the variable can be referenced by any sub or function.
Could the 'current' month value be placed in a cell (perhaps out of view). The setx sub could read this value and place the value into x. The add month could then use this x variable to display the month dedfined by x. At the end of the add month sub the value in the cell read into x could be increased.
In fac the setx sub functionality of reading the cell and assigning the value into x could be done in the add month sub.
Make any sense? Sound feasible?
TrowaD 2438 Posts Sunday September 12, 2010Registration dateContributorStatus October 16, 2018 Last seen - Apr 22, 2011 at 09:36 AM
Hi RayH,

As I stated before I have a hard time understanding the dim as principle. So let me ask another question about it:
Why dim x as Integer and not for example Variant or Byte?

When I use excel help on the variabels this comes out:
Integer: Use for whole numbers of 2-bytes (2-bytes? meaning 2 digits?) in the range of -32.768 to 32.767.
Byte: Use for positive numbers in the range of 0 to 255.
Variant: Can be used for anything?

I could add more variables, but hopefully I made myself clear in being confused about the use of variabels. Am I misinterpreting them and why can't excel see what kind of number x turns out to be?

***************************

Now back to my charts.
The 'current' month can be placed in a cell. Preferably use an inputbox since the chart fills up the whole screen. But how do you transform the month + year (like April 2011) into the column number that excel needs to adjust the chart?

Thanks for the patience to answer my questions,
Trowa