1) Not tested but I notice that you haven't declared oCell. Try...
Dim oCell as object
and see what happens.

2) Excel usually gives the #NAME? error when it can't find a function. Are
you sure 2007 knows where your 'CalcOpen' function is?
--
Hope this helps.
If this post was helpfull, please remember to click on the ''''YES''''
button at the bottom of the screen.
Thanks,
Gary Brown


"John Barr" wrote:

I have an excel VBA Function and it used to work fine in Office 2003. In
2007, it doesnt. I get an error #name. All it does is cycle through 1 column,
comparing the date value to determine if its between the date range passed
in, if it is, it scans the next column and determines if a date exists, if
not, it tallies a count of it and returns the total of those records without
a date in the 2nd column. Here is the code.

Function CalcOpen(ByVal vBegin, ByVal vEnd)
'Application.Volatile (True)
Dim sCell, iTot, sCells

iTot = 0

For Each oCell In Worksheets("Requests").Range("A2:A1000").Cells
If oCell.Value = DateValue(vBegin) And oCell.Value <=
DateValue(vEnd) Then
sCell = "B" + Mid(oCell.Address, 4)
If IsNull(Worksheets("Requests").Range(sCell).Cells.V alue) Or
IsEmpty(Worksheets("Requests").Range(sCell).Cells. Value) Then
iTot = iTot + 1
End If
End If
Next
CalcOpen = iTot
End Function