Sunday, October 12, 2008

Using Menus for Visual Studio Tools from VBA - Calling native VBA forms

I was very intrigued by the Menus for Visual Studio Tools for Microsoft Dynamics GP (VSTM) application created by my friend David Musgrave. I studied the sample code provided in the PDF documentation submitted with the product and after a few hours of careful reading, it dawned on me that if I could expose the VSTM assembly to COM interop other developers, including VBA developers could also have their native VBA forms called as part of Dynamics GP menus. After a few attempts over a few days to register the assembly with COM by using the Microsoft Assembly Registration tool, I abandoned the effort due to an existing issue registering the Microsoft.Dexterity.Bridge assembly which is referenced within the VSTM assembly to be able to make calls to Dexterity.

This however, did not discourage me from seeking other alternatives. Going back to basics, I realized that the VSTM assembly methods are just references to function scripts implemented within the VSTM dexterity dictionary. In fact, these function scripts could be called via the Dynamics Continuum Integration Library as they had corresponding names to the methods exposed in the VSTM assembly.

After this "eureka" moment, I got on a conference call with David at 1:00 AM EST on Wednesday last week and asked him if VSTM could be leveraged from VBA to facilitate adding native VBA forms to the standard GP menus. After about 1 hour of brainstorming and debating the implementation, the deal was sealed! So off I went to work on a proof of concept.

My first attempt was very rudimentary as with all POCs, since I could only articulate so much of how VSTM really worked under the hood. However, one thing was still pretty clear: the registration of the menus had to occur during the lauch of GP. I thought I could use some sort of Toobar event to register my menus, but I was wrong. While I was able to register the event with the Toolbar's Window_AfterOpen event, the menus would not show. After a few exchanges with David, he pointed me in the right direction.

The Challenges

To understand the challenges, it is worth understanding how VSTM works. VSTM loads as part of the Dynamics GP runtime engine start up process when the Dynamics application is launched. In the process, VSTM checks for any command registrations that will in turn setup any menu items a developer has asked to register. Each menu item generates a tag that VSTM used to establish which item is being selected from a menu.

VSTM uses a hidden form called VSTM_Command_Form, which in turn implements an API window. The API window is used to process any menu item registration and deliver the call back item (the tag) when a menu option is selected. Since the window's Title property has been set to "~internal~" it invisible to Modifier and/or VBA. The first challenge then was to get this window exposed to be able to add it to VBA with its fields.

To expose the window, I used the Runtime Execute utility in the Support Debugging Tool, which allows the execution of sanScript code. The following is the implementation of that code in the Support Debugging Tool tool.

From there on I could use the window's Tools menu to add the window the Register button and Command ID fields to VBA.

The next obvious question was, with the hundreds of VBA forms and customizations out there today, how can I reference a form created in another product from the VSTM product? The obvious way would be to add a reference to the product needed from VSTM -- in fact that reference is needed. However, forms are not exposed via the references.

What was needed is a Module in the source product containing a Public Sub that in turn would display the form. It turns out that Modules are exposed via references and any Public Sub within the module can be invoked from another product's project.

The Implementation

Since I got David so excited about the idea of making VSTM workable from VBA, he set out on a race against the clock to develop the sample he provided in his VSTM application PDF guide in VBA. In fact, he was done with the code in little under 2 hours. In his words "not bad!". Please see David's article on Using Menus for Visual Studio Tools from VBA to gain some insight into the techniques used.

This was all good, because I could use his sample code as a base for delivering the form calls and show how to use VSTM in a practical way.

1) Open the Customization Maintenance window. Go to Microsoft Dynamics GP > Tools > Customize > Customization Maintenance.

2) Click here to download the package file for this project. Save onto your desktop.

NOTE: Menus for Visual Studio Tools is only available for version 10 of Microsoft Dynamics GP, hence this code will only work with Microsoft Dynamics GP v10.

3) Click on the Import button in the Customization Maintenance window and select the package file saved on your desktop. This will create 2 forms and add the necessary VBA code to open them. The package will also expose the Menus for Visual Studio Tools "~internal~" API window to VBA.

4) Compile the projects and exit the VBA editor

5) Exit GP, If prompted to save VBA projects, click on Yes.

6) Launch GP and go to Cards > Inventory, you will now see an entry at the bottom of the menu. When the entry is selected, two options will display showing sample forms that can be choosen. The first option, will open a form created within the VSTM product project. The second option will display a form created within the Microsoft_Dynamics_GP product project.

Menus for Visual Studio Tools Resources

Additional information on VSTM can be found at:

Menus for Visual Studio Tools for Microsoft Dynamics GP 10 at Developing for Dynamics GP.

Menus for Visual Studio Tools - The wait is over at Developing for Dynamics GP.

First Look at Menus for Visual Studio Tools at The Dynamics GP Blogster

Final Notes

Please note that Menus for Visual Studio Tools for Microsoft Dynamics GP 10.0 is only planned for release with version 10.0 as the plan is to have menu support built into Visual Studio Tools for the next version of Microsoft Dynamics GP.

This customization uses a method of executing Dexterity sanScript code from VBA which is unsupported by Microsoft.

Once again, it is demonstrated that VBA can be a very powerful tool in developing portable customizations that take advantage of all the technology currently available around Microsoft Dynamics GP and while no one can guarantee some of these technologies will prevail, at least you know that I will be on the look to find a way to move forward.


All my thanks to David Musgrave who has been a key contributor, mentor, and inspiration for a good number of my technical articles. Did I ever mention that he also likes to decorate cakes? This may well be a topic for a fun post.

Until next post!

Mariano Gomez, MIS, MVP, MCP, PMP
Maximum Global Business, LLC

No comments: