After you capture a legacy UserForm’s data, and after it’s stored in a FormSpec worksheet, you might want to make adjustments to exploit MDUF’s relative positioning of controls. You can do this manually, or you can use MDUF scripting.

• Introduction to scripting
• Applying scripts to FormSpec worksheets
• Overview of the scripting language
• Scripting commands for infrastructure
• Scripting commands for layout
• Stacks
• Sequences
• Scripting macros
• Mapping over lists

Introduction to scripting

The main advantage of using scripts to express relative positioning of controls is that scripts save effort. They save effort in two situations. First, the process of capturing legacy UserForms is often iterative. That is, you might want to make changes in the legacy UserForm and recapture it. If that need arises, you can just re-run the script to re-install the position relations you had before the recapture. Second, when developing a new UserForm, writing scripts can be more efficient than entering formulas in FormSpec worksheet cells.

All scripts reside in the folder “scripts” that you create as a sibling of the Project workbook. So every project has its own scripts folder. Typically each script operates on one UserForm. To limit confusion, the script for a UserForm named “Foo” would be “Foo.txt”.

Applying scripts to FormSpec worksheets

To apply an MDUF script to one or more FormSpec worksheets of the active project, use the MDUF ribbon command This Project>Run Scripts… . MDUF then presents an mDialogBox titled “Choose Scripts to Run”. Select the scripts you want to run and click “OK”.

MDUF then determines whether a file called scripting-init.txt exists in the folder “scripts” that is a sibling of the folder that holds the active Project workbook. If it finds that file, it processes it first, then processes the file(s) you selected. This enables you to have a file of script macros that multiple script files use, avoiding the need to copy the macros into every script.

If MDUF doesn’t find scripting-init.txt in that place, it looks in ~/Library/Application Support/MDUF. If the file exists there, MDUF uses it. The buttons captioned “Select All” and “Deselect All” have obvious effects. In some cases, it’s easier to Select All and then deselect the scripts that don’t apply. The button “Matches Only” selects all scripts whose names “match” worksheet names in the active workbook. Here “match” means that if the script name is Foo.txt, there is a worksheet named Foo.

Invoking the MDUF ribbon command [mribbonanchor path=”This|Project>Run|Script|Again”] causes MDUF to run again whatever script it last ran. So if you selected multiple scripts in the above mDialogBox, MDUF runs the script lowest in the ListBox. This command is useful in scenarios that involve repeated adjustments of a single UserForm. 

Overview of the scripting language

MDUF scripts express relationships among the positions of the controls of a UserForm, or the relationship between the position of a control and the UserForm or Page that hosts that control. Each script consists of a sequence of one-line commands. There are two categories of commands. Infrastructure commands retrieve scripts, activate worksheets or workbooks, define macros, or terminate processing unconditionally. Layout commands insert formulas into the cells of FormSpec worksheets.

The general format of a script command is:

<command-name> <arguments-or-keywords>

The <arguments-or-keywords> is a sequence of words or lists separated by spaces or tabs. The lists are comma-separated strings of control names. The words are either:

• The names of controls
• Keywords such as “left:”, “right:”, “and:”, or “with-x-offset:”
• Expressions to be inserted into cell formulas

You can represent lists of control names by joining their names with commas. Space characters and tabs aren’t permitted in lists. In this guide, arguments of script commands that can be lists of names of controls are represented as, for example, <ctrl-name-list>. 

You’re free to format your script files for readability. Space characters and tabs at the beginnings or ends of script commands are ignored. Multiple sequential spaces or tabs within the text of script commands are converted to a single space character before processing begins. The line-continuation character is “_” (underscore), just as in VBA. The comment character is single-quote “’”, just as in VBA. On a given command line, everything to the right of the comment character is ignored. 

Scripting commands for infrastructure

Useful constants and variables

include <filename-or-pathname>

If <filename-or-pathname> doesn’t contain a path separator, it’s assumed to be a filename in the folder “scripts” that’s a sibling of the active Project workbook. MDUF then processes the contents of that file, after which control returns to the script file that included it.

If <filename-or-pathname> contains a path separator, it’s assumed to be the full pathname of a script file, which is then processed. After execution, control then returns to the file that included it.

activate <worksheet-name>

The MDUF scripting commands for layout operate only on the controls of the active sheet. This command activates the worksheet <worksheet-name> of the active workbook. If the active workbook has no such worksheet, MDUF posts a notification of the problem and script execution halts.

