• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Scrolling charts in VBA

S

Shaun

Member
Hi Everyone


I am trying to solve a issue.


I have a series of charts which are prepared using VBA. currently the charts are prepared in fixed periods, 1 month and 3 months. I would like to integrate the techniques found on Andy Pope's website:


http://www.andypope.info/charts/Scrolling.htm


I would still like to maintain the the 1 month and 3 months as a "resolution"


I have no idea how to even begin.


I have uploaded a sample workbook and Andy Pope's workbook demonstrating the technique, but use one scroll bar to control all charts.


http://rapidshare.com/files/1944978836/trading.xlsm

http://rapidshare.com/files/4023783711/scroller.xls


Any guidance and/or assistance would be greatly appreciated


Cheers


Shaun
 
Hi Narayan


I think I have applied the correct account settings, could you please re-try?


Cheers


Shaun
 
Hi All


I have managed to get a scrolling chart together utilising VBA. The Y axis (dates) scrolls as I had hoped AND I was able to retain the resolution that I was after.


The problem I have now is that the X axis labels are fixed and I am unable to work out how to apply a similar treatement.


Currently the X axis labels are defined by:

[pre]
Code: 
Dim Max As String
Dim Min As String
Max = ActiveSheet.Range("$AH$1")
Min = ActiveSheet.Range("$AH$2")
where:

[pre][code]$AH$1 =ROUNDUP(MAX(AF5:AF34,AK5:AK34,AL5:AL34),2)
$AH$2 =ROUNDDOWN(MIN(AG5:AG34,AK5:AK34,AL5:AL34),2)
and then applied:

With ActiveChart.Axes(xlValue)
.MinimumScale = Min
.MaximumScale = Max
.MinorUnitIsAuto = True
.MajorUnitIsAuto = True
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
[/pre]
When scrolling through the period the Min and Max updated on the spread sheet accordingly, but this update is not reflected in the chart X axis.


Would anyone be aware of how to make this automatically update also?


For anyone who may have wondered the same thing, here is the code I used to make the scrolling Y axis work. Please note, this will not work in the previously attached example file with out modification. If requested I will update the example file a upload.


The code is not pretty, but functional:

Dim eDate As Date
Dim sDate As Date
Dim rngRes As String
Dim rngNum As String
Dim rngScroll As String
Dim eCell As String
Dim FillRes As String
Dim Fill As String
eDate = ActiveSheet.Range("$A$5") 'End date of chart period
sDate = chart_period_date 'Start date of chart period
rngRes = Application.WorksheetFunction.NetworkDays(sDate, eDate) 'Number of weekdays
rngNum = Worksheets(Stock).Range("A:A").Cells.SpecialCells(xlCellTypeConstants).Count 'total number of cells in date colum (inc. header)
rngScroll = rngNum - rngRes 'Ensures no "blank" data is displayed
eCell = "=INDEX([" & Stock & ".xlsx]" & Stock & "!A5:A" & rngScroll + 4 & ",$AE$1)"
Windows("Share Trading.xlsm").Activate
Sheets("ASXCharts").Select
ActiveSheet.Range("$AE$1").Select
ActiveCell.FormulaR1C1 = rngScroll
ActiveSheet.Range("AD5").Select
ActiveSheet.Shapes.Range(Array("Scroll Bar 15")).Select
With Selection
.Value = 1
.Min = 1
.Max = rngScroll
.SmallChange = 1
.LargeChange = rngRes
.LinkedCell = "$AE$1"
.Display3DShading = True
End With
ActiveSheet.Range("$AD$5").Select
ActiveCell.Formula = eCell
Range("AD5").Select
Application.CutCopyMode = False
Worksheets("ASXCharts").Range("AD5:BD5").FillRight
Range("AD5").Select
Selection.NumberFormat = "m/d/yyyy"
Range("AE5:BD5").Select
Selection.NumberFormat = "General"
Range("AD5:BD5").Select
FillRes = rngRes + 12
Worksheets("ASXCharts").Range("AA5:BA" & FillRes).FillDown[/code][/pre]
Cheers


Shaun
 
Hi Shaun ,


I am not able to understand where the segment which manipulates the X-axis labels is placed within your code , but if the values of AH1 and AH2 are calculated only once , and the code is run after this calculation , then your X-axis labels should reflect the calculation.


Do you mean to say that the values of AH1 and AH2 keep on changing while you scroll horizontally ? If this is so , then you need to run the code for updating the X-axis labels as frequently as the values of AH1 and AH2 change.


Narayan
 
Hi Narayan


Yes the Max and Min values located at AH1 and AH2 respectively update as I scroll horizontally through the data.


The Min and Max values are picked up when the chart is being created. To explain in greater detail, once
Code: 
"=INDEX([" & Stock & ".xlsx]" & Stock & "!A5:A" & rngScroll + 4 & ",$AE$1)", which evaluates to [code]=INDEX('K:VariousMiscShare trading updatedstocks[awc.ax.xlsx]awc.ax'!A5:A2540,$AE$1), has been filled right and then down the Min and Max values automatically update. Then the first chart is created in the same fashion the two charts in the file previously posted are generated.


It is within the chart creation code that the Min and Min values are taken.

[pre][code]With ActiveChart.Axes(xlValue)
.MinimumScale = Min
.MaximumScale = Max
.MinorUnitIsAuto = True
.MajorUnitIsAuto = True
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With

So in essence, I would like to pull out:

[pre]With ActiveChart.Axes(xlValue)
.MinimumScale = Min
.MaximumScale = Max
End With[/code][/pre]

And make it behave the same as the Y axis.


I think it is possible because after the chart is created if I right mouse click the X axis>Format Axis>Axis Options and change Minimum from Fixed to Auto and Maximum from Fixed to Auto, the X axis behaves similarly to how I would like, but the range between the minimum and Maximum is too varied and without any consistency.


Writing this post might have given me an idea from where to start looking for a solution, I think that if I set:

.MinimumScaleIsAuto = True
.MaximumScaleIsAuto = True[/code][/pre]
AND, if Excel can “self scale” automatically there must be a way for me to manipulate that scale through VBA…hmmm…another challenge.


Any ideas?


I think I'm onto something:


http://newtonexcelbach.wordpress.com/2010/04/28/automating-chart-scale-limits/


Cheers


Shaun
 
Hi Narayan


After a bit research I have found exactly what I was after on Doug Jenkin's Blog - About Newton Excel Bach:


http://newtonexcelbach.wordpress.com/2012/11/25/automating-chart-scale-limits-update/


I am not sure what it is, I don't understand how it works. All I know is that it works and works very well. If anyone cares to have a look at the file, I would be very interested to know how it works.


Cheers


Shaun
 
Hi Shaun ,


I have downloaded the file SetScale.xlsm , and it makes use of only two properties :


.MinimumScale , .MaximumScale


That is why I cannot understand why your code did not work.


Anyway , if you have resolved your problem with this code , it's fine.


Narayan
 
Hi Narayan


I have no idea why it didn't behave as I thought it would, but it doesn't. There is a lot of discussion around about this issue, so I don't believe it is something that was experienced by just me, I have been wrong many times before!!!


Anyway I have gone from having absolutely no idea on how to get where I needed to be to being at the destination.


Cheers


Shaun
 
You must log in or register to reply here.
Back
Top