asked on

Excel VBA - change x-axis range of an Excel chart

I need to change the source range of the x-axis of an excel chart by a certain number of rows with the code below. Unfortunately, my approach is not working. Can anybody fix this variant to do this or provide a working solution for my problem? Thanks a lot!

Sub ShiftXAxis()

Dim wb As Workbook
Dim wsData As Worksheet
Dim wsChart As Worksheet
Dim shpRectangle As Excel.Shape
Dim strDailyUnitsShift As String
Dim vDateMove As Integer
Dim strMoveType As String
Dim vMinDateRow As Integer
Dim vMaxDateRow As Integer
Dim strChartSource As String
Dim vCurrentStartingRowXAxis As Integer
Dim vNewStartingRowXAxis As Integer

Dim rngNewXAxis As Range

Dim chrt As Excel.ChartObject
Dim strNewChartFormula As String

'set wb's | ws' | chrt | rectangle app caller
Set wb = ThisWorkbook
Set wsData = wb.Sheets("Data")
Set wsCharts = wb.Sheets("Charts")
Set chrt = wsCharts.ChartObjects("Chart 7")
Set shpRectangle = wsCharts.Shapes(Application.Caller) 'setting the shape that called the code

'set/get min/max | current x-axis parameters | shift change units
vMinDateRow = 3
vMaxDateRow = wsData.Cells(wsData.Rows.Count, 1).End(xlUp).Row
strChartSource = chrt.Chart.SeriesCollection(1).Formula
vStrPos = InStr(strChartSource, ":")
vCurrentStartingRowXAxis = Mid(strChartSource, 18, vStrPos - 18)
strDailyUnitsShift = shpRectangle.TextFrame.Characters.Text
vDateMove = Mid(strDailyUnitsShift, 2, 2)
strMoveType = Left(strDailyUnitsShift, 1)

'adjust new x-axis starting point
If strMoveType = "-" Then
   vNewStartingRowXAxis = vCurrentStartingRowXAxis - vDateMove
   Else
   vNewStartingRowXAxis = vCurrentStartingRowXAxis + vDateMove
End If

'check if new data exceeds data range | adapt
If vNewStartingRowXAxis < vMinDateRow Then
   vNewStartingRowXAxis = vMinDateRow
End If

If vNewStartingRowXAxis + 14 > vMaxDateRow Then
   vNewStartingRowXAxis = vMaxDateRow - 14
End If

strNewChartFormula = """" & "$A$" & vNewStartingRowXAxis & ":$J$" & vNewStartingRowXAxis + 14 & """"

rngNewXAxis = wsData.Range(strNewChartFormula)

With wsCharts
     With chrt
          .SeriesCollection(1).XValues = rngNewXAxis
     End With
End With

'chrt.Axes(xlCategory).SetSourceData Source:=Sheets("Data").Range("$A$1224:$J$1238")

'chrt.SetSourceData Source:=Sheets("Sheet1").Range("A1214:J1228")

End Sub

Open in new window

ExpExchAxisShifting.xlsm

redmondb

Hi, LifeIsSumproduct.

As a start, please see attached. Please let me know if you would like any of the following explained...
Line 54:
strNewChartFormula = "$A$" & vNewStartingRowXAxis & ":$J$" & vNewStartingRowXAxis + 14
Line 56:
Set rngNewXAxis = wsData.Range(strNewChartFormula)
Line 60:
.Chart.SeriesCollection(1).XValues = rngNewXAxis

Regards,
Brian.
ExpExchAxisShifting-V2.xlsm

LifeIsSumproduct

ASKER

Thanks a lot Brian.
I got your changes and they make sense. Initially, axis changes looked weird, then I realised that I had to replace the "J" in line 54 with an "A". Now it looks better.
Axis changes.
Strange thins is that line charts do not always change, although range (and accordingly data) change. I need to leave now but look at it again closer in a few hours.
But at first it looks pretty pretty good!
Thanks!

LifeIsSumproduct

ASKER

Ok. Now I looked at it very closely.
So while your answer to my question was correct and you made the code with your help now work without error, there is a huge flaw in my approach:

- while it does change the x-axis, it changes it only for (obviously) the first series, but what's even worse, it does not change the data range in sync.

do you have an easy hint how to approach this or shall I ask a new and separate question, what do you suggest?

redmondb

LifeIsSumproduct,

No problem, I'll look at it shortly.

Regards,
Brian.

ASKER CERTIFIED SOLUTION

redmondb

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

LifeIsSumproduct

ASKER

BRIAAAAAAAN!!!!!!!!!!! BRILLIANT!! ABSOLUTELY F.CKIN BRILLIANT! AWESOME!
and not only that it seems it does exactly what it should (at least after running a few tests), I learned a lot again from this! Thank you so much! Wish I could give you a million points!!! :-)

redmondb

Gee, thanks LifeIsSumproduct!

(On a more serious note, the code is quite fragile - things like including a series name or changing the sheet name are likely to break it. This link includes robust code for processing chart ranges if you're interested.)

LifeIsSumproduct

ASKER

thanks Brian!