Thursday, December 29, 2016

Excel VBA pie chart in userform

Used resources: Windows 7 Professional 32 bit - MS Excel 2010 32 bit - VBA 6.0

One day I was playing around with additional controls in a VBA project and I found an ActiveX control for drawing 2D and 3D charts within the user form. The control name was "Graphics server extension" and its source file is Graph32.ocx found in System32 folder in windows directory. This control is developed by "Bits Per Second Ltd" company and it does not have any documentation over the internet at all. I have no idea about the license of this control and how to make it legal.

You can download the library file from the following link. It has the following description:

Description: GRAPH OLE Control DLL
Company: Bits Per Second Ltd
Architecture: 32 bits
File Size: 216096 Byte ( 211.03 KB )

It also have the dependencies shown below:

In this tutorial, I am going to show how simply to programmatically add data to pie chart using Excel VBA.

[1] On screen initialization, clear all data in graph:

'Reset (clear) all data in graph
Graph1.DataReset = graphAllData

[2] Define number of sectors or data points in pie chart:

'Set number of sectors (points) in pie chart
Graph1.NumPoints = 3    ' Three sectors
[3] Add data to pie chart (percentage of each sector). Make sure that the summation of data is 100%:

'Define pie chart values. Method 1: using array
Graph1.GraphData = Array(50, 20, 30)

'Define pie chart values. Method 2: one-by-one

Graph1.Data(1) = 50     ' 50% sector

Graph1.Data(2) = 20     ' 20% sector

Graph1.Data(3) = 30     ' 30% sector
[4] Change color of data

' Define data colors using the default palette
Graph1.Color(1) = 9     ' Blue color index = 9
Graph1.Color(2) = 10    ' Green color index = 10
Graph1.Color(3) = 12    ' Red color index = 12

[5] Add legend data

' Define data legend
Graph1.Legend(1) = "Sector 1"
Graph1.Legend(2) = "Sector 2"
Graph1.Legend(3) = "Sector 3"

[6] Finally, the most important step, is programmatically redraw (update) the graph after adding data to it.

Graph1.DrawMode = graphDraw     ' Update the graph

Enjoy, feel free to ask


Excel VBA pie chart in user form

Excel VBA ActiveX graph

Graphs32.ocx tutorial

Graphs32.ocx documentation

Graphs32.ocx pie chart

Extended graphics server - pie chart

Bits Per Second Ltd - Extended graphics server