Calling Excel Add-In functions from a Macro

From support mail:

Q: I have done quite a bit with your Macro language. However, I have a question about your Excel Add-In. Do you know how I could call it from an Excel Macro? I tried recording a Macro and calling your Add-In, but it didn't show in the code when I was done.

A: Unfortunately Excel doesn't record calls to Add-In functions when recording a macro using keystrokes/button presses. But the fix is simple enough: Go through your normal steps in your macro recording just before you call XYXY or XYYY or whatever. Stop recording. For Excel 2007 or 2010 select the Developer tab, click Macros, select your macro, then click Edit. On Excel 2003 select Tools>Addins>Macros>select macro>Edit. At the end of the macro add a new line with

Call XYYY ' or whatever

And one final step: in the VBA window select Tools>References and check dplotlib.

If you are prompted for a password for the dplotlib entry on this screen, it is "dplot", without the quotation marks.

Edit: Starting with the Add-In distributed with v2.2.8.1 on March 9, 2010 you have access to several Add-In functions that were previously marked "private" and so were inaccessible to your macros. Namely DPlotGetActiveDocument, DPlot_Command, and DPlot_Request. How is this useful? If the default values for scaling, fonts, tick mark intervals, plot size, or whatever set by Add-In functions are not to your liking, you can control just about any aspect of the plot with a call to DPlot_Command, as in the following:

To call DPlot_Command or DPlot_Request you need the index of the currently active document (XYYY and other Add-In commands activate the plots that they create). That's where DPlotGetActiveDocument comes in. The DPlot_Command call in this case sets the tick mark interval to 365 (days, in this case) on the X axis and $1000 on the Y axis. For a rather lengthy list of all available DPlot_Command commands, see the Help file topic.

Important Edit:
When finished with calling any DPlot functions in your macro, if you have called DPlot_Command or DPlot_Request, you should disconnect from the currently active DDE conversation. Otherwise the plot will work as expected, but certain mouse operations (e.g. displaying coordinates while moving your mouse across the plot) will not work as expected, as DPlot will think there is still an active DDE conversation and responding to those actions is not appropriate. Disconnecting from a conversation can be accomplished simply with:

Call DPlot_Finish(doc)

About this Entry

This page contains a single entry by David Hyde published on March 6, 2010 3:41 PM.

Moving Average was the previous entry in this blog.

Square root symbol in text is the next entry in this blog.

Find recent content on the main index or look in the archives to find all content.



Powered by Movable Type 4.25