MDUF

Preparing the CaptureWorkbook

In this User Guide, the Project workbook that holds the captured UserForms is called the CaptureWorkbook. To make a CaptureWorkbook, start with a standard Project workbook. You can use a copy of the Project workbook that was included with the MDUF distribution. If you’ve used that workbook to make a template for your organization or for your project, you can use instead one of those templates for your CaptureWorkbook. Or you might want to make a template workbook especially for use as a CaptureWorkbook for your current legacy conversion effort.

The four types of workbooks you’ll need

Converting legacy UserForms to MDUF representation requires four different kinds of workbooks.

LegacyWorkbook

The legacy workbook contains the legacy UserForms in a fully operational context. If this workbook is operational, for safety reasons, you probably want to work with a copy of the one people use. Because the capture process can be lengthy, spanning several hours per UserForm, it’s possible that the operational copy of the legacy workbook might change over the duration of the capture process. If that happens, you should probably keep updating the copy you use for capture.

CaptureWorkbook

The capture workbook is an MDUF Project workbook that contains the MDUF representations of all UserForms of the LegacyWorkbook. You’ll eventually use the CaptureWorkbook to generate UserForms to be used in your target workbook or workbooks. The CaptureWorkbook ultimately becomes the project workbook you will use to maintain the captured UserForms.

TargetWorkbooks

The target workbooks are the workbooks that will ultimately host the UserForms generated from the CaptureWorkbook. Typically, there is only one target workbook, and it’s actually a copy of the LegacyWorkbook. After conversion, you use the CaptureWorkbook to make changes in the UserForms, and you use the target workbooks to host the operational UserForms that you generate from the CaptureWorkbook.

TestWorkbook

The TestWorkbook is used for testing the generated UserForms produced from the CaptureWorkbook. Typically, the TestWorkbook is a copy of the LegacyWorkbook.

The TargetWorkbook and the TestWorkbook both must support the UserForms. You can create them specially for this purpose, but typically they are just copies of the LegacyWorkbook. It isn’t advisable to use the actual LegacyWorkbook for these purposes, because MDUF overwrites the legacy UserForms when it generates new UserForms from the data in the CaptureWorkbook.

It might be tempting to create aliases in the MacOS Finder to eliminate the need to copy the LegacyWorkbook. Resist the temptation. Since VBA cannot understand Finder aliases, such a scheme won’t work. That’s fortunate in this case, because if it did work, MDUF would overwrite the legacy UserForms when it generates new UserForms from the data in the CaptureWorkbook. And that could render the LegacyWorkbook unusable.

Tip: Naming these workbooks

Managing four types of workbooks can get confusing, especially if you divide the work across the members of a team, or if you have a sequence of conversions to perform. To limit the risk of confusion, consider using standard names for the four workbooks. For example, call the legacy workbook Legacy.xlsm, the capture workbook Capture.xlsm, and so on. Since you might need different versions as the work progress, call them Legacy-01.xlsm, Legacy-02.xlsm, etc.

In some cases, the work you performed for one conversion project can be used for another. When that happens, you might want to load into Excel two workbooks from two different conversion projects. Remember that Excel can’t load two workbooks with the same filename. One workaround for this restriction is to make a copy of one of the workbooks and give the copy a non-clashing name. If you need to do this frequently, you can eliminate that step by prefixing your standard names with a short project identifier. So if the project identifier is Wxyz, your standard legacy workbook names would be Wxyz-Legacy-01.xlsm, Wxyz-Legacy-02.xlsm, etc. And your standard capture workbook names would be Wxyz-Capture-01, Wxyz-Capture-02, etc. 

A synchronization issue

The fact that the TargetWorkbook and the TestWorkbook are copies of the LegacyWorkbook creates some synchronization issues. Specifically, in some cases, the LegacyWorkbook is undergoing modification during the transition period when its UserForms are being captured in MDUF. If that’s happening, it might be necessary to refresh the copies of the LegacyWorkbook (namely, the TargetWorkbook and the TestWorkbook) when the LegacyWorkbook undergoes change. To be safe, it might be wise to create an agent that checks the modification dates of these files to ensure that you’re informed of the need to update the TargetWorkbook or the TestWorkbook. You can do this easily in AppleScript or even VBA. 

