Accurate, Easy, Fast Form Design, with a Little Help from Excel

• Download TZINFO_Development_and_Testing_Worksheets.ZIP - 13.5 KB

Introduction

The Microsoft Excel worksheet described in this article is a variation on a design that I've used for years to accelerate development of programs that display forms or use string resources, either managed or unmanaged.  In other words, the same design is useful for work in C, C++, and C#, and I have employed it for projects implemented an all three languages. Among other things, it promptly pinpoints three key issues in the design stage, when they are easiest to correct.

1. Duplicate mnemonics (accelerator key assignments) embedded in labels. I am unaware of any tool that is built into a development environment that does so. Nevertheless, this worksheet even keeps track of the assignments for you.
2. Duplicate control names. While the editor warns about and corrects them, the corrections happen after the fact, and your design document instantly goes out of sync. This tool calls them to your attention when they are much easier to correct, before you input them into the property sheets attached to the form designer.
3. Duplicate string names. Again, although the editor warns, if you've ever tried to correct one in the middle of an edit, you know that it can be annoying, at the very least, and, again, your code goes out of sync with your planning documents.

None of the above,, however, is why I started using Excel to organize strings for C, C++, and C# programs. Rather, it was the development of an efficient tool for generating large numbers of Windows string resources for use as error messages and for sharing between projects, about which I first wrote last year in ""Collision Proof Shared String Resources."

Background

It is tempting to think of Microsoft Excel as the go-to tool for number crunchers, and it certainly is that. Nevertheless, software designers and others can put Excel to good use, and this is one of many such applications that I have found for the undisputed king of Windows spreadsheet programs.  Nevertheless, I have attempted to write in such a way that you should be able to understand it regardless of how familiar you are with Excel.

This worksheet leverages a number of features that go well beyond its number crunching prowess.

• Text Parsing. Worksheet formulas identify and display the accelerator key marked in a string by searching for the & signal character and extracting the following character.
• String Concatenation. Other cells construct variable names and even complete, valid C# statements that you can drop into the code behind your forms to populate the labels on your form from strings. Why do all that error prone, tedious typing when Excel can do it for you perfectly every time?
• Conditional Formatting. Three conditional formatting formulas built around named ranges do the lion's share of the work to bring the three key features enumerated in the introduction to life.

Using the code

The Microsoft Excel workbook that accompanies this article is a working document from a real project. Indeed, the project remains underway as I write this article, and the three radio buttons described on the worksheet have yet to be created.

.

Figure 1 is the worksheet as it appears when it is free of errors.

Before launching into a tour of the worksheet, a brief orientation is in order.

1. The upper left corner contains a legend of the three color codes that are applied to cells by the conditional formatting rules to bring errors to your attention.
2. The left to right order of the cells is by design, to facilitate input, especially of the critical string resource names and values. Thankfully, both the Windows resource editor in the Visual C++ toolchain and the managed resource editor in the Visual C# toolchain use the same basic layout.
3. Apart from endeavoring to use very distinct colors, the colors chosen for the three types of errors are arbitrary. If you prefer different colors, please feel free to edit the format in the conditional formatting rules.
4. Cells that have a light cross-hatch pattern are empty or have formulas that return the empty string, to call attention to overlooked cells that should be populated.
5. Cells that have a light green background are input cells. Cells of any other color contain formulas or labels, and should not be changed during normal operations.
6. Below each table is a row of dark brown cells containing yellow text. These cells mark the ends of named ranges that are critical to the correct functioning of the conditional formatting rules that higlight errors that need your attention. These protected rows are joined to prevent accidental input into the cells that belong to these ranges, which woud jeopordize their effectiveness. As a bonus, joining them prevents the long text in the left-most cell from interfering with the format auto-fit feature. Insert extra rows as needed above these rows.

Following is a quick tour of the features visiible in Figure 1 above.

