MDUF

Concept of Operations

Before you dive into the details, it might be helpful to have a high-level view of what using MDUF is like—what the costs are, and what the benefits are, and more important, just exactly what’s involved in specifying a UserForm using MDUF.

Introduction

The fundamental principle of this Meta-Designer for UserForms (MDUF) is as follows. With MDUF, you can readily generate and maintain Excel UserForms using a nongraphical specification of a set of controls, the values of their properties, and associated code. You enter some of these property values into a conventional spreadsheet, and you edit others using UserForms. After you specify your UserForm in this way, MDUF can generate it and insert it into a target Excel workbook.

UserForms generated by MDUF are stand-alone and independent of MDUF. They don’t require any other part of MDUF to be present in Excel at runtime. Of course, your UserForms might require (and probably do require) your own code when they operate. As usual in Excel, you can enter this “back-end” into the code pane of the UserForm using the Visual Basic Editor. But in MDUF, you can also enter the code into the MDUF project workbook.

To use MDUF to design the UserForms for a target workbook, you work with three Excel workbooks.

The Project workbook contains UserForm specifications for as many UserForms as you like. Each UserForm is specified on its own worksheet (a “FormSpec” worksheet) in the Project workbook. The Project workbook does contain some macros, but only a minimal set, used mainly for editing FormSpec worksheets. MDUF users who support multiple projects can expect to have several Project workbooks.

The Target workbook is the workbook that hosts the UserForm you’re designing with MDUF. MDUF doesn’t impose constraints on the Target workbook, other than that it be macro-enabled, as would be any workbook that hosts UserForms. The target workbook must also contain the code (or references to code) that invokes the generated UserForm. That code also handles any events that result from user interaction with the UserForm. To support situations in which a given UserForm must be delivered to more than one workbook, FormSpec worksheets can specify any number of target workbooks. And the FormSpec worksheets of a given project workbook are independent of each other—they can have different target workbooks if necessary.

The MDUF add-in, the third workbook you use, is an Excel workbook in Excel Add-In format. If you install it as an add-in, and activate it, it remains out of sight and out of mind, except for the MDUF tab that appears in the Excel ribbon. The add-in contains the machinery that produces UserForms that are specified in the Project workbook. On demand, the FormMaker macros examine the Project workbook’s data. They use that data to generate and insert the specified UserForms into the Target workbook or workbooks. No project data resides in FormMaker. The MDUF user doesn’t modify the MDUF add-in in any way.

The MDUF add-in also contains machinery for harvesting information from Excel workbooks that contain legacy UserForms. This facility is used for creating MDUF representations of legacy UserForms when you want to use MDUF to maintain existing UserForms. Once you’ve captured a legacy UserForm, you can use MDUF for all future modifications of that UserForm.

Once you’ve defined a UserForm in a Project workbook, you can issue a command to generate the UserForm and insert it into the Target workbook (or Target workbooks). After that, you don’t need to edit the UserForm in Excel’s Graphical UserForm Editing Facility (GUFEF) (though, of course, you can do so if a GUFEF is available). Instead, if editing is necessary, you edit the FormSpec worksheets that define the UserForm or UserForms you want to modify. Then you regenerate the edited UserForms, which automatically displaces the previous versions. 

Some terminology

UserForm

Microsoft defines a UserForm object as, “a window or dialog box that makes up part of an application’s user interface.” In this Guide, we use the term a bit more narrowly to apply only to windows or dialog boxes defined by the user of Microsoft Office. That is, they are windows or dialog boxes defined by you or by users of your application.

oDialogBox

In this guide, an oDialogBox is a window or dialog box defined by Microsoft as part of Office. (The “o” stands for “Office”) For example, the VBA function InputBox displays an oDialogBox.

mDialogBox

In this guide, an mDialogBox is a window or dialog box defined internally by MDUF. (The “m” stands for “MDUF”) As it happens, all mDialogBoxes are defined using MDUF. They are all UserForms. 

Advantages of the MDUF approach

Using this approach is no more difficult than using the GUFEF available in most other versions of Office or Excel. It isn’t more difficult, but it is different. It offers significant advantages over Excel’s GUFEF, and some capabilities that Excel’s GUFEF doesn’t offer. Three examples of these advantages are as follows.

Relative positioning of UserForm controls

