Click here to Skip to main content
15,867,568 members
Articles / Web Development / HTML

The Improved Resource String Generator in Action: Thou Shalt Eat Thine Own Dog Food, and Like It!

Rate me:
Please Sign up or sign in to vote.
4.92/5 (15 votes)
12 Jul 2016BSD30 min read 22.8K   201   10   9
The Excel powered resource generator, version 2, is vastly improved and better documented.

Introduction

I was reminded today why it is essential for developers either to use their own creations (Eat their own dog food.) or work closely with dedicated testers who do so. It’s all too easy for a developer working independently to miss important details that make their creations harder to use than they should be, or outright defective. By the end of the project, I had a really convincing reminder of how much work it takes to anticipate everything that can sabotage even the simplest sounding task, and how many steps get done without giving them a second thought.

Background

About a year ago (March 2015, according to the revision history in the VBA modules), I created Win32_ResGen.XLSM, a macro enabled Microsoft Excel 2010 workbook to organize strings, define Win32 string resources, and turn them into resource compiler scripts. Though I have used it a number of times since then, it had been several months since I last did so. Moreover, the application at had differed in three key ways from the task that motivated its creation.

  1. The task at hand is to move a set of string literals that I defined as conventional preprocessor macros into a standard Windows resource script by way of a read only stringtable resource.
  2. The project into which the resources were to be incorporated had neither a stringtable nor a resource script.
  3. Since I upgraded the project from Visual Studio 6 to Visual Studio 2013, the resource script would be generated from scratch by the Visual Studio 2013 resource editors.

This combination of circumstances brought to my attention several shortcomings in the explanation of the workbook in Collision Proof Shared String Resources, in which it plays a major supporting role, and a deficiency in the implementation of the resource script parser that renders it unusable with scripts generated by current versions of the Microsoft resource editors. Until today, I had used it exclusively to modify scripts that were generated by the editors that shipped with Microsoft Visual Studio 6. I was unaware that the Visual Studio team made breaking changes sometime between 1998 and 2013.

Since I still have active projects that incorporate resource scripts generated by both editors, keeping this program backward compatible was a critical design goal.

Using the Code

The objectives of this article are twofold.

  1. Provide better instructions for day to day application of the workbook in the real world.
  2. Demonstrate a new use case that covers generation and maintenance of any stringtable.

Following is a guided tour of the workbook, using the new use case to supply a real world example.

Security Measures

To prevent accidental changes, the VBA project and most of the worksheets are password protected. However, since you may want or need to edit the worksheets or the VBA code to suit some requirement that I didn't anticipate, I am providing you with the passwords.

  • Protected worksheets: CodeProject
  • VBA project: TheCodeProject

Cells that have light green backgrounds are unprotected, and may be freely edited without unlocking the worksheet.

Since the project is unsigned, if you have a digital signature, you may want or need to sign it before you put it into production. For that, you will need the VBA project password at least once.

For your convenience, both passwords can be found in the two-paragraph document comments, available on the Summary tab of the Advanced property sheet. (Given what's there, I don't know why Microsoft labeled it Advanced, unless because most of the other tabs are advanced.)

Step by Step

Following is an illustrated step by step guide; most of the instructions are in the picture captions.

main Resource Script Generator worksheet

Figure 1 it the main Resource Script Generator worksheet, ready for a new task to be created.

Prompt to Name Task

Figure 2 is the prompt displayed by Microsoft Excel when you press trl-Shift-N to create a new resource profile.

Populated Task Name Prompt Window

Figure 3 is the input box shown in Figure 2 with a valid profile name entered into it. You can use any name that doesn’t appear in row 4 of the group of columns that starts in column E, and is captioned Stringtable Generation Profiles, shown in Figure 1.

Completion Confirmation Message

Figure 4 is the confirmation displayed when the macro completes its work. A new column has been inserted in the worksheet, which contains hyperlinks to a new worksheet and the two named workbook ranges in it that will contain your new resource script and its read only symbols.

Completed Main Resource Generator Worksheet

Figure 5 is the worksheet first shown in Figure 1 as it appears when the macro ends. The insertion point is in cell F7, just below the last of the three new hyperlinks mentioned in the caption of Figure 4. Leave this cell blank unless you enter additional details that you want included in the generated resource symbol header. If you do, they go into a named workbook range, and the range name goes here. Click the link in cell F4 to display the new worksheet into which you input and name your strings. The blank worksheet is shown in Figure 6.

Blank Resource Strings Worksheet for New Task

Figure 6 is the blank worksheet into which you enter your strings and assign names to them, which is accessible from the hyperlink in row 4 of each profile. A link in cell A1 takes you back to the main resource generator worksheet to complete the remaining profile settings.

Completed Resource String Worksheet

Figure 7 is a populated resource string worksheet. Worksheet protection keeps the insertion point moving to the next input cell as you input resource string IDs in column C and string text in column E. The insertion point is in cell C12, where it came to rest after the last string value was input in the row above it. Column D shows the length of each name, which the worksheet formulas need to work out the spacing for the code that goes into columns G and H. Column E is a hexadecimal representation of the string ID that was generated for you in column B. Cell A2 contains the starting number, which you may change, indicated by its light green background. Click the link in cell A1 to return to the main worksheet, complete your profile, generate the read only resource script and its header file, and add them to the main resource script file.

Completed Resource Script Profile in Man Resource Generator Worksheet

Figure 8 is the completed read-only resources and symbols profile, which is ready for use. Table 1 lists and describes the fields.

Table 1 lists and describes the remaining boxes in the profile form shown in Figure 8. The strange looking labels, which appear in column A, which is frozen, do double duty as substitution tokens.

Token Explanation
$$StandardErrorStatusCodes$$ Created for recording CPP macros to map exit codes to message IDs or vice versa, any workbook named range entered here becomes part of the read only symbols header, $$ReadOnlyResourceScriptName$$.H, two boxes down.
$$MainResourceScriptName$$ Enter the name of the main resource script file. This is the file that is created by the resource editors when the first resource of any kind goes into the project. For example, since I always add a Version resource right away, that’s usually when my resource script springs into existence. There is one only per project, and its extension is .RC.
$$ReadOnlyResourceScriptName$$ Two file names are derived from the name entered into this box, and I resisted the temptation to make it a formula, although I usually use the profile name (cell F4).If that suits you, enter =F4 into this cell, which is legal, since the naming convention prevents this creating any conflicts.
  1. The script file is named $$ReadOnlyResourceScriptName$$.RC2.
  2. The corresponding header file is named $$ReadOnlyResourceScriptName$$.H.
$$ReadOnlyResourceScriptNameUC$$ This field is protected, and is filled by formula by $$ReadOnlyResourceScriptName$$, converted to upper case, which becomes the guard symbol in the header file.
$$BookName$$ This field is obsolete, and is no longer used; the absolute (fully qualified) workbook name is written directly into the read only symbols header file.
$$Synopsis$$ Anything you enter into this cell is split into lines 80 or fewer characters each, and written into the comment box at the top of the read only symbols header file.

Read Only Resource Script Generation and Integration Succeeded!

Figure 9 is the message box that displays when you press Ctrl-Shift-G to generate the resource script and symbol files.

