Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Very wierd problem. Please help
I wrote a piece of code to generate a bunch of charts(40) in a
worksheet. It was running smoothly and generate some beautiful charts. However, last Thursday it stops due to a run-time error while running, 23 charts has been generated. The error message is "Unable to set the HasTitle property of the Axis class". It is very wierd because the code is in a loop and it pass the first 23 charts and block the 24th. Then I tried some modification and thought the problem maybe related to the font limitation in a chart. (see http://support.microsoft.com/kb/215573) this DOESN'T work. Then I try to run this code twice and triple. Find it is interesting that at first, it stops at 24th. If you stop and re-run, it stops at 17th and give another error message, similar to "unable to set xxx property of xxx class". If you stop and re-run for third time. It just go through!!! What a damn!!! If you re-run for forth time, it stops at 24th again. then repeat. Please help me, save me! Following is the code. Set cht1 = wsChart.ChartObjects.Add(posLeft, posTop, posWidth, posHeight) With cht1.Chart .ChartType = xlLine .SeriesCollection.NewSeries .SeriesCollection(1).Name = "Projected Net Loss" .SeriesCollection(1).Border.ColorIndex = 25 .SeriesCollection(1).Border.Weight = xlMedium .SeriesCollection(1).Border.LineStyle = xlContinuous .SeriesCollection(1).XValues = rngMonth .SeriesCollection(1).Values = rngProLoss .SeriesCollection.NewSeries .SeriesCollection(2).Name = "Actual Net Loss" .SeriesCollection(2).Border.ColorIndex = 3 .SeriesCollection(2).Border.Weight = xlThick .SeriesCollection(2).Border.LineStyle = xlContinuous .SeriesCollection(2).XValues = rngMonth .SeriesCollection(2).Values = rngActLoss .SeriesCollection.NewSeries .SeriesCollection(3).Name = "PCG Projected Net Loss" .SeriesCollection(3).Border.ColorIndex = 33 .SeriesCollection(3).Border.Weight = xlMedium .SeriesCollection(3).Border.LineStyle = xlContinuous .SeriesCollection(3).XValues = rngMonth .SeriesCollection(3).Values = rngPCGProLoss .Legend.Position = xlLegendPositionBottom .HasTitle = True .ChartTitle.Characters.Text = rngTitle.Value & " " & rngTitle.Offset(0, 2).Value .ChartTitle.Characters.Font.Size = 10 .ChartTitle.Font.Bold = True .ChartTitle.Left = 35 .Axes(xlCategory, xlPrimary).HasTitle = True '<- Get error message here. .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Month" .Axes(xlCategory, xlPrimary).AxisTitle.Font.Size = 9 .Axes(xlCategory, xlPrimary).CrossesAt = 1 .Axes(xlCategory, xlPrimary).TickLabelSpacing = 6 .Axes(xlCategory, xlPrimary).TickMarkSpacing = 6 .Axes(xlCategory, xlPrimary).TickLabels.Orientation = 90 .Axes(xlCategory, xlPrimary).TickLabels.Font.Size = 9 .Axes(xlValue, xlPrimary).HasTitle = True .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Loss $1000s" .Axes(xlValue, xlPrimary).AxisTitle.Font.Size = 9 .Axes(xlValue, xlPrimary).MinimumScale = 0 .Axes(xlValue, xlPrimary).TickLabels.Font.Size = 9 .Legend.Border.LineStyle = xlNone .Legend.Font.Size = 9 '.PlotArea.Border.LineStyle = xlNone .PlotArea.Interior.ColorIndex = xlNone .Axes(xlValue, xlPrimary).MajorGridlines.Border.LineStyle = xlDot Set cht1 = Nothing End With Thank you! any suggestion appreciated. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Very wierd problem. Please help
Hi
I find that anything that involves updating charts can always do with a sprinkling of DoEvents. So put DoEvents in after you do the .HasTitle = True. No harm to put it in after other .HasTitle lines too. I think (and it is a wild guess) that the code runs ahead trying to manipulate the Title object before it is properly created. The DoEvents forces the object to be properly created before you manipulate it. hope it helps! Paul On May 15, 4:30 am, Francis wrote: I wrote a piece of code to generate a bunch of charts(40) in a worksheet. It was running smoothly and generate some beautiful charts. However, last Thursday it stops due to a run-time error while running, 23 charts has been generated. The error message is "Unable to set the HasTitle property of the Axis class". It is very wierd because the code is in a loop and it pass the first 23 charts and block the 24th. .... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Very Wierd Execl "Memory" problem | Excel Discussion (Misc queries) | |||
This is wierd! | Excel Programming | |||
Wierd Save problem | Excel Discussion (Misc queries) | |||
WIERD Excel Problem | Setting up and Configuration of Excel | |||
Wierd variable values problem | Excel Programming |