MDUF

Creating the TestWorkbook

After capturing the legacy UserForms into the CaptureWorkbook, the next phase involves generating the UserForms into the TestWorkbook, which contains everything needed to verify that the generated UserForms look right and behave as expected.

Introduction to TestWorkbooks

Probably the simplest approach to creating the TestWorkbook is to copy the LegacyWorkbook, which obviously has all infrastructure needed for operation of its UserForms. But any workbook that contains the necessary infrastructure will suffice.

Even so, there is a persuasive argument for producing the TestWorkbook by simply copying the LegacyWorkbook. Upon inspecting the MDUF representation of the legacy UserForms, the need for some changes might become evident. Examples include typographical errors, inconsistency of control names, and prefixing control names.

But here’s the hitch: you must duplicate in the TestWorkbook whatever changes you make in the LegacyWorkbook. If the TestWorkbook is a copy of the LegacyWorkbook, then we need make each revision only once—in the LegacyWorkbook. After that, we just copy the LegacyWorkbook to create the revised TestWorkbook. But if the TestWorkbook is created independently, any revisions must be performed twice: once in the LegacyWorkbook, and once in the TestWorkbook.

Tip: If you elect to make the TestWorkbook by copying the LegacyWorkbook, it’s useful to have a way of being certain that the TestWorkbook is actually a copy of the current LegacyWorkbook. File modification dates are unreliable for this purpose, because the whole point of the exercise is to modify the TestWorkbook by inserting newly generated UserForms, which of course changes the modification date of the TestWorkbook. MDUF provides a solution. If you create a cell in your LegacyWorkbook that holds a version identifier, MDUF can compare the version identifiers of the TestWorkbook and LegacyWorkbook at UserForm generation time. It will alert you if they don’t match. To use this feature, ensure that your LegacyWorkbook has a worksheet named MDUFInfo, and name one of its cells Identifier.

Support for previewing generated UserForms

MDUF has a UserForm preview facility that makes previewing the UserForms you generate very convenient. See the section, “Conducting tests of generated UserForms“. To maximize convenience, you’ll need a module in the TestWorkbook to hold the preview macros. If you make the TestWorkbook by copying the legacy workbook, you must choose between two approaches:

  • Install a blank preview module in the legacy workbook.
  • After copying the legacy workbook to create the TestWorkbook, install the preview module in the TestWorkbook

If you expect to undergo many cycles of TestWorkbook creation, the former approach might be less burdensome. But in your work situation, you might not be able to modify the legacy workbook from cycle to cycle, due to external constraints. Consider carefully which approach fits better.

A word about modifying control names

Revisions of control names can be especially tricky because they usually entail revisions to the VBA code that references those controls or events associated with those controls. And if you use MDUF scripts, they must also be revised. Making changes in the LegacyWorkbook (and testing them) before capturing the UserForms ensures that the MDUF representation produced by the UCF faithfully represents the state of the LegacyWorkbook. 

Conducting tests of generated UserForms

Generated UserForms are the UserForms generated from the MDUF representations of the legacy UserForms. At first, generated UserForms reside in the TestWorkbook, until they’re checked out and approved for release. When approved, you then change the CaptureWorkbook’s target to the TargetWorkbook, and regenerate the captured UserForms one last time for release.

The TestWorkbook is likely a copy of the TargetWorkbook, in turn, likely a copy of the LegacyWorkbook, possibly with some minor modifications. If you’re capturing legacy UserForms, the generated UserForms should pass any tests you used on the legacy UserForms to determine that they were working properly.

But before you conduct tests of UserForm behavior, it’s useful to verify that the generated UserForms have the right appearance. All you need do to verify the appearance of a generated UserForm is display it and inspect it. The FormSpec worksheet has a cell named Form.ShowMacro that helps to make these “appearance checks” convenient. To use this mechanism, follow these two steps:

  • Write a “preview macro” for each generated UserForm. The preview macro shows the UserForm, and before doing so, it must provide any context the UserForm requires before it can be displayed.
  • Enter the name of the preview macro into the cell named Form.ShowMacro on the FormSpec worksheet.

MDUF provides some machinery that makes these two steps a bit less troublesome. This machinery relates to naming the preview macros, writing them, and invoking them. 

Preview macros

Preview macros enable you to preview generated UserForms outside their ordinary usage context. This speeds the appearance verification process because it reduces the need to create (possibly complex) test environments.

Naming preview macros: the standard pattern

Suppose there is a UserForm called frmFoo. Its preview macro name could be any legal VBA macro name. But let’s suppose that you gave the name PreviewFrmFoo to the preview macro for frmFoo. If the preview macro resides in a module called Mod, then you would enter into the cell named Form.ShowMacro the string Mod.PreviewFrmFoo .

You must enter these strings into the cell Form.ShowMacro for every UserForm you want to preview.

