Introduction

The ability to create instanced forms was introduced in MS Office Visual Basic for Applications (VBA) beginning with MS Office 2007. Instanced forms potentially offer MS Office developers flexibility and options not previously available. Prior to MS Office 2007, if a developer wanted to display, for example, multiple forms showing different search results, the developer had to create a separate form for each possible view, or accept the limitation of being able to show only one form of a particular type at a time. The need to have a dedicated form for each and every purpose tends to bloat the number of forms a developer must create and maintain.

Instanced forms have the potential to enable a developer to create a single form, and create multiple instances of that form as needed for various purposes. Using multiple instances of a single form greatly simplifies maintenance (fix one form once), and reduces overall file size by however many redundant forms can be eliminated.

Instanced forms in MS Office VBA, however, have a couple of significant drawbacks:

  • Instanced forms cannot be managed by the MS Office application’s forms system. Each instance of a specific form has the same name, so tools provided in the VBA parent application (such as the Application.Forms collection) cannot distinguish between any given form instance and any other instance of the same form.
  • The lifetime of an instanced form is limited to the lifetime of any variable holding a reference to the form instance. A form instance is unloaded as soon as the variable holding its reference goes out of scope. If a developer wants to keep an instanced form open, she must either declare a global variable to hold the form reference, or use looping code to prevent procedures from ending and having the form reference go out of scope. Neither approach is satisfactory – having large numbers of global variables is poor programming practice, and having multiple DoEvents loops increases overall code execution time.

A review of accessible Internet postings indicates that many MS Access developers have given up on using instanced forms, finding them generally too difficult to implement because of their drawbacks.

Unwilling to forego the benefits and flexibility inherent in the concept of instanced forms, Matthew S. Harris developed a VBA class object (the Forms Instance Manager Class) to provide the following capabilities:

  • Control the lifespan of instanced forms using a single global variable.
  • Distinguish one form instance from another, enabling the use of multiple instances of the same form.
  • Provide basic tools to manage a collection of instanced forms.

The Forms Instance Manager class object was designed (and intended for use in) MS Access VBA. The Forms Instance Manager Class should be usable in any version of MS Office 2007 or higher in any MS Office application that implements instanced forms.

This article covers these topics:

  • Methods & Properties of the Forms Instance Manager Class – the Forms Instance Manager object implements one property and 10 methods. The class’ methods enable you to add, remove and reference instanced forms. The class’ methods also include some basic tools which help you use the class and manage your collection of instanced forms.
  • Putting the Forms Instance Manager to Work – highlights of how to declare and use an instance of the Forms Instance Manager. Also important information on how to design forms for use with FIM, in particular the mandatory code required in a form’s Unload event. (You can get full source code, examples, and more detailed documentation here.)

Next, you’ll get an overview of the Forms Instance Manager class object.

The Forms Instance Manager (FIM) is implemented as a class object. The FIM is declared in a class module named FormsInstanceManagerClass.

For the FIM to work correctly, any variable of the FIM class must have global scope. Refer to Putting the Forms Instance Manager to Work for an example of declaring and using a FIM class variable.

The FIM encapsulates a standard VBA Collection data type as its only private data, and provides methods to ensure that the FIM collection is managed effectively for Form objects.

Each instanced form in the FIM’s collection has its Tag property set to a unique string value. This enables a form to provide a FIM reference to code external to the form and to FIM. This also enables handling groups of forms through partial string matching (for example, all forms whose unique identifier contains the string “Sales”).

The unique string identifier stored in the form’s Tag property is called the Key Tag for the form. You may choose your own Key Tag, or use FIM to generate unique Key Tags based on the form’s name.

