VBA UDF Excel 2010 #VALUE

Question

I have read the threads that are about UDF #VALUE errors and it seems that either I do not know enough about VBA to be able to use them or the problem I have is not the same.

Either way, I am trying to make a UDF to calculate the number of months in an age in the format YY:MM based on a formula I created that did the same.

Function TOTALMONTHS(YearsMonths As String)
Colonz = WorksheetFunction.Find(":", YearsMonths)
Yearz = Left(YearsMonths, Colonz - 1)
Monthz = Mid(YearsMonths, Colonz + 1, Lengthz - Colonz)
Lengthz = Len(YearsMonths)
TOTALMONTHS = Yearz * 12 + Monthz
End Function

The above code returns a #VALUE error when implemented in Excel 2010.

Any assistance on what mistake(s) I have made would be greatly appreciated!

Thank you!

Edit:

I am trying to and an If block to cater for ages containing ">" at the beginning. For example, 8:6 and >8:6. I think I maybe be getting a false positive on the search=1 result at the beginning but cannot figure out why.

Function TOTALMONTHS(YearsMonths As String) As Integer
If WorksheetFunction.Search(">", YearsMonths) = 1 Then
Greaterz = 2
Else
Greaterz = 1
End If
Colonz = WorksheetFunction.Find(":", YearsMonths)
Yearz = Mid(YearsMonths, Greaterz, Colonz - Greaterz)
monthz = Right(YearsMonths, Len(YearsMonths) - Colonz)
TOTALMONTHS = Yearz * 12 + monthz
End Function

I have no Idea how to do the "If" bit and also cannot figure out how to put code in a comment below... thanks in advance!!

SOLVED using the answer below - thank you so much!

And this is the final code to allow for ":" or "." delimiters and ">" symbols as well:

Function TOTALMONTHS(YearsMonths As String) As Integer

Dim Colonz As Integer, Yearz As Integer, monthz As Integer, Greaterz As Integer

' check if the stings consists of ">" sign
If InStr(YearsMonths, ">") >= 1 Then
    Greaterz = 2
Else
    Greaterz = 1
End If

' check position of ":" or "." sign
If InStr(YearsMonths, ":") >= 1 Then
    Colonz = InStr(YearsMonths, ":")
Else
    Colonz = InStr(YearsMonths, ".")
End If

Yearz = Mid(YearsMonths, Greaterz, Colonz - Greaterz)
monthz = Right(YearsMonths, Len(YearsMonths) - Colonz)
TOTALMONTHS = Yearz * 12 + monthz

End Function

Answer 1

Try the updated UDF code below:

Function TOTALMONTHS(YearsMonths As String) As Integer

Dim Colonz As Integer, Yearz As Integer, monthz As Integer, Greaterz As Integer

' check if the stings consists of ">" sign
If InStr(YearsMonths, ">") >= 1 Then
    Greaterz = 2
Else
    Greaterz = 1
End If

' check position of ":" sign
Colonz = InStr(YearsMonths, ":")

Yearz = Mid(YearsMonths, Greaterz, Colonz - Greaterz)
monthz = Right(YearsMonths, Len(YearsMonths) - Colonz)
TOTALMONTHS = Yearz * 12 + monthz

End Function

Below you can find the samples of data I tested this UDF code with:

(keep in mind, that cells in columns B and E need to be formatted as Text)