Click here to Skip to main content
13,667,095 members
Click here to Skip to main content
Add your own
alternative version


4 bookmarked
Posted 7 May 2015
Licenced BSD

Collision Proof Shared String Resources

, 12 Jul 2016
Rate this:
Please Sign up or sign in to vote.
Share Win32 string resources without the risk of conflicting resource IDs


This article presents a straightforward approach that solves two problems.

  1. Share Win32 string resources without fear of resource ID number conflicts.
  2. Use Microsoft Excel to efficiently manage read only Win32 string resources, without manually editing the resource script.

Even if the material seems inapplicable to your needs, it includes a number of useful tips that make the article worth at least a quick skim.

On 10 July 2016, I replaced the original sample with the vastly improved code and demonstrations that accompany a follow-up article, The Improved Resource String Generator in Action: Thou Shalt Eat Thine Own Dog Food, and Like It! When you finish reading this article, read that one, and follow its vastly simplified operating instructions.


For years, I have been frustrated by the record keeping required by the methods suggested for sharing Win32 string resources among applications. Why hasn't this record keeping been automated? One day, I had a specific application that was infinitely easier to code if all of its strings lived in a satellite DLL. As I was completing it, I realized that the same approach can be used to package any set of strings, so that you can have string libraries that are as easy to use as code libraries.

Since the project that gave rise to this requirement is a library that requires a custom string library for each application that employs it, I also needed a way to generate them efficiently. This gave rise to the Excel application, which uses worksheets to store the strings and their associated IDs and to drive a VBA macro that generates the read only resource script and its associated header, and integrates both into the default resource script of a Visual Studio project. When you revise a set of strings, the generator recognizes that the secondary script is already incorporated into the main script, and leaves the main resource script unchanged.

Using the Code

The demonstration package contains the following four directories.

  1. Excel contains the Microsoft Excel workbook, along with exported copies of its 3 VBA modules and a custom DLL that contains the custom date formatter that it uses. If you move the Excel workbook, take the DLL, too. Important: On Saturday, 12 March 2016, I replaced the Excel workbook with a new version that works as intended without unlocking the ResGen Parameters worksheet to edit or add a profile label. If you took the original download, please grab the new one; you will be much happier with the way the new Excel workbook behaves.
  2. SampleDLL contains a sample string resource DLL that was generated from the Excel workbook, along with a program that shows it in action.
  3. WWStandardErrorMessages is the current version of an active satellite DLL that I created for use in my own projects.
  4. _ResourceStringLib_Template is a project that you can use as the starting point for your own satellite DLLs.

Building the Code

This article incorporates the simplified CPPTimeZoneLab sample project that I included in the follow-up article cited above. Unlike the samples that accompanied the original version of the article, CPPTimeZoneLab is self contained. However, since it demonstrates a distinct use case, I left the original projects.

Building CPPTimeZoneLab

CPPTimeZoneLab  is little more than a "hello, world" project; indeed, I started by creating such a project, to which I added some read only resource strings in a .RC2 file, more thoroughly explained in The Improved Resource String Generator in Action: Thou Shalt Eat Thine Own Dog Food, and Like It!

Building Your Satellite DLLs

Make a copy of the _ResourceStringLib_Template directory, rename files as needed, and make it the destination of a new profile (discussed below). The Visual Studio 6 project (_ResourceStringLib_Template.dsp) and Visual Studio 2013 project (_ResourceStringLib_Template..vcxproj) are configured and ready to go. Use the Excel workbook to generate your strings and symbols, then open the project in Visual Studio 6 or Visual Studio 2013, and hit F7 to build a retail library. Since it contains only data, a Debug configuration is redundant, so I deleted it from both projects.

Using the Excel Application

The Excel workbook, Win32_ResGen.XLSM, contains an unsigned VBA project. Adjust the security settings on your copy of Microsoft Excel before you attempt to open it. If you have a code signing certificate, either self-signed or from a CA, you may want to use it to sign the VBA project.

The workbook opens with sheet ResGen Parameters as the active sheet. Cell D4 determines which of the profiles defined in the columns to its right drives the resource generator; its value is validated against a self-maintaining choice list. The cells that must be changed to generate a new library are shaded in light green. Everything else is either data required by the VBA routines or formulas, all of which are off limits in normal use, and are password protected. In case you need it, the password is CodeProject.

To the right of the Value column (column D), separated by one blank column, are three columns of values, each of which is the parameter profile for a different string library. Column GExample_String_Resource_Script, corresponds to SampleDLL. The name is entered in cell G5. There are two ways that you can add your own data set to this section of the worksheet.

  1. Insert a new column to the left of column G or H.
  2. Overwrite the values in any of the three columns.

