Maintaining The Utility Function Library

416-621-9348 cgreaves@chrisgreaves.com
Home Services Products

How To Build an Excel Library (Home); You Gotta Start Somewhere ; Creating A Function Library ; Compiling A Workbook To A Library ; Creating A Macro Library ; Referencing A Library ; A Toolbar Menu ; Making The Macro Library Available ; Some Minor Points ; Maintaining The Utility Function Library ; End-users And The Utility Function Library

You will be tempted to drop new functions directly into UX.XLA.

Don’t.

Take the longer route; you’ll be happier.

Open up UX001.XLS and immediately re-save it as UX002.XLS.

I recommend leaving an audit trail of working versions of libraries; it is so much faster to fall back to an earlier version that to try to unscramble a mess in a current version.

Besides which, for public release, you will have locked your utility libraries with a password, so you want to continue development on your machine with an unlocked version.

More importantly, you ought not to be modifying a production version of a library that is being used by other users or by other applications.

Here is the new function:

Public Function strSplitAt(strSource As String, strDelimiter As String) As String    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ''' Function:   strSplitAt
    '''
    ''' Comments:   Split away the leading part of a string of characters.
    '''
    ''' Arguments:  STRING          The string to be parsed
    '''             STRING          The delimiting string
    '''
    ''' Returns:    STRING
    '''
    ''' Date        Developer       Action
    ''' --------------------------------------------------------------------------
    ''' 2008/12/08  Chris Greaves   Created
    '''
    Dim lngIn As Long
    lngIn = InStr(1, strSource, strDelimiter)
    If lngIn > 0 Then
        strSplitAt = Left$(strSource, lngIn - 1)
        strSource = Right$(strSource, Len(strSource) - Len(strDelimiter) - lngIn + 1)
    Else
        strSplitAt = strSource
        strSource = ""
    End If
'Sub TESTstrSplitAt()
'    Debug.Assert "here " = strSplitAt("here abc isabc a stringabcandanother", "abc")
'    Debug.Assert "here" = strSplitAt("here,is,a,string", ",")
'    Debug.Assert "string" = strSplitAt("string", ",")
'    Debug.Assert "here " = strSplitAt("here is a string", "is")
'    Dim strIn As String
'    strIn = "here,is,a,string"
'    While strIn <> ""
'        Debug.Print "*" & strSplitAt(strIn, ",") & "*"
'    Wend
'End Sub
End Function

As usual you are invited to drag the self-testing procedure out of the function body, de-comment it, run it, and then re-comment and drag it back.

Visit www.ChrisGreaves.com for this image! UExcl017.JPG

And here is the module holding the function in our UX002.XLS.

Save your upgraded library as UX.XLA.

Delete the earlier UX001.XLA. From now on we will always save our UXnnn.XLS as UX.XLA.

re-open the Under001.XLS and re-assign (Tools, References) the library, using UX.XLA instead of UX001.XLA.

End-users And The Utility Function Library

Toronto and Mississauga, Friday, December 03, 2010 8:44 AM