Microsoft Excel Charting Tutorial (ActiveX VBA)

The following information demonstrates how to use Excel and ProEssentials ActiveX Charting. ProEssentials includes an Excel add-in called PeExcelAddin.dll

The ProEssentials add-in bridges the use of an image/shape on the sheet with an underlying chart window. This allows improved scientific, engineering, and financial charting within Excel, such as improved 3D, improved multiple axes, and improved end-user customization and export.

If the ProEssentials add-in is not installed the png representation persists. The add-in is purely a local per machine resource with no network dependency.

Double clicking the chart will activate the chart, enlarging in a modal window, allowing zooming, rotation, popup menu customization, export, etc.

Our AI Knowledge and AI-Resources will help write 100% non-hallucinogenic OCX based VBA code.

While working with code, pressing F1 in the VBA editor will launch PEONLREF.CHM for local intelligent help.

When installing ProEssentials, the setup program installs the ProEssentials DLL and ActiveX interfaces into the system directory. The setup program also registers the ActiveXs with the operating system, which prepares MS Excel, VBA for inclusion of ProEssentials components. You can manually register an ActiveX with "REGSVR32.EXE" found in your system32 on 64 bit systems. You can also use this utility to manually un-register an ActiveX by using the "-u" command.

Along with the below ActiveXs, the ProEssentials Excel add-in is installed in the ProEssentials10/Excel folder.

ProEssentials v10 only supports the 64 bit version of Excel. The below files are installed into the System32 folder on a Win64 system.

• PEGRP64H.DLL — ProEssentials x64 64 bit DLL
• PEGOH.OCX — Graph Object
• PESGOH.OCX — Scientific Graph Object
• PE3DOH.OCX — 3D Scientific Graph Object
• PEPSOH.OCX — Polar Object
• PEPCOH.OCX — Pie Chart Object

The below files are installed in the ProEssentials10/Excel folder.

• PeExcelAddin.Dll — Excel Plug-In Dll
• PeDemo.xlsm — Example Excel Workbook
• Install_addin.bat — Batch file to install add-in to Excel
• Uninstall_addin.bat — Batch file to uninstall add-in from Excel

Double click the Install_addin.bat batch file and agree to elevated privilege to install.

Now you can double click the PeDemo.xlsm demo sheet to open Excel along with the PeDemo workbook. You will be asked to enable macros and trust the sheet so the VBA code may run.

To add a chart to a sheet: First select a range of cells where the chart will be inserted. Select the ProEssentials top menu. Then select from the Insert Chart section.

The Add-In will automatically name the chart/png. PeChart_Pego_Pego1

Press Alt-F11 to open the VBA editor. Double click ThisWorkbook to open the VBA editor which will default to the Open event.

Within the Open event, you will always use similar code as our demo's Open event. This small amount of code will search for specially named shapes and create hidden chart windows that will initialize / refresh the shape's png.

Insert a new code Module. Within this module's General coding area, add a subroutine that follows this pattern.

The last portion of the shape's name — Sub Pego1 followed by _Initialize()

When the above Workbook_Open event runs, the ProEssentials Add-In will connect to all charts on the sheet, and then search and call any corresponding Initialize functions.

This routine should always follow the pattern shown:

1. Retrieve the Add-In object mgr.
2. Use mgr to retrieve the chart window iPego1 of type Pego.
3. End with calling mgr.RefreshImage "Pego1"

The code shows re-defining the returned object as iPego. This is important as it will enable the VBA editor to know the type lib and provide intellisense while writing code.

Pressing F1 with the cursor on ProEssentials syntax will launch PEONLREF.CHM context sensitive help.

See the readme file within the ProEssentials10/Excel folder for more information. The default state of the Add-In does not enable the Excel ProEssentials menu. This allows deployment for non-developers. The Add-in will still enable double-clicking and right-clicking the chart for activation and customization.