AgeFunc No Longer Works

[jhrBanker]

I created a file in Excel 2003 and entered the code for the AgeFunc function, and it worked fine. I've now upgraded to Excel 2007, and the function no longer works (cells contain #NAME?). The code still exists in the file (ALT-F11) reveals the following code:

Public Function AgeFunc(stdate As Variant, endate As Variant)

' Dim our variables.
Dim stvar As String
Dim stmon As String
Dim stday As String
Dim styr As String
Dim endvar As String
Dim endmon As String
Dim endday As String
Dim endyr As String
Dim stmonf As Integer
Dim stdayf As Integer
Dim styrf As Integer
Dim endmonf As Integer
Dim enddayf As Integer
Dim endyrf As Integer
Dim years As Integer

' This variable will be used to modify string length.
Dim fx As Integer
fx = 0

' Calls custom function sfunc which runs the Search worksheet function
' and returns the results.
' Searches for the first "/" sign in the start date.
stvar = sfunc("/", stdate)

' Parse the month and day from the start date.
stmon = Left(stdate, sfunc("/", stdate) - 1)
stday = Mid(stdate, stvar + 1, sfunc("/", stdate, sfunc("/", stdate) + 1) - stvar - 1)

' Check the length of the day and month strings and modify the string
' length variable.
If Len(stday) = 1 Then fx = fx + 1
If Len(stmon) = 2 Then fx = fx + 1

' Parse the year, using information from the string length variable.
styr = Right(stdate, Len(stdate) - (sfunc("/", stdate) + 1) - stvar + fx)

' Change the text values we obtained to integers for calculation
' purposes.
stmonf = CInt(stmon)
stdayf = CInt(stday)
styrf = CInt(styr)

' Check for valid date entries.
If stmonf < 1 Or stmonf > 12 Or stdayf < 1 Or stdayf > 31 Or styrf < 1 Then
AgeFunc = "Invalid Date"
Exit Function
End If

' Reset the string length variable.
fx = 0

' Parse the first "/" sign from the end date.
endvar = sfunc("/", endate)

' Parse the month and day from the end date.
endmon = Left(endate, sfunc("/", endate) - 1)
endday = Mid(endate, endvar + 1, sfunc("/", endate, sfunc("/", endate) + 1) - endvar - 1)

' Check the length of the day and month strings and modify the string
' length variable.
If Len(endday) = 1 Then fx = fx + 1
If Len(endmon) = 2 Then fx = fx + 1

' Parse the year, using information from the string length variable.
endyr = Right(endate, Len(endate) - (sfunc("/", endate) + 1) - endvar + fx)

' Change the text values we obtained to integers for calculation
' purposes.
endmonf = CInt(endmon)
enddayf = CInt(endday)
endyrf = CInt(endyr)

' Check for valid date entries.
If endmonf < 1 Or endmonf > 12 Or enddayf < 1 Or enddayf > 31 Or endyrf < 1 Then
AgeFunc = "Invalid Date"
Exit Function
End If

' Determine the initial number of years by subtracting the first and
' second year.
years = endyrf - styrf

' Look at the month and day values to make sure a full year has passed.
If stmonf > endmonf Then
years = years - 1
End If

If stmonf = endmonf And stdayf > enddayf Then
years = years - 1
End If

' Make sure that we are not returning a negative number and, if not,
' return the years.
If years < 0 Then
AgeFunc = "Invalid Date"
Else
AgeFunc = years
End If

End Function


Public Function sfunc(x As Variant, y As Variant, Optional z As Variant)
sfunc = Application.WorksheetFunction.Search(x, y, z)
End Function

[Palmetto]

Are macros enabled in your 2007 Excel? The function uses VBA code and thus macros must be enabled. Go to the Trust Center to enable them.

[NBVC]

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

[jhrBanker]

I created a file in Excel 2003 and entered the code for the AgeFunc function, and it worked fine. I've now upgraded to Excel 2007, and the function no longer works (cells contain #NAME?). The code still exists in the file (ALT-F11) reveals the following code:

Please Login or Register  to view this content.

[shg]

Thread merged.

[jhrBanker]

Thanks. Enabling the Macro's did it.
Thanks again.