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.
- 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.
- The project into which the resources were to be incorporated had neither a stringtable nor a resource script.
- 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.
- Provide better instructions for day to day application of the workbook in the real world.
- 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.
Figure 1 it the main Resource Script Generator worksheet, ready for a new task to be created.
Figure 2 is the prompt displayed by Microsoft Excel when you press trl-Shift-N to create a new resource profile.
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.
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.
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.
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.
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.
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.
- The script file is named $$ReadOnlyResourceScriptName$$.RC2.
- 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. |
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.
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 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.
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.
- 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. - 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.
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
Else
If KeepOrDiscardLine(strLine, pPreserveBlankLines) Then
If Len(strWork) = LENGTH_OF_EMPTY_STRING Then
strWork = strLine
Else
strWork = strWork & vbCrLf & strLine
End If
End If
End If
Next
If pfLastNewlineDisp = LAST_LINE_DLM_KEEP Then
LoadTemplateFromRange = strWork & vbCrLf
Else
LoadTemplateFromRange = strWork
End If
Listing 3 is the improved main loop in function LoadTemplateFromRange.
Private Function KeepOrDiscardLine(ByRef pstrSourceCodeLine As String, _
ByVal pPreserveBlankLines As Boolean) _
As Boolean
Const MARKER_TOP As String = "This cell marks the top of a range."
Const MARKER_BOTTOM As String = "This cell marks the bottom of a range."
Const MARKER_TOP_LENGTH As Integer = 35
Const MARKER_BOTTOM_LENGTH As Integer = 38
Select Case Len(pstrSourceCodeLine)
Case LENGTH_OF_EMPTY_STRING
KeepOrDiscardLine = pPreserveBlankLines
Case MARKER_TOP_LENGTH
If pstrSourceCodeLine = MARKER_TOP Then
KeepOrDiscardLine = False
Else
KeepOrDiscardLine = True
End If
Case MARKER_BOTTOM_LENGTH
If pstrSourceCodeLine = MARKER_BOTTOM Then
KeepOrDiscardLine = False
Else
KeepOrDiscardLine = True
End If
Case Else
KeepOrDiscardLine = True
End Select
End Function
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).
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.
- 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. - 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.
- 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. - The next test turns
IsSheetNameAvailable upside down by asking it to raise a red flag if the stringtable template worksheet is missing. - 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. - 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. - 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. - 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. - 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.
Private Function GetNewTaskName() As String
Const MAX_SHEETNAME_LENGTH As Integer = 31
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
Case vbCancel
GetNewTaskName = vbNullString
fNameInHand = True
End Select
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
Case vbCancel
GetNewTaskName = vbNullString
fNameInHand = True
End Select
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
Case vbCancel
GetNewTaskName = vbNullString
fNameInHand = True
End Select
End If
If fGoodCandidate Then
If IsSheetNameAvailable(strCandidateName) Then
GetNewTaskName = strCandidateName
fNameInHand = True
Else
Select Case MsgBox(Replace(MSG_UNAVAILABLE, _
TOKEN_CANDIDATE, _
strCandidateName), _
vbOKCancel Or vbExclamation, _
ActiveWorkbook.Name)
Case vbOK
DoEvents
Case vbCancel
GetNewTaskName = vbNullString
fNameInHand = True
End Select
End If
End If
Case vbBoolean
If CBool(varCandidateName) Then
Select Case MsgBox(MSG_UNEXPECTED_VALUE, _
vbOKCancel Or vbCritical, _
ActiveWorkbook.Name)
Case vbOK
DoEvents
Case vbCancel
GetNewTaskName = vbNullString
fNameInHand = True
End Select
End If
GetNewTaskName = vbNullString
fNameInHand = True
Case Else
MsgBox Replace(MSG_UNEXPECTED_TYPE, _
TOKEN_BAD_TYPENAME, _
TypeNameForVariant(VarType(varCandidateName))), _
vbCritical, _
ActiveWorkbook.Name
End Select
Loop Until fNameInHand
End Function
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.
Public Function IsSheetNameAvailable(ByRef pstrProposedName As String) As Boolean
If Len(pstrProposedName) = LENGTH_OF_EMPTY_STRING Then
IsSheetNameAvailable = False
Else
On Error GoTo IsSheetNameAvailable_Err
IsSheetNameAvailable = True
Dim wksCurrent As Worksheet
For Each wksCurrent In ThisWorkbook.Worksheets
If wksCurrent.Name = pstrProposedName Then
IsSheetNameAvailable = False
Exit For
End If
Next
End If
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
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.
- Most of the code in this routine is devoted to verifying the integrity of the worksheet, of which there are two aspects.
- Is the geometry of the range correct, indicating that its two boundary rows are intact, and that no columns have been added to it?
- Is the label row intact (unchanged from its design time value), indicating that the sheet is unused?
- 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] - 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.
Public Function MakeRangeNameConformant(ByRef pstrCandidate As String) As String
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)
If IsNumeric(strCurrChar) Then
strTemp = CHAR_UNDERSCORE & strCurrChar
Else
If CharIsValidinRangeName(pstrCandidate, INSTR_START_AT_BEGINNING) Then
strTemp = strCurrChar
End If
End If
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
Next
MakeRangeNameConformant = strTemp
Else
MakeRangeNameConformant = vbNullString
End If
End Function
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.
Private Function CharIsValidinRangeName(ByRef pstrCandidate As String, _
ByVal pintPosition As Integer) _
As Boolean
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
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
End Select
End Function
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).
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.
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 pwksNewTaskData Is Nothing And pwksResGenMain Is Nothing Then
DoEvents
Else
pwksResGenMain.Activate
If Application.DisplayAlerts = True Then
Application.DisplayAlerts = False
pwksNewTaskData.Delete
Application.DisplayAlerts = True
Else
pwksNewTaskData.Delete
End If
End If
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
End If
End If
End If
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
End If
End If
End If
If fGoForNextStep Then
If UndoColumnInsert() Then
MsgBox MSG_TASK_CANCELED, _
vbInformation, _
ActiveWorkbook.Name
Else
MsgBox ERRMSG_002, _
vbCritical, _
ActiveWorkbook.Name
End If
End If
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
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.
