"Compile Error in Hidden Module" 1

[Mantle51]

Hello,
In Excel 2000 the above referenced error message is received when trying to run a macro which is triggered through a custom menu item. The .xla project was developed in 2003 version and works flawlessly in that version, however in v2000 causes this error.

Any Ideas?

Thanks.........Mick

 

[TonyJollans]

Presumably you have used some part of the Excel 2003 Object model that does not exist in Excel 2000. Without seeing the code no-one can help much.

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

Professional Office Developers Association

 

[Mantle51]

OK, I just thought someone may have had a version work-around in mind without seeing the code.
Here is the main processing macro(Keep in mind there is also 2 forms, a Class Module and an Event triggered sheet)

Thanks..........
Sub JNLMacro()

Dim rng As Range, r As Range, iCol As Integer, Counter As Integer
Dim FinalRow As Long, i As Long, statCount As Integer
On Error GoTo Err_JNLMacro

'Determines Sheet preparedness
If Sheets.Count > 1 Then
MsgBox "There can only be one sheet in this workbook and it must be named Sheet1"
GoTo Exit_JNLMacro
ElseIf ActiveSheet.Name <> "Sheet1" Then
MsgBox "The Sheet Must Be Named Sheet1"
GoTo Exit_JNLMacro
End If
Application.ScreenUpdating = False
With UserForm2
.Show
End With
'Sets up counter
FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
statCount = FinalRow / 15
Call GasGuage(FinalRow, statCount)

'Copies and hides Inform Import sheet
Range("J:J,R:R,AI:AI,AM:AM,BW:BW").Copy
Sheets.Add.Name = "JNL Summary"
With Sheets("JNL Summary")
.Paste
End With
statCount = statCount * 1.7
Call GasGuage(FinalRow, statCount)
'Copies and pastes applicable fields into first 5 columns
Columns("A:B").Cut Destination:=Columns("F:G")
Columns("A:B").Delete Shift:=xlToLeft
With Sheets("Sheet1")
.Visible = False
.Name = "Inform Import"
End With

'Sorts asc by CUSIP
Range("A1:E" & FinalRow).Sort Key1:=Range("A1"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

statCount = statCount * 1.2
Call GasGuage(FinalRow, statCount)
''''Begin Aggregate''''''''''''''''''''''''''''''''''''''''''''''
Counter = 0
'The next line labours under the assumption that no blank cells exist within Col A used range
Set rng = Range("A2", [A2].End(xlDown))
For Each r In rng
With Cells(r.Row, 1)
If .Value = .Offset(1).Value Then
Counter = Counter + 1
Else: .Offset(, 5).Formula = "=SUM(D" & r.Row & "" & r.Row - Counter & ")"
.Offset(, 6).Formula = "=SUM(E" & r.Row & ":E" & r.Row - Counter & ")"
Counter = 0
End If
End With
statCount = statCount + 1
Call GasGuage(FinalRow, statCount)
Next

Columns("F:G").Copy
Columns("D:E").PasteSpecial (xlPasteValues)
Columns("F:G").Delete
Application.CutCopyMode = False

'Deletes rows with blank cells, leaving summed values
For i = FinalRow To 2 Step -1
With Cells(i, 4)
If .Value = "" Then
.EntireRow.Delete
End If
End With
Next i

'Simple Formatting
With Range("A1").Resize(1, 5)
.Value = Array("Cusip", "Asset Short Description", "Asset Classification Category Name", _
"Quantity", "Quantity Amortized")
.Font.Bold = True
End With
With Columns("D:E")
.Font.Bold = True
.NumberFormat = "#,##0.00"
End With

With Cells
.WrapText = False
.Columns.AutoFit
End With

Application.ScreenUpdating = True
Range("A2").Select
Unload UserForm2
ActiveWindow.FreezePanes = True

Exit_JNLMacro:
Exit Sub

Err_JNLMacro:
MsgBox Err.Description
Unload UserForm2
Resume Exit_JNLMacro
End Sub

 

[TonyJollans]

There are no special workarounds.

Almost certainly this is the problem ...

Range("A1:E" & FinalRow).Sort Key1:=Range("A1"), Order1:=xlAscending, Header:= _
        xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom[red], _
        DataOption1:=xlSortNormal[/red]

Just remove the DataOption1 parameter which is not in 2000 and it should still work in 2003 as well.

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

Professional Office Developers Association

 

[Mantle51]

A Megabyte of THANKS!!!!!!!