If successful, this command also assigns the value <worksheet-name> to the variable $current-form, which is available for use in subsequent scripting commands.

Recommended conventions: By convention, the filename of the script that applies to the UserForm named “Foo” is “Foo.txt”. The FormSpec worksheet that holds the data for the UserForm Foo is also named Foo. These conventions are optional. But if you adhere to these conventions, then the argument of the command activate is optional, because MDUF can deduce the argument if it is absent. This provides a small convenience if you ever repurpose a script file from one UserForm to another.

Caution: The conversion of multiple sequential spaces and tabs to a single space character applies to all parts of all scripting commands, including the argument of the activate command. If you use multiple sequential spaces and tabs in the names of the worksheets of your Project workbooks, or anywhere in the path of any Project workbook or script file, trouble will definitely ensue. This practice is not supported. For example, naming a worksheet “qwe     rty” is not supported. Also unsupported: worksheets whose names begin or end with spaces or tabs.

These constraints are not expected to be burdensome, because in MDUF, scripting is most often used in connection with capturing legacy UserForms. In MDUF, the capture process results in FormSpec worksheets whose names correspond to UserForm names. In the applications of Microsoft 365 for Mac, UserForm names cannot contain spaces or tabs. Consequently, the worksheet names of a capture workbook don’t contain spaces or tabs.

activate-workbook <workbook-name>

Activates the workbook named <workbook-name>, which must already be loaded into Excel. There is no need to wrap the name in double-quotes. However, spaces and tabs at the beginning and end of <workbook-name> are ignored. Also, the file extension “.xlsm” is optional. If you don’t provide it in the command, MDUF will compensate by appending the file extension “.xlsm”.

activate-my-worksheet

If the filename of the script is foo.txt, this command is equivalent to 

activate foo

That is, it activates the worksheet “foo” of the active workbook. This command insulates the script file contents from the need to explicitly state the name of the script file. In cases in which the name of the script file is derived from the name of the UserForm, it renders the script file more re-usable.

applescript <rest-of-command>

executes <rest-of-command> as if it were applescript. For example:

applescript say "Hello World"

text-file-as-applescript <pathname>

Executes as if AppleScript a text file whose pathname is <pathname> and which is written as if it were applescript.

applescript-file <pathname>

causes your Mac to run an AppleScript script file located at <pathname>.

exit

Terminates all script processing. This command is especially useful during script development.

defmacro, end-defmacro

These commands mark the beginning and end, respectively, of a scripting macro definition. See “Scripting macros” for more. 

Scripting commands for layout

Scripting commands for layout are relatively limited. They are sufficient for most layout tasks, but just barely. For example, there is no conditional statement (If-Then-Else), no Case statement, and no iteration. Still, the capabilities that are available are powerful enough to support relative positioning. This section provides a catalog. Here is a list of the commands:

const <name-of-constant> <expression>

This command defines a constant with name <name-of-constant>. When that name is encountered in a subsequent scripting command, it is replaced by <expression>, literally. That is, the const command defines a relationship between two strings. The content of <expression> is restricted to any string that can be evaluated to a single value in the context of a cell formula in Excel. Only one level of replacement is supported. If <expression> involves the name of a previously defined constant, the result involves the name of that previously defined constant, not the string replacement of that previously defined constant. It is an error to define $it or $current-form as constants

set-property <ctrl-name-list> <property-key> <formula>

Use this command to set the value or formula of the Width property cell or the Height property cell of the records that hold the layout information for the controls named in <ctrl-name-list>.

As a side effect, this command sets the value of the script variable “$it” to be the last control specified in .

If <property-key> is either height: or width:, <formula> can be any formula that evaluates to a numeric value suitable for a Width property cell or a Height property cell. It can also be numeric expression suitable for a Width or Height. If it is a formula, MDUF supplies the first character of the formula, namely the equal sign (=).

In some cases, with repeated maintenance, the heights or widths of controls are inadvertently altered. This command is included to save the trouble of checking the widths or heights of controls that should have a standard width or height. For example, most one-line labels should have a standard height.

If <property-key> is picture: or mouse-icon: then <formula> can be any formula that evaluates to a string of the form “<picture-library-name>.<image-control-name>”. (That’s a “dot” between the two names) At generation time, <picture-library-name> must be the name of a Picture Library, and <image-control-name> must be the name of an image control in that library. Except at generation time, there are no constraints (other than Excel’s) on <picture-library-name> or <image-control-name>. See “Specifying Picture or MouseIcon properties of a control or UserForm” for more.