---------------------------
Win32_ResGen_CPPTimeZone.XLSM
---------------------------

Read Only Resource Script and Symbols Header Generated

Resource Script File = C:\Users\DAVE\Documents\Visual Studio 2013\Projects\_Laboratory
\CPPTimeZoneLab\CPPTimeZoneLab\CPPTimeZoneLab.RC2

Header File = C:\Users\DAVE\Documents\Visual Studio 2013\Projects\_Laboratory\CPPTimeZoneLab\CPPTimeZoneLab\CPPTimeZoneLab.H

---------------------------
OK  
---------------------------

Listing 1 is the complete contents of the message box shown in Figure 9, which is captured by clicking on the message box, then pressing Ctrl-C, which copies the caption, message, and button face text into the Windows clipboard.

Output of Demonstration Program

Figure 10 is the output of the little demonstration program that I created to prove beyond reasonable doubt that the generated script inserts work correctly. The entire project is included as a nested archive, CPPTimeZoneLab.ZIP, which uses the resource script exactly as the generator left it. For your convenience, I included both debug and retail builds, both of which are built against the retail MSVCR120.LIB.

The other demonstration projects cover another use case, in which the resource stings go into a satellite DLL. This use case is important in its own right, because it demonstrates how a single set of resource strings can be shared by two or more projects, by treating them as a library of strings, muich as you would a library of common functions or classes. Since the prvious article covers this use case in depth, I shall refrain from further explanation here. Please see the previous article, Collision Proof Shared String Resources,

Points of Interest

Since the Microsoft Excel workbook that accompanies this article is an extension of the one that accompanies Collision Proof Shared String Resources, I shall confine myself to the interesting bits of the new code. That, in itself, leaves a lot of ground to cover, because the new code automates a considerably more complex and error prone part of the process.

The ResGen Macro, Ctrl-Shift-G

The original macro, ResGen, defined in source file >m_ResGen.bas (included in the article archive) is barely changed from the original, and the changes are narrowly confined. Existing function LoadTemplateFromRange gets a new optional argument, pPreserveBlankLines, which is overridden in only one of the three calls, in the code that processes your input data. Since the same routine processes everything that comes from a named range in the workbook, and the objective is to preserve blanks in the templates, while discarding them from your inputs (so you can leave blank rows at the end of the input range), the optional value is KEEP_BLANK_LINES, a Boolean constant with a value of True, which is appropriate for all but one invocation. Both of the ranges that contain your data are processed by that third call, which overrides the optional argument, as shown in Listing 2.

VBScript
strWork = Replace(strWork, _
                  CStr(rngParams.Cells(lngCurrRow, _
                                       putpParamCols.ColValue).Value), _
                       LoadTemplateFromRange(CStr(rngParams.Cells(lngCurrRow, _
                                                  putpParamCols.ColLiteral).Value), _
                                             LAST_LINE_DLM_DISCARD, _
                                             DISCARD_BLANK_LINES))

Listing 2 is the statement that calls LoadTemplateFromRange, which has been amended to override both optional arguments.

Since this is the only one of the three calls that overrides the first optional argument, it made sense to assign the new argument a default value that had to be overridden on the same call, since neither of the other two calls needs to override the first one, but would have had to do so if the default value was reversed, since you cannot skip unused optional arguments.

Function LoadTemplateFromRange gets two changes to compensate for additional rows added to three of the four data ranges to support the improved automation.

  1. The first line, which corresponds to the first row in the single-column range, is skipped if it contains the name of the source range, which it gets from its only required argument, pstrRangeName. This straightforward test is coded inline.
  2. All lines, including the first, unless it meets the test described above, are evaluated by a new Boolean function, KeepOrDiscardLine, which always flags the top and bottom range boundary markers to be skipped, and flags blank lines to be skipped, too, if argument pPreserveBlankLines is True.

A consequence of the second change is that argument pfLastNewlineDisp cannot be evaluated until the whole range has been processed. However, the result is a cleaner design that conforms to the way that I usually write loops that append to strings, for which this case demonstrates why this approach is almost always the best. Listing 3 is the improved body of the loop, which appends kept lines, even if blank, to string strWork. Blank lines, if kept, must be followed by a line feed, most easily accomplished by appending the line feed followed by the new line, which happens to be the empty string.

VBScript
For lngCurrRow = RANGE_FIRST_ROW To lngLastRow

    Dim strLine As String: strLine = CStr(rngTemplate.Cells(lngCurrRow, _
                                                            THE_ONE_AND_ONLY_COLUMN).Value)

    If lngCurrRow = RANGE_FIRST_ROW And strLine = pstrRangeName Then
        DoEvents            ' The first line contains the range name. Skip it.
    Else
        If KeepOrDiscardLine(strLine, pPreserveBlankLines) Then
            If Len(strWork) = LENGTH_OF_EMPTY_STRING Then
                strWork = strLine
            Else
                strWork = strWork & vbCrLf & strLine
            End If  ' If Len(strWork) = LENGTH_OF_EMPTY_STRING Then
        End If  ' If KeepOrDiscardLine(strLine, pPreserveBlankLines) Then
    End If  ' If lngCurrRow = RANGE_FIRST_ROW And strLine = pstrRangeName Then
Next    ' For lngCurrRow = RANGE_FIRST_ROW To lngLastRow


If pfLastNewlineDisp = LAST_LINE_DLM_KEEP Then
    LoadTemplateFromRange = strWork & vbCrLf
Else
    LoadTemplateFromRange = strWork
End If  ' If pfLastNewlineDisp = LAST_LINE_DLM_KEEP Then

Listing 3 is the improved main loop in function LoadTemplateFromRange.

VBScript
Private Function KeepOrDiscardLine(ByRef pstrSourceCodeLine As String, _
                                   ByVal pPreserveBlankLines As Boolean) _
                    As Boolean

'   ----------------------------------------------------------------------------
'   Function Name:      KeepOrDiscardLine
'
'   Function Abstract:  Evaluate the line against four conditions.
'                       1) The line is blank, and is discarded unless
'                          pPreserveBlankLines is TRUE.
'                       2) The line marks the top of a protected named range.
'                       3) The line marks the bottom of a protected named range.
'                       4) The line meets none of the above conditions.
'
'   Function Argument:  Argument pstrSourceCodeLine is the string to evaluate.
'
'                       Argument pPreserveBlankLines is the disposition of blank
'                       lines.
'
'   Function Return:    A member of the LineDisposition corresponds to each of
'                       the four cases listed in the abstract.
'
'   Remarks:            A SELECT CASE block evaluates the line length, which is
'                       very fast, since a Basic String is a counted string,
'                       meaning that its length is determined in advance and
'                       stored with it. Since the string length is sufficient to
'                       evaluate the first condition, it may as well be used as
'                       a first pass, to eliminate strings that are either too
'                       long or too short to meet the next two criteria, leaving
'                       only same length strings that stand a chance of matching
'                       to incur the computational cost of the equality test.
'   ----------------------------------------------------------------------------

    Const MARKER_TOP As String = "This cell marks the top of a range."          ' If MARKER_TOP changes, so must MARKER_TOP_LENGTH.
    Const MARKER_BOTTOM As String = "This cell marks the bottom of a range."    ' If MARKER_BOTTOM changes, so must MARKER_BOTTOM_LENGTH.
    Const MARKER_TOP_LENGTH As Integer = 35                                     ' If MARKER_TOP changes, so must MARKER_TOP_LENGTH.
    Const MARKER_BOTTOM_LENGTH As Integer = 38                                  ' If MARKER_BOTTOM changes, so must MARKER_BOTTOM_LENGTH.

    Select Case Len(pstrSourceCodeLine)
        Case LENGTH_OF_EMPTY_STRING
            KeepOrDiscardLine = pPreserveBlankLines                             ' Disposition of blank lines is encoded in argument pPreserveBlankLines.
        Case MARKER_TOP_LENGTH
            If pstrSourceCodeLine = MARKER_TOP Then
                KeepOrDiscardLine = False
            Else
                KeepOrDiscardLine = True
            End If  ' If pstrSourceCodeLine = MARKER_TOP Then
        Case MARKER_BOTTOM_LENGTH
            If pstrSourceCodeLine = MARKER_BOTTOM Then
                KeepOrDiscardLine = False
            Else
                KeepOrDiscardLine = True
            End If  ' If pstrSourceCodeLine = MARKER_BOTTOM Then
        Case Else
            KeepOrDiscardLine = True
    End Select  ' Select Case Len(pstrSourceCodeLine)

