[Use your browser's BACK button to return to the PRIME FAQ Page or click here if you came directly to this page.]

(Code) Dynamic Message Strings Using Excel/VBA and WordBasic

This was taken from the PRIME for Excel and PRIME for Word APIs. These APIs are available in our Word and Excel shareware packages which can be downloaded from our Products page.

Something you’ll do time and time again when developing applications in Excel, Word, or Visual Basic (or any programming language for that matter) is display a message to your users in a message box. More often than not, the message itself is comprised of two parts: some unchanging text and a dynamic piece (or pieces) of text. Like this, "This project's data file [filename goes here] is missing. It may have been moved or deleted. Please contact your System Administrator for technical support." The [filename goes here] piece might be "c:\winapps\winword\library\pcg\hotdog.dat" or something completely different, changing with each execution of your code.

A common programming technique is to initialize these messages as string variables with a literal marker representing each dynamic piece. The marker should be unusual enough (read: unlikely to occur naturally) to avoid any confusion, and for our purposes we use these four characters "<<>>". Now all you need is a function that takes the message variable as one argument and the current replacement text for the marker as another argument, and returns a contextually meaningful string like, "This project's data file c:\xyz.dat is missing. It may have been moved or deleted. Please contact your System Administrator for technical support."

Our PRIME API for Excel and Word includes just such a function. It’s called fstrReplaceMarker$ in WordBasic and fstrReplaceMarker in Excel.

Shown below is a WordBasic example that calls the function, presumed to be stored in the procedure library macro GeneralLib, followed by both the Excel VBA and WordBasic versions of the function. (There are some slight syntactic differences between the VBA and WordBasic versions but essentially it’s the same code.) With these functions you’ll be able to easily and dynamically replace one — or more — marked pieces of text within your projects’ message strings.

' WordBasic calling example
Sub MAIN
    DboxTitle$ = "Acme Inventory Control System 1.0"
    ICON_ATTENTION = 48
    MSG_DATAFILE_MISSING$ = "This project's " + \
        "data file <<>> is missing. It may have been moved or deleted. " + \
        "Please contact your System Administrator for technical support."
    ' ...
    Datafile$ = fDatafilename$    ' get the current data filename
    If Files$(Datafile$) = "" Then
        Prompt$ = \
            GeneralLib.fstrReplaceMarker$(MSG_DATAFILE_MISSING$, Datafile$)
        MsgBox Prompt$, DboxTitle$, ICON_ATTENTION
        Goto ExitSub
    End If
    ' ...
ExitSub:
End Sub

' Excel/VBA...
' --------------------------------------------------------------------
' Purpose:  Provide a simple way to replace a marker in global
'           constant message strings with context-relevant, current
'           information.
'           If the marker isn't found, function returns "".
'
' Inputs:   strMsg         - message string (*must* contain
'                            the marker)
'           strReplacement - string to replace the marker with
'
' Returns:  String.
'
' Updated:  10/28/94
' --------------------------------------------------------------------
' Copyright © 1994-96 PRIME Consulting Group, Inc.
' --------------------------------------------------------------------
Public Function fstrReplaceMarker(strMsg As String, strReplacement As String) As String
    ' ----- Declarations
    Const MARKER = "<<>>"
    Dim intPos As Integer
    ' ----- Main body
    intPos = InStr(strMsg, MARKER)
    If intPos = 0 Then
        fstrReplaceMarker = ""
        Exit Function
    End If
    strTemp = Left(strMsg, intPos - 1)
    strTemp = strTemp & strReplacement & _
        Right(strMsg, Len(strMsg) - (intPos + Len(MARKER) - 1))
    fstrReplaceMarker = strTemp
End Function

' WordBasic...
' --------------------------------------------------------------------
' Purpose:  Provide a simple way to replace a marker in global
'           constant message strings with context-relevant, current
'           information.
'           If the marker isn't found, function returns "".
'
' Inputs:   strMsg$         - message string (*must* contain
'                             the marker)
'           strReplacement$ - string to replace the marker with
'
' Returns:  String.
'
' Updated:  10/28/94
' --------------------------------------------------------------------
' Copyright © 1994-96 PRIME Consulting Group, Inc.
' --------------------------------------------------------------------
Function fstrReplaceMarker$(strMsg$, strReplacement$)
    ' ----- Initializations
    MARKER$ = "<<>>"
    ' ----- Main body
    intPos = InStr(strMsg$, MARKER$)
    If intPos = 0 Then
        fstrReplaceMarker$ = ""
        Goto EndFunction
    End If
    strTemp$ = Left$(strMsg$, intPos - 1)
    strTemp$ = strTemp$ + strReplacement$ + \
        Right$(strMsg$, Len(strMsg$) - (intPos + Len(MARKER$) - 1))
    fstrReplaceMarker$ = strTemp$
EndFunction:
End Function

The Naked PC Subscribe to our free electronic newsletter. Get the latest on all things PC, updates to PRIME Freeware page, and more. Type your email name and click Subscribe.

Return to Top