VBA Miscellaneous Functions for Microsoft Excel

Description

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.

Change History

  1. March 2014 - Initial Public Release

Code

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.

To contact the writer please use this form, or if you prefer use Linked In or even Facebook.

HTML check . CSS check

© Jim Champ, last edit