If <property-key> is visible:, <formula> can be either True or False, or any expression that evaluates to True or False in the cell formula context. This command then sets the formula of the cell that holds the Visible property of the controls named in <ctrl-name-list>. This might seem at first to be a superfluous scripting capability, but it can be useful in capture projects due to the need for a workaround for a shortcoming of Frame controls in Macintosh versions of Office. See “A word about Frame controls” for more.

clear-property <ctrl-name-list> <property-key>

Use this command to clear the value or formula of the Width property cell or the Height property cell of the records that hold the layout information for the controls named in <ctrl-name-list>.

This is useful when you want to exploit MDUF’s use of the default width or height for the named controls.

As a side effect, this command sets the value of the script variable “$it” to be the last control specified in <ctrl-name-list>.

flush <direction> <ctrl-name-list>

<direction> is either right:, left:, top:, or bottom:. <ctrl-name-list> is a list (a comma-separated string) of the names of the controls you’re adjusting.

For example, consider the command

flush right: cmdOK,cmdCancel

The effect of this command is to insert into the Left property cell the formula =FlushRight() for both the control cmdOK and the control cmdCancel.

As a side effect, this command sets the value of the script variable “$it” to be the last control specified in <ctrl-name-list>.

The flush command can also accept an offset clause after the name(s) of the affected control(s). For example consider this command:

flush left: lblPrompt 5

The above command would place into the Left cell of the lblPrompt record the formula

=FlushLeft()+5

You can specify the offset with any expression that evaluates to a number in the context of the cell’s formula. For example: 

flush left: lblPrompt 2*IndentUnderPrompt

The command place can position a control according to either absolute coordinates or relative to other controls, or relative to the form itself. Let’s consider absolute positioning first. There are four cases:

place <this-control> at-x: <x> and: at-y: <y>

place <this-control> at-y: <y> and: at-x: <x>

place <this-control> at-x: <x> and: <relation> <that-control>

place <this-control> at-y: <y> and: <relation> <that-control>

In all of the above commands, the second directive—the part starting with “and:”—is optional.

The first and second of these place the control whose name is <this-control> at the coordinates [x,y]. These forms are useful when you know the absolute coordinates of the desired placement. x and y can be any expressions that evaluate to numbers in the cell formula context.

The next one sets the left edge of the control whose name is <this-control> at the x-coordinate x. This form is useful when you know the horizontal position, but you want to place the vertical position relative to another control or relative to the form edge.

The last one sets the top edge of the control at the y-coordinate y. This form is useful when you know the vertical position, but you want to place the horizontal position relative to another control or to the form edge.

The place command is most often used to place a control relative to another control.

As a side effect, these commands set the value of the script variable “$it” to be the control <this-control>. 

place <this-control> <relative-to> <that-control>

This command places <thisControl> relative to <thatControl>. <relative-to> must be one of above:, beneath:, to-right-of:, to-left-of:, right-edge-of:, left-edge-of:, centered-relative-to:, top-edge-of:, bottom-edge-of:.

If <relative-to> is above:, beneath:, top-edge-of:, or bottom-edge-of: this command places a formula into the Top property cell of <this-control>. If <relative-to> is to-right-of:, to-left-of:, left-edge-of:, right-edge-of:, or centered-relative-to:, this command enters a formula into the Left property cell of <this-control>.

As a side effect, this command sets the value of the script variable “$it” to be the control <this-control>.

The formula entered into the cell depends on <relative-to>. For example:

place cmdCancel beneath: cmdOK

would enter the formula =Beneath(cmdOK) into the Top property cell of the cmdCancel record.

You can place a control as (horizontally) centered relative to the UserForm or centered relative to another control. To center lblLabelMax relative to a control, for example, lblLabel1:

place lblLabelMax centered-relative-to: lblLabel1

To center lblLabelMax relative to the UserForm frmThisForm:

place lblLabelMax centered-relative-to: frmThisForm

However, it’s better style to express the above as:

place lblLabelMax centered-relative-to: $current-form

The above is more convenient because it’s reusable. When placing a control relative to the edges of the UserForm rather than another control, there is no need to supply the literal name of the active FormSpec. The command activate always sets the value of the variable $current-form to the name of the active FormSpec.

If the control is hosted on a MultiPage, the command flush positions the control correctly with no special guidance from the script. And you can center it on the MultiPage by providing the name of the MultiPage after the directive centered-relative-to:, as shown in the example below. In the example, lstList1 is a control hosted on a page of the MultiPage mpgAccountInfo.

place lstList1 centered-relative-to: mpgAccountInfo

The place command has a variety of more specialized forms. You can use it to specify both the Top property and the Left property by employing the and: operator as, for example:

place cmdCancel beneath: cmdOK and: left-edge-of: lblFoo

The place command can also specify horizontal (x) and vertical (y) offset, if it specifies a horizontal or vertical position. Example:

place cmdCancel beneath: cmdOK with-y-offset: <amount>

This form of the place command results in appending an additive term to the formula that would otherwise be entered. <amount> can be a number, or a constant previously declared with a const command, or any string that evaluates to a number in the context of the cell formula.

You can include both vertical and horizontal offsets if you specify both Left and Top properties.

To move the control to the right (or down), use a positive number; to the left (or up) use a negative number.

As a side effect, the place and flush commands set the value of the script variable $it. The place command sets $it to be the first control specified in the command. The flush command sets $it to be the last control mentioned in the command. For example, the script below positions cmdOK in the upper right corner, cmdCancel below it, and cmdSelectAll below cmdCancel:

flush right cmdOK,cmdCancel
flush top cmdOK
place cmdCancel beneath: $it
place cmdSelectAll beneath: $it

nudge <ctrl-name-list> delta-x: <x-amount> delta-y: <y-amount>

The nudge command enables scripting of minor adjustments that would otherwise require manual intervention. It’s useful for making the final tweaks of captured UserForms. And it can simplify layout for idiomatic structures such as stacks and sequences.

The nudge commands of your script should be the last ones executed for the Left cells and Top cells they affect, because they modify the formulas that reside in the Left or Top property cells. Other script commands that follow nudge commands (other than nudge commands) simply overwrite the contents of those cells. If nudge commands weren’t the last ones executed, their effects would be overwritten by other commands.

At least one of the keyword-value pairs (delta-x: <x-amount>) and (delta-y: <y-amount>) is required. It isn’t necessary to provide a pair if the amount of the nudge is zero.

As a side effect, this command sets the value of the script variable “$it” to be the last control specified in <ctrl-name-list>.

The <x-amount> and <y-amount> arguments to the nudge command can be any strings that evaluate to numbers in the cell formula context.

For each of the controls named in <ctrl-name-list>, nudge appends an additive quantity (which can be negative) to the formulas in the Left property cell or the Top property cell. Suppose the Top property cell of cmdSelectAll contains the formula =Beneath(cmdClearAll). Then consider the effect of this command:

nudge delta-y: 6 cmdSelectAll

After executing that command, the formula of the Top property cell of cmdSelectAll becomes =Beneath(cmdClearAll)+6. 

Stacks

One common idiom for laying out controls on a UserForm is the “stack”—a column of controls, usually command buttons, evenly spaced vertically, and aligned either on their right edges or their left. The MDUF scripting command for placing controls in a stack is:

stack <top-control> <alignment> <ctrl-name-list>

In the above, <alignment> is either right-edge-of: or left-edge-of:. It determines whether we align the right edges or the left edges of the stacked controls. <top-control> is the name of the control at the top of the stack. Its placement must be determined at the time you invoke the stack command. <ctrl-name-list> is a list of names of controls in the stack, other than <top-control>. Their names are separated by commas. Upon exiting the command, $it is set to the last control in the list <ctrl-name-list>.

Some stacks, especially stacks of command buttons, are easier to understand visually when their members are grouped. To separate groups in a stack, use a label control whose name is unique in the UserForm, and whose name is of the form lblSeparator<suffix>. <suffix> can be any alphanumeric string. The height of a separator must be 2 twips. Clear its caption. If you want it to stand out from the UserForm’s backcolor, adjust its bordercolor to something contrasting. If you want the separator to appear to be invisible, make its colors the same as the UserForm’s backcolor. 

Sequences

A sequence is an arrangement of controls like a stack, but more general. A sequence has one control designated as the leader, and an ordered list of controls that follow it, referred to collectively as followers. The orientation of a sequence, relative to the leader, can be to-right-of:, to-left-of:, beneath:, or above:. If the orientation is to-right-of:, then the followers of the leader are arranged one after another to the right of the leader. If the orientation is to-left-of: then the followers of the leader are arranged one after another to the left of the leader. The above: orientation and the below: orientation are analogous.