• Column A, labeled Type, identifies the type of control described by the cells to its right. For a variety of reasons, not least of which is their sheer numbers, even neglecting your custom controls, along with the fact that their names play no role in the formulas, the types are unvalidated.  (I have developed other worksheets that used validated type names, when they played a role in generating object names.)
• Column B, labeled Name, is the place to enter the names of your controls. This worksheet lists labels on the left half of the active sheet, and the objects to which they apply on the right half. I shall say more shortly about why I designed it this way.
• Column C, labeled Accelerator, is the first of many computed columns. The forumulas in its cells identify the accelerator key in the text that appears in column E of the same row, pretty much the same way that the runtime engine does.
• Column D, labeled Resource String Name, uses a formula to derive a name for the string from that of the label to which it will be applied, (column B).
• Comumn E, labeled Text, should be self evident, but it deserves comment.. If the text of a label contains an embedded & character, the character that immediately follows it is designated as its nmemonic. Unless disabled (By default, this feature is on.), when you hold down the ALT key while you press the nmenmonic letter, a message goes to the form, which is expected to do somethng in response, such as select the adjacent text box or, in the case of a button, raise its Click event..
• Columns F and G, labeled Absolute Resource String Name and Assigment Statement for Form Load Event, respectively, employ string concatenation formulas to construct the fully qualified name of the application property that represents the string resource and, from that, a syntactically valid C# statement to use the string to set the labe's Text property. I know these work, because I pasted the entire column into the form's constructor, compiled the asswembly, and ran it. That's over a donen lines of C# code that I didn't have to write, and I've used this technique on forms that had many more labels.
• Columns H, I, and J, labeled TabIndex, LocationX, and LocationY,  were populated from the like named fields of the label's property sheet. I used these to get a quick view of where everything was in relation to each other, about which I shall explain in more detail at the end of this section.
• Column K, labeled TopToTop, uses a simple formula to compute the distance between the top of a control and the control directly above it, which gives me a quick read on whether the controls are evenly spaced.
• Column L, labeled Between, uses another simple formula to compute the distance between the top of the control and the bottom of the control directly above it. The values in this column and the one to its left are not directly available from the development environment, since they don't correspond to properties.
• Columns M and N, labeled Width and Height, were populated from the like named fields of its property sheet in the designer.
• Columns O through Y repeat the same information for the input controls, which are a mix of combo boxes, text boxes, and radio buttons. However, in place of the Accelerator and Resource String Name columns is a column of true/false values, labeled ReadOnly. I use this column to keep track of which text boxes I marked as Read Only because they are display only fields.
• On the left half of the sheet, just below the main table, is an area that begins in column B, with a column of joined cells labeled Message Mnemonic. Its alignment with the Name column above, and the fact that it overlaps the adjacent Accelerator column are no accident, since its purpose is to accept inputs from which to construct names for message strings that have a common prefix, which is the job of the formula in the cells of column D.
• The naming and purpose of the cells in the columns to the right of the merged cells of columns B and C is the same as it is for the rows in the wider table above it. Two things differ slightly.
  • The formula in the cells of column D applies a different prefix than does the formula in the cells directly above it, so that strings that go onto labels and messages that go into the user prompt text box are differentieated by their prefix. Though I considered setting aside a cell for the prefix, I decided against it, because it's easy enough to change the formula in the top cell, then copy it down, should the need arise to change it.
  • The formulas in the cells of column G, which construct a syntactically correct C# assignement statement, get the name of the object whose Text property to set from range txtMessageForUser, a one-cell named range that refers to the cell that contains the name of the read only text box designated to receive informational messages for the program's operator. Rename the text box, and you still get valid code.

You may wonder why I put all the effort into entering the tab orders, locations, and sizes of the controls, which is a fair question.

1. Relax; it wasn't as hard as it looks. When you save the design of Form1, Visual Studio stores the properties in Form1.Designer.cs, which you can open and view in either the Visual Studio code editor or your favorite C# aware text editor, such as Notepad++, or my favorite, UltraEdit. It's a lot easier to read and copy the values from a text file than it is to wade through the property sheet.
2. Although the designers offer decent tools for aligning objects to grids, I have found that they don't always get it to my satisfaction, especially when the row contains several kinds of controls of varying heights. Hence, I often look at the code in a text editor, and often use the editor to adjust the positons and dimensions of the controls (gasp!). I have discovered that it can take a lot less time to make the adjustments in a text editor than to navigate through the properties in the designer.

