Visit Gigasoft's Web Site
ProEssentials v8 Help
Chapter 3: Excel 2013 ActiveX Walk-Through

The following information demonstrates how to create your first Excel ActiveX Charting implementation. It discusses installation, adding ProEssentials to a sheet, writing your first few lines of code, and shows the final results. 

This walkthrough uses 32 bit ActiveXs so this examples is for MS Excel 2013 32 bit.
Installation...

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 Access, MS Excel, Visual Basic for inclusion of ProEssentials components. You can manually register an ActiveX with "REGSVR32.EXE" found in your system32 or syswow64 on 64 bit systems. You can also use this utility to manually un-register an ActiveX by using the "-u" command.

 

PEGRP32F.DLL

ProEssentials Pro DLL
PEGO32F.OCX Graph Object
PESGO32F.OCX Scientific Graph Object
PE3DO32F.OCX 3D Scientific Graph Object
PEPSO32F.OCX Polar Object
PEPCO32F.OCX Pie Chart Object

 

PEGRPSF.DLL

ProEssentials Standard DLL
PEGOSF.OCX Graph Object
PESGOSF.OCX Scientific Graph Object
PE3DOSF.OCX 3D Scientific Graph Object
PEPSOSF.OCX Polar Object
PEPCOSF.OCX Pie Chart Object
 

New Excel... Template...

From the New menu, create a new Fitness tracker spreadsheet from list of templates.

msaccess activex tutorial

The sheet opens showing the Fitness tracker template.

Right click the top menu, select Customize the Ribbon... then select the Developer check box to enable the Developer top menu.



With the Dashboard sheet showing...

 

Now click 1 Developer menu, 2 Insert Menu, and 3 the More Controls icon as shown below..

msaccess activex reading table code
This opens the More Controls dialog showing all ActiveX controls registered on the system.

Note both ProEssentials v7 and v8 setup EXEs have been installed as both v7 and v8 are showing within the list of controls.

Highlight the Gigasoft Pego v8 control and select OK. This places the control on your sheet.

Adding chart to the sheet...

Referring to below image, delete the existing chart and position the ProEssentials chart in place.

Toggling Design mode off and on will allow chart to redraw itself.

excel activex chart alternative

 

Adding Worksheet Activate event...

Select Developer, and then View Code menu item to show the VB Code Editor.
msaccess chart in design view.

Adjust the list boxes above code window so 'Worksheet' and 'Activate' items are shown. Then add the code below within the Activate event. Refer to image below showing the Excel VB code window.

 

Enter code within Activate event... Excel VBA code to read cells...

Pego1.MainTitle = ""
Pego1.SubTitle = ""
Pego1.PrepareImages = True
Pego1.CacheBmp = True
Pego1.AntiAliasGraphics = True
Pego1.AntiAliasText = True
Pego1.RenderEngine = PERE_DIRECT2D
Pego1.BitmapGradientMode = True
Pego1.QuickStyle = PEQS_LITE_SHADOW
Pego1.DeskColor = Pego1.PEargb(255, 255, 255, 255)
Pego1.FixedLineThickness = True
Pego1.SubsetLineTypes(0) = PELT_MEDIUM_THICK_SOLID
Pego1.FontSize = PEFS_LARGE
Pego1.FontSizeGlobalCntl = 1.8
Pego1.FixedFonts = False
Pego1.TextColor = Pego1.PEargb(255, 160, 160, 160)
Pego1.SubsetColors(0) = Pego1.PEargb(140, 0, 0, 220)
Pego1.SubsetGradientStyle(0) = PEPGS_RADIAL_BOTTOM_LEFT
Pego1.DataPrecision = PEDP_NODECIMALS
Pego1.ShowXAxis = PESA_NONE
Pego1.ShowYAxis = PESA_GRIDNUMBERS

Dim nRows As Integer
nRows = Sheet2.UsedRange.Rows.Count - 6
Pego1.Subsets = 1
Pego1.Points = nRows

Dim i As Integer
For i = 0 To nRows
Pego1.YData(0, i) = Sheet2.Cells(7 + i, 3)
Next i

Pego1.PEactions = REINITIALIZE_RESETIMAGE
Pego1.PEactions = INVALIDATE_RECT

This code both initializes the chart with settings and passes data.

 

This code is slightly unique to MS Excel and uses the...

Sheet.UsedRange.Rows.Count

to determine the number of rows to chart.

 

Once you become familiar with MS Excel VB code to load cells as needed, there are more advanced features to copy blocks of memory with our PEvset function.

 

Knowing how to use VB code to read Excel cells into your charts is the most robust method of using ProEssentials. Having the option of sending some cells to a Subset and others to our Annotation features will allow for full creativity with the ProEssentials product.

 

The below code windows show the VBA editor with code as shown.

 

Excel VB Code Window...

 

 

Excel ActiveX charting example...

Save the project, close the VB code editor, and toggle out of Design Mode...

ActiveX Chart inside MS EXCEL