Forms-capture settings

Before you can capture legacy UserForms, the CaptureWorkbook must contain information that MDUF uses to locate the UserForms you’re trying to capture. This information is referred to collectively as the forms-capture settings. After you create the forms-capture settings, you can capture the UserForms so specified, either one at a time, in small groups, or all at once.

Later, when you generate the captured UserForms using MDUF, the CaptureWorkbook needs to contain the information required for generating the UserForms. This information is referred to collectively as the forms-generation settings.

This portion of this User Guide discusses only the forms-capture settings. With the exception of legacy code considerations, forms-generation settings for captured legacy UserForms are no different from the settings for any other MDUF UserForms. For guidance in defining the settings unrelated to code, refer to the appropriate sections elsewhere in this User Guide. For matters relating to captured legacy code, see “Dealing with captured legacy code”.

Integer sizes and position coordinates

The section of the Project worksheet that holds properties for capture projects
The section of the Project worksheet that holds properties for capture projects

When MDUF captures the sizes (widths and heights) and positions (lefts and tops) of controls from legacy projects, the values returned are frequently non-integer. In most cases, these values aren’t intentional. That is, the UserForm wasn’t designed that way intentionally. These values are unnecessarily precise, and serve merely to clutter the MDUF representation of the UserForm. You can direct MDUF to round off these values to integers by setting the option Project!CaptureIntegerSizesAndPositions to TRUE.

In some instances, these non-integer values are intentional. So be certain that they are unintentional before exercising this option.

Capture colors only if necessary

Most controls, and the UserForm itself, have three color properties: ForeColor, BackColor, and BorderColor. The UCF (UserForm Capture Facility) can capture the values of these properties. But in most cases, these colors aren’t independent. That is, for example, in many projects, all controls (and the UserForm itself) have the same ForeColor. A similar configuration applies for BackColor and for BorderColor.

If this applies to your project, you can save some trouble during the capture process. Using the setting of the option CaptureControlColors on the Project worksheet of the CaptureWorkbook, you can arrange for smoothly capturing the colors of your project. Setting the value of this cell to FALSE prevents MDUF from capturing the colors of controls and UserForms.

To retrieve the exact color code from the legacy UserForms, capture them with CaptureControlColors set to TRUE. Copy the color codes to somewhere safe. Then discard what you’ve captured and recapture with CaptureControlColors set to FALSE. Enter the copied color codes into the Project worksheet and you’re done. See “Specifying colors for Projects“ for more.

Forms not to capture

The "Forms not to capture" section of the Project worksheet of a Project workbook
The “Forms not to capture” section of the Project worksheet of a Project workbook. To insert more rows, select a range that includes a cell in the range FormsNotToCapture. Then click the Insert Rows button. A number of cells equal to the number of rows in your selection will then be inserted.

The legacy workbook might contain some UserForms that you don’t want to capture. To inhibit their capture, enter their names in the range Project!FormsNotToCapture. If you need more cells in the range Project!FormsNotToCapture, select a cell in the range, and extend the selection down for a number of cells N. Click the button labeled “Insert Rows”. MDUF then extends the range by N cells.

Inhibiting UserForm capture is also useful when you’re working on converting the captured UserForms into MDUF representation, to exploit MDUF’s capabilities. For example, suppose you’re working on UserFormA, and you haven’t yet started to convert UserFormB or UserFormC. If you recapture the project (for example, to get the latest versions), MDUF might overwrite UserFormA, and you could lose the work you’ve done on UserFormA. You can always de-select UserFormA during the capture process, but this can be annoying, and you might forget to do it. Entering UserFormA in the FormsNotToCapture range prevents MDUF from trying to capture UserFormA. So your work on converting UserFormA is safe.

The Form.Capturable property of a FormSpec

After you capture a set of legacy UserForms, you might want to exploit the capabilities of MDUF to make the captured UserForms easier to maintain. It is recommended that you approach this form-by-form (FBF) within the captured project. The alternative would be to address the problem property-by-property (PBP). FBF is a more manageable approach, because it’s easier to remember what you’ve converted and what remains to be converted. And remembering that is important because you might need to recapture the UserForms before the job is complete. This happens when you realize that something is amiss or something can be improved.

