<ExcelFunction(Description:="To Decimal Inches")>
Shared Function CInches(<ExcelArgument(Name:="Inches", Description:="is the first number, to which will be added")> ByVal Text_string_containing_values_for____Feet_Inches)
'\ These values are used to examine the input string, one character at a time
Dim vVal As String '\ shorter name for input string
Dim i As Integer '\ counter to step through each character in input string
Dim vChar As Object '\ temporary storage of each input string character
'\ These variables hold the values we discover for feet, inches and the
'\ numerator and denominator of the fractional inches
Dim iFt As Integer '\ used to store number of feet
Dim iIn As Integer '\ number of inches
Dim iNumerator As Integer '\ numerator of fractional inches
Dim iDenominator As Integer '\ denominator of fractional inches
'\ In the process of discovering values for feet and inches, these variable
'\ are used to accumulate and hold numeric values
Dim iTemp As Integer '\ Used to build a number as each digit is read
'\ We want to ignore spaces, except for the very important space between
'\ the number of inches and the numerator of the fractional inches
'\ This variable is true if the last character processed was a space
Dim bLastCharWasSpace As Boolean
'\ First we assign input string to variable with shorter name
vVal = Text_string_containing_values_for____Feet_Inches
'\ If input string is numeric, then we don't want to convert it
If IsNumeric(vVal) Then
CInches = vVal
Exit Function
End If
'\ Now we step through each character in input string from left to right
iTemp = 0
bLastCharWasSpace = False
For i = 1 To Len(vVal)
vChar = Mid(vVal, i, 1)
'\ If character is a number, then we combine it with numbers before it
'\ to get a number that we can assign to feet, inches, numerator or denominator
If IsNumeric(vChar) Then
'\ If this is a number and the last character was a space then
'\ chances are, the number in iTemp is inches and we need to
'\ start over building the numerator of fractional inches
If bLastCharWasSpace = True And iIn = 0 Then
iIn = iTemp
iTemp = 0
End If
'\ As we read number from left to right, we multiply value of previous
'\ number (if any) by 10 and add this number
If iTemp = 0 Then
iTemp = vChar
Else
iTemp = iTemp * 10
iTemp = iTemp + vChar
End If
'\ The number we've been buiding must be feet
ElseIf vChar = "'" Or vChar = "f" Then
iFt = iTemp
iTemp = 0
'\ The number we've been bulding must be the numerator of fraction
ElseIf vChar = "/" Then
iNumerator = iTemp
iTemp = 0
'\ The number we've been building must be inches or
'\ the denominator of the fraction, so we check to see if
'\ there is a numerator
ElseIf vChar = """" Or vChar = "i" Then
If iNumerator > 0 Then
iDenominator = iTemp
iTemp = 0
'\ If no numerator, then the number must be inches
ElseIf iIn = 0 Then
iIn = iTemp
iTemp = 0
End If
End If
'\ Now we set our indicator so that when we process the next
'\ character, we will know if the last character was a space
If vChar = " " Then
bLastCharWasSpace = True
Else
bLastCharWasSpace = False
End If
Next i
'\ To avoid dividing by zero if there was no numerator for fraction,
'\ we set denominator to 1
If iNumerator = 0 And iDenominator = 0 Then iDenominator = 1
'\ Finally, we calculate number of decimal inches and return value
CInches = (iFt * 12) + iIn + (iNumerator / iDenominator)
End Function