End Function    ' KeepOrDiscardLine

Listing 4 is function KeepOrDiscardLine, comments and all. The constants have line comments, each of which begins with “if” that convey critical maintenance reminders about the relationships between the string and integer constants..

All VBA string objects are Basic Strings, which are counted strings. A counted string is one which has its characters counted when it is created and stored with the string. The internal structure of a Basic String, shown in Figure 11 consists of the text of the string, preceded by a 4 byte signed integer length. A corollary of this is that when you append to a string, a new string is created, the variable is updated with the address of the new string, and the memory occupied by the original string is discarded (returned to the global heap).

The Anatomy of a Basic String

Figure 11 gives a very rough idea of the internal structure of a Basic String. The 4 upper case Ls signify that the length occupies the 4 bytes immediately below the location that is given as its address.

Since VBA strings are counted, obtaining the length of one is ridiculously easy compared to the effort required to do so for a garden variety ASCIIZ string (a C string). Lunch isn’t really free, though, because the program that created the string probably had to expend the effort, since there is a good chance that the string began life as a C string. However, if the length of a Basic string is taken more than once, all but the first evaluation is practically free; back the pointer up 4 bytes and read the length from the 32 bit signed integer stored at that location.

In a sense, KeepOrDiscardLine uses the length three times, though, as a practical matter, Select Case takes it once only, and bases its decision about what to do next on it. The gain for KeepOrDiscardLine is that it must perform at most  one expensive string comparison. Cool, eh?

The New CreateNewProfile Macro, Ctrl-Shift-N

When I told my wife about this article, I said that it reminded me of the study that she told me about being assigned as a college project; document the steps required to brush your teeth and floss. She and her team identified 48 discrete steps!

With that in mind, herewith is a very detailed look at the steps required to fully automate inserting a column into a worksheet, inserting a copy of a template sheet at one of two designated locations in the tab order, overlaying uniquely named workbook ranges on two private (worksheet scope) ranges in the copied worksheet, and creating links to the new worksheet and the two ranges in the worksheet into which the new column was inserted, and leaving that worksheet activated, with the insertion point parked three rows below the cell containing the hyperlink to the new sheet. Though I may yet have missed something, I tried to anticipate everything that could go wrong, recover when I can, and restore the worksheet to a pristine state when I cannot.

The main worksheet, ResGen Parameters, contains two named ranges that drive this macro.

  1. Workbook range ActiveProfileName resolves to cell C4, the validated input cell that stores the name of the active task, which is also the name of its data worksheet. Since this range has workbook scope, its Worksheet property is used to select and activate the ResGen Parameters worksheet regardless of what sheet has the focus at macro startup.
  2. Worksheet range Active_Task is the row of cells that runs across the columns labeled Stringtable Generation Profiles; it starts in cell E4, and extends to the right as far as does the label cell above it (currently cell H4). When this macro isn’t using it, this range validates input to the ActiveProfileName cell. Since it has no use elsewhere, its scope is worksheet ResGen Parameters.

Two other ranges Substitution_Token_Labels and ValidSubstitutionTokens, both of which have workbook scope, support the resource script generator macro, activated by Ctrl-Shift-G. Custom object, clsRCScriptRangeCollection, a RCScriptRangeCollection, is a small object that stores information about two new ranges that is vital to creating the hyperlinks that occupy the cells in rows 5 and 6 of the task column. Worksheet object wksResGen gets a reference to worksheet ResGen Parameters, so that its Activate method can be used to jump back to this worksheet to populate rows 5 and 6.

The purpose of the With rngTaskList block is to insert a column immediately to the left of the column that stores information about the active profile (the one that was used last), to hold the metadata about the task that is about to spring into existence.

The first thing that may seem odd is the following test, at line 126, which determines whether the macro does any work: If rngStartHere Is Nothing Then, and depends on the fact that an uninitialized object is Nothing. If execution reaches this test without setting rngStartHere, defined at line 96, just above the short with block that scans the list of registered tasks, then the test inside For Each rngCurrTask In .Cells went unmet because the worksheet is in an inconsistent state.

Function GetNewTaskName initializes string strNewTaskName with the task name input by the operator. If string strNewTaskName is empty, the column insertion made by With rngTaskList is undone by calling subroutine UndoChanges, explained below. Otherwise, the real work gets underway.

  1. The task name is stored in the single cell that constitutes logical range rngStartHere, which is the cell in row 4 of the column that was just inserted. Storing the task name in this cell adds it to the list of tasks that can be selected for the generator and provides the label for a hyperlink that sets the focus to the worksheet where its strings are defined. I use logical to differentiate this nameless range, which exists only in the context of this subroutine, from named ranges defined in the workbook and its sheets.
  2. The next test turns IsSheetNameAvailable upside down by asking it to raise a red flag if the stringtable template worksheet is missing.
  3. Unless the template sheet is missing because an operator accidentally deleted it and saved the workbook, the workbook is searched for the worksheet that was created for the active (last used) task. If it is present, the new sheet inserted in front of it. Otherwise, the new sheet goes in front of the original worksheet, StringTable_Template, the value in program constant TASK_TEMPLATE.
  4. Whether done manually in the user interface or by code, copying a worksheet activates the copy, and Worksheet object wksScriptData gets a reference to the new sheet, which it passes to function LabelRC, along with clsRCScriptRangeCollection., which verifies the integrity of the worksheet and creates the two workbook scoped range names that power the resource script generator script.
  5. Unless LabelRC hits a snag (unlikely, though always possible), function CreateHyperlinksInResGen transforms the cells in rows 4 through 6 of the column that was inserted into worksheet ResGen Parameters at the beginning of the macro into hyperlinks.
  6. Unless CreateHyperlinksInResGen got into trouble (unlikely), worksheet ResGen Parameters is activated, the new task becomes active (Its name goes into cell C4.), and the selection is moved to the empty cell in row 7, where it is left as the macro ends.
  7. A message box informs you that the new profile is complete and ready to use, and protection is reinstated on sheet ResGen Parameters.