Though they aren't hyperlinks, the values in rows 5 and 6 are range names, both of which have workbook scope, and were created by selecting cells G2 through G5 and F2 through F5, respectively, of worksheet Example, and defining a named range from the selection, using the top cell as the name, and the rest as the contents.

To generate a resource script and header, you populate a profile, use cell D4 to select it, and press Ctrl-Shift-G to execute the macro. For a modest library, it takes less than a second to do its work, after which it displays a message box similar to the one shown in Figure 1.

Figure 1: Message box displayed when resource script has been generated and integrated

Figure 1 is an example of the message box displayed when the VBA macro finishes its work.

Tip: If you click on the message box, then press Ctrl-C, the text and caption are captured onto the Windows clipboard. This trick works with any standard Message Box. I use it to capture error messages that contain text that I need to have exactly, such as status codes or file names.

Worksheet Standard Messages is the input source from which the strings stored in ibrary WWStandardErrorMessages.dll were generated and numbered. This worksheet contains another magic range, Standard_Error_Status_Codes, from which the status codes defined in WWStandardErrorMessages.H were generated. Cell F7 of worksheet ResGen Parameters names this range, which replaces token $$StandardErrorStatusCodes$$ in the resource header template.

Worksheet Read Only Resource Templates contains the templates for the read only resource script and its companion resource symbol header file. Token $$StandardErrorStatusCodes$$ is eliminated unless the cell in row 7 of the active profile is populated.

The Demonstration Program

The program that accompanies the demonstration library, SampleDLLDemo.exe, employs deliberately overlapped string ID numbers. The ID numbers of the strings embedded in the main program file are defined in their usual place, resource.H. The strings are numbered from 1 through 12. The ID numbers of the strings that live in the satellite DLL are defined in SampleDLL.H, and are numbered 1 through 3. Since SampleDLL.H defines a guard variable, SAMPLEDLL_DEFINED, it can be safely included in any number of other headers. The guard variable name is composed from a token, so that each library gets its own guard variable.

The magic that prevents collisions is that the DLL gets its own instance handle, and the LoadString routine uses both the instance handle and the string ID to uniquely identify a string. The prototype of the LoadString function is as follows:

int WINAPI LoadString(
  _In_opt_ HINSTANCE hInstance,
  _In_     UINT      uID,
  _Out_    LPTSTR    lpBuffer,
  _In_     int       nBufferMax

You call the LoadLibrayEx function, specifying LOAD_LIBRARY_AS_DATAFILE ()0x00000002) for dwFlags, to load a satellite library, then use the returned HMODULE as the value of the hInstance argument in your call to LoadString.

  _In_       LPCTSTR lpFileName,
  _Reserved_ HANDLE  hFile,
  _In_       DWORD   dwFlags

When you are finished using the library, you call FreeLibrary to unload it. Its signature is about as simple as it gets.