The Forms Instance Manager class has these methods and properties:

  • Count – a read-only property. Returns the current count of instanced forms in the FIM.
  • FindForm_Match – a read-only property. Returns True or False depending on whether the last use of the FindForms method successfully found matching forms in FIM.
  • Add – this method adds an existing instanced form to the FIM. Note that the Add method will change the Tag property of the form being added.
  • Item – returns a reference to a specified form in FIM. You may specify a form with its Key Tag string, or with a numeric index.
  • Remove – removes an instanced form from the FIM’s collection. The form to be removed is specified by its Key Tag string, or by a numeric index. If FIM is the only object holding a reference to the instanced form, the instanced form is automatically unloaded (destroyed).
  • RemoveByReference – also removes an instanced form from the FIM’S collection. The form to be removed is specified by a form reference. As with the Remove method, the form is unloaded if FIM is the only object holding a reference to the form.
  • Clear – removes all forms from FIM. Any form not referenced by another object is unloaded when removed from FIM.
  • SetFocus – assigns focus to a form in the collection. You may use either the form’s Key Tag string, or a numeric index to specify a form in the FIM’s collection.
  • SetFocusByReference – assigns the focus to a form in the collection, using a reference to the form as an index.
  • KeyTag_Exists – returns True or False depending on whether a given string is found in FIM as a Key Tag string.
  • FormReference_Exists – returns True or False depending on whether a given form reference is found in FIM.
  • Get_KeyTag_From_FormName – returns a unique Key Tag string based on the form’s name. For example, if a form is named “Foo”, and that name already exists in FIM, then this method returns “Foo1”, “Foo2” and so on as needed to produce a unique Key Tag.
  • FindForms – returns an array of form objects whose Key Tag matches a string pattern. Must be used in conjunction with the FindForm_Match property to determine whether the FindForms search found any matching forms.

Forms intended for use with the Forms Instance Manager do have a couple of special requirements:

  • The form must have an Unload event-handler containing specific code that ensures the FIM contains only valid form references.
  • The form’s Tag property must be reserved for exclusive use by FIM.
  • Optionally, you may add a Load event-handler to your forms to have them automatically add themselves to a FIM collection.

The section Creating Forms for Use With the Instance Manager shows code examples for both the required Unload and optional Load event-handlers.

The next sections describe the private data of the Forms Instance Manager Class, and then go on to describe the syntax for using the FIM methods and properties.

The Forms Instance Manager declares two private data variables.

The first private data variable is:

Private MyForms As New Collection

The MyForms variable holds the collection of instanced forms managed by the FIM class.

The second private data variable is:

Private IsMatch As Boolean

The IsMatch variable is an internal flag indicating whether the last use of the FindForms method successfully found matching forms.

The Forms Instance Manager also declares several constants used in error-reporting. FIM raises several runtime errors if its methods are used with incorrect or invalid arguments. The constants enable the FIM code to use the VBA Err object to raise runtime errors with meaningful text descriptions of the error.

This section shows you the syntax for using the Forms Instance Manager’s methods and properties. To see the full source code for the Forms Instance Manager Class, you should download either the full documentation or the demonstration database.

COUNT Property

The Count property is read-only. It returns the count of forms in the FIM’s private MyForms collection.

The syntax for using the Count property is:

object.Count

where object is any reference to an object of the FormsInstanceManagerClass type.

FINDFORM_MATCH Property

The FindForm_Match property is read-only. It returns True if the last call to the FindForms method successfully found at least one matching form in the FIM’s private MyForms collection.

The syntax for using the FindForm_Match property is:

object.FindForm_Match

where object is any reference to an object of the FormsInstanceManagerClass type.

ADD Method

The Add method enables you to add a form instance to the FIM.

The syntax for using the Add method is:

object.Add formReference, keyvalue

where object is any reference to an object of the FormsInstanceManagerClass type, formReference is a valid reference to a Form object, and keyvalue is a String unique to the FIM collection.

Notice that the Add method requires two arguments – an object reference of type Form, and a String variable for the Key Tag. The Add method’s arguments must meet these criteria:

  • The formReference argument can be a reference to any form.
  • formReference may not be Nothing (that is, uninitialized), and it may not duplicate any reference already in the FIM.
  • The keyvalue argument can be any valid string.
  • keyvalue may not be blank, and it may not duplicate any existing Key Tag in the FIM.