These seven steps, which encompass several multi-step tasks, happen before you can blink your eyes, and eliminate many error prone manual procedures that take a long more time than an eye blink, no matter how good you are. These seven steps explode into many smaller steps and decisions. The following sections discuss the functions that do most of the work to complete these 7 steps. The sections are arranged alphabetically by function name.

CreateHyperlinksInResGen

After dropping anchor on sheet ResGen Parameters, at the spot where the macro began, a new logical range is defined at the origin (cell A1) of the new worksheet that was created to store the string definitions, and the two are combined to transform the cell in row 4 of the new column into a hyperlink that you can use to activate that worksheet.

Next, a new enmLabelColumn variable is defined and initialized, exactly as is done at the top of function LabelRC, which is the index of an identical DO loop. The difference is that far fewer tasks happen inside this loop. Though there are only three iterations, the last of which skips the body, this design affords the option of extending it to cover more ranges, by adding them to the enmLabelColumn enumeration that drives it.

The first task of the loop is a call to method pclsRCScriptRangeCollection.GetRangeName, which uses a straightforward Select Case to grab the string to use as named argument TextToDisplay in the call to the Add method on the Hyperlinks collection of worksheet ResGen Parameters that transforms the cell into a working hyperlink.

Unless the string returned by pclsRCScriptRangeCollection.GetRangeName is empty because it ran into trouble (unlikely), pclsRCScriptRangeCollection.GetRange gets a reference to the corresponding range object, from which function RangeAddressForFrmula derives a valid value for the SubAddress named argument of the aforementioned Add method.

Though I don't remember how I learned to construct a local hyperlink, it doesn't matter, since, I have working code from the VBA add-in that created the _Index worksheet in this workbook. You specify the target of a local hyperlink through named argument SubAddress, while the Anchor argument defines where it goes on the worksheet, which need not be the active sheet.

GetNewTaskName

Testing of this routine surfaced a problem that arises from its use of the InputBox method on the Application object, which is that when you drag the mouse across a range of cells, the corresponding range is displayed in the input box, and the contents of the origin cell (the cell in its upper left corner) is returned if you don’t notice, and accept the input. My initial reading of the documentation caused me to add extra cases to cover the vbObject variant type. A second reading, prompted by the behavior described above, confirmed that you won’t get back a Range object unless you override an optional argument that defaults to Text. That still left the issue of the text from the origin cell getting into the control, and ultimately led to the creation of a new function, MakeRangeNameConformant, which cleans up the range names before any attempt is made to use them.

Listing 5 is all of function GetNewTaskName, which has several noteworthy features.

  • The first executable statement calls Application.InputBox, a method on the Application object, which differs from the legacy InputBox function because it affords a mechanism to unambiguously determine that the operator activated its Cancel button, and it can return a reference to a worksheet range, a capability that this macro ignores.
  • Since Application.InputBox can return a Range object, text, or an indicator that its Cancel button was activated, its return type is Variant.
  • Since activating the Cancel button on the Application.InputBox causes it to return a Boolean, the first task to perform upon return is to evaluate its type, which falls to the VarType function, whose return value is the criterion of a the Select Case statement that encompasses the remainder of the DO loop, and of the routine.
  • Under normal circumstances, the Case vbString block executes, earning it a spot at the top of the block. This affects program performance because Select Case evaluates each case in turn until one is satisfied or it runs out of cases.
  • Once it has used the CStr function to convert the Variant returned by InputBox into a true String, the vbString block turns its attention to its length, which is tested first for zero, indicating that the input box was empty when its OK button was activated, then for a value greater than MAX_SHEETNAME_LENGTH (31). I separated these tests because their handling differs because the latter case displays the input string and its length in its message box, while the zero length string gets a simpler message without inserts.
    • There are two ways to handle inserts, and my choice depends on whether I put the static text directly into the MsgBox function call or constants. I prefer constants for commercial grade code, because they can be put together in an area close to the top of the program, where they are easy to find.
    • This approach favors substitution tokens that act as placeholders for the values to be inserted at run time. To ensure correctness of the tokens, I define them as separate constants that not only serve as arguments to the Replace function, but are used to construct the template string, itself.
    • Though I seldom see examples that define constants in this manner, I was delighted when I realized that a constant definition can be any expression that is valid for its type. Not only does this decrease the size of the program on disk and in memory, but it makes robust, error free message templates easy!
    • If most of your code is C, C++, C#, and VB.NET, it is easy to forget that the VBA compiler is less forgiving about the order in which you define things, so you must define your substitution tokens before you define the template constants.
  • I use nested Replace functions to replace two or more tokens in a string , because they don’t clutter your code with one-off scratch variables, and use the stack more efficiently. These are easy to get right if you format them as shown, since it makes a graph of the tokens and their replacements.
  • To afford you the opportunity to bail out, every prompt displayed by this routine has a Cancel button, requiring the message boxes to use the MsgBox function, which returns a value to indicate the selected button, rather than the classic MsgBox statement, which returns void. Since we have no other use for the return value, the MsgBox goes directly into the Select Case statements, rather than clutter the code with more one-off variables.
  • Finally, testing the Boolean for True and catching other variant types in the Case Else block are defensive measures. Unless the behavior of the InputBox changes, which is unlikely, though theoretically possible, neither of these blocks will ever execute. However, if they do, they give you a few clues about the cause of the unexpected behavior.
