Excel Charting tutorial with basic VBA code to read cells into a chart.

[ MS Excel ActiveX Charting Improvement ]

The Charting ActiveX alternative replacement for Microsoft Excel.

  • See the new v8 demo (only 15 meg and uninstalls no problem.)
  • Includes ActiveX Charting native demos.
  • Size, Shape, Right Click, Export, Zoom (especially date and log scales), Pan, and Rotate.
  • Study the code to discover the ease of use.
  • Your company, team, vendors, and end-users deserve the best.
See the demo...
activex chart for finance engineering and more activex Chart 3d surface scatter wireframe terrain

The following information demonstrates how to create your first MS Excel Charting spreadsheet. This page covers installation, adding ProEssentials to a spreadsheet, wiring events and adding a few lines of code.

This walkthrough uses 32 bit ActiveXs so this tutorial 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 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.

Excel 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.
Excel ActiveX use needs Developer tools.
Excel activex options.

With the Dashboard sheet showing...

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

Excel activex insert More Controls.
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.
Inserting ActiveX within Excel spreadsheet.

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.
excel 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 VB code window showing ActiveX code to load data from cells.
Excel ActiveX charting example...

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

ActiveX Charting inside MS EXCEL
Online developer reference

Complete online technical reference to the ProEssentials product. Chapter 2's .NET Reference is the best mechanism to navigate the large quantity of properties and features.  Walk-Throughs of .NET charting in VB.NET, C#.NET, ASP, VC, VB6, and Delphi get you started quickly.

learn more about activex charting, see Chapter 3

Online interactive demo

Financial, Engineering, Scientific, and Business examples give you an instant taste of ProEssentials' power.

Our online charting demo

Letter from the President

Thank you for researching. Call, email and let's talk charting.

Thank you.