The Add method raises a VBA runtime error if any of the arguments passed to it fail to meet the criteria above. The specific text of the runtime error shows the method generating the error and what the problem with the argument value is.

If the arguments to the Add method are valid, the form reference is added to the FIM collection, and the Tag property of the form being added is set to match the keyvalue argument.

ITEM Method

Like the Item method of any collection, the Forms Instance Manager’s Item method enables you to reference a particular member of the collection. You may use either a numeric index or a string index to reference a member in the collection.

The syntax for using the Item method is:

object.Item(Index)

where object is a variable of the FormsInstanceManagerClass type, and Index is either an integer or string value.

The Index argument must meet these criteria:

  • It must be an Integer, Long, or String data type.
  • If numeric, it must be greater than 0, and less than or equal to the count of forms in the collection.
  • If a string value, it must match a Key Tag value of a form already in the FIM’s collection.

The Item method raises a VBA runtime error if the Index argument does not meet these criteria.

Use the Item method in your code as a function – it returns a Form object reference.

Note that the VBA programming environment does not enable creating default methods, so the FIM’s Item method must always be used explicitly, for example:

Set MyFormHandle = MyFormInstancesCollection.Item(“SuperForm”)

OR

Set MyFormHandle = MyFormInstancesCollection.Item(3)

OR

MyFormInstancesCollection.Item(“SuperForm”).Visible = False

REMOVE Method

Use the Remove method to delete a form instance from the Forms Instance Manager. Assuming the FIM’s reference to the form instance is the only valid in-scope reference, using the Remove method unloads (destroys) the form instance. The Remove method is designed to behave the same way as the Remove method of any built-in Collection object.

The syntax for using the Remove method is:

object.Remove Index

where object is a variable of the FormsInstanceManagerClass type, and Index is either an integer or string value.

The Index argument must meet these criteria:

  • It must be an Integer, Long, or String datatype.
  • If numeric, it must be greater than 0, and less than or equal to the count of forms in the collection.
  • If a string value, it must match a Key Tag value of a form already in the FIM’s collection.

The Remove method raises a VBA runtime error if the Index argument does not meet these criteria.

REMOVEBYREFERENCE Method

The RemoveByReference method removes a form from the Forms Instance Manager’s collection, based on a form reference value. Using the RemoveByReference method unloads (destroys) the form instance (assuming FIM is the only reference to the form).

The syntax for using the RemoveByReference method is:

object.RemoveByReference FormReference

where object is a variable of the FormsInstanceManagerClass type, and FormReference is a reference to a Form object.

The RemoveByReference method provides an alternative to the Remove method. Use the RemoveByReference method when a reference to the form you want to remove is more readily available than the form’s ordinal or Key Tag index. In general, you should use the Remove method whenever possible, as the RemoveByReference method involves more iterative code and is therefore somewhat slower than the Remove method.

The RemoveByReference method raises a VBA runtime error if the FormReference argument is Nothing, or references a form not in the FIM’s collection.

CLEAR Method

The Clear method removes all of the Forms Instance Manager’s forms from the collection. The Clear method has no arguments.

The syntax for using the Clear method is:

object.Clear

where object is a variable of the FormsInstanceManagerClass type.

SETFOCUS Method

Use the SetFocus method of the Forms Instance Manager to give the focus to a specified form in the collection.

The syntax for using the SetFocus method is:

object.SetFocus Index

where object is a variable of the FormsInstanceManagerClass type, and Index is either an integer or string value.

Note that the following two lines of code have identical effect (fManager is an initialized instance of FormsInstanceManagerClass type and “foo” is the Key Tag of a form in the collection):

fManager.Item(“foo”).SetFocus ‘calls the form’s SetFocus method