VBScript
Private Function GetNewTaskName() As String

    Const MAX_SHEETNAME_LENGTH As Integer = 31
   
    '   ------------------------------------------------------------------------
    '   Interpolating constants into other constant strings is legal, but their
    '   names must first be defined. The VBA lexer doesn't look ahead.
    '   ------------------------------------------------------------------------

    Const TOKEN_CANDIDATE As String = "$$Candidate$$"
    Const TOKEN_STRLEN As String = "$$StrLen$$"
    Const TOKEN_BAD_TYPENAME As String = "$$TypeName$$"
    Const TRY_AGAIN_PROMPT As String = vbLf & vbLf & "Please choose a different name."
    Const WARNING_IF_PERSISTS As String = vbLf & "Please investigate if this error persists."

    Const MSG_NAME_HAS_INVALID_CHARS As String = "The name you entered, " & TOKEN_CANDIDATE & ", contains invalid characters." & "The following characters are invlid:  :  \  /  ?  *  [  ]" & TRY_AGAIN_PROMPT
    Const MSG_NAME_IS_BLANK As String = "Your input registered as the empty string. Please try again."
    Const MSG_NAME_TOO_LONG As String = "The name you entered, " & TOKEN_CANDIDATE & "," & vbLf & "contains " & TOKEN_STRLEN & " characters, which is too long." & vbLf & vbLf & "Please enter a name that contains 31 or fewer characters." & TRY_AGAIN_PROMPT
    Const MSG_NO_RANGES_PLEASE As String = "You accidentally selected a range from the worksheet." & vbLf & "This input box needs a name that it can assign to a new worksheet." & vbLf & vbLf & "Please input your selection again."
    Const MSG_UNAVAILABLE As String = "The name you entered, " & TOKEN_CANDIDATE & ", belongs to another task and its worksheet." & TRY_AGAIN_PROMPT
    Const MSG_UNEXPECTED_VALUE As String = "The Input Box returned an unexpected Boolean value." & WARNING_IF_PERSISTS
    Const MSG_UNEXPECTED_TYPE As String = "The input box returned an unexpected Variant type of " & TOKEN_BAD_TYPENAME & "." & WARNING_IF_PERSISTS

    Const NAME_PROMPT As String = "Enter a name for the new task. The selected name will become the name of its worksheet. Hence, it cannot be the name of an existing worksheet."

    Const TYPENAME_RANGE As String = "Range"

    Dim fNameInHand As Boolean: fNameInHand = False

    Do
        Dim fGoodCandidate As Boolean: fGoodCandidate = True
        Dim varCandidateName As Variant: varCandidateName = Application.InputBox(NAME_PROMPT, _
                                                                                 ActiveWorkbook.Name, _
                                                                                 vbNullString)

        Select Case VarType(varCandidateName)
            Case vbString
                Dim strCandidateName As String: strCandidateName = CStr(varCandidateName)               

                Dim intStrLen As Integer: intStrLen = Len(strCandidateName)

                If intStrLen = LENGTH_OF_EMPTY_STRING Then
                    fGoodCandidate = False

                    Select Case MsgBox(MSG_NAME_IS_BLANK, _
                                       vbOKCancel Or vbExclamation, _
                                       ActiveWorkbook.Name)
                        Case vbOK
                            DoEvents                          ' Go around.
                        Case vbCancel
                            GetNewTaskName = vbNullString     ' Returning the empty string signals cancellation.
                            fNameInHand = True                ' Force DO loop to end.
                    End Select  ' Select Case MsgBox(MSG_NAME_IS_BLANK, vbOKCancel Or vbExclamation, ActiveWorkbook.Name)
                ElseIf intStrLen > MAX_SHEETNAME_LENGTH Then
                    fGoodCandidate = False

                    Select Case MsgBox(Replace(Replace(MSG_NAME_TOO_LONG, _
                                                       TOKEN_CANDIDATE, _
                                                       strCandidateName), _
                                                TOKEN_STRLEN, _
                                                intStrLen), _
                                        vbOKCancel Or vbExclamation, _
                                        ActiveWorkbook.Name)
                        Case vbOK
                            DoEvents                          ' Go around.
                        Case vbCancel
                            GetNewTaskName = vbNullString     ' Returning the empty string signals cancellation.
                            fNameInHand = True                ' Force DO loop to end.
                    End Select  ' Select Case MsgBox(Replace(Replace(MSG_NAME_TOO_LONG, TOKEN_CANDIDATE, strCandidateName), TOKEN_STRLEN, intStrLen), vbOKCancel Or vbExclamation, ActiveWorkbook.Name)
                ElseIf SheetNameIsInvalid(strCandidateName) Then
                    fGoodCandidate = False

                    Select Case MsgBox(Replace(MSG_NAME_HAS_INVALID_CHARS, _
                                               TOKEN_CANDIDATE, _
                                               strCandidateName), _
                                       vbOKCancel Or vbExclamation, _
                                       ActiveWorkbook.Name)
                        Case vbOK
                            DoEvents                          ' Go around.
                        Case vbCancel
                            GetNewTaskName = vbNullString     ' Returning the empty string signals cancellation.
                            fNameInHand = True                ' Force DO loop to end.
                    End Select  ' Select Case MsgBox(Replace(MSG_NAME_HAS_INVALID_CHARS, TOKEN_CANDIDATE, strCandidateName), vbOKCancel Or vbExclamation, ActiveWorkbook.Name)
                End If  ' If intStrLen = LENGTH_OF_EMPTY_STRING Then

                If fGoodCandidate Then
                    If IsSheetNameAvailable(strCandidateName) Then
                        GetNewTaskName = strCandidateName     ' Name is available; return it to caller.
                        fNameInHand = True                    ' Force DO loop to end.
                    Else
                        Select Case MsgBox(Replace(MSG_UNAVAILABLE, _
                                                   TOKEN_CANDIDATE, _
                                                   strCandidateName), _
                                           vbOKCancel Or vbExclamation, _
                                           ActiveWorkbook.Name)
                            Case vbOK
                                DoEvents                      ' Go around.
                            Case vbCancel
                                GetNewTaskName = vbNullString ' Returning the empty string signals cancellation.
                                fNameInHand = True            ' Force DO loop to end.
                        End Select  ' Select Case MsgBox(Replace(MSG_UNAVAILABLE, TOKEN_CANDIDATE, strCandidateName), vbOKCancel, ActiveWorkbook.Name)
                    End If  ' If IsSheetNameAvailable(strCandidateName) Then
                End If  ' If fGoodCandidate Then

            Case vbBoolean                                                      ' Either way, we are done.
                If CBool(varCandidateName) Then
                    Select Case MsgBox(MSG_UNEXPECTED_VALUE, _
                                       vbOKCancel Or vbCritical, _
                                       ActiveWorkbook.Name)
                        Case vbOK
                            DoEvents                          ' Go around.
                        Case vbCancel
                            GetNewTaskName = vbNullString     ' Returning the empty string signals cancellation.
                            fNameInHand = True                ' Force DO loop to end.
                    End Select  ' Select Case MsgBox(MSG_UNEXPECTED_VALUE, vbOKCancel Or vbCritical, ActiveWorkbook.Name)
                End If  ' If CBool(varCandidateName) Then

                GetNewTaskName = vbNullString                 ' Returning the empty string signals cancellation.
                fNameInHand = True                            ' Force DO loop to end.
            Case Else                                         ' In the unlikely event that this happens, I'll take the hit for the extra vartype.
                MsgBox Replace(MSG_UNEXPECTED_TYPE, _
                               TOKEN_BAD_TYPENAME, _
                               TypeNameForVariant(VarType(varCandidateName))), _
                       vbCritical, _
                       ActiveWorkbook.Name
        End Select  ' Select Case VarType(varCandidateName)

    Loop Until fNameInHand

End Function    ' GetNewTaskName

Listing 5 is VBA function GetNewTaskName, which is straightforward for the most part, but has a couple of noteworthy quirks.

IsSheetNameAvailable

This function is noteworthy in its own right because it implements a loop that enumerates the Worksheets collection, testing the Name of each sheet against the proposed name. Though enumerating the list might seem expensive, I prefer it to the allegedly simpler method of looking up the worksheet by name. For one, I’ve had spotty luck with it, and for another, if the sheet doesn’t exist, this approach raises a run-time error, which I work hard to avoid, because they disrupt program flow. (I think you could make a good case that On Error GoTo is the most harmful GoTo of all.) Listing 6 is the entire function, which has a high proportion of comments to code.

  • The very first task is to verify that the sheet name string contains text, to avoid wasting time searching the list for the empty string, which will never match a worksheet name, transforming it into a good general purpose function. If the name is blank, the function sets its return value to False, because the blank name is, strictly speaking, unavailable, and returns.
  • Next, the function sets its  return value to True, anticipating that the specified name is available, sets the error trap, and enters its main loop.
  • Though it should never encounter a runtime error, should that happen, the error is reported via message box, the return value changes to False, and the function returns.
  • If a matching name is found, the function sets its return value to False, breaks out of the loop, and returns without searching the rest of the list.
  • If the loop ends because it scanned the entire list without finding a match,, the function simply returns, since the return value is already True.
