VBA Function Not Recognized or Execution Issue

  • Thread starter Thread starter John Barr
  • Start date Start date
J

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
 
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
 
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.
 
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.
 
Macro Security is set to enabled.

Dave Peterson said:
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.
Click to expand...
 
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.
 
Back
Top