Introduction
This article presents a straightforward approach that solves two problems.
- Share
Win32 string resources without fear of resource ID number conflicts.
- 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.
Background
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.
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.SampleDLL contains a sample string resource DLL that was generated from the Excel workbook, along with a program that shows it in action.WWStandardErrorMessages is the current version of an active satellite DLL that I created for use in my own projects._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 G, Example_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.
- Insert a new column to the left of column G or H.
- 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 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.
HMODULE WINAPI LoadLibraryEx(
_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.
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.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.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.
#include "stdafx.h"
#include "resource.H"
#include "..\SampleDLL.H"
int ReportEr5rorAndQuit ( FB_RES_STRING_ID puintHintID ) ;
PROC_AllocFNBuff ( szPgmDir , lpPgmDir ) ;
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 ) ) ;
}
FreeLibrary ( hSatelliteDll );
} else
{
return ReportEr5rorAndQuit ( IDS_ERR_HINT_LOADLIBRARYEX );
} } else
{
return ReportEr5rorAndQuit ( IDS_ERR_HINT_LOADSTRING ) ;
} } else
{
return ReportEr5rorAndQuit ( IDS_ERR_HINT_GETMODDIRNAME ) ;
}
_tprintf ( FBGetStringLocalDflt ( IDS_MSG_EOJ ) ) ;
return 0 ;
}
int ReportEr5rorAndQuit ( FB_RES_STRING_ID puintHintID )
{
DWORD dwLastError = GetLastError ( );
_tprintf ( FB_FormatMessage ( FBGetStringLocalDflt ( puintHintID ) ,
dwLastError ,
SCF2_HEXADECIMAL ) ) ;
return dwLastError;
}
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.
#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 ;
do
{
lpLastDelimiterScan = _tcsstr ( lpLastDelimiterScan ,
lpchrPathDlm ) ;
if ( lpLastDelimiterScan )
{
lpLastPathDelimiter = lpLastDelimiterScan ;
lpLastDelimiterScan++ ;
} // TRUE block, if ( lpLastDelimiterScan )
else
{
lpLastPathDelimiter++ ;
} // FALSE block, if ( lpLastDelimiterScan )
} while ( lpLastDelimiterScan ) ;
return lpLastPathDelimiter ;
} // TRUE (expected outcome) block, if ( strlen ( ppgmptr ) )
else
{
return lpchrArg0IsBlank ;
} // FALSE (UNexpected outcome) block, if ( strlen ( ppgmptr ) )
} // TRUE (expected outcome) block, if ( ppgmptr )
else
{
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.
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.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.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
FindingTextinclude2Marker
FindingTextinclude2BlockEnd
FindingTextInclude2Begin
FindingTextinclude2End
FindingTextInclude3Begin
FindingTextInclude3End
FindingTextinclude3Marker
FindingTextinclude3BlockEnd
FindingEndOfFile
End Enum
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), _
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
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
Else
strNewScript = strNewScript & strCurrLine & vbCrLf
End If End If
Case FindingTextInclude2Begin
strNewScript = strNewScript & strCurrLine & vbCrLf
If strCurrLine = DLM_TEXTINCLUDE_2 Then
enmState = FindingTextinclude2End
End If
Case FindingTextinclude2End
If strCurrLine = AFX_EMPTY_LINE Then
strNewScript = strNewScript _
& strHeaderTextInclude & vbCrLf _
& strCurrLine & vbCrLf
enmState = FindingTextInclude3Begin
Else
strNewScript = strNewScript & strCurrLine & vbCrLf
End If
Case FindingTextInclude3Begin
strNewScript = strNewScript & strCurrLine & vbCrLf
If strCurrLine = DLM_TEXTINCLUDE_3 Then
enmState = FindingTextInclude3End
End If
Case FindingTextInclude3End
If strCurrLine <> AFX_NEWLINE Then
If strCurrLine = AFX_EMPTY_LINE Then
strNewScript = strNewScript _
& strScriptTextInclude & vbCrLf _
& strCurrLine & vbCrLf
enmState = FindingTextinclude3Marker
Else
strNewScript = strNewScript & strCurrLine & vbCrLf
End If End If
Case FindingTextinclude3Marker
strNewScript = strNewScript & strCurrLine & vbCrLf
If strCurrLine = TEXTINCLUDE_3_MARKER Then
enmState = FindingTextinclude3BlockEnd
End If
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
Else
strNewScript = strNewScript & strCurrLine & vbCrLf
End If End If
Case FindingEndOfFile
strNewScript = strNewScript & strCurrLine & vbCrLf
End Select
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), _
StrPtr(pstrFileName)))
End Function
Just as MakeFQFN is a wrapper for PathCombine, Ptr2StrU 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, _
THE_ONE_AND_ONLY_COLUMN).Value)
If lngCurrRow < lngLastRow Then
strWork = strWork & strLine & vbCrLf
Else
If pfLastNewlineDisp = LAST_LINE_DLM_KEEP Then
strWork = strWork & strLine & vbCrLf
Else
strWork = strWork & strLine
End If End If Next
LoadTemplateFromRange = strWork
Else 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, _
ThisWorkbook.Name
Set rngTemplate = Nothing
End If Else
If Len(pstrRangeName) > LENGTH_OF_EMPTY_STRING Then
MsgBox pstrRangeName & " is invalid as a range name in " & ActiveWorkbook.FullName, _
vbApplicationModal Or vbExclamation, _
ActiveWorkbook.Name
End If
LoadTemplateFromRange = vbNullString End If
LoadTemplateFromRange_End:
Exit Function
LoadTemplateFromRange_Err:
MsgBox VBA_RT_ERRMSG_PREFIX & Err.Number & " - " & Err.Description, _
vbApplicationModal Or vbExclamation, _
ActiveWorkbook.Name
Err.Clear
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 End If Next
RangeNameExists = fFound
Else
RangeNameExists = False
End If
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 If pstrToken = CStr(rngParams.Cells(lngCurrRow, putpColumns.ColValue).Value) Then
LookupParameterValue = CStr(rngParams.Cells(lngCurrRow, putpColumns.ColLiteral).Value)
fDone = True
Else
lngCurrRow = lngCurrRow + ARRAY_NEXT_ELEMENT
fDone = lngCurrRow > lngLastRow
End If Loop Until fDone
Else
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, _
ThisWorkbook.Name
End If
LookupParameterValue_End:
Exit Function
LookupParameterValue_Err:
MsgBox "Error report from VBA function LookupParameterValue, " _
& "on behalf of workbook Macro ResGen:" & vbLf & vbLf _
& "Error " & Err.Number & " - " & Err.Description, _
vbExclamation, _
ThisWorkbook.Name
Err.Clear
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
If pFAppendBackslash Then
ActiveDocumentDirectoryName = ActiveWorkbook.Path & PATH_DELIMITER_WINDOWS
Else
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>
Please feel free to import these routines into your own workbooks.
History
- 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
