| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Why QUOTIENT function is not being recognized? | Excel Worksheet Functions | |||
| Issue with code execution -- it is very slow | Excel Programming | |||
| Worksheet VBA function - not recognized | Excel Worksheet Functions | |||
| User defined function not recognized | Excel Programming | |||
| Function name not recognized | Excel Programming | |||
Linear Mode
