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.