Option Explicit
‘ Define a character or series of characters to separate reported feet and inches
‘ E.g. if you want 15 feet, 5 inches to be expressed as 15’-5″ then enter “-” as the separator
Public Const FtInchSeparator As String = “-“
‘ Define a character or series of characters to separate reported inches and inch fractions
‘ E.g. if you want 15 and 3/16 inches to be expressed as 5-0 3/16″ then enter “-” as the separator
Public Const InchInchFractionSeparator As String = ” “
Public Function StringToFt(StringDim As String)
‘ Converts a string of text of Feet and Inches in to a decimal number in feet
‘ E.g. 15’-5 3/16″ convert to 15.4322916666667
‘
‘ Strings contained in parenthesis are interpreted as negative, and the resultant value is correspondingly reported as negative.
‘ E.g. (15’-5 3/16″) converts to -15.4322916666667
‘
‘ Feet and inches are separated by the Public Constant named ‘FtInchSeparator’, which has been defined above.
‘
‘ The code is also written to recognize superscript and subscript characters in the inch fractions.
‘
‘ Written by: Dan Ashby
‘ Contact: dan.t.ashby@gmail.com
‘
‘ Last Updated: 1 December 2017
‘ Check for presence of inch symbol
Dim InchSymLoc As Integer: InchSymLoc = InStr(1, StringDim, “”””)
‘ Remove inch sign, if present, and strip leading and trailing spaces
StringDim = Trim(Replace(StringDim, “”””, “”))
‘ Check for parenthesis, indicating a negative value
Dim Neg As Double: Neg = 1 ‘ Set default behavior, which assumes the input value is positive
If Left(StringDim, 1) = “(” And Right(StringDim, 1) = “)” Then ‘ Check for enclosing parenthesis
Neg = -1 ‘ If enclosing parenthesis are found, store negative unit value to change the sign of the final reported value
StringDim = Mid(StringDim, 2, Len(StringDim) – 2) ‘ Trim the leading and trailing characters (the parenthesis)
End If
‘ Generate array of Unicodes corresponding for the superscript and subscript numbers 0, 1, 2, 3…9
Dim i As Integer
Dim SupCodes As Variant
Dim Subcodes As Variant
SupCodes = Array(8304, 185, 178, 179, 8308, 8309, 8310, 8311, 8312, 8313) ‘Superscript Unicode character codes for numbers 1 through 9
Subcodes = Array(8320, 8321, 8322, 8323, 8324, 8325, 8326, 8327, 8328, 8329) ‘Subscript Unicode character codes for numbers 1 through 9
For i = 0 To 9 ‘ Find and replace any superscript or subscript characters with the corresponding integer
StringDim = Replace(StringDim, ChrW(SupCodes(i)), i)
StringDim = Replace(StringDim, ChrW(Subcodes(i)), i)
Next i
‘ Vet the string for illegal characters or unexpected format
Dim Chr As String
Dim FtSymLoc As Integer: FtSymLoc = InStr(1, StringDim, “‘”)
Dim FtInchSeparatorLoc As Integer: FtInchSeparatorLoc = InStr(1, StringDim, FtInchSeparator)
Dim InchInchFractionSeparatorLoc As Integer: InchInchFractionSeparatorLoc = InStr(1 + IIf(FtSymLoc = 0, 0, FtSymLoc + Len(FtInchSeparator)), StringDim, InchInchFractionSeparator)
For i = 1 To Len(StringDim)
If i = FtSymLoc Then ‘ Skip over the Ft symbol, and the FtInchSeparator characters
i = i + Len(FtInchSeparator)
ElseIf i = InchInchFractionSeparatorLoc Then ‘ Skip over the InchInchFractionSeparator characters
i = i + Len(InchInchFractionSeparator) – 1
Else
‘ Check the string, character-by-character, to ensure that the input matches the format expected
‘ Aside from the previously skipped Ft symbol, the FtInchSeparator and the InchInchFractionSeparator,
‘ the string should now only contain numbers, and potentially a fraction slash symbol.
Chr = Mid(StringDim, i, 1)
If Not (IsNumeric(Chr) Or Chr = “/”) Then
‘ If illegal characters are found, exit the function and report the offending character
StringToFt = “Unexpected character encountered: ‘” & Chr & “‘. Use format 15′” & FtInchSeparator & “5” & InchInchFractionSeparator & “3/16″””
Exit Function
End If
End If
Next i
‘ Extract number of whole feet from the string
Dim Ft As Double
If FtSymLoc = 0 Then
‘ Given foot symbol has not been found, double-check that inch units were provided
If InchSymLoc = 0 Then
‘ If neither foot nor inch symbols are found, then the input string is ambiguous, as it’s not clear if it ought to be interpreted as inches or feet
StringToFt = “Ambiguous input: Provide unit symbols indicating whether input is in feet or inches. Use format 15′” & FtInchSeparator & “5” & InchInchFractionSeparator & “3/16″””
Exit Function
End If
‘ If inch symbols are provided, but no foot symbol was found, then interpret this to mean dimension string was given in inches only.
Ft = 0
Else ‘ Foot symbol (‘) has been found
‘ Given foot symbol has been found, check that – if an inch symbol is also within the string – the inches and feet are separated using the FtInchSeparator character(s)
If InchSymLoc <> 0 And FtInchSeparatorLoc = 0 Then
StringToFt = “Invalid input format: The character(s) used to separate the feet and inches is invalid. Use format 15′” & FtInchSeparator & “5” & InchInchFractionSeparator & “3/16″””
Exit Function
End If
‘ Extract number of feet from the string
Ft = Val(Left(StringDim, FtSymLoc – 1)) ‘ Extract number of feet from the string
‘ If the last character of the string was the foot symbol, then there are no inches or inch fractions to process, therefore report the whole number of feet contained in the string
If FtSymLoc = Len(StringDim) Then
StringToFt = IIf(Ft <> 0, Neg, 1) * Ft
Exit Function
End If
StringDim = Trim(Right(StringDim, Len(StringDim) – FtSymLoc – Len(FtInchSeparator))) ‘ Trim characters preceding the inch and inch fraction parts of the string
‘ Given foot symbol has been found, anything remaining to the right of it must be in inches. Check this has an inch symbol provided
If InchSymLoc = 0 And StringDim <> “” Then
StringToFt = “Invalid input format: The inch symbol is missing from the end of the string. Use format 15′” & FtInchSeparator & “5” & InchInchFractionSeparator & “3/16″””
Exit Function
End If
End If
‘ Extract number of inches and inch fractions from the string
‘ At this point, the preceding code has stripped out the feet, foot symbol, and the characters separating the feet and inches,
‘ therefore StringDim now contains only the characters reflecting the inch and inch fractions, if appliccable
Dim Inch As Double
Dim Nmrtr As Double, Dnmtr As Double: Dnmtr = 1 ‘ Set denominator to 1 to avoid DIV0! error if there does not exist an inch fraction
‘Dim SpaceSymLoc As Integer: SpaceSymLoc = InStr(1, StringDim, ” “) ‘ Locate space between inch and inch fraction, if present
Dim FracSymLoc As Integer: FracSymLoc = InStr(1, StringDim, “/”) ‘ Locate inch fraction slash
InchInchFractionSeparatorLoc = InStr(1, StringDim, InchInchFractionSeparator)
If InchInchFractionSeparatorLoc <> 0 Then ‘ String contains both whole inches and an inch fraction
Inch = Val(Left(StringDim, InchInchFractionSeparatorLoc – 1)) ‘ Whole inches are contained within the string preceding the characters that separates the inches from the inch fraction
Nmrtr = Val(Mid(StringDim, InchInchFractionSeparatorLoc + Len(InchInchFractionSeparator), FracSymLoc – InchInchFractionSeparatorLoc)) ‘ Inch fraction numerator is between the end of the characters separating inches and inch fractions, and the fraction slash symbol
Dnmtr = Val(Right(StringDim, Len(StringDim) – FracSymLoc)) ‘ Inch fraction denominator is whatever remains after the fraction slash symbol
ElseIf FracSymLoc <> 0 Then ‘ Check for the presence of a fraction slash symbol, indicating that there is an inch fraction within the string
Inch = 0 ‘ Given that the case where a space character was found has been previously handled, this ElseIf statement handles instances where no space was found, hence there are no whole inches in the string.
Nmrtr = Val(Left(StringDim, FracSymLoc – 1)) ‘ Inch fraction numerator is whatever lies to the left of the fraction slash symbol
Dnmtr = Val(Right(StringDim, Len(StringDim) – FracSymLoc)) ‘ Inch fraction denominator is whatever lies to the right of the fraction slash symbol
Else ‘ String does not contain any inch fraction
Inch = Val(StringDim) ‘ Remaining string contains only whole inches
End If
StringToFt = Ft + (Inch + Nmrtr / Dnmtr) / 12 ‘ Sum all components, dividing the inches by 12 to report resulting value in feet.
StringToFt = IIf(StringToFt <> 0, Neg, 1) * StringToFt ‘ Handle the case where the input is zero, to avoid the result being reported as negative zero.
End Function
Public Function FtToString(Value As Variant, Optional Dnmtr As Double = 16, Optional ShowSupSub As Integer = 1)
‘ Converts a numeric value (assumed to be in feet) in to a string in the format of feet and inches.
‘ E.g. 15.4322916666667 is displayed as 15’-5 3/16″
‘
‘ Optional Variable: Dnmtr (denominator) which allows user to manually define the precision of the inch fractions.
‘ E.g. enter 8 to round result to the nearest 1/8″. Enter 1 to give values to the nearest inch. Default precision is 1/16″.
‘ Optional Variable: ShowSupSub which allows user to display string using normal numeric characters, instead of the default which shows superscript/subscript characters.
‘ Enter 0 to return non-super/subscript fractions.
‘
‘ Strings contained in parenthesis are interpreted as negative, and the resultant value is correspondingly reported as negative.
‘ E.g. -15.4322916666667 is displayed as (15’-5 3/16″)
‘
‘ Written by: Dan Ashby
‘ Contact: dan.t.ashby@gmail.com
‘
‘ Last Updated: 1 December 2017
‘ Check input is numeric
If Not IsNumeric(Value) Then
FtToString = “Invalid input; not numeric”
Exit Function
End If
‘ Check denominator selected is a standard fraction used by convention in the imperial system;
‘ By convention inch fractions are reported as 1/2″, 1/4″, 1/8″ … therefore denominator must be a base-2 number
If Log(Dnmtr) / Log(2) <> Round(Log(Dnmtr) / Log(2)) Then
FtToString = “Invalid denominator; by convention must use 1, 2, 4, 16, 32 64…”
Exit Function
End If
‘ Introduce a variable to capture the sign convention of the original value
Dim Neg As Boolean: Neg = False ‘ No sign convention is displayed for positive numbers; this is the default behavior
If Value < 0 Then
Value = Value * -1 ‘ Convert to positive value (makes coding easier). The sign convention is conveyed at the end by enclosing the output string in parenthesis
Neg = True ‘ Make a note of sign convention, such that parenthesis can be subsequently added to notate negative value
End If
‘ Round value to ensure consistent behavior
Value = Fix(CDec(Value * 12 * Dnmtr) + 0.5) / 12 / Dnmtr
‘ The above line of code serves two functions:
‘ 1) Converts to Arithmetic Rounding (by default Visual Basic 6.0 uses Bankers Rounding)
‘ This isn’t desirable here as this would result in 1/32″ and 3/32″ both rounding to 1/16″
‘ 2) Corrects for floating point error that produces erratic rounding of results.
‘ E.g. floating point error would otherwise result in the following behavior:
‘ 5′-4″ 1/32″ rounds to 5’-4 1/16″ (i.e. the 1/32″ was rounded UP)
‘ 5′-5″ 1/32″ rounds to 5’-5″ (i.e. the 1/32″ was rounded DOWN)
‘ Note: The examples given above assume the default 1/16″ precision is used
‘ For further information refer: [URL]https://support.microsoft.com/en-us/help/196652/how-to-implement-custom-rounding-procedures[/URL]
Dim Ft As Double, Inch As Double, Nmrtr As Double ‘Dim as Double variables
Ft = Int(Value) ‘ Store feet as the integer value of in input (rounds input value down to nearest whole number)
Inch = Int((Value – Ft) * 12) ‘ Store the value of inches as a decimal, taken to be the input value, converted to inches, that remains after subtracting the whole number of feet
Nmrtr = Round(((Value – Ft) * 12 – Inch) * Dnmtr) ‘ Numerator is taken to be the nearest wole number of inch fractions that remain of the input value, converted to inches, after subtracting the whole number of feet and inches
‘ If fraction rounds up to an inch, increment inch and set fraction to nul.
If Nmrtr = Dnmtr Then
Nmrtr = 0
Inch = Inch + 1
End If
‘ If inches round up to a foot for the precision specified, increment the foot value and set inch to nul.
If Inch = 12 Then
Inch = 0
Ft = Ft + 1
End If
‘ If there’s a fraction of an inch to report, this must first be simplified and formatted
If Nmrtr <> 0 Then
‘ The numerator must always be an odd number.
‘ If it’s divisible by 2, then this indicates that the fraction may be further simplified. E.g. 6/16″ simplifies to 3/8″
Do While Nmrtr Mod 2 = 0 ‘ Simplify fraction until numerator is an odd number
Nmrtr = Nmrtr / 2
Dnmtr = Dnmtr / 2
Loop
‘ Create fraction string to permit replacement of characters with superscript/subscript
Dim NmrtrStr As String: NmrtrStr = Nmrtr
Dim DnmtrStr As String: DnmtrStr = Dnmtr
‘ If ShowSupSub is True then convert the numerator to superscript and denominator to subscript
If ShowSupSub = 1 Then
‘ Generate array of Unicodes corresponding for the superscript and subscript numbers 0, 1, 2, 3…9
Dim SupCodes As Variant
Dim Subcodes As Variant
SupCodes = Array(8304, 185, 178, 179, 8308, 8309, 8310, 8311, 8312, 8313) ‘Superscript codes
Subcodes = Array(8320, 8321, 8322, 8323, 8324, 8325, 8326, 8327, 8328, 8329) ‘Subscript codes
‘ Convert numerator value to corresponding superscript characters
Dim i As Integer
For i = 1 To Len(NmrtrStr)
If IsNumeric(Mid(NmrtrStr, i, 1)) Then
NmrtrStr = Replace(NmrtrStr, (Mid(NmrtrStr, i, 1)), ChrW(SupCodes(Mid(NmrtrStr, i, 1))))
End If
Next i
‘ Convert denominator value to corresponding subscript characters
For i = 1 To Len(DnmtrStr)
If IsNumeric(Mid(DnmtrStr, i, 1)) Then
DnmtrStr = Replace(DnmtrStr, Mid(DnmtrStr, i, 1), ChrW(Subcodes(Mid(DnmtrStr, i, 1))))
End If
Next i
End If ‘Exits if statement checking for superscript/subscript application
End If ‘Exits if statement checking for presence of a fraction
‘Format the string to include foot and inch symbols
FtToString = IIf(Neg, “(“, “”) & _
IIf(Ft = 0, “”, Ft & “‘”) & _
IIf(Ft <> 0, FtInchSeparator, “”) & _
IIf(Inch = 0 And Ft = 0 And Nmrtr <> 0, “”, Inch) & _
IIf((Ft <> 0 Or Inch <> 0) And Nmrtr <> 0, InchInchFractionSeparator, “”) & _
IIf(Nmrtr <> 0, NmrtrStr & “/” & DnmtrStr, “”) & _
“””” & _
IIf(Neg, “)”, “”)
‘ Line 1 displays a open parenthesis, if the original value was negative
‘ Line 2 displays feet and foot symbol if non-zero, otherwise nothing
‘ Line 3 displays a character or characters that separate the feet and inches (or inch fraction), as applicable. This is set as a public constant, as it’s referenced by the modules that convert both to and from strings.
‘ Line 4 displays a zero if all values are zero, otherwise nothing (if a zero value was entered, 0″ will be returned)
‘ Line 5 displays the inches and inch fraction separator character(s), as applicable
‘ Line 6 displays the inch fraction, as applicable
‘ Line 7 displays the inch symbol
‘ Line 8 displace a closing parenthesis, if the original value was negative
End Function