I laid out the sheet with labels described in its left half, and the associated controls on the right half, to make it easy to pan back and forth to view the details about a control and its label. It beats the pants off doing the same in the designer!

Before we return to base camp, you should see what happens when there are errors in your object names and accelerator key assignemnts.

Figure 2 shows how the worksheet calls attention to two controls that have been assigned the same name. Change either of them, and the error flags vanish.

Figure 3 shows how the sheet looks when a control and its label are assigned the same name. Since the duplicates occur in the same row, only one row is highlighted, which is your hint that the duplicate lies in the right half of the same row. For other projects, I have used various formulas to generate unique control names. This project has too few controls to warrant the effort.

Figure 4 shows how the worksheet warns of duplicate accelerators. To correct it, move the & character in front of a different letter of the cell in column E of either hightlighted row.

Figure 5 shows how the worksheet alerts you of duplicate string names. Notice that the duplicate is in the lower part of the worksheet; I had to intentionally corrupt a formula to make this happen, but I wanted to show that the validation spans both tables.

Points of Interest

Welcome back to base camp; it's cracker barrel time. Let's have some cheese, crackers, and fresh, hot coffee!

While most of the formulas that inhabit this workbook are straightforward, workaday worksheet formulas, a couple are sufficiently obscure to require explanation.

The formula that identifies your accelerator mnemonics from the text is moderately complex.

=IF(ISBLANK(E5),"",IFERROR(UPPER(MID(E5,FIND("&",E5)+1,1)),""))

• The net effect of this formula is as follows.
  • If the cell to its immediate right is empty, this cell is left blank.
  • If the text in the cell to its immediate right is devoid of an & character or the last character is &, the cell is left blank.
  • Otherwise, the cell contains the acceleration key, upper cased, for consistency.
