Hybrid - Using Menus for Visual Studio Tools from VBA Example
After the release of Menus for Visual Studio Tools for Microsoft Dynamics GP 10.0, my good friend Mariano Gomez asked me if it was possible to use the tool to add menus from Visual Basic for Applications (VBA).
Mariano was initially working on an approach that would expose the Menus for Visual Studio Tools .NET assembly to COM interop by registering and creating a type library with the Assembly Registration tool (REGASM.EXE). However, he was not able to get this working due to an issue with the Dexterity.Bridge assembly used by the Menus for Visual Studio Tools assembly.
So, when Mariano approached me, I thought that we should be able to use standard VBA events to handle the menu registration and callback events and use the Continuum Integration Library to call pass through Dexterity sanScript to call the API (Application Programming Interface) functions.
Because the parameter handling of the Continuum Integration Library does not work with VBA, we had to use the DUOS (Dynamics User Object Store) as a method of passing parameters between the Dexterity and VBA code. This works well as the DUOS is natively visible from VBA and Dexterity. The method used creates DUOS records from VBA with blank values for each of the parameters to pass. These table records in the SY_User_Object_Store (SY90000) table are updated by the Dexterity code with the values to return. Once control is returned to VBA, it can read the values back from the DUOS. After the values are returned to VBA, the records in the DUOS can be removed.
If you start with the sample code attached to this post, the following steps have been already completed for you. To be able to add the hidden Menus for Visual Studio Tools API window to VBA you will need to get the window opened. Below are the steps to achieve this:
- Use the Support Debugging Tool in Advanced Mode to access the Runtime Execute window and execute the following code in the context of the Menus for Visual Studio Tools Product:
open window API of form VSTM_Command_Form;
- Then use Tools >> Customise >> Add Current Window to Visual Basic to expose the window to VBA and then use Tools >> Customise >> Add Fields to Visual Basic... and click on the Register Button and Command ID fields to expose them to VBA. You can then close the window.
- As closing the window will also close the VSTM_Command_Form form, you will either need to restart the application or use Runtime Execute to re-open the form with the following code:
open form VSTM_Command_Form;
- You can close the Runtime Execute window now.
- From the menus select Microsoft Dynamics GP >> Tools >> Customise >> Visual Basic Editor to open the Visual Basic environment. Expand the Menus for Visual Studio Tools product and double click on the internal (Window) module.
- Under Properties for the internal window change the (Name) property to API.
You can then use the Register_BeforeUserChanged() event to register your menu items and the CommandID_BeforeUserChanged() event to handle the callbacks. To call the Menus for Visual Studio Tools API you will need to use pass through Dexterity sanScript. Please look at the attached code sample attached for an example of adding items to the Inventory Cards menu.
Mariano has taken my sample code which is based on the same C# and VB demonstration code as provided with Menus for Visual Studio Tools and created a practical example of how it can be used to add a VBA user form to the Microsoft Dynamics GP menus. Have a look at his post, Using Menus for Visual Studio Tools from VBA - Calling native VBA forms.
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 means that any solutions written using this method will not upgrade to v11.0.
NOTE: This customisation uses a method of executing Dexterity sanScript code from VBA which is unsupported by Microsoft.
Example code for v10.0 is attached at the bottom of the article.
Please see the "Installation Instructions.txt" file in each version's archive for more information.