fManager.SetFocus “foo” ‘calls the SetFocus method of FIM

The SetFocus method (in the second line above) provides a slightly more terse means of moving focus to a specified form than referencing a form through the FIM Item method to call the form’s own SetFocus method. (first line above).

The SetFocus method raises a VBA runtime error if Index is not a valid type (not an Integer, Long, or String), or if the value does not reference a form in the FIM’s collection.

SETFOCUSBYREFERENCE Method

The SetFocusByReference method sets the focus to a form in the Forms Instance Manager, indexed by the form’s reference.

The syntax for using the SetFocusByReference method is:

object.SetFocusByReference FormReference

where object is a variable of the FormsInstanceManagerClass type, and FormReference is a reference to a Form object.

The SetFocusByReference method provides an alternative to the SetFocus method. Use the SetFocusByReference method when a reference to the form you want to set the focus to is more readily available than the form’s ordinal or Key Tag index. In general, you should use the SetFocus method whenever possible, as the SetFocusByReference method involves more iterative code and is therefore somewhat slower than the SetFocus method.

The SetFocusByReference method raises a VBA runtime error if the FormReference argument is Nothing, or does not reference a form in the FIM’s collection.

KEYTAG_EXISTS Method

The KeyTag_Exists method returns True if a specified string exists as a Key Tag value in the Forms Instance Manager’s collection of forms, False if it does not. Use this method to determine whether any given string is valid to use as a Key Tag for other FIM methods such as Add, Item, Remove, and SetFocus. The KeyTag_Exists method is used internally by several FIM methods as part of the code that checks the validity of index arguments.

The syntax for using the KeyTag_Exists method is:

object.KeyTag_Exists(key)

where object is a variable of the FormsInstanceManagerClass type, and key is a string value.

FORMREFERENCE_EXISTS Method

The FormReference_Exists method returns True if a specified form reference exists in the Forms Instance Manager’s collection of forms, False if it does not. Use this method to determine whether any given form reference is valid for use as an index value for other FIM methods such as RemoveByReference, or SetFocusByReference. The FormReference_Exists method is used internally by several FIM methods as part of the code that checks the validity of index arguments.

The syntax for using the FormReference_Exists method is:

object.FormReference_Exists(FormReference)

where object is a variable of the FormsInstanceManagerClass type, and FormReference is a valid reference to a Form object.

GET_KEYTAG_FROM_FORMNAME Method

This method is not strictly essential to the tasks of managing the collection of instanced forms. It does, however offer a major convenience to programmers using the Forms Instance Manager – it provides a means of generating unique Key Tag values based on a form’s name. The Get_KeyTag_From_FormName method makes it possible to implement instanced forms that “automatically” add themselves to the Forms Instance Manager’s collection, as shown in Designing Forms For Use With FIM.

The syntax for using the Get_KeyTag_From_FormName method is:

object.Get_KeyTag_From_FormName(FormReference)

where object is a variable of the FormsInstanceManagerClass type, and FormReference is a valid reference to a Form object.

If a form instance of a given name has not yet been add to the FIM collection, Get_KeyTag_From_FormName will return the form name as the unique result. For example, if the form is named “SalesAgents”, and no form named SalesAgents has yet been added to the FIM collection, then the return value of Get_KeyTag_From_FormName will be “SalesAgents”.

If the FIM already contains forms with a given name, then the return value of Get_KeyTag_From_FormName will be the form name with a numeric suffix. Continuing the example of a form named SalesAgents, for the second SalesAgents form the return value of Get_KeyTag_From_FormName will be “SalesAgents1”, the third would be “SalesAgents2” and so on.

The KeyTag_Exists method raises a VBA runtime error if the FormReference argument is Nothing.

FINDFORMS Method

The FindForms function returns a Variant containing an array of form references. Use the FindForms function to find all of the forms in the FIM collection whose Key Tag value matches a specific string pattern. The search for matching Key Tag values is case-insensitive. To determine whether the FindForms function has successfully found any matching forms, you must use the FindForm_Match property of the FIM collection.

