Microsoft Excel Charting Walkthrough using ActiveX and Visual Basic
EXCEL CHARTING Walk Through Tuturial in Visual Basic
ProEssentials ActiveX Charting components are used when creating stand-alone client-side EXEs or within containers that accept ActiveX components. This ActiveX Charting Walk-through includes instructions for Microsoft Excel 2007-2017 and should apply to later and earlier versions of Excel. For Access Click here for Access Charting ActiveX Walkthrough
- If you haven't seen the v9 canned demo? it's only 17M and does not require an install, just run it.
- Download our Winforms C# Chart Demo + WPF Demo + 100% native MFC Demo.
- Only Experience equals Knowledge: Size, Shape, Right Click, Export, Zoom (especially date and log scales), Pan, and Rotate.
- Watch 100 Million points continuously updated v9-v10 Performance Preview.
For help finding ActiveX specific features in our help system, the .Net Reference section is the best source for researching properties. Our help topics show ActiveX OCX specific syntax in the OCX/VCL header near top of topic.
When installing ProEssentials, the setup program installs the ProEssentials 32 bit DLL into the System32 directory on 32 bit systems. On 64 bit systems, the 32 bit DLL is installed in SysWow64 and the 64 bit DLL is installed in System32.
The setup also installs the following Charting OCX files into the same system folders as DLLs and registers them. Note, you do not register our DLLs. Note, the 32 bit and 64 bit OCXs have the same file names and classIDs and the OS will correctly invoke the appropriate version based on the run time need. The relevant files are:
Pro Components:
- PEGOG.OCX : Graph Component
- PESGOG.OCX : Scientific Graph Component
- PE3DOG.OCX : 3D Scientific Graph Component
- PEPSOG.OCX : Polar Smith Component
- PEPCOG.OCX : Pie Chart Component
- PEGRP32G.DLL : 32 bit Pro DLL
- PEGRP64G.DLL : 64 bit Pro DLL
Standard Components:
- PEGOSG.OCX : Graph Component
- PESGOSG.OCX : Scientific Graph Component
- PE3DOSG.OCX : 3D Scientific Graph Component
- PEPSOSG.OCX : Polar Smith Component
- PEPCOSG.OCX : Pie Chart Component
- PEGRPSG.DLL : 32bit Standard DLL
- PEGRP6SG.DLL : 64bit Standard DLL
The following information demonstrates how to create your first ProEssentials Excel ActiveX Charting implementation using the Visual Basic. It discusses using the OCX charting components to add interactive engineering and scientific charting content to your Excel spreadsheets. Please see the other charting examples provided within the product/evaluatio; including a larger MS Access example database already containing charts and example code on various forms.
1) From the [New] menu, create a new [Wedding Budget] spreadsheet from provided templates.
The spreadsheet template project opens and shows the example data and chart.
2) Right click the top menu, select Customize the Ribbon... then select the Developer check box to enable the Developer top menu.
3) With the Wedding Budget Summary sheet showing...
click 1 Developer menu, 2 Insert Menu, and 3 the More Controls icon as shown below..
This opens the More Controls dialog showing all ActiveX controls registered on the system.
Highlight the Gigasoft Pego v9 control and select OK. Then left click and drag to draw the control as shown below.
Toggle the Design Mode menu shows the control in the default state, 1 subset, 4 points.
4) Right Click the chart and select Properties and verify and / or set the Name property to Pego1.
5) Select Developer, and then View Code menu item to show the Visual Basic Code Editor.
The list boxes at top of code window should be General and Declarations. Then add the code below to create a LoadData function to pass data from within spreadsheet to the chart.
Enter the code as shown below.
Private Sub LoadData()
Dim nRows As Integer
nRows = 10
Pego1.Subsets = 1
Pego1.Points = nRows
Dim i As Integer
For i = 0 To nRows - 1
Pego1.YData(0, nRows - i - 1) = Sheet2.Cells(7 + i, 5)
Next i
For i = 0 To nRows - 1
Pego1.PointLabels(nRows - i - 1) = Sheet2.Cells(7 + i, 3)
Next i
End Sub
Adjust the list boxes at top of 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. You may copy and paste, but also try to write a few lines that use enums to see how intellisense works.
Pego1.MainTitle = "Wedding Budget - Actual"
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_MEDIUM
Pego1.FontSizeGlobalCntl = 1.5
Pego1.TextColor = Pego1.PEargb(255, 0, 0, 0)
Pego1.SubsetColors(0) = Pego1.PEargb(180, 0, 0, 180)
Pego1.DataPrecision = PEDP_TWODECIMALS
Pego1.PlottingMethod = GPM_HORIZONTALBAR
Pego1.GridLineControl = PEGLC_YAXIS
Pego1.DataShadows = PEDS_3D
Pego1.AllowHorizontalBar = True
Pego1.ShowYAxis = PESA_GRIDNUMBERS
Pego1.ShowXAxis = PESA_GRIDNUMBERS
Pego1.YAxisOnRight = True
Pego1.AxisFormatY = "$|,|"
Pego1.YAxisLineLimit = 5
Pego1.GridBands = False
Pego1.AllowDataHotSpots = True
Call LoadData
Pego1.PEactions = REINITIALIZE_RESETIMAGE
Your project code should look similar to...
6) The code above enabled the DataHotSpot event, so we should place some appropriate code in the DataHotSpot event.
To add the DataHotSpot event, we need to be inside the Visual Basic editor.
Use the top drop down list boxes to 1, select Pego1 and 2, select the DataHotSpot event.
Add the following code to the Pego1_DataHotSpot event.
Call MsgBox("Subset " + Str$(SubsetIndex) + _
", Point " + Str$(PointIndex) + _
" with a value of " + Str$(Pego1.YData(SubsetIndex, PointIndex)))
7) Use the top drop down list boxes to 1, select Worksheet and 2, select the Calculate event.
Add the following code to the WorkSheet_Calculate event. Then repeat for the Change event. These two events will cause the Gigasoft ActiveX Charting Component to update anytime the spreadsheet values change.
Call LoadData
Pego1.PEactions = REINITIALIZE_RESETIMAGE
7) Save the spreadsheet, toggle Design Mode off and success, your window should show the Gigasoft chart within Excel as below. Move the mouse over a bar and click to trigger the DataHotSpot event.
This completes this walkthrough.
Please read the remaining sections within Chapter 3 and review the demo code and documentation that's installed with the eval/product.
Once installed, the demo program can be accessed via shortcut...
Start / ProEssentials v9 / PeDemo
Note that our main charting demo is replicated in WPF and Winform C#.NET, VB.NET, VC++ MFC, Delphi, Builder all accessible from where you installed ProEssentials. These are great for modifying an existing demo to test potential modifications before implementing within your applications.
Our Mission
Your success is our #1 goal by providing the easiest and most professional benefit to your organization and end-users.
We are Engineers
ProEssentials was born from professional Electrical Engineers needing their own charting components. Join our large list of top engineering companies using ProEssentials.
Thank You
Thank you for being a ProEssentials customer, and thank you for researching the ProEssentials charting engine.