Macro crashes if I run it twice

G

Guest

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 = ActiveSheet.Range(Cells((20 + MonthVar), 1), Cells((21 +
MonthVar + WeekVar), 1))
Set CBCWP = ActiveSheet.Range(Cells((20 + MonthVar), 3), Cells((21 +
MonthVar + WeekVar), 3))
Set CBCWS = ActiveSheet.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 worksheet 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
 
E

Ed

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
 
G

Guest

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 = ActiveSheet.Range(Cells((20 + MonthVar), 1), Cells((21 +
MonthVar + WeekVar), 1))
Set CBCWP = ActiveSheet.Range(Cells((20 + MonthVar), 3), Cells((21 +
MonthVar + WeekVar), 3))
Set CBCWS = ActiveSheet.Range(Cells((20 + MonthVar), 2), Cells((21 +
MonthVar + WeekVar), 2))
Set CACWP = ActiveSheet.Range(Cells((20 + MonthVar), 4), Cells((21 +
MonthVar + WeekVar), 4))
Set CCV = ActiveSheet.Range(Cells((20 + MonthVar), 5), Cells((21 +
MonthVar + WeekVar), 5))
Set CCPI = ActiveSheet.Range(Cells((20 + MonthVar), 6), Cells((21 +
MonthVar + WeekVar), 6))
Set CSV = ActiveSheet.Range(Cells((20 + MonthVar), 7), Cells((21 +
MonthVar + WeekVar), 7))
Set CSPI = ActiveSheet.Range(Cells((20 + MonthVar), 8), Cells((21 +
MonthVar + WeekVar), 8))
Set CCPIUCL = ActiveSheet.Range(Cells((20 + MonthVar), 12),
Cells((21 + MonthVar + WeekVar), 12))
Set CCPIAvg = ActiveSheet.Range(Cells((20 + MonthVar), 13),
Cells((21 + MonthVar + WeekVar), 13))
Set CCPILCL = ActiveSheet.Range(Cells((20 + MonthVar), 14),
Cells((21 + MonthVar + WeekVar), 14))
Set CCPIUSL = ActiveSheet.Range(Cells((20 + MonthVar), 15),
Cells((21 + MonthVar + WeekVar), 15))
Set CCPITarget = ActiveSheet.Range(Cells((20 + MonthVar), 16),
Cells((21 + MonthVar + WeekVar), 16))
Set CCPILSL = ActiveSheet.Range(Cells((20 + MonthVar), 17),
Cells((21 + MonthVar + WeekVar), 17))
Set CSPIUCL = ActiveSheet.Range(Cells((20 + MonthVar), 18),
Cells((21 + MonthVar + WeekVar), 18))
Set CSPIAvg = ActiveSheet.Range(Cells((20 + MonthVar), 19),
Cells((21 + MonthVar + WeekVar), 19))
Set CSPILCL = ActiveSheet.Range(Cells((20 + MonthVar), 20),
Cells((21 + MonthVar + WeekVar), 20))
Set CSPIUSL = ActiveSheet.Range(Cells((20 + MonthVar), 21),
Cells((21 + MonthVar + WeekVar), 21))
Set CSPITarget = ActiveSheet.Range(Cells((20 + MonthVar), 22),
Cells((21 + MonthVar + WeekVar), 22))
Set CSPILSL = ActiveSheet.Range(Cells((20 + MonthVar), 23),
Cells((21 + MonthVar + WeekVar), 23))
Set CCVUCL = ActiveSheet.Range(Cells((20 + MonthVar), 24), Cells((21
+ MonthVar + WeekVar), 24))
Set CCVAvg = ActiveSheet.Range(Cells((20 + MonthVar), 25), Cells((21
+ MonthVar + WeekVar), 25))
Set CCVLCL = ActiveSheet.Range(Cells((20 + MonthVar), 26), Cells((21
+ MonthVar + WeekVar), 26))
Set CCVUSL = ActiveSheet.Range(Cells((20 + MonthVar), 27), Cells((21
+ MonthVar + WeekVar), 27))
Set CCVTarget = ActiveSheet.Range(Cells((20 + MonthVar), 28),
Cells((21 + MonthVar + WeekVar), 28))
Set CCVLSL = ActiveSheet.Range(Cells((20 + MonthVar), 29), Cells((21
+ MonthVar + WeekVar), 29))
Set CSVUCL = ActiveSheet.Range(Cells((20 + MonthVar), 30), Cells((21
+ MonthVar + WeekVar), 30))
Set CSVAvg = ActiveSheet.Range(Cells((20 + MonthVar), 31), Cells((21
+ MonthVar + WeekVar), 31))
Set CSVLCL = ActiveSheet.Range(Cells((20 + MonthVar), 32), Cells((21
+ MonthVar + WeekVar), 32))
Set CSVUSL = ActiveSheet.Range(Cells((20 + MonthVar), 33), Cells((21
+ MonthVar + WeekVar), 33))
Set CSVTarget = ActiveSheet.Range(Cells((20 + MonthVar), 34),
Cells((21 + MonthVar + WeekVar), 34))
Set CSVLSL = ActiveSheet.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 Sheet #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
 