BOOL WINAPI FreeLibrary(
  _In_ HMODULE hModule

I usually wrap my call to FreeLibrary in a block IF statement, where the True block does whatever comes next, while the False block calls GetLastError, makes a report, and shut down.

Points of Interest

There are several interesting features of the VBA code in the Excel document and the demonstration program.

The Demonstration Program

The first place to look for interesting things is stdafx.h, where I put the macros, including four function-style macros that simplify the call to LoadLibraryEx to load the satellite DLL and  most of the calls to FB_LoadString, a custom routine that I use to load strings into one of five static buffers that belong to the DLL that exports it. FB_LoadString is exported by FixedStringBuffers.dll, which I discuss in another CodeProject article, Using Static Buffers to Improve Error Reporting Success. The goal of the macros is to hide the arguments that take the same values on all calls, so that the working code can focus on the values that change with each call. Although this technique hides details that you may need when working in the disassembly view of a debugger, in normal use, I think of them as black boxes, just like stock macros, such as MAKELANGID.

Three features work together to simplify loading a satellite DLL from the directory from which the program loaded.

  1. PROC_AllocFNBuff, a macro defined in ProcessInfo.H, allocates a buffer of MAX_PATH characters (TCHARs) and a pointer to it, initializing the pointer with the address of the buffer.
  2. PROC_GetModuleDirectoryName, also defined in ProcessInfo.H, fills the buffer with the name of the directory from which the program that started the current process loaded. Using the directory of the first EXE to load into a process means that a subsidiary routine in a DLL that may have loaded from elsewhere can find the directory from which that original EXE was loaded. PROC_GetModuleDirectoryName returns the address of the null character that terminates the path string, which goes into lpModulePath, and is fed to LoadString.
  3. LoadString starts writing at lpModulePath, overwriting the null character with the first character of the name of the satellite DLL, and appends a new null character after the last character, so that the resulting string is a complete path name, ready to feed to LoadLibraryEx.

The entire main module is blessedly short.

// SampleDLLDemo.cpp : Defines the entry point for the console application.

#include "stdafx.h"
#include "resource.H"
#include "..\SampleDLL.H"

int ReportEr5rorAndQuit ( FB_RES_STRING_ID puintHintID ) ;

PROC_AllocFNBuff ( szPgmDir , lpPgmDir ) ;

//    ----------------------------------------------------------------------------
//    FBGetStringLocalDflt is a macro, defined in stdafx.h, that hides the invariant
//    arguments required to ask library routine FB_LoadString to load a specified
//    string resource into its first static buffer, whose address is its return
//    value.
//    ----------------------------------------------------------------------------

int _tmain ( int argc , _TCHAR* argv [ ] )
    _tprintf ( FBGetStringLocalDflt ( IDS_MSG_BOJ ) ,
               ProgramIDFromArgV ( argv [ ARGV_PROGRAM_NAME_P6C ] ) ) ;

    if ( LPTSTR lpModulePath = PROC_GetModuleDirectoryName ( NULL , lpPgmDir ) )                                                        
        _tprintf ( FBGetStringLocalDflt ( IDS_MSG_SATELLITE_PATH ) ,
                   lpPgmDir ) ;

        if ( LoadString ( FB_LOOK_IN_THIS_EXE ,
                          IDS_SATELLITE_DLLNAME ,
                          lpModulePath ,
                          MAX_PATH - ByteOffsetToTCHARsP6C ( lpPgmDir , lpModulePath ) ) )
            _tprintf ( FBGetStringLocalDflt ( IDS_MSG_SATELLITE_FQFN ) ,
                       lpPgmDir ) ;

            if ( HMODULE hSatelliteDll = DllLoadAsData_WW ( lpPgmDir ) )
                for ( unsigned int uintNId = IDS_EXAMPLE_1 ;
                                   uintNId <= IDS_EXAMPLE_3;
                                   uintNId++ )
                    _tprintf ( FBGetStringLocalDflt ( IDS_MSG_STRING_FROM_SATELLITE ) ,
                               uintNId ,
                               FBGetStringLocalAlt1 ( LabelIDFromValueID ( uintNId ) ) ,
                               FB_LoadString ( hSatelliteDll ,
                                               uintNId ,
                                               FB_ALTERNATE_BUFFER_2 ,
                                               FB_HIDE_LENGTH ) ) ;
                }    // for ( unsigned int uintNId = IDS_EXAMPLE_1 ; uintNId <= IDS_EXAMPLE_3; uintNId++ )

                FreeLibrary ( hSatelliteDll );
            }    // TRUE (expected outcome) block, 
                 // if ( HMODULE hSatelliteDll = DllLoadAsData_WW ( lpPgmDir ) )
                return ReportEr5rorAndQuit ( IDS_ERR_HINT_LOADLIBRARYEX );
            }    // FALSE (UNexpected outcome) block, 
                 // if ( HMODULE hSatelliteDll = DllLoadAsData_WW ( lpPgmDir ) )
        }    // TRUE (expected outcome) block, if ( LoadString 
             // ( FB_LOOK_IN_THIS_EXE , IDS_SATELLITE_DLLNAME , lpModulePath , 
             // MAX_PATH - ByteOffsetToTCHARsP6C ( lpPgmDir , lpModulePath ) ) )
            return ReportEr5rorAndQuit ( IDS_ERR_HINT_LOADSTRING ) ;
        }    // FALSE (UNexpected outcome) block, if ( LoadString 
             // ( FB_LOOK_IN_THIS_EXE , IDS_SATELLITE_DLLNAME , lpModulePath , 
             // MAX_PATH - ByteOffsetToTCHARsP6C ( lpPgmDir , lpModulePath ) ) )
    }    // TRUE (expected outcome) block, 
         // if ( LPTSTR lpModulePath = PROC_GetModuleDirectoryName ( NULL , lpPgmDir ) )
        return ReportEr5rorAndQuit ( IDS_ERR_HINT_GETMODDIRNAME ) ;
    }    // FALSE (UNexpected outcome) block, 
         // if ( LPTSTR lpModulePath = PROC_GetModuleDirectoryName ( NULL , lpPgmDir ) )

    _tprintf ( FBGetStringLocalDflt ( IDS_MSG_EOJ ) ) ;
    return 0 ;
}    // int _tmain

int ReportEr5rorAndQuit ( FB_RES_STRING_ID puintHintID )
    DWORD dwLastError = GetLastError ( );
    _tprintf ( FB_FormatMessage ( FBGetStringLocalDflt ( puintHintID ) ,
               dwLastError ,
               SCF2_HEXADECIMAL ) ) ;
    return dwLastError;
}    // int ReportEr5rorAndQuit

