Error when more than 60 items on X-axis

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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/
_______
 
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
 
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/
_______
 
Back
Top