VBA Function Not Recognized or Execution Issue

[John Barr]

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.Value) Or
IsEmpty(Worksheets("Requests").Range(sCell).Cells.Value) Then
iTot = iTot + 1
End If
End If
Next
CalcOpen = iTot
End Function

 

[Tom Ogilvy]

Did you put it in a general module?

 

[Gary Brown]

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]

Yes. I can email the spreadsheet if u want to see it.

 

[John Barr]

Hmmm. Based on the 2003 syntax, oCell didnt need declared, but I after
declaring it, I still get "Invalid Name Error". Also, the spreadsheet was
saved into 2007 from 2003 original, so I know it worked before and the
reference was there. I can send you a copy of the spreadsheet if you would
like.

 

[Dave Peterson]

Did you allow macros to run when you opened the workbook?

If the function is in the correct spot, then maybe it's a macro security issue.

 

[John Barr]

Macro Security is set to enabled.

Did you allow macros to run when you opened the workbook?

If the function is in the correct spot, then maybe it's a macro security issue.

 

[Dave Peterson]

And when you opened the workbook, you got a warning bar at the top and you
allowed excel to run macros?

If yes, I don't have another guess.