The other routine in the demonstration program, ProgramIDFromArgV, defined in ProgramIDFromArgV.CPP, is also noteworthy for the way it extracts name of the program from argV [0] without recourse to the Lightweight Shell API.


    File Name:            ProgramIDFromArgV.C

    File Synopsis:        This file defines function ProgramIDFromArgV.

    Function Synopsis:    ProgramIDFromArgV provides a portable mechanism to 
                          identify the name of a program, for display on its
                          console. Hence, it depends exclusively on CRT routines.

    Author:               David A. Gray

    Revision History

    Date       By  Synopsis
    ---------- --- -------------------------------------------------------------
    2015/01/04 DAG Function created and tested to meet an immediate need.
    2015/05/06 DAG Replace all char entities with TCHAR entities, so that this
                   routine works in either ANSI or Unicode.

#include "stdafx.h"

TCHAR chrArg0IsNull  [ ] = TEXT 
( "ERROR: The first string in the argument list passed into routine ProgramIDFromArgV 
is a null reference.\n" ) ;
TCHAR chrArg0IsBlank [ ] = TEXT 
( "ERROR: The first string in the argument list passed into routine ProgramIDFromArgV 
is the empty string.\n" ) ;
TCHAR chrPathDlm     [ ] = TEXT ( "\\" ) ;

TCHAR * lpchrArg0IsNull  = ( TCHAR * ) &chrArg0IsNull ;
TCHAR * lpchrArg0IsBlank = ( TCHAR * ) &chrArg0IsBlank ;
TCHAR * lpchrPathDlm     = ( TCHAR * ) &chrPathDlm ;

TCHAR * ProgramIDFromArgV ( const TCHAR * ppgmptr )
    TCHAR *    lpLastPathDelimiter = NULL ;
    TCHAR *    lpLastDelimiterScan = NULL ;

    if ( ppgmptr )
        if ( _tcslen ( ppgmptr ) )
            lpLastDelimiterScan = ( TCHAR * ) ppgmptr ;
            lpLastPathDelimiter = ( TCHAR * ) ppgmptr ;

                lpLastDelimiterScan = _tcsstr ( lpLastDelimiterScan ,
                                                lpchrPathDlm ) ;

                if ( lpLastDelimiterScan )
                    lpLastPathDelimiter    = lpLastDelimiterScan ;
                    lpLastDelimiterScan++ ;
                }    // TRUE block, if ( lpLastDelimiterScan )
                    lpLastPathDelimiter++ ;
                }    // FALSE block, if ( lpLastDelimiterScan )
            } while ( lpLastDelimiterScan ) ;
            return lpLastPathDelimiter ;
        }    // TRUE (expected outcome) block, if ( strlen ( ppgmptr ) )
            return lpchrArg0IsBlank ;
        }    // FALSE (UNexpected outcome) block, if ( strlen ( ppgmptr ) )
    }    // TRUE (expected outcome) block, if ( ppgmptr )
        return lpchrArg0IsNull ;
    }    // FALSE (UNexpected outcome) if ( ppgmptr )
}    // LPTSTR ProgramIDFromArgV

I just noticed that the flower box still says it's a .C file, but I'll leave it, since the oversight is harmless to the existing implementation, and reminds readers that it compiles equally well as straight C. The ANSI implementation, in another project, is straight ANSI C. I converted it to C++ for this application, so that it can use the precompiled headers generated from stdafx.h.

The VBA Code

There are three modules in the VBA project.

  1. m_RCImport.BAS imports a read-only resource script and its symbols into the main resource script of an application. Since it is called from another module, function ImportReadOnlyResources is marked public.
  2. m_ResGen.BAS exposes a single subroutine, ResGen, which is the routine that runs when hot key combination CTRL-SHIFT-G is activated when the Excel document has the focus.
  3. m_WorksheetFunctions.BAS exposes two functions, ActiveDocumentDirectoryName and ParentDirectoryName, which make the name of the directory containing the active document and the parent of any directory available to worksheets.

Function ImportReadOnlyResources in m_RCImport.BAS implements a simple Finte State Machine, driven by a custom enumeration, enmState, an instance of the State enumeration, which is defined as follows:

Enum State
End Enum    ' Type State

Private function LoadFileIntoString uses the Scripting.FileSystemObject to read the main resource script from the file named in pstrMainRCFQFN,, the first of three arguments to function ImportReadOnlyResources, into one long string, which is subsequently split into an array of lines. I put it into a function so that its error handler can address issues specific to file I/O operations in a way that provides a more precisely worded error message. After reporting an error, it returns the empty string to signal the calling routine that it failed.