You can choose how the controls in the list of followers are aligned. For a sequence that has orientation beneath: or above:, the follower controls can be aligned to the source either left-edge-of: or right-edge-of:. For a sequence that has orientation to-right-of: or to-left-of:, the follower controls can be aligned to the source either top-edge-of: or bottom-edge-of:.

The basic form of the sequence command is:

sequence <direction-from-leader> <leader-control> <alignment-with-respect-to-leader> <follower-controls>

However, you can append offsets to end of the above form, just as you would with the place command. You append x offsets, or y offsets, or both. Whatever offsets you do append are applied to all follower controls but not to the leader control.

After this command, the variable $it evaluates to the name of the last follower.

The following two commands produce identical results: 

stack cmdSelectAll right-edge-of: cmdClear,cmdReset,cmdSave
sequence beneath: cmdSelectAll right-edge-of: cmdClear,cmdReset,cmdSave

Scripting macros

You can define combinations of script commands as macros using the command defmacro.

defmacro <macro-name> <argument-name1> <argument-name2> …

This command gathers all subsequent lines of the script, up to the line that contains only end-defmacro. Afterwards, you can invoke the macro using the command

<macro-name> <argument-1> <argument-2> …

<macro-name> can be any sequence of characters that meet the following criteria:

• Each character must be a letter, a number, or hyphen.
• Upper case and lower case are equivalent.
• The name cannot be a reserved word. Reserved words include set-property, place, flush, defmacro, end-macro, all, and:, left:, right:, top:, bottom:, with-x-offset:, with-y-offset:, at-x:, at-y:, and others.

Macros can have any fixed number of arguments. In macro definitions, argument names begin with the character $. The remaining characters of argument names can be letters, numbers, or hyphen. As everywhere in MDUF scripting, the case of letters is ignored. Macro names must be unique. If two macros have the same name, the macro defined last is the only one MDUF can use.

MDUF scripting macros “capture” the variable $it. That is, they are free to access it and change its value, just as any script line would. The value $it takes on is the value that it would have taken on had the lines of the macro actually been inserted into the script after making appropriate variable substitutions. For these reasons, $it is not permitted as the name of a macro argument.

The command defmacro is not permitted within a defmacro.

MDUF scripting macros are useful for implementing idioms that you use repeatedly. For example, many UserForms have an OK button in their upper right corner, and a Cancel button immediately beneath it. Here’s a macro for dealing with that idiom.

defmacro ok-cancel $ok $cancel
  flush right $ok,$cancel
  flush top $ok
  place $cancel beneath: $it 'after this line, $it is $cancel
end-defmacro

To invoke a macro, use it as if it were a built-in part of MDUF scripting. For example, to invoke the macro ok-cancel for a UserForm that has command buttons named cmdOK and cmdCancel, the script would be:

ok-cancel cmdOK cmdCancel

You can create a library of macros by placing them in a file, one after the other. To access them, use the include command to include the macro file. Or place them in a file called scripting-init.txt in the Project’s script directory. 

Mapping over lists

Lists are represented as comma-separated strings. Items within lists cannot contain commas. This is not anticipated to be restrictive because lists are used for representing controls. In Microsoft 365, UserForm names and control names must contain only alphanumeric characters (and possibly underscores). Consequently, well-formed lists consist of only control names and commas. Space characters are not permitted.

For example, a list of command button controls would be represented as 

cmdSetAll,cmdClearAll,cmdCycleAll

To perform the operation Foo, which takes a single argument, on the members of a list, use the command map:

map Foo cmdSetAll,cmdClearAll,cmdCycleAll

If Bar is a macro or command that accepts two arguments, you can map it across two lists as follows:

map Bar c1,c2,c3 d1,d2,d3

When MDUF encounters the above map command, it creates the following script:

Bar c1 d1
Bar c2 d2
Bar c3 d3

By executing this created script, MDUF essentially “expands” the map command. In effect, map is nothing more than shorthand.

In the above example, if Bar is a macro, MDUF would continue expanding the script it created by replacing each line with its macro-expanded form.

In mapping over UserForms, MDUF has two variables that can be especially useful. $all-active-forms is a list of all forms that are active; $all-forms is a list of all UserForms, including both active and inactive UserForms. Both variables exclude templates.