In Excel’s GUFEF, one sets the positions of controls within a UserForm by dragging their representations to the desired spot in a graphical representation of the UserForm. The tool that enables you to do this is called the Designer. It’s also possible to enter numeric values for a control’s left edge and top edge in the control’s Properties dialog. Analogous capabilities are available for width and height and many other properties.

But Excel’s GUFEF doesn’t offer a way of declaring, for example, “Place this ListBox beneath the Label named X”. One can perform this operation in Visual Basic for Applications, but doing so requires special knowledge and capabilities most UserForm designers either don’t have or don’t want to exercise.

MDUF offers a variety of placement macros that make relative placement easy. In MDUF you can declare the positions of controls relative to other controls, or relative to the edges of the UserForm. More

Single-point definitions of UserForm properties

For aesthetic reasons, it’s best to choose consistent values for certain properties of UserForms. For example, using only one font or a few font sizes for all or many of a UserForm’s controls is often wise. But in Excel’s GUFEF, you must specify the default font for every UserForm you create, if it differs from Excel’s default UserForm font. The controls you place on your UserForm then adopt the font and font size of the UserForm. So far, that’s reasonable.

But trouble arises for users of Excel’s GUFEF when they decide to change the font or font size of existing UserForms. Changing the font or font size for a UserForm has no effect on the font or font size of the UserForm’s existing controls. In that situation you must somehow set the font or font size for each control.

For Excel-based applications that have multiple UserForms, you must repeat this process, in some cases, for every control on every form; in other cases, for every type of control. This is inconvenient and a possible source of error unless you can write VBA code to perform the operations for you.

In MDUF, you can declare the font and font size for each control, on a control-by-control basis, if you wish, just as you would in Excel’s GUFEF. But in MDUF there is an alternative. You can declare a default font or default font size for an entire UserForm, or for an entire project and all its UserForms. To change a font or font size for an entire  UserForm, you change the contents of the very few relevant cells (as few as one or two) of the FormSpec worksheet. When you next regenerate the  UserForm, the changes take effect. To change a font or font size for an entire project, you change the contents of the very few relevant cells (as few as one or two) of the Project worksheet of the Project workbook. When you next regenerate the UserForms of the Project, the changes take effect.

In MDUF, other properties of UserForms and controls also have this feature. You can declare, in analogous ways, the values of properties for an entire project, or for an entire UserForm, by changing a single cell. This capability works for colors, spacing between controls, inner margins of UserForms, heights and widths of controls, and more.

Simplified updating of packages of UserForms

When the specifications for the UserForms (or a subset of the UserForms) of an Excel-based application reside in the same MDUF Project workbook, updating all of them is simply a matter of invoking the MDUF Ribbon command This Project>Generation>Generate Forms…. MDUF then presents an mDialogBox that lets you specify which UserForms you want to generate. After you select them, MDUF generates all of the selected UserForms in that MDUF Project workbook, inserting them into the target workbooks where they belong.

This capability makes it easier to adjust a color scheme, or widen the space around controls, or change a release number, or any of the other details that can be so labor-intensive in Excel’s GUFEF.

This process opens a new way of dealing with UserForms.

For example, suppose we need to insert similar but not identical UserForms into five Target workbooks. They might be bug report UserForms, or system version display UserForms—anything really. The UserForms differ only in some of the text they display, such as a company name or a release number. To do this tedious work, we create a macro that modifies the Project workbook and the other variable data for each iteration before generating the UserForm for each different target.

What makes such an approach possible is MDUF’s concept of a project. More about this in “Creating UserForms and Projects”. 

Converting legacy UserForms to MDUF

Some MDUF users have collections of workbooks with existing (legacy) UserForms created in Excel’s GUFEF. MDUF can capture these UserForms and create MDUF representations of them in a Project workbook. Users can then exploit the advantages of MDUF described in this guide. Then they can generate the UserForms and replace the legacy UserForms with the versions generated using MDUF. It’s a bit of work, but if maintenance of the UserForms is a significant anticipated future cost, the return on investment can be favorable. See “Overview of Capturing Legacy UserForms.” 

Converting UserForms from MDUF to Excel representation

If at some point you decide to adopt a different approach to creating and maintaining UserForms, you’ll need to extract your data from MDUF. To do that, just generate your UserForms. The generated UserForms are then in conventional Excel representation. You can use them as you would use any UserForms created in Excel’s Designer.