Introduction

This sheet shows how you can read the data in the steel shapes data file distributed by the AISC. It is completely data driven, so that all information about the steel shapes is in the Excel files and not in the worksheet.

Here are two list boxes. The first one shows a list of all of the available file names, which should be chosen to be meaningful. When you select one of the files, the second list box is populated with a list of the shapes that can be found in the selected file. Selecting a shape in this list box causes all of the variables from the Excel worksheet to be defined (including their units as listed in the Excel file) for further use. The selections are persistent. When first opening this sheet the file and shape selections remain as they were when the sheet was last saved.

Because the file format is generic, it may apply to data sets from other sources or you own data sets. The first row is seen as a header or or comment thus is ignored. The second row is taken as a list of variable names to be defined. The third row is taken as a description of the units for each variable. The remaining rows (four onwards) are the data rows. The first column is taken as the shape name and used as the key for user selection.

The following area contains some initialization parameters and the memory units. It can usually be left collapsed, as there is nothing that is generally needed to be seen here.

Data Directory

The following names define the location of the data files. They are all in a single directory. BaseDir is the path to that directory, SubDir is the name of the directory itself (it could also be a relative path). Splitting the specification this way and using CWD as the path, enables you to specify the data directory in the same directory as this worksheet.

Memory

The following two text boxes are the memory units. While it is possible to pass additional parameters and use a single text box, here, with only two strings to be remembered, it's easier to just have two identical text boxes. The scripts are very simple. Each text box is set up as a function with two input parameters: a string and a command. If the command is 1 (write) it stores the string in a static variable (Data) and also in the text control. Regardless of the command, the text box returns the value of the static variable. During initialization Data is set to the contents of the text box. Thus the function always returns the last stored value, whether after or before loading the sheet. The actual script is listed at the bottom of this article, along with the scripts for the other scripted objects.

User Interface

The two list boxes below are the user interface, where you can select the desired shape.

File Selection

The first box is a simplified version of the file selection from Xavier Colonna. The list box takes as inputs the directory path, the directory name, and the (saved) selected file name. All of the significant calculations are done in the Exec routine.

The script starts by combining the two pieces of the directory name (with required delimiters) to get the full pathname. It then compares the pathname to the previous value of that path. If it has changed (as it does the first time around) the script needs to rebuild the selection list.

To do so the script creates a new instance of a file system object. From that it gets the folder object representing the data directory. It then loops through all the files in the directory and extracts their name. After getting the list of file names it loops through the names looking for valid files. For now a file is valid if it has a .xls extension. All valid file names (with the extension removed) are placed in the selection list. It then attempts to select the previously selected file name. If it cannot access the directory, or it does not find any valid files, suitable error messages are displayed.

It then outputs the directory name (to avoid having to recalculate that elsewhere) and the selected file name.

Shape Selection

The second box lists the shapes available in the selected file and allows you to choose one of them. The script takes as inputs the selected file name (which is also saved here), the data directory name, and the previously selected shape name.

The main routine is the Exec subroutine. It starts by combining the directory name and the file name (with the implicit extension) to get the full name of the data file. If there is no selected filename it just clears the list and terminates. It then uses the full file name to create an Excel workbook object. From that it extracts the worksheet object (using the first sheet in the book) and the Excel application itself.

If the file name has changed, it calls the FillList routine to populate the list box. This routine loops through the values in the first column of the sheet (ignoring the first three rows) and put those values into the selection list (stopping if an empty cell is found). It also attempts to select the previously selected shape.

It then returns two values, an array containing all the data, and the selected shape name. The function GetValues is used to get the appropriate values. It extracts rows two and three of the sheet, along with the row for the selected shape, and combines them into a three row array containing the variable names, the variable units, and the data values. Note that while in general VBS arrays have a zero lower bound, the arrays returned by Excel have a lower bound of one.

Choose Steel Shape:

The Definer

This innocuous looking text box actually does all the definitions. It takes as inputs the three row matrix (names, units, and values) output from the shape selection box and the name of the selected shape (mostly for checking, but also a convenient place to implement the saving of that selection). The actual script is quite large (you can see it at the end of the Mathcad file) and is not described in detail.

The underlying principle is to build the XML representation of an assignment statement for the values, and assign that XML to a region. While the previous selection code works in Mathcad 11, this assignment process only works in XML based versions of Mathcad (Mathcad 12+).

The unit processing capability of this control is actually well beyond that needed for the provided sample files. It should cover unit specifications found in many similar files. If you look through the script you can see provision for complex numbers. This is not applicable to this application, but the script is just slightly modified from a more general sheet.

A unit specification can be an empty string. In this case, no unit is applied to the value, and the value is used as a string or a number, depending on the input format. Or a unit specification can be the string "text." In this case the value is used as a string (no matter how input), and no unit is appended. Or a unit specification can be a unit description. In this case the value is used as a number (no matter how input -- if it cannot be converted to a number it is left as a string and an error occurs in the definition), and the unit is appended to that value.

A unit description starts with a unit name. A unit name is a sequence of one or more alphabetic characters (a-z, any case) or the special character °. A unit name must be defined in Mathcad. It may be the name of a unit or other Mathcad defined value, or it may be a user defined name. The names are referenced in the constant style, and so for consistency should be defined in that style. A unit name may be followed by an exponent, consisting of one (usually) or more decimal digits, with an optional leading "-". The exponent need not be separated from the name, but may be separated by any number of spaces and/or a single "^". The exponent is the power to which the unit is raised. A unit name may also be just a single character "1", with no exponent. A unit set may be constructed as a sequence of unit names, with their optional exponents, separated by any number of spaces and/or a single "*" or "-". Such a set describes the product of the individual unit names, each taken to the specified power. A unit description may be a single such unit set, or may be two such unit sets separated by a single "/" (with any number of optional spaces around it). The unit set preceding the "/" becomes the numerator of the unit fraction while the unit set following the "/" becomes the denominator of that fraction.

The general structure of the variable definition part of the script is to build an XML string by concatenation. A routine for a particular structure concatenates the opening tag, then adds the content (by calling suitable subroutines) and finally adds the closing tag.

The unit strings are parsed out using regular expressions. You can get a complete description of the regular expression processor at the Microsoft web site. Note there are two versions of the regular expression processor, and the expressions used here use features of the more advanced one. If you do not see a description of these features you are looking at documentation for the older, simpler processor.

The CalculateShape routine is mostly for aesthetics. It calculates an array shape that keeps the assignment roughly square. The assignment could be done as a single column or a single row, but that would not look anywhere near as neat.

This is the actual assignment statement. What is important is that it be a math region with a tag of "AISCAssign" (which is hardcoded into the assignment text box). The actual contents don't matter much and are replaced by the assignment script whenever a selection is made. Due to a restriction in Mathcad's automation interface, it cannot be hidden in a collapsed region, but must be visible.

See the Mathcad file for the actual scripts.

Was this article helpful? Let us know.