Thread: 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

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

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
>

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
>>