VBScript
Public Function IsSheetNameAvailable(ByRef pstrProposedName As String) As Boolean

'   ----------------------------------------------------------------------------
'   Function Name:      IsSheetNameAvailable
'
'   Function Abstract:  Return TRUE if a proposed worksheet name is available
'                       for assignment to a new worksheet.
'
'   Function Arguments: pstrProposedName (String) = proposed worksheet name
'
'   Function Returns:   TRUE if the name is avaliable for use, otherwise FALSE
'
'   Remarks:            In the unlikely event of a run-time error, the function
'                       reports via message box, then returns FALSE. The enpty
'                       string also elicits a return value of FALSE, for reasons
'                       that I presume are self-evident.
'   ----------------------------------------------------------------------------

    If Len(pstrProposedName) = LENGTH_OF_EMPTY_STRING Then
        IsSheetNameAvailable = False
    Else
        On Error GoTo IsSheetNameAvailable_Err                                  ' Defer wiring up the exception handler until we know it's needed.
        IsSheetNameAvailable = True                                             ' Anticipating that the name is available is more computationally fficient.

        Dim wksCurrent As Worksheet

        For Each wksCurrent In ThisWorkbook.Worksheets
            If wksCurrent.Name = pstrProposedName Then
                IsSheetNameAvailable = False                                    ' Signal that the name belongs to another sheet.
                Exit For                                                        ' Finding a match ends the search.
            End If  ' If wksCurrent.Name = pstrProposedName Then
        Next    ' For Each wksCurrent In ThisWorkbook.Worksheets
    End If  ' If Len(pstrProposedName) = LENGTH_OF_EMPTY_STRING Then

IsSheetNameAvailable_End:

    Exit Function

IsSheetNameAvailable_Err:

    MsgBox "Error report from custom VBA function IsSheetNameAvailable:" & vbLf & vbLf _
                & "Error " & Err.Number & " - " _
                & Err.Description, _
            vbExclamation, _
            ThisWorkbook.Name
    Err.Clear

    IsSheetNameAvailable = False
    Resume IsSheetNameAvailable_End

End Function    ' IsSheetNameAvailable

Listing 6 is function IsSheetNameAvailable, comments and all.

LabelRC

This module defines the two workbook scoped range names that specify the code for the resource script and the corresponding symbols. Since this routine is driven by the same loop that drives CreateHyperlinksInResGen, I won’t repeat the explanation.

  1. Most of the code in this routine is devoted to verifying the integrity of the worksheet, of which there are two aspects.
    1. Is the geometry of the range correct, indicating that its two boundary rows are intact, and that no columns have been added to it?
    2. Is the label row intact (unchanged from its design time value), indicating that the sheet is unused?
  2. Function MakeRangeNameConformant addresses an issue that surfaced late in the game, although I should have remembered that spaces are illegal in range names. However, since I usually use underscores and camel casing for names of just about everything, even files and ranges, it slipped my mind until I started playing with completely random strings for range names to test another quirk of the Application.InputBox method. Oops! That’s OK, though, because it prompted me to learn exactly what does and doesn’t belong in a valid range name, leading to MakeRangeNameConformant.[1]
  3. Since the Name property of a worksheet range that has local (worksheet) scope, such as this one, returns its fully qualified address, this routine bypasses RANGEADDRESSFORFRMULA.

Since this routine is structurally identical to CreateHyperlinksInResGen, its source code is omitted, to keep the article from growing even longer than it is already.

MakeRangeNameConformant. 

This function implements the name syntax rules set forth in the article cited in footnote 1. Its algorithm is essentially the one used by the Excel user interface when you use the Create from Selection option in the Defined Names section of the Formulas tab of the Ribbon, except that a leading backslash becomes a leading underscore..

The rules boil down to this.

  • The first character of the name must be a letter, a number, or a backslash. If the first character is a number, you may keep it, but only by preceding it with an underscore, effectively making it second.
  • Subsequent characters can be letters, numbers, periods, and underscores.
  • The name cannot contain more than 255 characters.

MakeRangeNameConformant substitutes underscores for invalid characters, unless the first character is a number, which is preceded by an underscore and kept. This function deviates from the official rules with respect to the first character,  because it treats a leading backslash as invalid, replacing it with an underscore.

The loop that processes the second and subsequent characters deviates somewhat from my usual practice of avoiding function calls in the criterion clause of a loop, IIf(intStrLen > MAX_LENGTH, MAX_LENGTH, intStrLen). This compact expression effectively cuts off processing after the 255th character in the unlikely event that there are more. The whole routine is reproduced in Listing 7.

VBScript
Public Function MakeRangeNameConformant(ByRef pstrCandidate As String) As String

'   ----------------------------------------------------------------------------
'   Name:               MakeRangeNameConformant
'
'   Abstract:           Transform any string into a valid name for a Range.
'
'   Arguments:          pstrCandidate   = Name to transform
'
'   Returns:            Unless the string is enpty, a new string is returned in
'                       which illegal characters are replaced with underscores.
'                       If the first character is a number, it is prefixed with
'                       an underscore, so that the digit, which is legal in any
'                       other position, can stay. If the length of the input
'                       string exceeds 255 characters, the excess is truncated.
'
'   Remarks:            Private function CharIsValidinRangeName, defined in this
'                       module, evaluates each character in the name.
'   ----------------------------------------------------------------------------

    Const MAX_LENGTH As Integer = 255

    Dim intStrLen As Integer: intStrLen = Len(pstrCandidate)
    Dim intCurrPos As Integer

    If intStrLen > LENGTH_OF_EMPTY_STRING Then
        Dim strTemp As String
        Dim strCurrChar As String: strCurrChar = Left(pstrCandidate, INSTR_START_AT_BEGINNING)

        '   --------------------------------------------------------------------
        '   Unsurprisingly, the first character is a special case.
        '   --------------------------------------------------------------------

        If IsNumeric(strCurrChar) Then
            strTemp = CHAR_UNDERSCORE & strCurrChar
        Else
            If CharIsValidinRangeName(pstrCandidate, INSTR_START_AT_BEGINNING) Then
                strTemp = strCurrChar
            End If  ' If CharIsValidinRangeName(pstrCandidate, INSTR_START_AT_BEGINNING) Then
        End If  ' If IsNumeric(Left(pstrCandidate, 1)) Then

        '   --------------------------------------------------------------------
        '   The remaining characters are treated identically.
        '
        '   Limit expression IIf(intStrLen > MAX_LENGTH, MAX_LENGTH, intStrLen)
        '   effectively truncates the string, since it stops the append loop at
        '   the 255th character.
        '   --------------------------------------------------------------------

        For intCurrPos = SECOND_CHARACTER To IIf(intStrLen > MAX_LENGTH, MAX_LENGTH, intStrLen)
            If CharIsValidinRangeName(pstrCandidate, intCurrPos) Then
                strTemp = strTemp & Mid(pstrCandidate, _
                                        intCurrPos, _
                                        ONE_CHAR_ONLY)
            Else
                strTemp = strTemp & CHAR_UNDERSCORE
            End If  ' If CharIsValidinRangeName(strCurrChar, intCurrPos) Then
        Next    ' For intCurrPos = SECOND_CHARACTER To IIf(intStrLen > MAX_LENGTH, MAX_LENGTH, intStrLen)

        MakeRangeNameConformant = strTemp
    Else
        MakeRangeNameConformant = vbNullString
    End If  ' If Len(pstrCandidate) > LENGTH_OF_EMPTY_STRING Then