To support the FBF approach, each FormSpec has a cell named Form.Capturable. It can contain either TRUE or FALSE. If FALSE, then when you recapture the project, MDUF skips over that FormSpec, to avoid overwriting all the work you’ve done. 

Specifying the LegacyWorkbook

On the Project worksheet of the CaptureWorkbook, find the cell that’s captioned “LegacyWorkbook”. This cell specifies only one workbook—the workbook that contains the legacy UserForms you want to capture. If the LegacyWorkbook is located where you keep the CaptureWorkbook, you can just enter the filename of the legacy workbook. But if the LegacyWorkbook is somewhere else, you have two choices.

Specify the LegacyWorkbook by filename only 

Entering the name of the LegacyWorkbook into the cell Project!LegacyWorkbook does work, as long as the workbook is already loaded into Excel when you invoke the command to capture its UserForms. This option is very convenient when the LegacyWorkbook resides somewhere different from the location of the CaptureWorkbook, and copying it to the location of the CaptureWorkbook is impractical. Copying can be impractical when the LegacyWorkbook is undergoing a stream of modifications for some reason. In that case, there is a risk that the copy might become outdated, and the capture process would then lead to undesirable results. By simply loading the LegacyWorkbook from wherever it’s stored before the capture process begins, the synchronization issue is resolved.

Specify the LegacyWorkbook by full pathname

On the Project worksheet of the CaptureWorkbook, find the cell that’s captioned “LegacyWorkbook”. To specify the LegacyWorkbook by full pathname, enter the entire pathname of the LegacyWorkbook, as in /Users/rbrenner/legacy-workbook.xlsm. Or you can enter the relative pathname of the LegacyWorkbook, relative to the location of the CaptureWorkbook. For example, if the full pathname of the CaptureWorkbook is:

 /Users/rbrenner/capture-one/target-ux.xlsm

and the LegacyWorkbook is:

/Users/rbrenner/legacy-one/source-ux.xlsm

you would enter into the LegacyWorkbook cell:

../../legacy-one/source-ux.xlsm

Alternatively, since in this example, the LegacyWorkbook is in rbrenner’s home directory, you could enter:

~/legacy-one/source-ux.xlsm

MDUF understands relative pathnames that contain “../” for going up one level in the directory, “~” for indicating your home directory, and “*” for wildcard characters. In addition, MDUF interprets the at sign (“@”) as the path of the directory of the active project. Thus @/Wxyz.xlsm is interpreted by MDUF to be the FullName of the MDUF Project workbook whose filename is Wxyz.xlsm. 

Forms-capture settings for multiple simultaneous captures

The UCF (UserForm Capture Facility) supports capturing UserForms automatically from multiple workbooks into the same CaptureWorkbook. There are some constraints, but they are minimal.

  • You can specify each of the legacy workbooks as you would specify them if performing a capture from a single workbook. 
  • Enter the file specifications of the legacy workbooks into the LegacyWorkbook cell of the Project worksheet of the CaptureWorkbook. Separate the file specifications of these workbooks with commas. You must include the filename extensions (.xlsm or .xlam) in the file specifications.
  • The names of the legacy workbooks cannot be MacOS Finder aliases.
  • If the names of the UserForms you intend to capture from the legacy workbooks aren’t unique, the last UserForm encountered for a given name is the only UserForm of that name whose data is retained. That is, suppose a UserForm called Foo exists in LegacyBook1, and you intend to capture it. Then, if the UCF encounters another UserForm named Foo from any of the other legacy workbooks from which you’re capturing UserForms into this CaptureWorkbook, MDUF overwrites the data from the earlier UserForms. To deal with this problem, use additional CaptureWorkbooks. 

One note of caution

When you set the target workbook for a captured legacy UserForm, you eventually want it to be the same as the legacy workbook. But after you set the target workbook to be the legacy workbook, then any time you generate the UserForm, it replaces the UserForm of the same name in the target workbook. If the target workbook is the same as the legacy workbook, MDUF overwrites the legacy UserForm, which probably isn’t what you want. See the section, “Creating the TestWorkbook”.