Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Error when more than 60 items on X-axis
Hello,
I want to present three line-curves over a number of weeks. And I have the following code in my VB-program: ReDim WeekAxis(1 To iLastWeek) As Integer ..... (calculate ranges) ' Create Chart on new Worksheet (Left, Top, Width, Height) Set ch = Worksheets("TempGraph").ChartObjects.Add(5, 5, 600, 350) ch.Chart.ChartWizard Source:=Worksheets("TempGraph").Range("a60:t62"), _ gallery:=xlLine, Title:="Deliverables versus time" ch.Chart.SetSourceData Source:=Worksheets("TempGraph").Range("A60"). _ Resize(cNumRows, iNumCols) ' X-axis title and Category With ch.Chart.Axes(xlCategory) .CategoryNames = WeekAxis .HasTitle = True .AxisTitle.Text = "Week" End With The problem is when I dimension my "WeekAxis" variable: - When "iLastWeek" is 59 or less everything works OK But - When "iLastWeek" is 60 or more I get an error Has anybody any ideas about this? Do you need more info to understand my problem? /konpego |
#2
|
|||
|
|||
Is 'WeekAxis' a VBA array? There is a limit to how many characters Excel
will accept in the definition of XValues or Values, and apparently CategoryNames, which I rarely use. They must be short names, 2-3 characters each, or you'd crash well before 59 elements. Put this array into a worksheet range, and use this range for your CategoryNames. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ konpego wrote: Hello, I want to present three line-curves over a number of weeks. And I have the following code in my VB-program: ReDim WeekAxis(1 To iLastWeek) As Integer .... (calculate ranges) ' Create Chart on new Worksheet (Left, Top, Width, Height) Set ch = Worksheets("TempGraph").ChartObjects.Add(5, 5, 600, 350) ch.Chart.ChartWizard Source:=Worksheets("TempGraph").Range("a60:t62"), _ gallery:=xlLine, Title:="Deliverables versus time" ch.Chart.SetSourceData Source:=Worksheets("TempGraph").Range("A60"). _ Resize(cNumRows, iNumCols) ' X-axis title and Category With ch.Chart.Axes(xlCategory) .CategoryNames = WeekAxis .HasTitle = True .AxisTitle.Text = "Week" End With The problem is when I dimension my "WeekAxis" variable: - When "iLastWeek" is 59 or less everything works OK But - When "iLastWeek" is 60 or more I get an error Has anybody any ideas about this? Do you need more info to understand my problem? /konpego |
#3
|
|||
|
|||
Thanks Jon,
But I need more info (only the average VB_Excel programmer!). I know that my range starts at e.g "A50" but I only know that it contains, lets say 65 weeks. How do I translate my array /that has been dimensioned to "1 to 65" into a range starting at "A50". The array already exists as a WS range but I don't know how to translate into a range...? Please help me! /konpego "Jon Peltier" wrote: Is 'WeekAxis' a VBA array? There is a limit to how many characters Excel will accept in the definition of XValues or Values, and apparently CategoryNames, which I rarely use. They must be short names, 2-3 characters each, or you'd crash well before 59 elements. Put this array into a worksheet range, and use this range for your CategoryNames. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ konpego wrote: Hello, I want to present three line-curves over a number of weeks. And I have the following code in my VB-program: ReDim WeekAxis(1 To iLastWeek) As Integer .... (calculate ranges) ' Create Chart on new Worksheet (Left, Top, Width, Height) Set ch = Worksheets("TempGraph").ChartObjects.Add(5, 5, 600, 350) ch.Chart.ChartWizard Source:=Worksheets("TempGraph").Range("a60:t62"), _ gallery:=xlLine, Title:="Deliverables versus time" ch.Chart.SetSourceData Source:=Worksheets("TempGraph").Range("A60"). _ Resize(cNumRows, iNumCols) ' X-axis title and Category With ch.Chart.Axes(xlCategory) .CategoryNames = WeekAxis .HasTitle = True .AxisTitle.Text = "Week" End With The problem is when I dimension my "WeekAxis" variable: - When "iLastWeek" is 59 or less everything works OK But - When "iLastWeek" is 60 or more I get an error Has anybody any ideas about this? Do you need more info to understand my problem? /konpego |
#4
|
|||
|
|||
This fills the value of MyArray into the sheet, in a vertical range
starting at A50, extending far enough to hold all elements of MyArray: ActiveSheet.Range("A50").resize(UBound(MyArray,1)+ 1-LBound(MyArray,1)) _ .Value = MyArray - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ konpego wrote: Thanks Jon, But I need more info (only the average VB_Excel programmer!). I know that my range starts at e.g "A50" but I only know that it contains, lets say 65 weeks. How do I translate my array /that has been dimensioned to "1 to 65" into a range starting at "A50". The array already exists as a WS range but I don't know how to translate into a range...? Please help me! /konpego "Jon Peltier" wrote: Is 'WeekAxis' a VBA array? There is a limit to how many characters Excel will accept in the definition of XValues or Values, and apparently CategoryNames, which I rarely use. They must be short names, 2-3 characters each, or you'd crash well before 59 elements. Put this array into a worksheet range, and use this range for your CategoryNames. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ konpego wrote: Hello, I want to present three line-curves over a number of weeks. And I have the following code in my VB-program: ReDim WeekAxis(1 To iLastWeek) As Integer .... (calculate ranges) ' Create Chart on new Worksheet (Left, Top, Width, Height) Set ch = Worksheets("TempGraph").ChartObjects.Add(5, 5, 600, 350) ch.Chart.ChartWizard Source:=Worksheets("TempGraph").Range("a60:t62"), _ gallery:=xlLine, Title:="Deliverables versus time" ch.Chart.SetSourceData Source:=Worksheets("TempGraph").Range("A60"). _ Resize(cNumRows, iNumCols) ' X-axis title and Category With ch.Chart.Axes(xlCategory) .CategoryNames = WeekAxis .HasTitle = True .AxisTitle.Text = "Week" End With The problem is when I dimension my "WeekAxis" variable: - When "iLastWeek" is 59 or less everything works OK But - When "iLastWeek" is 60 or more I get an error Has anybody any ideas about this? Do you need more info to understand my problem? /konpego |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
change axis info on radar charts | Charts and Charting in Excel | |||
space between y axis and data points.. | Charts and Charting in Excel | |||
Second X axis at top of chart? | Charts and Charting in Excel | |||
Labels on Chart with Negative Value Axis | Charts and Charting in Excel | |||
Graphing Reversing the Axis | Setting up and Configuration of Excel |