End Function    ' MakeRangeNameConformant

Listing 7 is all of function MakeRangeNameConformant. Working with companion function CharIsValidinRangeName, it makes a valid range name of any string you feed to it, except the empty string, which cannot be salvaged.

CharIsValidinRangeName is called once for each character in the string to make the determination, except when the initial character is a number, which is handled by MakeRangeNameConformant inline. Since Excel remains firmly stuck in the Land of ANSI, the simplest way to evaluate character groups is by deriving the integer code that represents each character, and range check the numbers. A Select Case block handles the special cases (backslash, underscore, and period), while range checks in its Case Else block make short work of the rest.

VBScript
Private Function CharIsValidinRangeName(ByRef pstrCandidate As String, _
                                        ByVal pintPosition As Integer) _
                    As Boolean

'   ----------------------------------------------------------------------------
'   Name:               CharIsValidinRangeName
'
'   Abstract:           Evaluate the validity of a charecter in a proposed range
'                       name based on its position in the string.
'
'   Arguments:          pstrCandidate   = Name to transform
'
'                       pintPosition    = Position of character to evaluate.
'
'   Returns:            TRUE if the character is valid at the specified position, else FALSE.
'
'   Remarks:            This function begins by invoking intrinsic function ASC
'                       to return the integer ASCII code of the character under
'                       evaluation, so that the evaluation consists entirely of
'                       numeric comparisons, of which the frist three degenerate
'                       cases are dispatched by the select case block. The rest
'                       are evaluated by range testing, starting with upper case
'                       letters, then lower case letters, and, finally, digits.
'
'                       While the first of the three cases is dispatched with a
'                       simple response of TRUE, whether the second and third of
'                       them returns TRUE depends on whether the character under
'                       the microscope is the first or a subsequent character.
'                       It takes advantage of the fact that, when a relational
'                       expression is assigned to a Boolean variable, it is set
'                       to TRUE if the expression is TRUE, and FALSE otherwise.
'
'                       MakeRangeNameConformant is the only function that calls
'                       this routine, which it does once for each character in a
'                       string, up to the length limit of 255 for a range name.
'   ----------------------------------------------------------------------------

    Const CODE_BACKSLASH As Integer = 92
    Const CODE_FULL_STOP As Integer = 46
    Const CODE_UNDERSCORE As Integer = 95

    Const CODE_DECIMAL_DIGIT_0 = 48
    Const CODE_DECIMAL_DIGIT_9 = 57

    Const CODE_UC_LETTER_FIRST = 65
    Const CODE_UC_LETTER_LAST = 90

    Const CODE_LC_LETTER_FIRST = 97
    Const CODE_LC_LETTER_LAST = 122

    '   ------------------------------------------------------------------------
    '   Although ASC would swallow the rest of the string whole, why waste the
    '   memory and CPU cycles to store extra characters that it will ignore?
    '   ------------------------------------------------------------------------

    Dim intCharCode As Integer: intCharCode = IIf(pintPosition = INSTR_START_AT_BEGINNING, _
                                              Asc(pstrCandidate), _
                                              Asc(Mid(pstrCandidate, pintPosition, ONE_CHAR_ONLY)))


    Select Case intCharCode
        Case CODE_UNDERSCORE
            CharIsValidinRangeName = True
        Case CODE_BACKSLASH
            CharIsValidinRangeName = (pintPosition = INSTR_START_AT_BEGINNING)
        Case CODE_FULL_STOP
            CharIsValidinRangeName = (pintPosition > INSTR_START_AT_BEGINNING)
        Case Else
            If intCharCode >= CODE_DECIMAL_DIGIT_0 And intCharCode <= CODE_DECIMAL_DIGIT_9 Then
                CharIsValidinRangeName = (pintPosition > INSTR_START_AT_BEGINNING)
            ElseIf intCharCode >= CODE_UC_LETTER_FIRST And intCharCode <= CODE_UC_LETTER_LAST Then
                CharIsValidinRangeName = True
            ElseIf intCharCode >= CODE_LC_LETTER_FIRST And intCharCode <= CODE_LC_LETTER_LAST Then
                CharIsValidinRangeName = True
            Else
                CharIsValidinRangeName = False
            End If  ' If intCharCode >= CODE_UC_LETTER_FIRST And intCharCode <= CODE_UC_LETTER_LAST Then
    End Select  ' intCharCode

End Function    ' CharIsValidinRangeName

Listing 8 is all of function CharIsValidinRangeName, which MakeRangeNameConformant calls once for each character in a proposed range name string, up to the maximum permitted length of 255 characters.

RangeAddressForFrmula

Coaxing a valid range address from Excel is not as easy as I think it should be. One would think it would just be a matter of getting the value of one of its properties. However, for every range that I have ever evaluated, both Address and AddressLocal return the local address, e. g., $A$1:$G$10.

To further complicate matters, although spaces are allowed in worksheet names, as are a host of other characters that you would think were forbidden, a worksheet name that contains them must be enclosed in single quote marks, ASCII code 039 (0x27).

Finally, to create a valid absolute (fully qualified) range address, you must splice together the worksheet name, quoted if necessary, and the absolute local address, with a shebang (!, ASCII code 033, 0x21) sandwiched between them. Using the underused Immediate IF (IIF) function, this requires but one statement, but it’s a whopper (Listing 9).

VBScript
RangeAddressForFrmula = IIf(InStr(prng.Worksheet.Name, SPACE_CHAR_WW) > INSTR_NOT_FOUND, _
                            QUOTE_CHAR_SGL_WW _
                            & prng.Worksheet.Name _
                            & QUOTE_CHAR_SGL_WW, _
                            prng.Worksheet.Name) _
                        & FORMULA_SHEET_NAME_DLM _
                        & prng.AddressLocal

Listing 9 is the one and only executable statement in function RangeAddressForFrmula, made possible by the underused IIF function.

Trivia quiz: How many of you remember that the Lotus 1-2-3 manual referred to this type of address as an absolute reference, and that the leading technical newsletter for 1-2-3 users had the same name?

UndoChanges

Working from references to the new task worksheet, worksheet ResGen Parameters, and the RCScriptRangeCollection object,  this routine can undo every change made by the macro, leaving the workbook in a pristine state. Even if the RCScriptRangeCollection object exists, its properties are uninitialized until immediately after the related objects become part of the workbook.