E

Ed

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
worksheet, 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
 
G

Guest

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
worksheet, 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
 
G

Guest

Ignore the remark that I changed the variables from global to local, the
macro crashed before I had saved the change. Also, the code is too big to
show the whole thing but i've attached the first part of it:



Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlR As Excel.Range

Sub CAM_Macro()

Dim ReportStartDate As Date
Dim ReportEndDate As Date
Dim NumberOfWeeks As Variant
Dim NumberOfMonths As Variant

Check_If_Project_Open

Check_If_Task_Selected

Get_Tasks

Call Get_Report_Dates(ReportStartDate, ReportEndDate)

Call Get_TimePhased_Values(NumberOfWeeks, NumberOfMonths, ReportStartDate,
ReportEndDate)

Open_Excel

Call Display_Selected_Data(ByVal ReportEndDate)

Call Display_Current_Period_Data(ByVal ReportStartDate, ByVal ReportEndDate,
ByVal NumberOfMonths)

Call Display_Summary_Setup(ByVal ReportStartDate, ByVal ReportEndDate)

Call Display_Monthly_Summary(ByVal NumberOfMonths, ByVal ReportStartDate,
ByVal ReportEndDate)

Call Display_Weekly_Summary(ByVal NumberOfWeeks, ByVal ReportStartDate,
ByVal ReportEndDate)

Call Display_Charts(ByVal NumberOfMonths, ByVal NumberOfWeeks, ByVal
ReportEndDate)

Call Display_Overbudget_Tasks(ByVal ReportEndDate)

Call Display_PastDue_Tasks(ByVal ReportEndDate)

End_Report

Set xlApp = Nothing
Set xlBook = Nothing
Set xlR = Nothing

End Sub
Private Sub Check_If_Project_Open()

'Procedure checks if a project file is open

On Error GoTo NoFileOpen

Dim strName As String

'If there is no active project, this will throw an error and go to the
error handler.

strName = ActiveProject.Name

Exit Sub

NoFileOpen:

MsgBox "There is no project open! Open a project and rerun the macro.",
vbCritical + R_TO_L, Title:=Application.Name

End 'End the macro

End Sub

Private Sub Check_If_Task_Selected()

'Procedure checks if a project file is open

On Error GoTo NoTaskSelected

If ActiveSelection.Tasks.Count = 1 Then

Exit Sub

ElseIf ActiveSelection.Tasks.Count > 1 Then

MsgBox "You have selected more than 1 task. Select a single task and
run the macro again." _
, vbCritical + R_TO_L, Title:=Application.Name

End ' End the macro
End If

NoTaskSelected:

MsgBox "You Have Not Selected A Task!", vbCritical + R_TO_L,
Title:=Application.Name

End 'End the macro

End Sub

Private Sub Get_Tasks()
Dim N As Integer
Dim BeginningRowNumber As Integer
Dim EndRowNumber As Integer
Dim MasterTask As Task
Dim PlacementTask As Task
Dim TName As String
Dim Counter As Integer

Set MasterTask = ActiveSelection.Tasks.Item(1)
BeginningRowNumber = MasterTask.ID
Set PlacementTask = MasterTask

Do While PlacementTask.OutlineChildren.Count > 0

N = PlacementTask.OutlineChildren.Count

Set PlacementTask = PlacementTask.OutlineChildren.Item(N)
TName = PlacementTask.Name

Loop

EndRowNumber = PlacementTask.ID
Counter = 1

Do While ActiveSelection.Tasks.Item(1).ID < EndRowNumber

SelectCellDown 1
Counter = Counter + 1

If ActiveSelection = 0 Then

Exit Do

End If

Loop

If ActiveSelection = 0 Then
SelectCellUp
Counter = Counter - 1
End If

If ActiveSelection.Tasks.Item(1).ID > EndRowNumber Then
SelectCellUp
Counter = Counter - 1
End If
If Counter = 1 Then
Exit Sub
End If

SelectCellUp (Counter - 1), True


End Sub

Private Sub Get_Report_Dates(StartDate As Date, AsOfDate As Date)

