Problems with macros running in different versions of Excel

[rkmusselman]

I have created a tool that uses many different macros. One of these is an auto open macro. It works fine on my desktop (MS Windows XP SP2 and Excel 2002) and my laptop same system config. It also works well on another desktop with windows 2000 professional and Excel 2002. I have also run this on my desktop with Excel 2000. However I have some users that are using windows 2000 Professional and Excel 2000 that have the auto open macro bomb on them. I also have some users that have this same thing happen when they use Excel 2003 and Windows XP SP2.

Does anyone have a similar experience with Excel macros and if so any suggestions on what might be wrong. My macro seems to bomb out with the macro below:

' Refresh Macro
' Macro recorded 4/5/97
'
'
Private Sub Refresh()
ActiveWorkbook.Unprotect ("password")
Application.ScreenUpdating = False
'Sub UnHideSheets()
'This macro needs to be placed in the Workbook that has very hidden
'worksheets and run from there.
'This should be done to update any hidden sheets and then removed after
'the sheets are hidden using the macro above.
Dim c
For Each c In ThisWorkbook.Sheets
c.Visible = True
Next c
Sheets("A").Select
Application.Goto Reference:="START_COPY"
ActiveSheet.Unprotect ("password")
' Refresh Small Charts on Page
refresh_charts "Chart 7", "m_cum_st", "m_cum", "Y", "A"
refresh_charts2 "Chart 6", "chart6_st", "chart6_range", "Y", "CRF"
refresh_charts2 "Chart 5", "chart5_st", "chart5_range", "Y", "CRF"
refresh_charts "Chart 4", "m_hrcum_st", "m_hrcum", "Y", "A"
refresh_charts "Chart 46", "m_visit_st", "m_visit", "Y", "A"
enroll_charts "Chart 2", "chart2_st", "chart2_range", "Y"
enroll_charts "Chart 1", "chart1_st", "chart1_range", "Y"
Investigator_chart "Chart 3", "Invest_chart_st", "Invest_chart_range", "Y"
' Refresh Large Charts in Workbook
refresh_charts "MONT CUM", "m_cum_st", "m_cum", "N", "A"
refresh_charts2 "CRFCUM", "chart6_st", "chart6_range", "N", "CRF"
refresh_charts2 "CRF STATUS", "chart5_st", "chart5_range", "N", "CRF"
refresh_charts "MONHRSCUM", "m_hrcum_st", "m_hrcum", "N", "A"
refresh_charts "MONITVISIT", "m_visit_st", "m_visit", "N", "A"
enroll_charts "CUMULATIVE", "chart2_st", "chart2_range", "N"
enroll_charts "WEEKLY ENROLL", "chart1_st", "chart1_range", "N"
Investigator_chart "INVAPPROV", "Invest_chart_st", "Invest_chart_range", "N"
'Add new graphs to this area
Sheets("A").Select
ActiveSheet.Protect ("password")
Sheets("MONHRSCUM").Visible = xlHidden
Sheets("MONt CUM").Visible = xlHidden
Sheets("Weekly Enroll").Visible = xlHidden
'Sub HideWorkbooksW_M()
'Hides Sheets A B and CRF in Weekly and Monthly models
Sheets("CRF").Visible = xlVeryHidden
Sheets("B").Visible = xlVeryHidden
Sheets("A").Visible = xlVeryHidden
Sheets("PtVisits").Visible = xlVeryHidden
Sheets("CumPtVisits").Select
Sheets("CumPtVisits").Visible = xlVeryHidden
ActiveWorkbook.Protect ("password")

Sheets("Table of Contents").Select
ActiveSheet.Range("A1").Select

End Sub


It seems to bomb when it goes to the graphs and fails at the line (first occurance) n= n+1 below:

Sub refresh_charts(ochart, Begrange, P_range, Small_charts, wk_page)
Dim Current_cell As Object
Dim BeforeCell As Object
Dim StartCell As Object
Dim EndCell As Object
Dim Freqwk As Object
Dim Visit As Object

' Build the Xvalues for Weekly
Set Freqwk = Worksheets("A").Range("F23")
Set Visit = Worksheets("A").Range("I22")
Set Current_cell = Worksheets("A").Range("X_RANGE_ST")
Set StartCell = Current_cell
n = 1
Maxwks = Val(Freqwk) * Val(Visit)
If Maxwks >= 166 Then
Maxwks = 166
End If
For Each Current_cell In Worksheets("A").Range("X_RANGE")
If n > Maxwks Then Exit For
n = n + 1
Next
Set EndCell = Current_cell
If Small_charts = "Y" Then
ActiveSheet.ChartObjects(ochart).Activate
ActiveChart.SeriesCollection(1).XValues = Worksheets("A").Range(StartCell, EndCell)
Else
Charts(ochart).SeriesCollection(1).XValues = Worksheets("A").Range(StartCell, EndCell)
End If
' Find End of Data
Set Current_cell = Worksheets(wk_page).Range(Begrange)
Set StartCell = Current_cell
n = 1
Maxwks = Val(Freqwk) * Val(Visit)
If Maxwks >= 166 Then
Maxwks = 166
End If
For Each Current_cell In Worksheets(wk_page).Range(P_range)
If (ochart = "Chart 6" Or ochart = "CRF STATUS") And Current_cell.Value = " " Then Exit For
If (ochart = "Chart 5" Or ochart = "CRFCUM") And Current_cell.Value = " " Then Exit For
If n > Maxwks Then Exit For
n = n + 1
Set BeforeCell = Current_cell
Next
Set EndCell = BeforeCell
If Small_charts = "Y" Then
ActiveSheet.ChartObjects(ochart).Activate
ActiveChart.SeriesCollection(1).Values = Worksheets(wk_page).Range(StartCell, EndCell)
Else
Charts(ochart).SeriesCollection(1).Values = Worksheets(wk_page).Range(StartCell, EndCell)
End If
End Sub


Any help would be greatly appreciated.

 

[BrianB]

The first principle is to write macros in the *oldest* version of Excel required.

 

[rkmusselman]

These macros were originally written in Excel 97. That's what's so unusual.

RKM

 

[BrianB]

I have fallen into the trap of accidentally saving Excel97 workbook in Excel 2000 which gave problems. Perhaps you too ? Now I put "XL97" as part of the file name to remind me to always open in 97. I can then open and SaveAs a separate version if necessary.

 

[Chitosunday]

Try breaking your macro into various sub and execute each one individually.
If there is no problem, then probably it is a bug of microsoft office. If there is no problem when you execute each one, then i may have a solution but if there is still a problem, i can't give you one.