Since the length of a Basic String is stored in a four-byte integer located just in front of the string, itself, the simplest test for the empty string is to evaluate its length. If the length is greater than zero, two calls to another private function, CreateTextInclude, transforms the header and script filenames, which are passed into LoadFileIntoString through its second and third arguments, into valid #include directives. This one-statement function hides the somewhat obtuse formatting required to construct it. (This way, if I got it wrong, I had only one statement to fix.)

Private Function CreateTextInclude(ByRef pstrIncludeFN As String) As String

    CreateTextInclude = Space$(4) & Chr$(34) & "#include " _
                        & Chr$(34) & Chr$(34) _
                        & pstrIncludeFN _
                        & Chr$(34) & Chr$(34) _
                        & "\r\n" & Chr$(34) _

End Function

Before going any further, the resource script string is scanned for the presence of the two #include directives. If both directives are already present, the routine quits without taking further action, leaving the resource script as is. The test consists of the following compound IF statement.

If InStr(strMainData, strHeaderTextInclude) = INSTR_NOT_FOUND And InStr
(strMainData, strScriptTextInclude) = INSTR_NOT_FOUND Then

INSTR_NOT_FOUND is defined in module m_ResGen.BAS as a public constant long integer with a value of zero.

The existing resource script is split into an array of lines by the following statement, which replaces every CR/LF pair with a LF character, which becomes the delimiter for the split. At the cost of an embedded Replace, with its associated memory consumption, this method reduces splitting the contents of a standard Windows text file into an array of lines to a single statement..

