VBA Function Not Recognized or Execution Issue
 

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

VBA Function Not Recognized or Execution Issue
 

Did you put it in a general module?

--
Regards,
Tom Ogilvy


"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

VBA Function Not Recognized or Execution Issue
 

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

VBA Function Not Recognized or Execution Issue
 

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

"Tom Ogilvy" wrote:

Did you put it in a general module?

--
Regards,
Tom Ogilvy


"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

VBA Function Not Recognized or Execution Issue
 

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.

"Gary Brown" wrote:

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

VBA Function Not Recognized or Execution Issue
 

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 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

--

Dave Peterson

VBA Function Not Recognized or Execution Issue
 

Macro Security is set to enabled.

"Dave Peterson" wrote:

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 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

--

Dave Peterson

VBA Function Not Recognized or Execution Issue
 

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.

John Barr wrote:

Macro Security is set to enabled.

"Dave Peterson" wrote:

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 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

--

Dave Peterson


--

Dave Peterson