Re: Macro crashes if I run it twice

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Ed (ed_millis_at_NO_SPAM.yahoo.com)
Date: 01/25/05 

Date: Tue, 25 Jan 2005 11:04:27 -0700

Well, Curtis, let's see what we can get from this.

> I've commented out co2 but I still get the error with co3.
Okay - then the problem is not with that particular section of code, but
with something common to how you are setting up the objects. The error
"This method of that object doesn't work" usually suggests Excel is not
recognizing it as the object you think it is.

> I had originally delcared the following as Global variables:
> but I removed the above variables from
> Global and now define them in my main sub and then pass them as arguments
to
> each sub that needs them....still get the error.
I'm not saying this is the best way to do things, but it's how I've managed
to make things work: if I have related multiple macros that all use some of
the same variables, I will put all the macros in one module, and declare all
the variables for all the macros at the very top (before the first sub).
That way, I know my application, workbook, range, etc. object reference is
recognized in each individual macro.

> Dim xlapp As Excel.Application
> Dim xlBook As Excel.Workbook
> Dim xlR As Excel.Range
>
> I'm not sure what you mean by "Why not set an object to the
> work***, too?"
It looks like all your charts are set in xlBook.Worksheets(4). Why not add
Dim xlWks4 As Work***
and
Set xlWks4 = xlBook.Worksheets(4) ? Just saves some typing, and sometimes
closes up the possibilities of errors.

For instance, I just noticed that right after
> > > xlApp.Calculation = xlCalculationAutomatic 'Set Calculation back
to
> > > automatic for the Excel File

you begin setting the chart ranges:

> > > With xlBook.Worksheets(3)
> > >
> > > 'Set up the Chart Ranges based on the dates selected to run the report
> > > Set CWeek = Active***.Range(Cells((20 + MonthVar), 1),
Cells((21 ... {etc.}

I think the "ActiveSheet" might override the "With xlBook.Worksheets(3)".
This might also affect running it the second time, because you end up with
Worksheets(4) active. If you use object references throughout vs.
Active***, you always know exactly where you are going. And when you use
With, you don't need to repeat the object. So
> > > With xlBook.Worksheets(3)
gives
> > > Set CWeek = .Range(Cells((20 + MonthVar), 1), Cells((21 ...
{etc.}
(no Active***, but you still need the "." before Range).

One other item (this is kind of scattered, but then so am I!): you have
> > > Set xlR = xlBook.Worksheets(4).Range("A1")
> > > With xlR
        *** stuff***
> > > End With

then you have
> > > With xlR.Range("A2")
but xlR is a defined range encompassing only cell A1; the range xlR doesn't
have a cell referenced by A2 (or B2, which you also use). You might try
xlR.Offset(0, 1) for A2, and xlR.Offset(1, 1) for B2. Or, if you don't ever
use the xlR range object again, just use

With xlWks4
    With .Cells(1,1)
        *** stuff***
    End With

    With .Cells(1,2)
        *** stuff***
     End With

    With .Cells(2,2)
        *** stuff***
     End With
End With

With these things in place, I would set a breakpoint at the line where you
define the co2 ChartType. When the code stops there, open the Locals window
and examine the properties of the co2 object. Do you see anything in
<carets>? These are usually errors, telling you something is not set or not
defined. Run it the first time and look it over, then examine it again when
it stops on the second run, and see if anything is different. The walk
through with F8 and see if yu error again.

> > > Set co2 = xlBook.Worksheets(4).ChartObjects.Add(5, 100, 700, 380)
> > > co2.Chart.ChartType = xlLineMarkers ' break on this line,
so co2 object is Set

Well, that's about it for now. Take out all the comments and Debug.Print
statements and make your changes, and let me know if I've helped at all or
muddled things worse!

Ed

"Please Help" <PleaseHelp@discussions.microsoft.com> wrote in message
news:40C1FA9B-9754-4DFC-B24D-7A973539CF82@microsoft.com...
> Ed, if you're JV than I guess that makes me as the 6th man on the club
team.
> Thanks for all your time, its too kind.
>
> Okay, I've done what you suggested with the following results.....
>
> I've commented out co2 but I still get the error with co3.
>
> I had originally delcared the following as Global variables:
>
> Dim xlapp As Excel.Application
> Dim xlBook As Excel.Workbook
> Dim xlR As Excel.Range
>
> I'm not sure what you mean by "Why not set an object to the
> work***, too?" (i'm a vba newbie), but I removed the above variables
from
> Global and now define them in my main sub and then pass them as arguments
to
> each sub that needs them....still get the error.
>
> This is complete overkill, but I've attached the entire project. I'm sure
> you have better things to do than look thru 1500 lines of my off code, but
> just thought something may jump out at a quick glance.
>
> Ed, I really appreciate your help! Thanks!!!!!!!!!
>
> Curtis
>
> "Ed" wrote:
>
> > First, I'm sorry that you seem to be stuck with me! Rather than
varsity,
> > you've got JV scrub!
> >
> > That being said, it looks like your code is not believing that co2
refers to
> > a ChartObject, and therefore doesn't have a SetSourceData method. I'm
about
> > out of here for the day, so here's some questions for tonight, and I'll
be
> > back tomorrow.
> > *** What happens if you comment out the whole co2 procedure? co3 is
> > basically a duplicate - does it run okay? If so looks for differences.
If
> > not, then look to how you're setting your objects.
> > *** Speaking of objects, where is xlBook set? Why not set an object to
the
> > work***, too?
> > *** have you tried walking through this as a recorded macro, and looking
at
> > that code to what is different from what you have?
> >
> > Back tomorrow.
> > Ed
> >
> > "Please Help" <PleaseHelp@discussions.microsoft.com> wrote in message
> > news:B3E913FB-502B-4C3F-89F5-947574D38923@microsoft.com...
> > > Ed,
> > >
> > > First of all thank you for your help. I tried what you mentioned and
I'm
> > > sorry to say that I couldn't really get any valuable information out
of
> > it.
> > > When I run the Debug.Print statements I get the correct values for the
> > rows
> > > and columns of my ranges and the union, even when the SetSourceData
method
> > > fails.
> > >
> > > Any other ideas? I've attached the whole sub below... Thanks again,
> > you're
> > > the only person to offer help and Im very grateful.
> > >
> > >
> > >
> > > Private Sub Display_Charts(ByVal MonthVar As Variant, ByVal WeekVar As
> > > Variant, ByVal AsOfDate As Date)
> > >
> > > Dim CWeek As Range, CBCWP As Range, CBCWS As Range, CACWP As Range
> > > Dim CCV As Range, CCPI As Range, CSV As Range, CSPI As Range
> > > Dim CCPIUCL As Range, CCPIAvg As Range, CCPILCL As Range, CCPIUSL As
> > Range,
> > > CCPITarget As Range, CCPILSL As Range
> > > Dim CSPIUCL As Range, CSPIAvg As Range, CSPILCL As Range, CSPIUSL As
> > Range,
> > > CSPITarget As Range, CSPILSL As Range
> > > Dim CCVUCL As Range, CCVAvg As Range, CCVLCL As Range, CCVUSL As
Range,
> > > CCVTarget As Range, CCVLSL As Range
> > > Dim CSVUCL As Range, CSVAvg As Range, CSVLCL As Range, CSVUSL As
Range,
> > > CSVTarget As Range, CSVLSL As Range
> > >
> > > Dim co2SD As Range, co3SD As Range, co4SD As Range, co5SD As Range
> > > Dim co2 As ChartObject, co3 As ChartObject, co4 As ChartObject, co5 As
> > > ChartObject
> > >
> > > xlApp.Calculation = xlCalculationAutomatic 'Set Calculation back
to
> > > automatic for the Excel File
> > >
> > > With xlBook.Worksheets(3)
> > >
> > > 'Set up the Chart Ranges based on the dates selected to run the report
> > > Set CWeek = Active***.Range(Cells((20 + MonthVar), 1),
Cells((21
> > +
> > > MonthVar + WeekVar), 1))
> > > Set CBCWP = Active***.Range(Cells((20 + MonthVar), 3),
Cells((21
> > +
> > > MonthVar + WeekVar), 3))
> > > Set CBCWS = Active***.Range(Cells((20 + MonthVar), 2),
Cells((21
> > +
> > > MonthVar + WeekVar), 2))
> > > Set CACWP = Active***.Range(Cells((20 + MonthVar), 4),
Cells((21
> > +
> > > MonthVar + WeekVar), 4))
> > > Set CCV = Active***.Range(Cells((20 + MonthVar), 5),
Cells((21 +
> > > MonthVar + WeekVar), 5))
> > > Set CCPI = Active***.Range(Cells((20 + MonthVar), 6),
Cells((21
> > +
> > > MonthVar + WeekVar), 6))
> > > Set CSV = Active***.Range(Cells((20 + MonthVar), 7),
Cells((21 +
> > > MonthVar + WeekVar), 7))
> > > Set CSPI = Active***.Range(Cells((20 + MonthVar), 8),
Cells((21
> > +
> > > MonthVar + WeekVar), 8))
> > > Set CCPIUCL = Active***.Range(Cells((20 + MonthVar), 12),
> > > Cells((21 + MonthVar + WeekVar), 12))
> > > Set CCPIAvg = Active***.Range(Cells((20 + MonthVar), 13),
> > > Cells((21 + MonthVar + WeekVar), 13))
> > > Set CCPILCL = Active***.Range(Cells((20 + MonthVar), 14),
> > > Cells((21 + MonthVar + WeekVar), 14))
> > > Set CCPIUSL = Active***.Range(Cells((20 + MonthVar), 15),
> > > Cells((21 + MonthVar + WeekVar), 15))
> > > Set CCPITarget = Active***.Range(Cells((20 + MonthVar), 16),
> > > Cells((21 + MonthVar + WeekVar), 16))
> > > Set CCPILSL = Active***.Range(Cells((20 + MonthVar), 17),
> > > Cells((21 + MonthVar + WeekVar), 17))
> > > Set CSPIUCL = Active***.Range(Cells((20 + MonthVar), 18),
> > > Cells((21 + MonthVar + WeekVar), 18))
> > > Set CSPIAvg = Active***.Range(Cells((20 + MonthVar), 19),
> > > Cells((21 + MonthVar + WeekVar), 19))
> > > Set CSPILCL = Active***.Range(Cells((20 + MonthVar), 20),
> > > Cells((21 + MonthVar + WeekVar), 20))
> > > Set CSPIUSL = Active***.Range(Cells((20 + MonthVar), 21),
> > > Cells((21 + MonthVar + WeekVar), 21))
> > > Set CSPITarget = Active***.Range(Cells((20 + MonthVar), 22),
> > > Cells((21 + MonthVar + WeekVar), 22))
> > > Set CSPILSL = Active***.Range(Cells((20 + MonthVar), 23),
> > > Cells((21 + MonthVar + WeekVar), 23))
> > > Set CCVUCL = Active***.Range(Cells((20 + MonthVar), 24),
> > Cells((21
> > > + MonthVar + WeekVar), 24))
> > > Set CCVAvg = Active***.Range(Cells((20 + MonthVar), 25),
> > Cells((21
> > > + MonthVar + WeekVar), 25))
> > > Set CCVLCL = Active***.Range(Cells((20 + MonthVar), 26),
> > Cells((21
> > > + MonthVar + WeekVar), 26))
> > > Set CCVUSL = Active***.Range(Cells((20 + MonthVar), 27),
> > Cells((21
> > > + MonthVar + WeekVar), 27))
> > > Set CCVTarget = Active***.Range(Cells((20 + MonthVar), 28),
> > > Cells((21 + MonthVar + WeekVar), 28))
> > > Set CCVLSL = Active***.Range(Cells((20 + MonthVar), 29),
> > Cells((21
> > > + MonthVar + WeekVar), 29))
> > > Set CSVUCL = Active***.Range(Cells((20 + MonthVar), 30),
> > Cells((21
> > > + MonthVar + WeekVar), 30))
> > > Set CSVAvg = Active***.Range(Cells((20 + MonthVar), 31),
> > Cells((21
> > > + MonthVar + WeekVar), 31))
> > > Set CSVLCL = Active***.Range(Cells((20 + MonthVar), 32),
> > Cells((21
> > > + MonthVar + WeekVar), 32))
> > > Set CSVUSL = Active***.Range(Cells((20 + MonthVar), 33),
> > Cells((21
> > > + MonthVar + WeekVar), 33))
> > > Set CSVTarget = Active***.Range(Cells((20 + MonthVar), 34),
> > > Cells((21 + MonthVar + WeekVar), 34))
> > > Set CSVLSL = Active***.Range(Cells((20 + MonthVar), 35),
> > Cells((21
> > > + MonthVar + WeekVar), 35))
> > >
> > > End With
> > >
> > > 'Set Unions for each chart range for source data
> > >
> > > Debug.Print CWeek.Rows.Count
> > > Debug.Print CWeek.Columns.Count
> > > Debug.Print CCPI.Rows.Count
> > > Debug.Print CCPI.Columns.Count
> > > Debug.Print CCPIUCL.Rows.Count
> > > Debug.Print CCPIUCL.Columns.Count
> > > Debug.Print CCPIAvg.Rows.Count
> > > Debug.Print CCPIAvg.Columns.Count
> > > Debug.Print CCPILCL.Rows.Count
> > > Debug.Print CCPILCL.Columns.Count
> > > Debug.Print CCPIUSL.Rows.Count
> > > Debug.Print CCPIUSL.Columns.Count
> > > Debug.Print CCPITarget.Rows.Count
> > > Debug.Print CCPITarget.Columns.Count
> > > Debug.Print CCPILSL.Rows.Count
> > > Debug.Print CCPILSL.Columns.Count
> > >
> > > Set co2SD = Union(CWeek, CCPI, CCPIUCL, CCPIAvg, CCPILCL, CCPIUSL,
> > > CCPITarget, CCPILSL)
> > > Set co3SD = Union(CWeek, CSPI, CSPIUCL, CSPIAvg, CSPILCL, CSPIUSL,
> > > CSPITarget, CSPILSL)
> > > Set co4SD = Union(CWeek, CCV, CCVUCL, CCVAvg, CCVLCL, CCVUSL,
CCVTarget,
> > > CCVLSL)
> > > Set co5SD = Union(CWeek, CSV, CSVUCL, CSVAvg, CSVLCL, CSVUSL,
CSVTarget,
> > > CSVLSL)
> > >
> > > Debug.Print co2SD.Rows.Count
> > > Debug.Print co2SD.Columns.Count
> > >
> > >
> > > 'Move to *** #4
> > >
> > > xlBook.Worksheets(4).Select
> > > xlBook.Worksheets(4).Name = "Report Graphs"
> > >
> > > Set xlR = xlBook.Worksheets(4).Range("A1")
> > > With xlR
> > > .Formula = "Cost and Schedule Weekly Charts for " &
> > ActiveProject.Name
> > > .Font.Bold = True
> > > .Font.Size = 14
> > > .ColumnWidth = 21
> > > End With
> > >
> > > With xlR.Range("A2")
> > > .Formula = "As of: "
> > > .Font.Bold = True
> > > .Font.Size = 12
> > > End With
> > > With xlR.Range("B2")
> > > .Formula = AsOfDate
> > > .NumberFormat = "mm/dd/yyyy"
> > > .Font.Bold = True
> > > .Font.Size = 12
> > > .ColumnWidth = 12
> > > End With
> > >
> > > ' Create 2nd Chart showing the Cost Performance Index
> > >
> > > Set co2 = xlBook.Worksheets(4).ChartObjects.Add(5, 100, 700, 380)
> > > co2.Chart.ChartType = xlLineMarkers
> > > co2.Chart.SetSourceData Source:=co2SD, _
> > > PlotBy:=xlColumns
> > > co2.Chart.Location Where:=xlLocationAsObject, Name:="Report
> > Graphs"
> > > With co2.Chart
> > > .HasTitle = True
> > > .ChartTitle.Characters.Text = "CPI Graph"
> > > .Axes(xlCategory, xlPrimary).HasTitle = True
> > > .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text =
"Week"
> > > .Axes(xlValue, xlPrimary).HasTitle = True
> > > .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Index"
> > > End With
> > >
> > > With co2.Chart
> > > .HasAxis(xlCategory, xlPrimary) = True
> > > .HasAxis(xlValue, xlPrimary) = True
> > > End With
> > > co2.Chart.Axes(xlCategory, xlPrimary).CategoryType =
xlCategoryScale
> > > co2.Chart.Axes(xlValue).HasMajorGridlines = False
> > > co2.Chart.HasLegend = True
> > > co2.Chart.Legend.Position = xlLegendPositionBottom
> > > ' co2.Chart.HasDataTable = True
> > > ' co2.Chart.DataTable.ShowLegendKey = True
> > >
> > > co2.Chart.SeriesCollection(1).Border.Weight = xlMedium
> > > co2.Chart.SeriesCollection(2).Select
> > > With Selection.Border
> > > .ColorIndex = 3
> > > .Weight = xlThin
> > > .LineStyle = xlDot
> > > End With
> > > Selection.MarkerStyle = xlNone
> > > co2.Chart.SeriesCollection(3).Select
> > > With Selection.Border
> > > .Color = RGB(120, 120, 120)
> > > .Weight = xlThin
> > > End With
> > > Selection.MarkerStyle = xlNone
> > > co2.Chart.SeriesCollection(4).Select
> > > With Selection.Border
> > > .ColorIndex = 3
> > > .Weight = xlThin
> > > .LineStyle = xlDot
> > > End With
> > > Selection.MarkerStyle = xlNone
> > > co2.Chart.SeriesCollection(5).Select
> > > With Selection.Border
> > > .ColorIndex = 5
> > > .Weight = xlMedium
> > > .LineStyle = xlDot
> > > End With
> > > Selection.MarkerStyle = xlNone
> > > co2.Chart.SeriesCollection(6).Select
> > > With Selection.Border
> > > .ColorIndex = 1
> > > .Weight = xlMedium
> > > End With
> > > Selection.MarkerStyle = xlNone
> > > co2.Chart.SeriesCollection(7).Select
> > > With Selection.Border
> > > .ColorIndex = 5
> > > .Weight = xlMedium
> > > .LineStyle = xlDot
> > > End With
> > > Selection.MarkerStyle = xlNone
> > >
> > > ' Create 3rd Chart showing the Schedule Efficiency Index
> > >
> > > Set co3 = xlBook.Worksheets(4).ChartObjects.Add(5, 495, 700, 380)
> > > co3.Chart.ChartType = xlLineMarkers
> > > co3.Chart.SetSourceData Source:=co3SD, _
> > > PlotBy:=xlColumns
> > > co3.Chart.Location Where:=xlLocationAsObject, Name:="Report
> > Graphs"
> > > With co3.Chart
> > > .HasTitle = True
> > > .ChartTitle.Characters.Text = "SPI Graph"
> > > .Axes(xlCategory, xlPrimary).HasTitle = True
> > > .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text =
"Week"
> > > .Axes(xlValue, xlPrimary).HasTitle = True
> > > .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Index"
> > > End With
> > > With co3.Chart
> > > .HasAxis(xlCategory, xlPrimary) = True
> > > .HasAxis(xlValue, xlPrimary) = True
> > > End With
> > > co3.Chart.Axes(xlCategory, xlPrimary).CategoryType =
xlCategoryScale
> > > co3.Chart.Axes(xlValue).HasMajorGridlines = False
> > > co3.Chart.HasLegend = True
> > > co3.Chart.Legend.Position = xlLegendPositionBottom
> > >
> > > ' co3.Chart.HasDataTable = True
> > > ' co3.Chart.DataTable.ShowLegendKey = True
> > >
> > > co3.Chart.SeriesCollection(1).Border.Weight = xlMedium
> > > co3.Chart.SeriesCollection(2).Select
> > > With Selection.Border
> > > .ColorIndex = 3
> > > .Weight = xlThin
> > > .LineStyle = xlDot
> > > End With
> > > Selection.MarkerStyle = xlNone
> > > co3.Chart.SeriesCollection(3).Select
> > > With Selection.Border
> > > .Color = RGB(120, 120, 120)
> > > .Weight = xlThin
> > > End With
> > > Selection.MarkerStyle = xlNone
> > > co3.Chart.SeriesCollection(4).Select
> > > With Selection.Border
> > > .ColorIndex = 3
> > > .Weight = xlThin
> > > .LineStyle = xlDot
> > > End With
> > > Selection.MarkerStyle = xlNone
> > > co3.Chart.SeriesCollection(5).Select
> > > With Selection.Border
> > > .ColorIndex = 5
> > > .Weight = xlMedium
> > > .LineStyle = xlDot
> > > End With
> > > Selection.MarkerStyle = xlNone
> > > co3.Chart.SeriesCollection(6).Select
> > > With Selection.Border
> > > .ColorIndex = 1
> > > .Weight = xlMedium
> > > End With
> > > Selection.MarkerStyle = xlNone
> > > co3.Chart.SeriesCollection(7).Select
> > > With Selection.Border
> > > .ColorIndex = 5
> > > .Weight = xlMedium
> > > .LineStyle = xlDot
> > > End With
> > > Selection.MarkerStyle = xlNone
> > >
> > > ' Create 4th Chart showing the Cost Variance
> > >
> > > Set co4 = xlBook.Worksheets(4).ChartObjects.Add(5, 890, 700, 380)
> > > co4.Chart.ChartType = xlLineMarkers
> > > co4.Chart.SetSourceData Source:=co4SD, PlotBy:=xlColumns
> > > co4.Chart.Location Where:=xlLocationAsObject, Name:="Report
Graphs"
> > > With co4.Chart
> > > .HasTitle = True
> > > .ChartTitle.Characters.Text = "CV Graph"
> > > .Axes(xlCategory, xlPrimary).HasTitle = True
> > > .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text =
"Week"
> > > .Axes(xlValue, xlPrimary).HasTitle = True
> > > .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text =
"Dollars"
> > > End With
> > > With co4.Chart
> > > .HasAxis(xlCategory, xlPrimary) = True
> > > .HasAxis(xlValue, xlPrimary) = True
> > > End With
> > > co4.Chart.Axes(xlCategory, xlPrimary).CategoryType =
xlCategoryScale
> > > co4.Chart.Axes(xlValue).HasMajorGridlines = False
> > > co4.Chart.HasLegend = True
> > > co4.Chart.Legend.Position = xlLegendPositionBottom
> > > ' co4.Chart.HasDataTable = True
> > > ' co4.Chart.DataTable.ShowLegendKey = True
> > >
> > > co4.Chart.SeriesCollection(1).Border.Weight = xlMedium
> > > co4.Chart.SeriesCollection(2).Select
> > > With Selection.Border
> > > .ColorIndex = 3
> > > .Weight = xlThin
> > > .LineStyle = xlDot
> > > End With
> > > Selection.MarkerStyle = xlNone
> > > co4.Chart.SeriesCollection(3).Select
> > > With Selection.Border
> > > .Color = RGB(120, 120, 120)
> > > .Weight = xlThin
> > > End With
> > > Selection.MarkerStyle = xlNone
> > > co4.Chart.SeriesCollection(4).Select
> > > With Selection.Border
> > > .ColorIndex = 3
> > > .Weight = xlThin
> > > .LineStyle = xlDot
> > > End With
> > > Selection.MarkerStyle = xlNone
> > > co4.Chart.SeriesCollection(5).Select
> > > With Selection.Border
> > > .ColorIndex = 5
> > > .Weight = xlMedium
> > > .LineStyle = xlDot
> > > End With
> > > Selection.MarkerStyle = xlNone
> > > co4.Chart.SeriesCollection(6).Select
> > > With Selection.Border
> > > .ColorIndex = 1
> > > .Weight = xlMedium
> > > End With
> > > Selection.MarkerStyle = xlNone
> > > co4.Chart.SeriesCollection(7).Select
> > > With Selection.Border
> > > .ColorIndex = 5
> > > .Weight = xlMedium
> > > .LineStyle = xlDot
> > > End With
> > > Selection.MarkerStyle = xlNone
> > >
> > > ' Create 5th Chart showing the Schedule Variance
> > >
> > > Set co5 = xlBook.Worksheets(4).ChartObjects.Add(5, 1285, 700, 380)
> > > co5.Chart.ChartType = xlLineMarkers
> > > co5.Chart.SetSourceData Source:=co5SD, PlotBy:=xlColumns
> > > co5.Chart.Location Where:=xlLocationAsObject, Name:="Report
Graphs"
> > > With co5.Chart
> > > .HasTitle = True
> > > .ChartTitle.Characters.Text = "SV Graph"
> > > .Axes(xlCategory, xlPrimary).HasTitle = True
> > > .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text =
"Week"
> > > .Axes(xlValue, xlPrimary).HasTitle = True
> > > .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text =
"Dollars"
> > > End With
> > > With co5.Chart
> > > .HasAxis(xlCategory, xlPrimary) = True
> > > .HasAxis(xlValue, xlPrimary) = True
> > > End With
> > > co5.Chart.Axes(xlCategory, xlPrimary).CategoryType =
xlCategoryScale
> > > co5.Chart.Axes(xlValue).HasMajorGridlines = False
> > > co5.Chart.HasLegend = True
> > > co5.Chart.Legend.Position = xlLegendPositionBottom
> > > ' co5.Chart.HasDataTable = True
> > > ' co5.Chart.DataTable.ShowLegendKey = True
> > >
> > > co5.Chart.SeriesCollection(1).Border.Weight = xlMedium
> > > co5.Chart.SeriesCollection(2).Select
> > > With Selection.Border
> > > .ColorIndex = 3
> > > .Weight = xlThin
> > > .LineStyle = xlDot
> > > End With
> > > Selection.MarkerStyle = xlNone
> > > co5.Chart.SeriesCollection(3).Select
> > > With Selection.Border
> > > .Color = RGB(120, 120, 120)
> > > .Weight = xlThin
> > > End With
> > > Selection.MarkerStyle = xlNone
> > > co5.Chart.SeriesCollection(4).Select
> > > With Selection.Border
> > > .ColorIndex = 3
> > > .Weight = xlThin
> > > .LineStyle = xlDot
> > > End With
> > > Selection.MarkerStyle = xlNone
> > > co5.Chart.SeriesCollection(5).Select
> > > With Selection.Border
> > > .ColorIndex = 5
> > > .Weight = xlMedium
> > > .LineStyle = xlDot
> > > End With
> > > Selection.MarkerStyle = xlNone
> > > co5.Chart.SeriesCollection(6).Select
> > > With Selection.Border
> > > .ColorIndex = 1
> > > .Weight = xlMedium
> > > End With
> > > Selection.MarkerStyle = xlNone
> > > co5.Chart.SeriesCollection(7).Select
> > > With Selection.Border
> > > .ColorIndex = 5
> > > .Weight = xlMedium
> > > .LineStyle = xlDot
> > > End With
> > > Selection.MarkerStyle = xlNone
> > >
> > >
> > > ' Set Page Setup for printing the Graphs
> > >
> > > With xlBook.Worksheets(4).PageSetup
> > > .PrintTitleRows = "$1:$6"
> > > .PrintTitleColumns = ""
> > > .LeftFooter = "&A"
> > > .CenterFooter = "Page &P of &N"
> > > .RightFooter = "&D"
> > >
> > > End With
> > > ' xlBook.Worksheets(4).PageSetup.PrintArea = "$A$4:$M$161"
> > > With xlBook.Worksheets(4).PageSetup
> > > .LeftMargin = xlApp.InchesToPoints(0.5)
> > > .RightMargin = xlApp.InchesToPoints(0.5)
> > > .CenterHorizontally = True
> > > .CenterVertically = True
> > > .Orientation = xlLandscape
> > > .PaperSize = xlPaperLetter
> > > .Zoom = False
> > > .FitToPagesWide = 1
> > > .FitToPagesTall = False
> > > End With
> > >
> > >
> > > 'Reset Variables
> > > Set co2 = Nothing
> > > Set co3 = Nothing
> > > Set co4 = Nothing
> > > Set co5 = Nothing
> > > Set co2SD = Nothing
> > > Set co3SD = Nothing
> > > Set co4SD = Nothing
> > > Set co5SD = Nothing
> > >
> > >
> > > Set CWeek = Nothing
> > > Set CBCWP = Nothing
> > > Set CBCWS = Nothing
> > > Set CACWP = Nothing
> > > Set CCV = Nothing
> > > Set CCPI = Nothing
> > > Set CSV = Nothing
> > > Set CSPI = Nothing
> > > Set CCPIUCL = Nothing
> > > Set CCPIAvg = Nothing
> > > Set CCPILCL = Nothing
> > > Set CCPIUSL = Nothing
> > > Set CCPITarget = Nothing
> > > Set CCPILSL = Nothing
> > > Set CSPIUCL = Nothing
> > > Set CSPIAvg = Nothing
> > > Set CSPILCL = Nothing
> > > Set CSPIUSL = Nothing
> > > Set CSPITarget = Nothing
> > > Set CSPILSL = Nothing
> > > Set CCVUCL = Nothing
> > > Set CCVAvg = Nothing
> > > Set CCVLCL = Nothing
> > > Set CCVUSL = Nothing
> > > Set CCVTarget = Nothing
> > > Set CCVLSL = Nothing
> > > Set CSVUCL = Nothing
> > > Set CSVAvg = Nothing
> > > Set CSVLCL = Nothing
> > > Set CSVUSL = Nothing
> > > Set CSVTarget = Nothing
> > > Set CSVLSL = Nothing
> > >
> > >
> > > Set MonthVar = Nothing
> > > Set WeekVar = Nothing
> > >
> > > End Sub
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > > "Ed" wrote:
> > >
> > > > I'm not an MVP-level expert by any means, but since no one else has
> > replied
> > > > yet, I'll give you what I would do if I were hunting down something
like
> > > > this.
> > > >
> > > > Given the nature of the error, it seems like your data source,
"co2SD",
> > is
> > > > empty of data, or at least doesn't have what the Method is looking
for.
> > > > Since it works the first time but not the second, my thought is that
> > > > something is changing in at least one of the ranges you Union, and
> > renders
> > > > that object unusable. Also, you don't show the creation of all the
> > ranges
> > > > in your Union statement - either you have cut them out of your post
to
> > save
> > > > space, or they are created in another procedure that is not shown
here.
> > You
> > > > also don't show what you do with them when you're done with them.
> > > >
> > > > (BTW, if the result of Union is a Range object, and you Union
Ranges,
> > why
> > > > Dim everything as Variant rather than as Range?)
> > > >
> > > > Here's how I would try to track it. Before the Union statement, set
> > > > Debug.Print statements for something about each of those ranges -
maybe
> > > > something like
> > > > Debug.Print Range("CWeek")Rows.Count
> > > > Debug.Print Range("CWeek").Columns.Count
> > > > etc. Then do the same for co2SD after the Union. Step through it
> > either
> > > > using F8, or F5 with break points, and watch BOTH the Immediate and
the
> > > > Locals windows. The Locals window will tell you if the variable is
> > getting
> > > > set at all, and give you useful information about it. The Immediate
> > window
> > > > will show your Debug.Print results to tell you if your range is
useful
> > to
> > > > your Union statement.
> > > >
> > > > Then step through it again and see what is different. I would
suspect
> > one
> > > > of the ranges in the Union is not getting set properly the second
time
> > > > because your macro affects it and the method used to create it is
not
> > > > rerunning without restarting Excel.
> > > >
> > > > HTH
> > > > Ed
> > > >
> > > > "Please Help" <Please Help@discussions.microsoft.com> wrote in
message
> > > > news:E1BF0785-F532-48FC-BC69-42601F753193@microsoft.com...
> > > > > Hi everyone,
> > > > >
> > > > > My macro will run perfect the first time I run it. If I run it
again,
> > > > > without closing the excel file that was created by my first macro,
I
> > get
> > > > an
> > > > > error message. If I close the excel file and then run the macro,
it
> > again
> > > > > works fine. Why is this and how can I fix it?
> > > > >
> > > > > Details:
> > > > > The error message I get is "Method SetSourceData of...failed".
The
> > macro
> > > > > pulls data from a ms project file and places it into excel. I
then
> > use
> > > > this
> > > > > data to create charts. The error occurs when I create my first
chart.
> > > > >
> > > > > Code:
> > > > >
> > > > > Dim CWeek As Range, CBCWP As Range, CBCWS As Range......(etc.)
> > > > >
> > > > > Dim co2SD As Variant, co3SD As Variant, co4SD As Variant, co5SD As
> > Variant
> > > > >
> > > > > Dim co2 As ChartObject, co3 As ChartObject, co4 As ChartObject,
co5 As
> > > > > ChartObject
> > > > >
> > > > >
> > > > > 'Set up the Chart Ranges based on the dates selected to run the
report
> > > > > ' This data is on the 3rd chart
> > > > >
> > > > > Set CWeek = Active***.Range(Cells((20 + MonthVar), 1),
> > Cells((21
> > > > +
> > > > > MonthVar + WeekVar), 1))
> > > > > Set CBCWP = Active***.Range(Cells((20 + MonthVar), 3),
> > Cells((21
> > > > +
> > > > > MonthVar + WeekVar), 3))
> > > > > Set CBCWS = Active***.Range(Cells((20 + MonthVar), 2),
> > Cells((21
> > > > +
> > > > > MonthVar + WeekVar), 2))
> > > > >
> > > > > 'Set Unions for each chart range for source data
> > > > >
> > > > > Set co2SD = Union(CWeek, CCPI, CCPIUCL, CCPIAvg, CCPILCL,
> > CCPIUSL,
> > > > > CCPITarget, CCPILSL)
> > > > > Set co3SD = Union(CWeek, CSPI, CSPIUCL, CSPIAvg, CSPILCL,
> > CSPIUSL,
> > > > > CSPITarget, CSPILSL)........
> > > > >
> > > > > ' Go to 4th work*** and display charts
> > > > >
> > > > > xlBook.Worksheets(4).Select
> > > > > xlBook.Worksheets(4).Name = "Report Graphs"
> > > > >
> > > > > ' Create 1st Chart showing the Cost Performance Index
> > > > >
> > > > > Set co2 = xlBook.Worksheets(4).ChartObjects.Add(5, 100, 700,
380)
> > > > > co2.Chart.ChartType = xlLineMarkers
> > > > >
> > > > > ' ERROR OCCURS HERE:
> > > > > co2.Chart.SetSourceData Source:=co2SD, PlotBy:=xlColumns
> > > > >
> > > > > co2.Chart.Location Where:=xlLocationAsObject, Name:="Report
> > Graphs"
> > > > > With co2.Chart
> > > > > .HasTitle = True
> > > > > .ChartTitle.Characters.Text = "CPI Graph"
> > > > > .Axes(xlCategory, xlPrimary).HasTitle = True
> > > > > .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text =
> > "Week"
> > > > > .Axes(xlValue, xlPrimary).HasTitle = True
> > > > > .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text =
"Index"
> > > > > End With
> > > > >
> > > >
> > > >
> > > >
> >
> >
> >

Quantcast