The code is straightforward; its most noticeable quirk is the way it handles the Application.DisplayAlerts property, which covers the unlikely, though technical feasible, case in which the property is already OFF. If this happens to be the case, having it unexpectedly left ON could have devastating consequences for somebody else’s work. Hence, its state is tested. If it is initially ON, it is temporarily turned OFF, to prevent deleting the blank task worksheet from raising a prompt, then reinstated. If it is initially OFF, the worksheet is deleted without further ado, and it is left the way we found it.

VBScript
Public Sub UndoChanges(Optional ByRef pwksNewTaskData As Worksheet = Nothing, _
                       Optional ByRef pwksResGenMain As Worksheet = Nothing, _
                       Optional ByRef pclsRCScriptRangeCollection As RCScriptRangeCollection = Nothing)

    On Error GoTo UndoChanges_Err

    Dim fGoForNextStep As Boolean: fGoForNextStep = True

    '   ------------------------------------------------------------------------
    '   If the caller included a worksheet reference in pwksNewTaskData, it goes
    '   away.
    '   ------------------------------------------------------------------------

    If pwksNewTaskData Is Nothing And pwksResGenMain Is Nothing Then
        DoEvents
    Else
        pwksResGenMain.Activate                                                 ' Activate the main ResGen worksheet.

        '   --------------------------------------------------------------------
        '   Though DisplayAlerts is ON by default, in the unlikely event that it
        '   has been disabled, it is bad form to assume the flag is ON. Hence,
        '   this code tests the current state of the flag. If it is ON, it is
        '   temporarily turned OFF, to suppress a prompt for permission from the
        '   user to delete a sheet that he cannot possibly have yet seen, let
        '   alone populated. Otherwise, the sheet is deleted, and the prompt is
        '   left OFF.
        '   --------------------------------------------------------------------

        If Application.DisplayAlerts = True Then
            Application.DisplayAlerts = False
            pwksNewTaskData.Delete                                              ' Delete the new worksheet.
            Application.DisplayAlerts = True
        Else
            pwksNewTaskData.Delete                                              ' Just do it.
        End If  ' If Application.DisplayAlerts = True Then
    End If  ' If pwksNewTaskData Is Nothing And pwksResGenMain Is Nothing Then

    '   ------------------------------------------------------------------------
    '   If the caller specified range name pstrRCScriptRangeName, delete it.
    '   ------------------------------------------------------------------------

    If fGoForNextStep Then
        If Not pclsRCScriptRangeCollection Is Nothing Then
            If Not pclsRCScriptRangeCollection.StringDetails Is Nothing Then
                If UndoWorkbookRange(pclsRCScriptRangeCollection.StringDetailsRngNm) Then
                    DoEvents
                Else
                    fGoForNextStep = False
                End If  ' If UndoWorkbookRange(pstrRCScriptRangeName) Then
            End If  ' If Not pclsRCScriptRangeCollection.StringDetails Is Nothing Then
        End If  ' if not pclsRCScriptRangeCollection is nothing then
    End If  ' If fGoForNextStep Then

    '   ------------------------------------------------------------------------
    '   If the caller specified range name pstrRCSymbolsRangeName, delete it.
    '   ------------------------------------------------------------------------

    If fGoForNextStep Then
        If Not pclsRCScriptRangeCollection Is Nothing Then
            If Not pclsRCScriptRangeCollection.ResourceIDs Is Nothing Then
                If UndoWorkbookRange(pclsRCScriptRangeCollection.ResourceIDRngNm) Then
                    DoEvents
                Else
                    fGoForNextStep = False
                End If  ' If UndoWorkbookRange(pstrRCSymbolsRangeName) Then
            End If  ' If Not pclsRCScriptRangeCollection.ResourceIDs Is Nothing Then
        End If  ' if not pclsRCScriptRangeCollection is nothing then
    End If  ' If fGoForNextStep Then

    '   ------------------------------------------------------------------------
    '   There is always a column insertion to undo.
    '   ------------------------------------------------------------------------

    If fGoForNextStep Then
        If UndoColumnInsert() Then
            MsgBox MSG_TASK_CANCELED, _
                   vbInformation, _
                   ActiveWorkbook.Name
        Else
            MsgBox ERRMSG_002, _
                   vbCritical, _
                   ActiveWorkbook.Name
        End If  ' If UndoColumnInsert() Then
    End If  ' If fGoForNextStep Then

UndoChanges_End:

    Exit Sub

UndoChanges_Err:

    MsgBox "Error report from custom VBA function UndoChanges:" & vbLf & vbLf _
                & "Error " & Err.Number & " - " _
                & Err.Description, _
            vbExclamation, _
            ThisWorkbook.Name
    Err.Clear
    Resume UndoChanges_End

End Sub         ' UndoChanges

Listing 10 is all of function UndoChanges. Working with its helpers, UndoWorkbookRange and UndoColumnInsert (not shown), it can undo changes made by the macro at any stage from barely underway to almost done.

Closing Thoughts

This project reminded me why you must prepare to spend a lot more time than you think at first blush, especially given how quickly a task happens in the user inerface, and do your homework, before you commit to automating a seemingly simple task. I knew this going in, and I still hit a speed bump or two that made the project consume almost a week of virtually non-stop coding and testing cycles. It also reminded me that if you think 48 steps to brush your teeth and floss is an exaggeration, you have never thoroughly documented a procedure.

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, updated the workbook with a corrected version in which the starting resource ID cell is unprotected, consistent with its light green background color, and add the worksheet and VBA project passwords as comments in the document properties, so that they are always readily accessible when you use the workbook. The only change in the article is the addition of the overlooked Security Measures section about the worksheet and VBA project proection passwords.

Monday, 27 June 2016, restored image links that got broken during the submission process.

Saturday, 25 June 2016 is when I released this article to the CP editors.

License

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


Written By
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, Python, 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, reading, especially nonfiction and thoughtfully written, thought provoking science fiction

Comments and Discussions

 
GeneralAppreciation Pin
Member 1346757216-Oct-17 0:11
Member 1346757216-Oct-17 0:11 
GeneralMy vote of 5 Pin
_Vitor Garcia_13-Jul-16 2:03
_Vitor Garcia_13-Jul-16 2:03 
Not only because of the article but also because of the closing thought: if you think 48 steps to brush your teeth and floss is an exaggeration, you have never thoroughly documented a procedure.
Well Done !
GeneralRe: My vote of 5 Pin
David A. Gray13-Jul-16 7:15
David A. Gray13-Jul-16 7:15 
QuestionMy vote of 5 Pin
PeejayAdams28-Jun-16 5:27
PeejayAdams28-Jun-16 5:27 
AnswerRe: My vote of 5 Pin
David A. Gray28-Jun-16 6:24
David A. Gray28-Jun-16 6:24 
AnswerRe: My vote of 5 Pin
xavigonza30-Jun-16 2:48
xavigonza30-Jun-16 2:48 
GeneralRe: My vote of 5 Pin
David A. Gray12-Jul-16 8:25
David A. Gray12-Jul-16 8:25 
PraiseThanks Pin
Member 1260744527-Jun-16 21:58
professionalMember 1260744527-Jun-16 21:58 
GeneralRe: Thanks Pin
David A. Gray28-Jun-16 6:28
David A. Gray28-Jun-16 6:28 
GeneralMessage Closed Pin
19-Mar-19 1:03
Member 1418774319-Mar-19 1:03 

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.