Toolbar > VBA User Interface


The MGM workbook includes Visual Basic for Applications code (VBA) that implements the user interface and growth model. For most users this part of the workbook will not be of interest. If in doubt, "leave it alone".

The VBA code is locked and the workbook structure is Protected. Worksheets may not be copied and new worksheets may not be inserted into the MGM workbook. Some individual worksheets are Protected and formulas are hidden from view, most notably the Stand worksheet that is closely linked with the VBA components that make the growth projections.

Automating usage of MGM from another Excel workbook

For advanced users familiar with VBA, limited access to MGM VBA objects and code is permitted. These VBA components are available from an external Excel workbook after an explicit Reference to the MGM workbook has been set from the VBA Tools menu (see following figure). The specific name of the highlighted reference may vary as new version of MGM are released.

When automating processing from an external workbook, ALWAYS use a working copy of the MGM workbook (with its won unique name) so that the original is preserved. After this external reference is established, opening the external workbook will automatically open the referenced MGM workbook. That MGM workbook may not be closed while the external workbook is open. For additional convenience, the option checkbox to show the Edit MGM Notes dialog on the MGM Toolbar at startup should be unchecked (off). A change in the MGM version number (i.e. an update) will cause the Reference to be broken and it will have to be re-established by unchecking the old reference and browsing for the new workbook.

Setting the external analysis workbook Reference to the MGM workbook

Setting up or changing the Reference to an MGM workbook can sometimes cause unexpected results (like crashing Excel). This is particularly true when since the reference must be changed each time the MGM workbook version changes. To minimize the possibility of unusual actions the following steps should be used when changing the reference.

Install the new or updated MGM workbook version as directed in the MGM help file. Ensure that all relevant workbooks are in the same directory.
The VBA project must be modified (manually) to update the external References settings to reflect the current version of MGM workbook that is being linked with the external analysis workbook.

Start a new Excel session with no workbooks open.

On opening the external analysis workbook, a compiler error will be encountered if an old reference is present and the old MGM workbook has been deleted. This will automatically open the VBA editor window.

If an error message was received, reset the VBA project using the reset button on the VBA toolbar at the top of editor window. If no error message occurs then open the VBA editor window.

Using the Tools|References menu dialog, uncheck the old reference to any previous MGM version; then browse and locate the new MGM workbook and add that as a VBA reference.

Save the revised external analysis workbook; close the VBA editor window; exit from Excel without closing any of the open workbooks; if prompted do NOT save changes to any workbooks (e.g. MGM workbook).

Re-open the external analysis workbook to verify that no errors occur and allow for proper initialization of MGM.
Once the VBA Reference to MGM has been established the names of public MGM components may be used with or without the 'MGM.' prefix. Using the 'MGM.' prefix makes explicit those code segments that are part of the MGM project and facilitates understanding the coding.

Public MGM VBA components

Public VBA components (macros and other objects) for the MGM VBA project allow the programmer to work within the MGM workbook using VBA code constructed in an external workbook. The following actions may accomplished:

Set the names of the subordinate MGM workbooks (Crop Plans, Records, and Stands)
Import a crop plan worksheet from the Crop Plans workbook into the MGM Crop Plans worksheet
Execute all crop plans found in the MGM Crop Plans worksheet
Save the current contents of the MGM Records worksheet to the Records workbook
determine if errors were encountered during projections
Additional access is provided to a limited number of MGM worksheets and charts that may be copied and pasted into other Excel workbooks

These tools give the programmer considerable flexibility to automate the construction and execute crop plans and also to retrieve any desired results of the projections. Two examples follow the table illustrating some of these capabilities.

The following table summarizes the MGM library objects that are available.

Example 1. Crop Plans already present in a Crop Plans workbook

Example 1 presents a macro that can be executed from an external workbook. It shows typical usage of these MGM VBA library objects. It includes several optional code segments and comments (signalled by a single-quote character (')). These comments provide additional explanation for specific VBA coding features. This example assumes that the Crop Plans workbook (dbCrops) already contains the Crop Plans that are to be projected.