Add/remove month button for chart.
Solved/Closed
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
-
Apr 5, 2011 at 10:20 AM
RayH - Apr 26, 2011 at 03:31 PM
RayH - Apr 26, 2011 at 03:31 PM
Related:
- Add/remove month button for chart.
- Amd crossfire compatibility chart - Guide
- Chart gpt download - Download - Other
- Pdf and xps add in 2007 - Download - Other
- At button - Guide
- Wbs chart - Download - Organisation and teamwork
5 responses
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.
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)
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)
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
552
Apr 26, 2011 at 09:32 AM
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
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
aquarelle
Posts
7140
Registration date
Saturday April 7, 2007
Status
Moderator
Last seen
March 25, 2024
491
Apr 5, 2011 at 10:56 AM
Apr 5, 2011 at 10:56 AM
Hi Trowa,
Happy to see you :)
Could you post an workbook example, please ?
Best regards
Happy to see you :)
Could you post an workbook example, please ?
Best regards
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
552
Apr 7, 2011 at 10:31 AM
Apr 7, 2011 at 10:31 AM
Hi Aquarelle,
Good to see you too,
Here is my workbook:
https://authentification.site/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
Good to see you too,
Here is my workbook:
https://authentification.site/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
Posts
7140
Registration date
Saturday April 7, 2007
Status
Moderator
Last seen
March 25, 2024
491
Apr 7, 2011 at 03:05 PM
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
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
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
552
Apr 8, 2011 at 08:13 AM
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
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
Posts
7140
Registration date
Saturday April 7, 2007
Status
Moderator
Last seen
March 25, 2024
491
Apr 14, 2011 at 01:54 PM
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 :)
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
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
552
Apr 15, 2011 at 09:24 AM
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.
Just glad to see you are still thinking about me ;)
Hope to hear from you soon.
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.
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
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
552
Apr 15, 2011 at 09:16 AM
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
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.
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.
Didn't find the answer you are looking for?
Ask a question
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
552
Apr 15, 2011 at 10:37 AM
Apr 15, 2011 at 10:37 AM
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
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.
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
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
552
Apr 18, 2011 at 08:38 AM
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
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?
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
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
552
Apr 22, 2011 at 09:36 AM
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
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
