Just in case someone finds them useful here are a couple of custom functions, one utterly trivial and the other one marginally more complicated. Feet2Inches() is intended to take a text string of imperial lengths, formatted as 12'3" and convert it into a Double precision number in inches. I don't think I really need to say anything about Inches2Metres(), its just included because I often use the two together.
There is nothing very clever about these at all, but I do use them because I have hobbies that involve objects in imperial dimensions, so here they are.
To use them just paste the code section into an appropriate Excel template, either your main personal macro template, or else a custom one you will use for imperial dimensions.
I've presented this as a code secton rather than a download. I think its all translated properly for that presentation, but if you try it and find any problems please let me know.
Option Explicit
'University of Illinois/NCSA Open Source License
'Copyright (c) 1995-2014 Jim Champ
'All rights reserved.
'Jim's WordHTM
'Developed by: Jim Champ
'Permission is hereby granted, free of charge, to any person obtaining a
'copy of this software and associated documentation files (the "Software"),
'to deal with the Software without restriction, including without
'limitation the rights to use, copy, modify, merge, publish, distribute,
'sublicense, and/or sell copies of the Software, and to permit persons to
'whom the Software is furnished to do so, subject to the following
'conditions:
' * Redistributions of source code must retain the above copyright
' notice, this list of conditions and the following disclaimers.
' * Redistributions in binary form must reproduce the above copyright
' notice, this list of conditions and the following disclaimers in the
' documentation and/or other materials provided with the distribution.
' * Neither the names of Jim Champ,
' nor the names of its contributors may be used to endorse or promote
' products derived from this Software without specific prior written
' permission.
' THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS
' OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
' MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT.
' IN NO EVENT SHALL THE CONTRIBUTORS OR COPYRIGHT HOLDERS BE LIABLE FOR
' ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF
' CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH
' THE SOFTWARE OR THE USE OR OTHER DEALINGS WITH THE SOFTWARE.
Public Function Inches2Metres(varDim As Variant) As Variant
Inches2Metres = CDbl(varDim) * 25.4 / 1000
End Function
Public Function Feet2Inches(strDim As String) As Variant
strDim = Trim(strDim)
If Len(strDim) < 1 Then
Feet2Inches = vbNullChar
Exit Function
End If
'string is in format xxx'xxxx"
'may be decimal inches...
Dim iSquotPos, iDquotPos As Integer
Dim strFeet, strInches As String
Dim iFeet As Integer
Dim iInches As Double
iSquotPos = InStr(strDim, Chr$(39))
If iSquotPos < 1 Then
'assume inches only
iDquotPos = InStr(strDim, Chr$(34))
If iDquotPos < 1 Then
'hopefully is numeric
Feet2Inches = CDbl(strDim)
Else
'there's a double quote in there
strDim = Trim(strDim)
If Right$(strDim, 1) = Chr$(34) Then
strDim = CDbl(Left$(strDim, Len(strDim) - 1))
Else
'syntax error...
Feet2Inches = "N/A"
End If
End If
Else
strFeet = Left$(strDim, iSquotPos - 1)
strInches = Mid$(strDim, iSquotPos + 1)
iFeet = CInt(strFeet)
If (Len(strInches) < 1) Then
iInches = 0
Else
iDquotPos = InStr(strInches, Chr$(34))
If iDquotPos < 1 Then
'hopefully is numeric
iInches = CDbl(strInches)
Else
'there's a double quote in there
strInches = Trim(strInches)
If Right$(strInches, 1) = Chr$(34) Then
iInches = CDbl(Left$(strInches, Len(strInches) - 1))
Else
'syntax error...
Feet2Inches = "N/A"
End If
End If
End If
Feet2Inches = CDbl(iFeet * 12 + iInches)
End If
End Function
These snippets and utilities are licensed under the University of Illinois/NCSA Open Source License. Here is the text of the license as it applies to this code.