But MDUF has a shortcut. The default module name for Preview modules is “Previews”. And the default prefix for preview macro names is “Preview”. So if you leave blank the cells Form.ShowMacro on every FormSpec worksheet, and leave blank the cell Project!NameOfPreviewsModule, and name your preview macros according to the pattern Preview<FormName>, then MDUF will be able to find your preview macros on its own. To tell MDUF to do so, take these steps:

  • Make certain that the cell Form.ShowMacro on the UserForm’s FormSpec worksheet in the CaptureWorkbook is empty.
  • Make certain that the cell Project!NameOfPreviewsModule in the CaptureWorkbook is empty.
  • Make certain that the cell Project!PreviewMacroNamePrefix in the CaptureWorkbook is empty.
  • Name your preview macros in the TestWorkbook according to the pattern Preview<FormName>.
  • Write your preview macros

Naming preview macros: the customized approach

In some instances, the shortcut approach above doesn’t provide enough flexibility. For those cases, there are some alternatives that give you greater freedom.

  • If the name of the module that holds the previews macro is anything other than “Previews”, you must enter it into the cell Project!NameOfPreviewsModule of the CaptureWorkbook.
  • Make certain that the cell Project!PreviewMacroNamePrefix of the CaptureWorkbook contains the string to be used as the prefix for the name of the preview macros for all UserForms you want to preview.
  • If the cell Project!PreviewMacroNamePrefix of the CaptureWorkbook contains <Prefix>, and if the name of the preview macro for UserForm frmFoo is anything other than <Prefix>FrmFoo, enter that name into the cell Form.ShowMacro on the FormSpec worksheet for the UserForm frmFoo.

In this customized approach, as in the standard pattern above, all preview macros of the TestWorkbook must reside in the module whose name is in the cell Project!NameOfPreviewsModule. If a macro needs to reside elsewhere, indicate the name of its module in the cell Form.ShowMacro. For example, if the preview macro for frmFoo is named Bar, and if it resides in module Mod, then Form.ShowMacro should contain Mod.Bar.

So the next step is writing the preview macros.

Writing the preview macros

The preview macros are relatively simple. In fact, some of them might already exist as part of the system in which your UserForms reside. For example, to use some UserForms, all you need do is present them. For these UserForms, we still write preview macros, because the existence of a standard programming interface for all UserForms makes MDUF more maintainable, and, even more important, it makes MDUF easier to use.

Suppose frmFoo is a UserForm of this especially simple kind. And suppose you decide to use the default naming conventions for preview macros. Then the preview macro, which resides in the Previews module of the TestWorkbook, would be

Sub PreviewFrmFoo()
    frmFoo.Show
End Sub

For a more complicated case, where some initialization is required before showing the UserForm, the preview macro might be:

Sub PreviewFrmFoo()
    <do some stuff>
    With frmFoo
        <do some more stuff>
        .Show
    End With
End Sub

MDUF cannot write your preview macros for you. But it can write frameworks that you can edit. The framework for this example looks like this:

Sub PreviewFrmFoo()
    With frmFoo
        
        .Show
    End With
End Sub

If the active workbook is a Project workbook, the MDUF ribbon command This Project>Emit Preview Code… causes MDUF to present an mDialogBox that lets you select the UserForms for which you want preview code. When you click OK, MDUF emits a text file that contains preview macro frameworks for the UserForms you selected. You can choose the location and filename for this text file. When it’s complete, you can open it in a text editor, copy it, and insert its contents into the module where you keep the previewers for the active project. You can then edit the new frameworks as needed.

Tip: When you captured the UserForms into the CaptureWorkbook, you also captured the back-end code for your UserForms into the CaptureWorkbook. Look in the UserForm Code Block of the FormSpec worksheet. Some pieces of this code might be needed for the preview macro for that UserForm. Instead of writing it from scratch, consider using pieces of what you find in the UserForm Code Block.

Tip: If you made your TestWorkbook by copying the LegacyWorkbook, insert the preview frameworks into the LegacyWorkbook, make the edits, and re-create the TestWorkbook. If you do this work in the TestWorkbook, then you’ll lose it when next you copy the LegacyWorkbook to make a new TestWorkbook. 

Previewing UserForms

To preview a UserForm, invoke the preview macro you’ve written for it. To do that, activate the Project workbook and invoke the MDUF ribbon command  .

MDUF then displays the mDialogBox titled “Choose Forms”. It contains a ListBox that displays the names of all UserForms in the active project. Select the form or forms you want to preview and click “OK”.

Use this command when you want to see what the UserForm looks like, or you want to test its behavior. For example, after you make an adjustment, or after you add some controls, you might want to review progress. If the TestWorkbook isn’t already loaded in Excel, Excel first loads the TestWorkbook. If the TestWorkbook doesn’t exist, or if the UserForm doesn’t exist in the TestWorkbook, MDUF posts an error message.

To preview an entire project, select all the items in the ListBox and click “OK”.

If you’re previewing several UserForms, and you want to abort the preview, press and hold the Escape key.