Dim TaskStartDate As Date
Dim TaskEndDate As Date

On Error GoTo DateError

TaskStartDate = ActiveSelection.Tasks.Item(1).Start
TaskEndDate = ActiveSelection.Tasks.Item(1).Finish

OptionsCalculation Automatic:=True

StartDate = InputBox("Enter the reporting Start Date. The date should be in
the following format mm/dd/yy.", _
"Report Start Date", Format$(TaskStartDate, "Short Date"))

If Round(StartDate) < Round(TaskStartDate) Then
MsgBox "Enter a valid date greater than the Project Start Date",
vbCritical
End
End If


AsOfDate = InputBox("Enter the reporting Stop Date.", _
"Report Stop Date", Format$(TaskEndDate, "Short Date")) & " 11:59 PM"

If Round(AsOfDate) < Round(StartDate) Then
MsgBox "The reporting Stop Date must be after the reporting Start
Date.", vbCritical
End
End If

Exit Sub

DateError:

MsgBox "Invalid Date!", vbCritical
End ' End the macro

End Sub

Private Sub Get_TimePhased_Values(WeekVar As Variant, MonthVar As Variant,
StartDate As Date, AsOfDate As Date)

MonthVar = Round((((Year(AsOfDate) - Year(StartDate)) * 12) +
(Month(AsOfDate) - Month(StartDate))), 0)

WeekVar = Round((((Year(AsOfDate) - Year(StartDate)) * 52) +
(Format(AsOfDate, "ww") - Format(StartDate, "ww"))), 0)

End Sub

Private Sub Open_Excel()

If xlApp Is Nothing Then
Set xlApp = CreateObject("Excel.Application") 'Start new instance
If xlApp Is Nothing Then
MsgBox "Can't Find Excel, please try again.", vbCritical
End 'Stop, can't proceed without Excel
End If
xlApp.Visible = True
Else
Set xlR = Nothing
Set xlApp = Nothing
Set xlBook = Nothing
Set xlApp = CreateObject("Excel.Application") ' Start New Instance
If xlApp Is Nothing Then
MsgBox "Can't Find Excel, please try again.", vbCritical
End 'Stop, can't proceed without Excel
End If
xlApp.Visible = True
End If
Application.ActivateMicrosoftApp pjMicrosoftExcel

End Sub

Private Sub Display_Selected_Data(ByVal AsOfDate As Date)

Dim Proj As Project
Dim T As Task
Dim ProjectName As String

Set xlBook = xlApp.Workbooks.Add
xlApp.Calculation = xlCalculationManual ' Set Manual Calculation
xlBook.Worksheets.Add Count:=4

xlBook.Worksheets(1).Name = "Project Summary" ' Name the first worksheet

'Go to first worksheet and enter all tasks
xlBook.Worksheets(1).Select


........
 
E

Ed

Well, Curtis, let's see what we can get from this.
I've commented out co2 but I still get the error with co3.
Click to expand...
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.
Click to expand...
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
worksheet, too?"
Click to expand...
It looks like all your charts are set in xlBook.Worksheets(4). Why not add
Dim xlWks4 As Worksheet
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
you begin setting the chart ranges:
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.
ActiveSheet, you always know exactly where you are going. And when you use
With, you don't need to repeat the object. So{etc.}
(no ActiveSheet, but you still need the "." before Range).

One other item (this is kind of scattered, but then so am I!): you have
then you havebut 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.
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
 
E

Ed

Curtis -

Before you go much further making too many changes, make a backup of this!
You can Export the module if you want. I usually select all and copy, the
paste into Word as unformatted text (or Notepad), and save it. That way, if
something goes really wrong, you can at least start from where you were this
morning, rather than where you were last week or before!

Ed
 
G

Guest

Ed,

A co-worker has solved my issues. I wasn't being clear when defining the
ranges (CWeek, CCPI, etc..), for each statement I had to specify the workbook
and sheet, rather than using the activesheet method. Thanks again for all of
your help! It was very cool of you.

Curtis
 
E

Ed

Curtis, I'm glad your problem is solved! (Maybe we ought to get your
co-worker here on the NG, so he can solve *my* problems, too!! <G> )

I've had others stick with me and help me work through code issues - it's
how I've learned most of what I know about VBA. That, and helping others.

Take care.
Ed
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

SetSourceData Failure 1
Creating Charts using VBA 0
How to change the row number in macro code? 1
"Lines on 2 Axes" fails on Excel 2007 1
Chart error 2
Replace... 1
Need to replace existinf graph for new graph with the same name 2
Naming a range and then use it to create chart 2

Top