What does Excel97 not like about this Code Q

[John]

I am getting an error "compile error in hidden module EMail". It relates to
the code below not exceuting an I can't work out why. It works fine on Excel
2000 and above. It doesn't go into debugjust the dialog box with the message
above

Thanks



Sub Email()


Dim IngPosY As Long ' Input Cell Number
Dim IngOutY As Long ' Output Cell Number
Dim strSheetName As String ' Input Sheet Name

Dim oWS As Worksheet
Dim i As Long
Dim sName As String
Dim fCreated As Boolean


Sheets("Coda").Visible = True
Sheets("Coda Template").Visible = True
Sheets("Coda").Select

ActiveSheet.Unprotect Password:="1234"
Cells.Select
Selection.ClearContents
Range("A1").Select


IngPosY = 2 ' Starting row on starting sheet

Range("A1") = "Document_Number"
Range("B1") = "Line_Number"
Range("C1") = "Document_Type"
Range("D1") = "Document_date"
Range("E1") = "Nominal"
Range("F1") = "Subaccount"
Range("G1") = "Level3"
Range("H1") = "Document_Value"
Range("I1") = "Document_Year"
Range("J1") = "Document_Period"
Range("K1") = "External_Text"
Range("L1") = "Quantity_1"
Range("M1") = "Description"


IngOutY = 2

Do While Len(Worksheets("Coda Template").Range("A" & IngPosY)) > 0
Range("a" & IngOutY) = Worksheets("Coda Template").Range("A" &
IngPosY)
Range("B" & IngOutY) = Worksheets("Coda Template").Range("B" &
IngPosY)
Range("C" & IngOutY) = Worksheets("Coda Template").Range("C" &
IngPosY)
Range("D" & IngOutY) = Worksheets("Coda Template").Range("D" &
IngPosY)
Range("E" & IngOutY) = Worksheets("Coda Template").Range("E" &
IngPosY)
Range("F" & IngOutY) = Worksheets("Coda Template").Range("F" &
IngPosY)
Range("G" & IngOutY) = Worksheets("Coda Template").Range("G" &
IngPosY)
Range("H" & IngOutY) = Round(Worksheets("Coda Template").Range("H" &
IngPosY), 2)
Range("I" & IngOutY) = Worksheets("Coda Template").Range("I" &
IngPosY)
Range("J" & IngOutY) = Worksheets("Coda Template").Range("J" &
IngPosY)
Range("K" & IngOutY) = Worksheets("Coda Template").Range("K" &
IngPosY)
Range("L" & IngOutY) = Worksheets("Coda Template").Range("L" &
IngPosY)
Range("M" & IngOutY) = Worksheets("Coda Template").Range("M" &
IngPosY)


IngPosY = IngPosY + 1
IngOutY = IngOutY + 1

Loop

Rows("1:1").Select
Selection.Font.Bold = True
Selection.Font.ColorIndex = 6
Rows("1:1").Select
With Selection.Interior
.ColorIndex = 49
.Pattern = xlSolid
End With
Columns("A:B").Select
Selection.NumberFormat = "0"
Columns("C:C").Select
Selection.NumberFormat = "@"
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.MergeCells = False
End With
Columns("D").Select
Selection.NumberFormat = "dd/mm/yyyy"
Columns("E:G").Select
Selection.NumberFormat = "0"
Columns("H:H").Select
Selection.NumberFormat = "0.00"
With Selection
.HorizontalAlignment = xlRight
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Columns("I:J").Select
Selection.NumberFormat = "0"
Columns("K:K").Select
Selection.NumberFormat = "@"
Columns("L:L").Select
Selection.NumberFormat = "0.00"
Cells.Select
Columns("M:M").Select
Selection.NumberFormat = "@"
Cells.EntireColumn.AutoFit

For i = Range("E500").End(xlUp).Row To 2 Step -1
If Left(Range("E" & i).Value, 3) <> "VAT" Or _
Range("L" & i).Value = 0 Then
If Range("H" & i).Value = 0 Then
Range("H" & i).EntireRow.Delete
End If
End If
Next i
Range("B2").Formula = "1"
Range("B3").Select
With Selection
.Formula = "=IF(LEFT(E3,5)=""16000"",1,B2+1)"
.AutoFill Destination:=Range("B3:B" & Range("b500").End(xlUp).Row)
End With

Range("A2").Select


Sheets("Coda").Visible = False
Sheets("Coda Template").Visible = False

Sheets("Claim Form").Select

Range("B10").Select

End Sub

 

[Chip Pearson]

John,

The problem is with the line

Range("H" & IngOutY) = Round(Worksheets("Coda
Template").Range("H" & IngPosY), 2)

VBA5 (which is what Excel97 runs) doesn't have a Round function.
Round was added in VBA6 (which is in Excel 2000 and later).

Change the line to
Range("H" & IngOutY) = Application.Round(Worksheets("Coda
Template").Range("H" & IngPosY), 2)



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[John]

An update on this. I've found that it debugs on "Round"

I am Rounding whatever value is in Coda Template "H"

Is there a similar function in Excel97?

Thanks

 

[John]

Thanks Chip

John,

The problem is with the line

Range("H" & IngOutY) = Round(Worksheets("Coda Template").Range("H" &
IngPosY), 2)

VBA5 (which is what Excel97 runs) doesn't have a Round function. Round was
added in VBA6 (which is in Excel 2000 and later).

Change the line to
Range("H" & IngOutY) = Application.Round(Worksheets("Coda
Template").Range("H" & IngPosY), 2)



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com