The syntax for using the FindForms method is:

object.FindForms(StringPattern)

where object is a variable of the FormsInstanceManagerClass type, and StringPattern is a string containing a pattern to be matched. The StringPattern argument should contain a pattern-matching string conforming to the rules for the VBA Like operator (* to represent any number of characters, ? to represent any single character, # to represent numeric digits, and so on). Refer to the VBA online Help reference for more information about pattern-matching strings used with the Like operator.

The best way to learn how to use the Forms Instance Manager Class is with the demonstration database, or with the full documentation. You will find much more detail on the source code, how it works, and how to use the FIM class there. You can download the complete source code and full documentation from this download page.

The demonstration database contains these items:

  • The complete VBA source code for the Forms Instance Manager Class module (FormsInstanceManagerClass).
  • A module named test_InstanceMgr which contains code showing how to perform basic operations with the Forms Instance Manager, such as creating a class instance, adding and removing instanced forms in the manager, and other tasks.
  • A Main form to invoke routines from the test_InstanceMgr module and provide access to the documentation and licenses stored in the demo database.
  • An “auto-add” form (frm_Instance_AutoAdd) which demonstrates how to set up a form to automatically add itself to the Forms Instance Manager whenever an instance of the form is created.
  • A “manual add” form (frm_Instance_ManualAdd) to demonstrate how instanced forms are manually added with code to the Forms Instance Manager.
  • A single table (tbl_Documentation) which has a single Attachment field containing an expanded version of this article, and copies of the GNU Licenses under which this code is distributed.

The next few sections of this article hit the highlights of using the Forms Instance Manager.

Create & Reference a FIM Class

To create a usable instance of the Forms Instance Manager Class object, you must have a declaration in a regular module (not a form or class module) with the following syntax:

Public Name As New FormsInstanceManagerClass

where Name is any valid variable name.

For example, the test_InstanceMgr module of the demo package contains the following declaration to create its class instance:

Public frmManager As New FormsInstanceManagerClass

Your instance of the FIM class object must be global in scope to your application. If your instance of the FIM class object goes out of scope, all of the forms it manages will be closed!

Make references to your instance of the FIM object class as you would to any other globally available object or variable. If your FIM object class instance is named frmManager, declared in a module named test_InstanceMgr, any of the following reference styles work:

frmManager.Clear ‘this and the following statement have the same effect

test_InstanceMgr.frmManager.Clear

frmManager.Item(idx).SetFocus ‘this and the following statement have the same effect

test_InstanceMgr.frmManager.Item(idx).SetFocus

Designing Forms for Use With the Instance Manager

Forms intended for use with the Forms Instance Manager must be designed with two characteristics:

  • The form must have an Unload event-handler, which must contain specific code to ensure that the Forms Instance Manager contains only valid form references. The section, Mandatory Code for the Unload Event, explains what code you must put in the form’s Unload event-handler.
  • The form’s Tag property must be reserved for exclusive use by the Forms Instance Manager and for interacting with the Forms Instance Manager. The form’s Tag property contains the unique string ID used by FIM to identify a specific form instance – essentially giving the form instance a unique name.

Refer to the section on the Add Method for more information about how FIM uses the Tag property.

Mandatory Code for the Unload Event

An issue arises if a form is closed outside of the Form Instance Manager’s control. For example, a user might click the Close button on the form, or a programmer might mistakenly write code that closes a form instance without using the Remove method of the FIM class. If a form is closed without using the FIM’s Remove method, the FIM’s form collection will end up containing an invalid form reference, which is certain to lead to runtime errors in the FIM method code at some point.

To ensure that the FIM’s form collection contains only valid form references, each form intended for use with the Forms Instance Manager class must, upon closing, remove its own reference from the FIM’s form collection.

The Unload event occurs whenever a form is unloaded from memory, so the event-handler for the Unload event is the ideal location to put the code to remove the form’s reference from FIM. It is possible (even likely) that the that the Unload event will occur at some point after a call to one of the Remove methods has already taken place. The code in the Unload event-handler should therefore check whether the form still exists in FIM before attempting to remove itself.

The following listing shows the code required in a form’s Unload event-handler to ensure that a form instance is removed from the FIM collection:

1 Private Sub Form_Unload(Cancel As Integer)

2  ‘ensure Forms Instance Manager knows this form has closed

3  With test_InstanceMgr.frmManager

4    If .FormReference_Exists(Me) Then .RemoveByReference Me

5  End With

6 End Sub

In the preceding code, test_InstanceMgr is a module containing a global instance of the FIM class named frmManager.

Line 4 in the code example uses the FormReference_Exists method with the form’s self-referential object (Me) as an argument to determine whether the closing form is currently in the FIM’s forms collection. If it is, the RemoveByReference method is called with Me as the argument, thereby removing the closing form’s reference from the FIM’s forms collection.

One could just as easily use the KeyTag_Exists method and the Remove method, as shown in the following Unload event-handler:

1 Private Sub Form_Unload(Cancel As Integer)

2  ‘ensure Forms Instance Manager knows this form has closed

3  With test_InstanceMgr.frmManager

4    If .KeyTag_Exists(Me.Tag) Then .Remove Me.Tag

5  End With

6 End Sub

In either case, be certain you test for the existence of the form being unloaded before calling the Remove method in the Unload event-handler.

Now that you have learned what code is mandatory for you to incorporate in forms intended for use with the Forms Instance Manager class object, you are ready to learn what optional code you may add in order to have an instanced form automatically add itself to the FIM’s collection of forms, as shown in the next section.

Optional Code for the Load Event (“Auto-Add” Form)

You can add code to a form so that, when a new instance is created, the form will automatically add itself to the FIM’s forms collection.

The Load event occurs whenever a form (or a new instance of a form) is created. The Load event is therefore the ideal location for the code causing a form to add itself to the FIM’s forms collection.

The following code listing shows the code needed in a form’s Load event-handler to have a form automatically add itself to the FIM’s forms collection:

1 Private Sub Form_Load()

2  ‘add self to Forms Instance Manager on creation

3  With test_InstanceMgr.frmManager

4    .Add Me, .Get_KeyTag_From_FormName(Me)

5  End With

6 End Sub

In the preceding code, test_InstanceMgr is a module containing a global instance of the FIM class named frmManager.

Line 4 in the code example uses the Add method with the form’s self-referential object (Me) as the form reference argument. The sKeyTag argument, which provides the unique Key Tag identifier for this instance of the form, is supplied by the Get_KeyTag_From_FormName method, which returns a unique string based on the name of the form. (See the description of the Get_KeyTag_From_FormName Method for more information.)

As an example, look at this code snippet:

1 Set frm = New Form_frm_Instance_AutoAdd

2 Set frm = New Form_frm_Instance_AutoAdd

3 Set frm = New Form_frm_Instance_AutoAdd

These three lines each create a new instance of the form frm_Instance_AutoAdd, which contains a Load event-handler with the code for each form to automatically add itself to the FIM’s forms collection.

Assuming the FIM collection is empty when these lines execute, line 1 results in a new form instance with a Tag property containing the string “frm_Instance_AutoAdd”, and the form is added to the FIM collection with a Key Tag of the same value. Line 2 results in a new form instance “frm_Instance_AutoAdd1”, and line 3 results in a new form instance “frm_Instance_AutoAdd2”.

This article has presented an overview of a VBA class object, Forms Instance Manager (FIM), designed to help MS Office programmers use and manage instanced forms. You learned about the methods the FIM class implements, some of the design choices behind FIM’s operation, and how to design forms for use with FIM.

You can read expanded documentation or download the complete source code of the FIM class object and some demonstration code from this download page.