If you elected to install the DPlot Interface add-in when you installed DPlot, you should see a "DPlot" menu entry in Microsoft Excel 2003 and previous versions:
In Excel 2007, if you select the Add-Ins tab then you should see a DPlot entry at the left of the screen under the Home tab:
In either case, if you do not see something similar to the above then either 1) you did not install the optional Add-In when you installed DPlot, 2) the Add-In actually was installed but a security setting in Excel has hidden it, or 3) a user with an account different than your own (most likely an administrator) installed DPlot for you. If you did not install the Add-In you can do so simply by re-installing DPlot to the same folder where it currently exists (the default is c:\Program Files\DPlot), this time making sure to check the "MS Excel Add-In" box. You will need to exit DPlot before re-installing.
If you did install the Add-In but do not see a DPlot menu in Excel:
In Excel 2003 and previous verions
Select "Add-Ins" on the Tools menu:
There should be a "DPlot Interface" box:
Check that box, then click OK. If there is not a "DPlot Interface" box, then most likely DPlot was installed by a different user than you, probably an administrator because you have a limited-privileges user account. In that case click the "Browse" button and navigate to the folder where DPlot was installed (default location=c:\Program Files\DPlot). Select the file dplotlib.xla then click OK.
In Excel 2007 and later
Click the Office button in the upper left corner:
On the resulting screen click "Excel Options" at the bottom of the window.
Click "Add-Ins" in the left pane.
At the bottom of the next screen, ensure that the "Manage" selection is "Excel Add-Ins" then click "Go"
The next dialog is identical to that for Excel 2003 shown above. Check the "DPlot Interface" box (or follow the instructions above if that box is not present), then click OK.
If you properly installed the Add-In but now when starting Excel 2003 or previous versions you see an error message complaining about Run-time error '438', the most likely cause is some previous operation that has, for whatever reason, caused Excel's toolbar to temporarily lose its mind. You can correct this problem with the following steps:
|•||Start Excel. Select Tools>Customize. Click the Toolbars tab.|
|•||Near the end of the Toolbars list, find Worksheet Menu Bar. Select that entry so that it is highlighted.|
|•||Click the Reset button. When asked whether you really want to do this, click OK.|
|•||Click the Close button, then exit and restart Excel.|
|•||If you previously deactivated the DPlot Add-In to get rid of this error message, select Tools>Addins, check the DPlot Interface box, then click OK.|
In Excel 2013 and later
If you followed the instructions above under "In Excel 2007 and later" and either the Add-Ins tab is not shown or the DPlot menu is not displayed when clicking the Add-Ins tab, it is most likely because of new functionality in Excel 2013 that gives every workbook a unique ribbon, which the Add-In is not currently set up to handle. Rather it might handle it, but not in all cases. In this case you will need to load the DPlot Add-In separately as a workbook. First, unload the Add-In by selecting Excel Options>AddIns>Manage as described above, then uncheck the "DPlot Interface" entry and click OK. Next open whatever workbook you wish to work on if it is not already open, and finally open the Add-In's source workbook DPLOTLIB.XLS in the DPlot folder below your "My Documents" folder. You should now see an Add-Ins tab on the ribbon and a DPlot menu when you click "Add-Ins". This situation will be resolved in a future DPlot release.
To use the Add-In, start Microsoft Excel and open the worksheet containing the data you'd like to plot in DPlot. If you are unfamiliar with the Add-In you will find the example Excel spreadsheet EXAMPLES.XLS useful. This sheet includes examples for all of the functions in the Add-In. EXAMPLES.XLS is installed to the DPlot folder below your "My Documents" folder and is also stored on the DPlot web site. (The links here to EXAMPLES.XLS may or may not open the file in Excel, depending on which service pack of the particular version of Windows you are using.)
The Add-In contains functions that allow you to pass data from Excel to DPlot, producing any type of plot that DPlot currently supports.
Key Add-In features:
|•||For all functions other than ZGrid (see below), you may use multiple selections if your data columns are not adjacent. This differs from using Copy/Paste, which results in copying ALL values within the extents of all selections.|
|•||Data is passed as floating point numbers and internally is independent of the formatting. With Copy/Paste, data is passed as text, exactly as it appears in your worksheet. If you only show 2 decimal places then the data is truncated to 2 decimal places in DPlot.|
|•||Blank cells are interpreted as you'd expect.|
|•||All functions other than OneD attempt to use the same formatting in DPlot that is used in Excel (dates, for example).|
Select alternating X,Y columns and produce an XY plot. Column selections may have differing numbers of rows.
Same as above, but X is taken from the first selected column and all subsequently-selected columns are Y values for separate curves. If only one column is selected, it is assumed to be Y values and X is generated, starting at 0 and incremented by 1. Y columns may have blank cells.
Creates 1 curve on an XY plot with X is taken from the first selected column, Y taken from the second selected column, and text labels in the third column. Label cells may be blank.
Creates a bar chart with non-numeric labels on the X axis and 1 or more groups of amplitudes.
Same as Bar chart command, with data sets in the same row rather than the same column.
Similar to XYXY, but data is arranged by rows rather than columns, with alternating X and Y values in adjacent columns. Each row is used to produce one curve.
Select an X, a Y, and a Z column (one or more selections). Produce a 3D surface plot.
Select one or more groups of X,Y,Z columns and produce a 3D scatter plot, one data set per X,Y,Z group.
Select a table with X in the first row, Y in the first column, and the remainder of the table filled with Z values for the corresponding X and Y. Produces a 3D surface plot.
Select one or more columns of amplitudes and produce a box-and-whisker plot.
Editing the Add-In
The Visual Basic for Applications (VBA) routines in DPLOTLIB.XLA are protected against editing, mainly to avoid confusion with other VBA functions you might create. If you are comfortable with VBA and would like to experiment with the add-in's features:
|1)||We strongly suggest that you first make a backup copy of the .XLA file. In Office 2000 and later versions, DPLOTLIB.XLA can be found in the \Microsoft\Addins folder below the Application Data folder. For earlier versions of Excel DPLOTLIB.XLA will be stored in the \Library folder below EXCEL.EXE. Save the backup copy of the Add-In to a different folder so that you won't become confused on Excel's Add-In's dialog.|
|2)||Unload the Add-In: Select Tools>Add-Ins and uncheck "DPlot Interface".|
|3)||Select Tools>Macro>Security. On the Security Level tab, ensure that "Low" is checked. Don't be concerned about changing your security level; this is only a temporary change that will enable you to edit and run VBA functions.|
|4)||Open the Add-In file (DPLOTLIB.XLA, see above for the location of this file) using File>Open.|
|5)||Select Tools>Macro>Visual Basic Editor:|
In the Project Explorer window, click on the + symbol next to DPlotLib (dplotlib.xla):
If you do not see the Project Explorer window, select Project Explorer on the View menu. After clicking on the + symbol next to DPlotLib (dplotlib.xla) you will be prompted for a password. Enter "dplot" in all lowercase letters (without the quotation marks).
|6)||The functions under Microsoft Excel Objects (This Workbook) simply create the DPlot menu and delete the menu if/when the add-in is unloaded. The meat of the add-in is under Modules in DPlotData. For more information on DPLOTLIB functions see the DPLOTLIB documentation at http://www.dplot.com/lib/index.htm|
|7)||After making any changes, save your work, exit the VBA editor, reset your security level if necessary, and re-load the add-in using Tools>Add-Ins.|
If you find that the Add-In commands almost, but not quite, meet your needs and you are reasonably comfortable with VBA, you can call the standard Add-In commands from your own VBA routine and then make changes to a plot, typically by sending command strings to DPlot. A couple of examples follow.
|1)||Data arranged in columns such that the normal Add-In commands can produce the desired plot from a selection:|
Dim doc As Long
Dim ret As Long
Dim Sel As Object
Dim row As Long
Dim rnge As String
Dim d As Double
Set Sel = Selection ' Preserve user's current selection
Range("D:D,I:I").Select ' dates in column D, cumulative monthly sales in column I
doc = DPlotGetActiveDocument()
If doc > 0 Then
' generate a new curve with amplitudes equal to the peak value for each month
' delete the original curve
ret = DPlot_Command(doc, "[DateFormat(""yyyy"")]")
' set the tick mark intervals to 365 days on X, $1000 on Y
ret = DPlot_Command(doc, "[TickInterval(1,365,1000)]")
' set the axis extents: whole years on X, 0 to next highest $1000 increment on Y
ret = DPlot_Command(doc, "[ManualScale(" & Str$(DateSerial(2001, 1, 1)) & ",0," & _
DateSerial(year(Now) + 1, 1, 1) & ",=(CEIL($YMAX/1000)*1000))]")
' generate 12-month moving average using previous 12 months
ret = DPlot_Command(doc, "[RunPlugin(""Moving Average"",""1,12,0,1"")]")
Sel.Select ' restore previous selection
A few notes on the above:
|a)||To call the Add-In functions 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. For a rather lengthy list of all available DPlot_Command commands, see Sending data to DPlot from another application.|
|b)||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:|
|c)||In the VBA editor select Tools>References and check dplotlib. If you do not do this you will get a "Sub or Function not defined" error from Excel.|
|2)||Data produced by another VBA module or data source or not conveniently arranged for the Add-In functions:|
Dim d As DPLOT ' DPLOT structure defined in the Add-In
Dim doc As Long
Dim ret As Long
Dim x(0 To 3) As Double
Dim y(0 To 3) As Double
x(0) = 1
x(1) = 2
x(2) = 3
x(3) = 4
y(0) = 1
y(1) = 2
y(2) = 3
y(3) = 4
d.Version = DPLOT_DDE_VERSION
d.DataFormat = DATA_XYXY
d.MaxCurves = 1 ' Must be >= number of curves
d.MaxPoints = 4 ' Must be >= maximum number of points for all curves
d.NumCurves = 1 ' Actual number of curves
d.NP(0) = 4 ' Number of points in first curve
d.LineType(0) = 1
d.ScaleCode = SCALE_LINEARX_LINEARY
DPlot_Plot8 d, x, y, ""
doc = DPlotGetActiveDocument()
If doc > 0 Then
ret = DPlot_Command(doc, "[AppShow()]")
The same notes from the previous example apply here.