Dim avarLines As Variant: avarLines = Split(Replace(strMainData, _
                                                    vbCrLf, _
                                                    vbLf), _

The array returned by the statement shown above contains one element for each line. Each line is processed by a conventonal For loop. Each iteration executes the following Select Case block that implements the state machine. 

Select Case enmState
    Case FindingTextinclude2Marker
        strNewScript = strNewScript & strCurrLine & vbCrLf

        If strCurrLine = TEXTINCLUDE_2_MARKER Then
            enmState = FindingTextinclude2BlockEnd
        End If  ' If strCurrLine = TEXTINCLUDE_2_MARKER Then

    Case FindingTextinclude2BlockEnd
        If Len(strCurrLine) > LENGTH_OF_EMPTY_STRING Then
            If strCurrLine = AFX_RC_BOUNDARY Then
                strNewScript = strNewScript _
                               & "#include " & Chr$(34) & pstrHeaderFN & Chr$(34) _
                               & vbCrLf _
                               & vbCrLf _
                               & strCurrLine _
                               & vbCrLf
                enmState = FindingTextInclude2Begin
                strNewScript = strNewScript & strCurrLine & vbCrLf
            End If  ' If strCurrLine = AFX_RC_BOUNDARY Then
        End If  ' If Len(strCurrLine) > LENGTH_OF_EMPTY_STRING Then

    Case FindingTextInclude2Begin
        strNewScript = strNewScript & strCurrLine & vbCrLf

        If strCurrLine = DLM_TEXTINCLUDE_2 Then
            enmState = FindingTextinclude2End
        End If  ' If strCurrLine = DLM_TEXTINCLUDE_2 Then

    Case FindingTextinclude2End
        If strCurrLine = AFX_EMPTY_LINE Then
            strNewScript = strNewScript _
                           & strHeaderTextInclude & vbCrLf _
                           & strCurrLine & vbCrLf
            enmState = FindingTextInclude3Begin
            strNewScript = strNewScript & strCurrLine & vbCrLf
        End If  ' If strCurrLine = AFX_EMPTY_LINE Then

    Case FindingTextInclude3Begin
        strNewScript = strNewScript & strCurrLine & vbCrLf

        If strCurrLine = DLM_TEXTINCLUDE_3 Then
            enmState = FindingTextInclude3End
        End If  ' If strCurrLine = DLM_TEXTINCLUDE_2 Then

    Case FindingTextInclude3End
        If strCurrLine <> AFX_NEWLINE Then
            If strCurrLine = AFX_EMPTY_LINE Then
                strNewScript = strNewScript _
                               & strScriptTextInclude & vbCrLf _
                               & strCurrLine & vbCrLf
                enmState = FindingTextinclude3Marker
                strNewScript = strNewScript & strCurrLine & vbCrLf
            End If  ' If strCurrLine = AFX_EMPTY_LINE Then
        End If  ' If strCurrLine <> AFX_NEWLINE Then

    Case FindingTextinclude3Marker
        strNewScript = strNewScript & strCurrLine & vbCrLf

        If strCurrLine = TEXTINCLUDE_3_MARKER Then
            enmState = FindingTextinclude3BlockEnd
        End If  ' If strCurrLine = TEXTINCLUDE_2_MARKER Then

    Case FindingTextinclude3BlockEnd
        If Len(strCurrLine) > LENGTH_OF_EMPTY_STRING Then
            If strCurrLine = AFX_RC_BOUNDARY Then
                strNewScript = strNewScript _
                               & "#include " & Chr$(34) & pstrScriptFN & Chr$(34) _
                               & vbCrLf _
                               & vbCrLf _
                               & strCurrLine _
                               & vbCrLf
                enmState = FindingEndOfFile
                strNewScript = strNewScript & strCurrLine & vbCrLf
            End If  ' If strCurrLine = AFX_RC_BOUNDARY Then
        End If  ' If Len(strCurrLine) > LENGTH_OF_EMPTY_STRING Then

    Case FindingEndOfFile
        strNewScript = strNewScript & strCurrLine & vbCrLf
End Select  ' Select Case enmState

Before the main resource script file is updated, a backup is created by calling another private function, MakeBackupFile, which constructs a name for the backup file from the input filename, then calls FileCopy, a built-in VBA subroutine, to make the backup file.

Many of the more interesting things that happen in module m_ResGen.BAS happen outside of its primary public routine, ResGen, which is a straightforward nested IF block, guarded by a generic error handling block that displays a message box, then shuts down the macro.

Next comes the first of three calls to function MakeFQFN, which employs Lightweight Shell API routine PathCombine to construct a fully qualified file name from the directory named in pstrPath and the file named in pstrFileName.

Public Function MakeFQFN(pstrPath As String, _
                         pstrFileName As String) _
                As String
    Dim strBuff As String * MAX_PATH
    MakeFQFN = Ptr2StrU(PathCombine(StrPtr(strBuff), _
                                    StrPtr(pstrPath), _

End Function

Just as MakeFQFN is a wrapper for PathCombinePtr2StrU wraps CopyMemory (which is really RtlMoveMemory).

Function LoadTemplateFromRange returns the template for a resource script or symbol header from one of two named ranges. The process is straightforward; read each cell in the range, and append it to a string, followed by a CR/LF pair, except for the last line, which is unterminated. Before attempting to use it as a range name, function RangeNameExists is called to check the name against the ThisWorkbook.Names property. 

For the most part, LoadTemplateFromRange is a straightforward For loop, which I chose over a For Each loop because I needed to know when I reached the last row, so that I could omit the final line break. Just before the main loop is entered, rngTemplate.Rows.Count is copied into local variable lngLastRow so that it doesn't have to be queried on each iteration.

Private Function LoadTemplateFromRange(pstrRangeName As String, _
                                       Optional ByVal pfLastNewlineDisp = LAST_LINE_DLM_KEEP) _
                 As String

    Const THE_ONE_AND_ONLY_COLUMN As Integer = 1

    LoadTemplateFromRange = vbNullString

    On Error GoTo LoadTemplateFromRange_Err

    If RangeNameExists(pstrRangeName) Then
        Dim rngTemplate As Range: Set rngTemplate = ThisWorkbook.Names(pstrRangeName).RefersToRange
        Dim strWork As String: strWork = vbNullString

        If rngTemplate.Columns.Count = THE_ONE_AND_ONLY_COLUMN Then
            Dim lngLastRow As Long: lngLastRow = rngTemplate.Rows.Count
            Dim lngCurrRow As Long

            For lngCurrRow = RANGE_FIRST_ROW To lngLastRow
                Dim strLine As String: strLine = CStr(rngTemplate.Cells(lngCurrRow, _

                If lngCurrRow < lngLastRow Then
                    strWork = strWork & strLine & vbCrLf
                    If pfLastNewlineDisp = LAST_LINE_DLM_KEEP Then
                        strWork = strWork & strLine & vbCrLf
                        strWork = strWork & strLine
                    End If  ' If pfLastNewlineDisp = LAST_LINE_DLM_KEEP Then
                End If  ' If lngCurrRow < lngLastRow Then
            Next    ' For lngCurrRow = RANGE_FIRST_ROW To lngLastRow

            LoadTemplateFromRange = strWork
        Else    ' If rngTemplate.Columns.Count = THE_ONE_AND_ONLY_COLUMN Then
            MsgBox "Worksheet Format Error: Template range " _
                        & pstrRangeName _
                        & " contains too many columns." _
                        & vbLf & _
                        "Only " _
                        & THE_ONE_AND_ONLY_COLUMN _
                        & " column of cells is permitted.", _
                   vbApplicationModal Or vbExclamation, _
            Set rngTemplate = Nothing
        End If  ' If rngTemplate.Columns.Count = THE_ONE_AND_ONLY_COLUMN Then
        If Len(pstrRangeName) > LENGTH_OF_EMPTY_STRING Then
            MsgBox pstrRangeName & " is invalid as a range name in " & ActiveWorkbook.FullName, _
                   vbApplicationModal Or vbExclamation, _
        End If  ' If Len(pstrRangeName) > LENGTH_OF_EMPTY_STRING Then

        LoadTemplateFromRange = vbNullString    ' Regardless, return the empty string.
    End If  ' If RangeNameExists(pstrRangeName) Then


    Exit Function


    MsgBox VBA_RT_ERRMSG_PREFIX & Err.Number & " - " & Err.Description, _
           vbApplicationModal Or vbExclamation, _
    LoadTemplateFromRange = vbNullString
    Resume LoadTemplateFromRange_End

End Function

Since it is a utility function that will eventually find its way into a library, function RangeNameExists is marked public. Since it has no need to count things, this routine uses a For Each loop to enumerate the members of the ThisWorkbook.Names collection.

Public Function RangeNameExists(ByRef pstrName As String) As Boolean

    If Len(pstrName) > LENGTH_OF_EMPTY_STRING Then
        Dim fFound As Boolean: fFound = False
        Dim wbAllNames As Names: Set wbAllNames = ThisWorkbook.Names
        Dim wbCurrName As Name

        For Each wbCurrName In wbAllNames
            If wbCurrName.Name = pstrName Then
                fFound = True
                Exit For    ' Done!
            End If  ' If wbCurrName.Name = pstrName Then
        Next    ' For Each wbCurrName In wbAllNames

        RangeNameExists = fFound
        RangeNameExists = False
    End If  ' If Len(pstrName) > LENGTH_OF_EMPTY_STRING Then

End Function

The next interesting private functions encountered is LookupParameterValue, which looks up paramters in the Substitution_Token_Data range of the ResGen Parameters worksheet.

Public Function LookupParameterValue(ByRef pstrToken As String, _
                                     ByRef putpColumns As utpParameterColumns) _
                As String

    On Error GoTo LookupParameterValue_Err
    LookupParameterValue = vbNullString

    Dim rngParams As Range: Set rngParams = ActiveWorkbook.Names(RN_RESGEN_PARAMETER_TABLE).RefersToRange

    If rngParams.Columns.Count >= putpColumns.ColLiteral Then
        Dim lngCurrRow As Long: lngCurrRow = RANGE_FIRST_ROW
        Dim lngLastRow As Long: lngLastRow = rngParams.Rows.Count
        Dim fDone As Boolean: fDone = False

        Do  ' Until fDone
            If pstrToken = CStr(rngParams.Cells(lngCurrRow, putpColumns.ColValue).Value) Then
                LookupParameterValue = CStr(rngParams.Cells(lngCurrRow, putpColumns.ColLiteral).Value)
                fDone = True
                lngCurrRow = lngCurrRow + ARRAY_NEXT_ELEMENT
                fDone = lngCurrRow > lngLastRow
            End If  ' If pstrToken = CStr(rngParams.Cells(lngCurrRow, putpColumns.ColValue).Value) Then
        Loop Until fDone
        MsgBox "Error report from VBA function LookupParameterValue, " _
                    & "on behalf of workbook Macro ResGen:" & vbLf & vbLf _
                    & "Named worksheet range " & rngParams.Name _
                    & ", located at " & rngParams.AddressLocal _
                    & " in worksheet " & rngParams.Worksheet.Name & "." & vbLf _
                    & "The range contains too few columns." & vbLf _
                    & "It contains " & rngParams.Columns.Count _
                    & " columns; it must contain at least " _
                    & putpColumns.ColValue & " columns.", _
                vbExclamation, _
    End If  ' If rngParams.Columns.Count >= putpColumns.ColLiteral Then


    Exit Function


    MsgBox "Error report from VBA function LookupParameterValue, " _
                & "on behalf of workbook Macro ResGen:" & vbLf & vbLf _
                & "Error " & Err.Number & " - " & Err.Description, _
            vbExclamation, _
    Resume LookupParameterValue_End

End Function

The most significant features of this routine are that rngParams.Rows.Count is copied into local variable lngLastRow so that it doesn't have to be queried on eacfh iteration, and that the work is done by a Do loop that executes until Boolean variable fDone becomes True. Since the loop must execute at least once, testing fDone is deferred until the bottom of the loop.

There are a number of other interesting functions, which are left as an exercise for curious readers.

Worksheet Functions Worth Reusing

The first of the two worksheet functions, ActiveDocumentDirectoryName, is trivial, because all it does is expose a property of the ActiveWorkbook object in the Excel Object Model.

Public Function ActiveDocumentDirectoryName( _
                Optional pFAppendBackslash As Boolean = True) _
                As String

    '   ------------------------------------------------------------------------
    '   Abstract:   Return the name of the directory from which the active
    '               document loaded.
    '   In:         pFAppendBackslash       = TRUE (default) to have a backslash
    '                                         appended to the returned name, or
    '                                         FALSE to omit it.
    '   Out:        Fully qualified name of parent of pstrChildDirectoryName.
    '   ------------------------------------------------------------------------

    If pFAppendBackslash Then
        ActiveDocumentDirectoryName = ActiveWorkbook.Path & PATH_DELIMITER_WINDOWS
        ActiveDocumentDirectoryName = ActiveWorkbook.Path
    End If
End Function

​The second function, ParentDirectoryName, is almost as straightforward; it reads properties from a Scripting.FileSystemObject.

<span id="cke_bm_669E" style="display: none;"> </span>Public Function ParentDirectoryName( _
                ByRef pstrChildDirectoryName As String, _
                Optional pFAppendBackslash As Boolean = True) _
                As String

    '   ------------------------------------------------------------------------
    '   Abstract:   Return the name of the parent of a specified directory.
    '   In:         pstrChildDirectoryName  = Fully qualified directory nme from
    '                                         which to extract name of parent.
    '                                         Path pstrChildDirectoryName must
    '                                         exist.
    '               pFAppendBackslash       = TRUE (default) to have a backslash
    '                                         appended to the returned name, or
    '                                         FALSE to omit it.
    '   Out:        Fully qualified name of parent of pstrChildDirectoryName.
    '   ------------------------------------------------------------------------

    If Len(pstrChildDirectoryName) > LENGTH_OF_EMPTY_STRING Then
        Dim fso As FileSystemObject: Set fso = New FileSystemObject

        If fso.FolderExists(pstrChildDirectoryName) Then
            If pFAppendBackslash Then
                ParentDirectoryName = fso.GetParentFolderName(pstrChildDirectoryName) _
                & PATH_DELIMITER_WINDOWS
                ParentDirectoryName = fso.GetParentFolderName(pstrChildDirectoryName)
            End If  ' If pFAppendBackslash Then
            ParentDirectoryName = "PathNotFound"
        End If  ' If fso.FolderExists(pstrChildDirectoryName) Then
        ParentDirectoryName = "Unspecified"
    End If  ' If Len(pstrChildDirectoryName) > LENGTH_OF_EMPTY_STRING Then

End Function

Please feel free to import these routines into your own workbooks.


  • Tuesday, 12 July 2016, updated the workbook with a more robust resource generator macro that gracefully handles a resource script in which the TEXTINCLUDE marker lines have been trimmed of their trailing space.
  • Sunday, 10 July 2016 - Incorporate the vastly improved sample archive that accompanies The Improved Resource String Generator in Action: Thou Shalt Eat Thine Own Dog Food, and Like It!
  • Saturday, 09 May 2015 - Make the demo project archive visible.
  • Friday, 08 May 2015 - Fixed broken image tag
  • Thursday, 07 May 2015 - Article completed


This article, along with any associated source code and files, is licensed under The BSD License


About the Author

David A. Gray
Software Developer (Senior)
United States United States
I deliver robust, clean, adaptable, future-ready applications that are properly documented for users and maintainers. I have deep knowledge in multiple technologies and broad familiarity with computer and software technologies of yesterday, today, and tomorrow.

While it isn't perceived as sexy, my focus has always been the back end of the application stack, where data arrives from a multitude of sources, and is converted into reports that express my interpretation of The Fundamental Principle of Tabular Reporting, and are the most visible aspect of the system to senior executives who approve the projects and sign the checks.

While I can design a front end, I prefer to work at the back end, getting data into the system from outside sources, such as other computers, electronic sensors, and so forth, and getting it out of the system, as reports to IDENTIFY and SOLVE problems.

When presented with a problem, I focus on identifying and solving the root problem for the long term.

Specialties: Design: Relational data base design, focusing on reporting; organization and presentation of large document collections such as MSDS libraries

Development: Powerful, imaginative utility programs and scripts for automated systems management and maintenance

Industries: Property management, Employee Health and Safety, Services

Languages: C#, C++, C, VBA, Visual Basic, Perl, WinBatch, SQL, XML, HTML, Javascript

Outside Interests: Great music (mostly, but by no means limited to, classical), viewing and photographing sunsets and clouds, traveling by car on small country roads, attending museum exhibits (fine art, history, science, technology), long walks, especially where there is little or no motor traffic

You may also be interested in...


Comments and Discussions

QuestionDownload? Pin
pwasser7-May-15 22:28
memberpwasser7-May-15 22:28 
AnswerRe: Download? Pin
David A. Gray9-May-15 16:56
memberDavid A. Gray9-May-15 16:56 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Cookies | Terms of Use | Mobile
Web01-2016 | 2.8.180810.1 | Last Updated 13 Jul 2016
Article Copyright 2015 by David A. Gray
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid