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