Chapter3 Excel ActiveX Walk-Through

ProEssentials Charting Library

Click here to see this page in full context

Chapter 3: Excel ActiveX Walk-Through, v2021 / Office365

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.

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.

Note that 32 and 64 bit OCXs use the identical file name but are different files.

PEGRP64H.DLL	ProEssentials x64 64 bit DLL
PEGRP32H.DLL	ProEssentials x86 32bit 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

New Excel... Template...

From the New menu, create a new Weight Loss Track spreadsheet from list of templates.

msaccess activex tutorial

The sheet opens showing the Weight Loss 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...

Right click the existing chart and select Delete. The Sheet now shows no charting control. .

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 ProEssentials v8 and v9 setups have installed v8 and v9 and are showing within the list of controls.

Highlight the Gigasoft Pego v10 control and select OK.

NOTE, it's important to realize Excel is now waiting for you to place the control. So immediately click the location where to install the control and drag as needed to size the control. This places the control on your sheet. If the Sheet is in design mode you may tweak the position and size. Toggle between DesignMode and non-DesignMode to force a redraw to clean up the chart image.

Adding chart to the sheet...

Togging out of design-mode should show a similar result as below image.
excel activex chart alternative

While in Design-Mode, right click chart and select Properties.

Double check that the Name property shows Pego1

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.PEactions = REVERT_TO_DEFAULTS

' General Init
Pego1.RenderEngine = PERE_DIRECT2D
Pego1.PrepareImages = True
Pego1.CacheBmp = True
Pego1.FixedFonts = True
Pego1.AntiAliasGraphics = True
Pego1.AntiAliasText = True
Pego1.FontSize = PEFS_MEDIUM
Pego1.FontSizeGlobalCntl = 1.2
Pego1.LabelBold = True

' set MarkDataPoints and enlarge
Pego1.MarkDataPoints = True
Pego1.MaximumMarkerSize = PEMPS_LARGE
Pego1.HotSpotSize = 5

Pego1.MainTitle = ""
Pego1.SubTitle = ""

' Configure Grid
Pego1.ShowYAxis = PESA_GRIDNUMBERS
Pego1.YAxisOnRight = True
Pego1.ShowXAxis = PESA_GRIDNUMBERS
Pego1.GridBands = False
Pego1.GridLineControl = PEGLC_YAXIS
Pego1.GridLineAlpha = 25

' Set Chart Colors
Pego1.DeskColor = Sheet1.Cells(1, 1).Interior.Color
Pego1.TextColor = Pego1.PEargb(255, 200, 200, 200)
Pego1.GraphForeColor = Pego1.PEargb(255, 200, 200, 200)
Pego1.GraphBackColor = Sheet1.Cells(1, 1).Interior.Color
Pego1.BorderTypes = PETAB_NO_BORDER

' Set Plotting Method and Subset Colors
Pego1.PlottingMethod = GPM_AREA
Pego1.SubsetColors(0) = Pego1.PEargb(255, 66, 186, 195)
Pego1.AreaGradientStyle = PEPGS_VERTICAL_ASCENT
Pego1.SubsetGradientStartColors(0) = Pego1.PEargb(255, 247, 176, 43)
Pego1.AreaBorder = True

Pego1.AutoMinMaxPadding = 50 ' To add padding to match original chart

Pego1.ShowTickMarkRY = PESTM_TICKS_HIDE ' Hide Ticks

' Determine Quantity of Data
Dim nRowsWithData As Integer
Dim r As Integer
For r = Sheet1.UsedRange.Rows.Count To 0 Step -1
If (Sheet1.Cells(r, 2) <> 0) Then
nRowsWithData = r
Exit For
End If
Next r

' Send Data to Chart
Pego1.Points = nRowsWithData - 7
For r = 0 To Pego1.Points - 1
Pego1.YData(0, r) = Sheet1.Cells(8 + r, 3)
Pego1.PointLabels(r) = Format(Sheet1.Cells(8 + r, 2), "M/D")
Next r

' Add Line Annotation
Pego1.HorzLineAnnotation(0) = Val(Sheet1.Cells(3, 2))
Pego1.HorzLineAnnotationType(0) = PELAT_MEDIUM_SOLID
Pego1.HorzLineAnnotationColor(0) = Pego1.PEargb(255, 247, 176, 43)
Pego1.HorzLineAnnotationText(0) = ""

' Add another Line Annotation only to show Text in different color
Pego1.HorzLineAnnotation(1) = Val(Sheet1.Cells(3, 2))
Pego1.HorzLineAnnotationType(1) = -1
Pego1.HorzLineAnnotationColor(1) = Pego1.PEargb(255, 240, 240, 240)
Pego1.HorzLineAnnotationText(1) = "|lGOAL WEIGHT"
Pego1.LineAnnotationTextSize = 120

Pego1.ShowHorzLineAnnotations = True

Pego1.PEactions = REINITIALIZE_RESETIMAGE

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. This is ideal when handling millions of data points.

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.

Note we added one extra event to update the chart when Worksheet changes...

Private Sub Worksheet_Change(ByVal Target As Range)

Call Worksheet_Activate

End Sub

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


Gigasoft Home \| News \| Contact Us \| Purchase a new License or Upgrade Product/Patches \| Resource Files Online Documentation
©2026 Gigasoft, Inc. \| All rights reserved. Gigasoft is a registered trademark, and ProEssentials a trademark of Gigasoft, Inc.