• The outermost IF function is straightforward, yet its presence permits every row in this column to be populated, without cluttering the worksheet with stray text in unused rows.
• The value if false clause opens with the somewhat confusing IFERROR function, which returns the result of evaluating a function, unless that result is an error code (#ERROR!, #N/A!, #DIV/0! and #VALUE!), in which case, it returns the second argument (the empty string in this case). Think of it as a catch block for a worksheet function; when the expected substring is not found, the FIND function returns #VALUE!. Apart from that, FIND is straightfoward; scan the second argument, cell E5, for the first, or only, &. Otherwise, its return value invalidates the whole expression.
  • The value returned by FIND supplies the second of three arguments to a MID function that extracts the character immediately to the right of the first & character in the cell to its immediate right. When it succeeds, FIND returns the position, counting from 1, where 1 is the first character in the scanned string named in its second argument.
  • The first argument of MID is the string from which to extract a substring, which happens to be the string scanned by FIND, while the second argument is the position of the first character to include in the returned substring. Adding 1 to the value returned by FIND gives the position of the character that immediately follows it, which is the designated accelerator.
  • The third argument of MID is the number of characters to extract from the string; specifying 1 gets the desired accelerator, and nothing more.
  • The entire MID function is nested inside UPPER, whose meaning should be obvious.
• Since there is nothing to assign, the last character being an & does not define an accelerator. Since this condition causes MID to fail, the desired outcome is achieved, and no accelerator is displayed.

I don't know why the Microsoft Excel programmers designed FIND to return an error value when a simple zero would do, unless there is some ancient history (maybe a similarly designed fature of Lotus 1-2-3 or MultiPlan) behind it. It's been way too many years since the last time I did anything with Lotus 1-2-3, and I used MultiPlan once, for maybe an hour, even longer ago. In any case, it's too lste now to do anything about it, because there are far too many worksheets that depend upon its established, well documented behavior. At this point, a find function that returned zero must be a new function, with its own name and quirks.

The other noteworthy function is in the formula of the conditional formatting rule that highlights duplicate control names, and it's really complex.

In case you don't know, a conditional formula must return Boolean (True or False), a value of True causes the conditional formatting defined in the rule to be applied, and application is additive By additive, I mean that any attribute not specified by the rule is inherited from the underlying format. Hence, if you specify a background color but not a test color, the cell inherits the text color of the underlying cell format. Moreover, while you can apply styling attributes, such as bold, italic, and colors to the font, you cannot change its face or size. So, if the underlying cell is formatted as 10 point Consolas, you're stuck with it.

First, I'll show it the way it appears in the formula editor, then I'll lay it out more like a nested C/C++/C# function.

=AND(OR(LEN($B3)>0,LEN($Q3)>0),OR(COUNTIF(LabelNames,$B3)+COUNTIF(InputControlNames,$B3)>1,COUNTIF(LabelNames,$Q3)+COUNTIF(InputControlNames,$Q3)>1))

AND ( OR ( LEN ( $B3 ) > 0 ,
           LEN ( $Q3 ) > 0
         ) ,
      OR (   COUNTIF ( LabelNames        , $B3 ) 
           + COUNTIF ( InputControlNames , $B3 ) > 1 ,
             COUNTIF ( LabelNames        , $Q3 )
           + COUNTIF ( InputControlNames , $Q3 ) > 1
         )
    )

Before I explain this formula, I should explain that it could be simplified if COUNTIF worked with non-contiguous ranges. Unfortunately, I have proved to my satisfaction that a non contiguous range causes COUNTIF to return the dreaded #VALUE! just discussed in connection with IFERROR. So far as I know, this behavior has never been publicly documented until today. When I discovered it, I appended a note to the 4 year old BBS thread at http://www.mrexcel.com/forum/excel-questions/623620-countif-value-error.html that led me to the experiment that proved it to my satisfaction.

Cells in columns B and Q of each row are set aside for control names.

• Column B is reserved for the names of label controls.
• Column Q is reserved for the names of the controls to which the labels apply.

Since the objective is to use the same formula to highlight a whole row of cells, the column portion of addresses that refer to cells in the current row are made aboslute by preceding the column letter with a $. Conversely, the row reference is unadorned, so that the formula can be copied down the table. I assume you know that when a cell is copied down, unanchored references are adjusted based on the position of the destination cell relative to the origin cell. These rules apply as well to conditional formatting formulas.

1. First, both of two conditions must be true.
   1. At least one of the two cells in the current row that are set aside for control names must contain text.
   2. At least one of the non-blank control names must be a duplicate.
2. Using the LEN function to test the length of the contents of a cell is more robust than the traditional ISBLANK test, which returns FALSE for any cell that contains a formula. Since it contains a formula, the cell isn't technically blank, although most people would condider it so. On the rare occasion that I use ISBLANK, I almost always back it up with a length test, and many of my worksheets rely entirely on LEN, because ISBLANK went off my mental radar when I discovered how it really behaves.
3. COUNTIF is another somewhat obtuse function; its first argument is the range of cells to be counted, while the second argument is the criterion for counting. That is, count all cells in the range named in the first argument if they meet the criterion specified in the second. Contributing further to its obtueenes is the variety of ways in which the criterion can be specified; it can be a literal such as 10 or "Fred" or a relational expression such as ">=10" or a cell reference. This formula exercises the third, and simplest option; count cells if their value is equal to that of the specified cell.
4. The other tricky bit arises from the fact that the range we really want counted is not contiguous. However, the desired result is achieved by taking the sum of the counts over both ranges against the same criterion cell. If cell b3 contains FooBar and cell Q3 is eithr blank or contains BarFoo, the first count returns 1, while the second returns zero, unless another row in either column contains either FooBar or BarFoo.

The worksheet is protected against accidental changes. Should you need to do so, however, you are free to unlock it; the password is TheCodeProject.

History

This article was published on Saturday, 18 June 2016.

Pictures that went missing when the article was initially published should be restored.