Posts Tagged graph

Using Office Web Components to draw graphs on Excel userforms

The Office Web Components (OWC) are a series of components that you can add into a web page or any Office application. A typical use of the OWC in Office application is to add a graph in a user defined form. When I developed a userform for curve fitting, I’d like to see how good the data was fitted by a given optimized parameter set. If it is not good enough, user can rerun optimization process to refit the data even can get rid of some data points. The userform is an interactive tool for curve fitting. I really need draw a scatter plot and predicted curve on the userform. The way to achieve the goal is to utilize OWC objects.

The posts give a simple example to demonstrate how to use the OWC to draw a graph on a userform in Excel. To facilitate learning, here is an step-by-step instruction. I suppose you a;ready open Excel with an blank document.

  • Click Tools -> Macro -> Visual Basic Editor to open VBA for Application
  • Select VBAProject(Book1) in the Project window
  • Click Insert -> UserForm to create new userform in the selected VBA project
  • Put a command button to the userform
  • Find the Toolbox window and right click its blank area
  • Click Additional Controls … from the popup menu
  • Select “Microsoft Office Chart xx.xx” and click Ok button in the popup window. Now there is one more button in the toolbox window
  • Put a new control to the userform and adjust its size. The new control is the chart space holder that you can add new chart and series to it. The form looks like Figure 1.
    Figure 1
  • Double click the command button and switch to code view. Here you can enter code to generate graph. To demonstration purpose, I generate two data series. They will be drawn on a chart object. The code is listed at the end of the post.
  • Once you have done this. You can click “Run Sub/UserForm’ button on the toolbar. Then click the CommandButon1 in the userform windowto show the curve. The windows looks like Figure 2.
    Figure 2

The source code

Private Sub CommandButton1_Click()
    Dim i As Integer
    Dim NumObs As Integer
    NumObs = 10
    ReDim obs(NumObs) As Variant
    ReDim pred(NumObs) As Variant
    ReDim ot(NumObs) As Variant
    For i = 1 To NumObs
        ot(i) = i * 5
        obs(i) = i * 5 + Rnd() * 5
        pred(i) = i * 5 + Rnd()
   Dim oChart As WCChart
   Dim oSeries1 As WCSeries
   Dim oSeries2 As WCSeries
   With ChartSpace1
        Set oChart = .Charts.Add
        Set oSeries1 = oChart.SeriesCollection.Add
        oChart.Type = chChartTypeScatterMarkers
        oSeries1.Type = chChartTypeScatterMarkers
        oSeries1.SetData chDimXValues, chDataLiteral, ot
        oSeries1.SetData chDimYValues, chDataLiteral, obs
        Set oSeries2 = oChart.SeriesCollection.Add
        oSeries2.Type = chChartTypeScatterLine
        oSeries2.SetData chDimXValues, chDataLiteral, ot
        oSeries2.SetData chDimYValues, chDataLiteral, pred
   End With
   With oChart
        With .Axes(chAxisPositionBottom)
            .MajorUnit = 20
            .MajorTickMarks = chTickMarkInside
            .MajorGridlines.Line.Color = RGB(230, 230, 230)
        End With
        With .Axes(chAxisPositionLeft)
            .MajorUnit = 5
            .MajorTickMarks = chTickMarkInside
            .MajorGridlines.Line.Color = RGB(230, 230, 230)
        End With

        With .PlotArea
            .Interior.Color = RGB(255, 255, 255)
        End With
        Dim xmin, xmax
        xmin = 1000
        xmax = -1000
        For i = 1 To NumObs
            If obs(i) < xmin Then
                xmin = obs(i)
            End If
            If obs(i) > xmax Then
                xmax = obs(i)
            End If
        'Set the maximum and minimum axis values
        .Scalings(chDimXValues).Maximum = Round(ot(NumObs) + 2, 0)
        .Scalings(chDimXValues).Minimum = Round(ot(1) - 2, 0)
        .Scalings(chDimYValues).Maximum = 60 'Round(xmax + 1, 0)
        .Scalings(chDimYValues).Minimum = 0 'Round(xmin - 1, 0)

        'Change the marker and line styles for the series
        With oSeries1
            .Marker.Style = chMarkerStyleSquare
            .Marker.Size = 3
            .Interior.Color = RGB(0, 0, 0)
            .Line.Weight = 0
            .Line.Color = RGB(255, 255, 255)
        End With
        With oSeries2
            .Line.Weight = 1
            .Line.Color = RGB(0, 0, 255)
        End With
   End With
End Sub

The above code and steps were realized in Microsoft Excel 2000. For Excel 2003, you have do the following change: WCChart to ChChart and WCSeries to ChSeries. The OWC version change from 9.0 to 11.0. Other things are essentially same.

Download the sample userform and code – form-chart


Tags: , , , , , ,

eDigitizer user guide

As a researcher, you may encounter the following situations from time to time. You need to get original data for a graph published in a scientific paper. The simple way is to use a ruler and estimate the value of each data point by eye. If you want to get the data more accurately, you have to turn to digitization software. Back to 2002, I wrote a MATLAB program that can do the job. Unfortunately, you have to own a copy of MATLAB to use the program. As inquiries increase recently, the eDigitizer is implemented in the .NET environment. There is no MATLAB required. Now the brand new implementation can run under Windows with .Net framework version 2 installed. In addition. The following lists it main features.

  1. Zoom and rotate images
  2. Test the orthogonality of axes
  3. Digitize axes
  4. Digitize data points
  5. Save digitized data to a text file

Get eDigitizer

Visit to download a copy of the application. This is a green software. You do not need to install it. Just expand the downloaded zip file to a folder and click the executable file – edigitizer-net2.exe. Since it need Microsoft .NET framework 2.0 runtime library, you have to install .Net framework 2.0 runtime redistribution package from the Microsoft web site.

Launch eDigitizer

First launch eDigitizer by double clicking it. You have to find the program in your hard disk by using Windows explorer or similar applications. Figure 1 shows the main window of the eDigitizer.

Figure 1

There are two functional area in eDigitizer’s main window. On the left side is a fixed area that houses all function commands. First functional block is related to image processing, such as load, zoom, rotate, restore and save image. The second functional block is related to digitizing X and Y axes and testing perpendicularity of them. The last functional block includes functions to digitize series. You can digitize as many series as you want. All data will be stored in text file. On the right side includes a zooming area with multiple tabs, image tab, graph tab and data tab. It takes the major portion of the main window. The size of this area changes with the eDigitizer window. If you move the mouse cursor to this area, the cursor changes to a big blue cross. That can help users to locate points on the screen. Before you load any image to the system, these tab areas have nothing. Next step is loading image.

Load image

Loading an image is pretty simple. Just click “Browse” button on the top of left panel and an “Open an image” dialog window will pop up (Figure 2). You can navigate to the folder where images are stored and select one to open.

Figure 2

Once the selected image is loaded, the main window looks like Figure 3.

Figure 3

Digitize axes and test perpendicularity

Now you can directly click “Test” button in the Axes frame to test the orthogonality of the axes. Move the mouse cursor to the graph and it changes to the big blue cross. You have to sample three points (the origin, one point on X axis and one point on Y axis in sequence, Figure 4) in the coordinates.

Figure 4

Click the origin, one point on X axis and one point on Y Axis sequentially, and then you will see the Theta (θ) and Alpha (α) values in the Axes frame of the left panel (refer to Figure 3). Figure 5 shows the definition of the two angles. If the alpha value is less than 1 degree and theta is around 90 degree. The orthogonality of the axes is good. You can directly digitize data points without rotation of the image. Otherwise, you have to rotate the image based on the alpha value you got in this step.

Figure 5

Next step is digitizing X and Y axes. Click X or Y button in Axes frame. The two textboxes below the buttons will change to “Name/Unit” and “Values”. What you need to do is enter the variable name and unit of X or Y axes in the first textbox and enter a comma separate values based on you actual situation into the second textbox. For the sample image we see in Figure 3, we have the following:

X axis: Name/Unit: Time (Hour); Values: -12, 0, 12, 24, 36, 48, 60, 72
Y axis: Name/Unit: mRNA; Values: 0, 0.5, 1.0

After you enter the pair for X axis, you should click “Collect” button to digitize the points in X axis. You have to digitize the points in the same order as you enter them in the Values textbox. The application will know when you finish the digitization based on the number of values you entered in the Values textbox. Once all points are digitized, there will be linear equation with R2 value shown up just above the “Collect” button. For this situation, the intercept of the linear equation is -21.55 and the slope is 0.24 with a R2 of 1.0 (See Figure 6)

Figure 6

You can follow the same step to digitize the three points on Y axis.

Digitize series

Now we are ready to digitize our first series. Let’s pick “wt(col)” line to demonstrate the process.

  1. Enter “wt(col)” in the Name textbox in the Series frame
  2. Click “Collect” button
  3. Move cursor to image and it will change to a big blue cross
  4. Move the big blue cross to the center of the first open square on the left of the image and click it
  5. Move the big blue cross to the next open square and click it, repeat this step until you complete the last open square on the right side of the image
  6. Click “Commit” button in the Series frame. Note: before digitizing, this button is called “Collect”.
  7. Switch to “Graph” tab, you can see the digitized series like Figure 7
  8. Switch to “Data” tab, you can see the digitized data like Figure 8

Figure 7

Figure 8

Congratulations! You have completed your first digitization. You can click “Save” button in the Series frame to save your results to a file for late use. Is it simple? Yes, it is pretty simple. You can digitize as many series of you want and save them into one text file.



Tags: , , , , ,

Graph Digitization in MATLAB

Sometimes we need digitize graphs from scientific papers because we can not get the original data. The digitized data can be used to fit model or redraw graphs. How can we achieve the goal? At the beginning I think we can do that by reading the image data and classifying the data by a given threshhold. Then put the data to spreadsheet (EXCEL) and do further precessing. After I tried, I found it was difficult to do it in EXCEL.

I turned to MATLAB. I found there are several general functions in MATLAB that can handle this problem very easily. By using imread function to read the image data to memory, and image function to show the image, then ginput to get data points in the graphs. After all data point is digitized, use linear regression method to transfer the x and y coordiate values to actual values with the units used in the graph. Then show the data again and save them to text files.

The following code is a complete m function. You can modify it to fit your problem. The arguments of the function are:

  1. imgfilename : the filename of image file, must in the same directory as the m file is;
  2. xp : the actual values of X axis, a row vector;
  3. yp : the actual values of Y axis, a row vector;
  4. line1 : the name of the first line, a string;
  5. line2 : the name of the second line, a string;

The results will be saved to two seperate text files, one file per line. The first column is X value, the second column is Y values. In my case, X is time and Y is gene expression level.

function digitize(imgfilename,xp,yp,line1,line2)
a = imread([imgfilename '.bmp']);
A1=[ones(size(TIME)) TIME];
A2=[ones(size(TOC1)) TOC1];
XLerTime=[ones(size(XLer)) XLer]*C1;
YLerToc1=[ones(size(YLer)) YLer]*C2;
XlhyTime=[ones(size(Xlhy)) Xlhy]*C1;
YlhyToc1=[ones(size(Ylhy)) Ylhy]*C2;
hold on;
DATA1=[XLerTime YLerToc1];
DATA2=[XlhyTime YlhyToc1];
save([imgfilename line1 '.txt'],'DATA1','-ascii');
save([imgfilename line2 '.txt'],'DATA2','-ascii');

After the simple version was finished. I told my major professor and he suggest me to do some statistical tests for the digitizing processing. I rewrote the program. The source code is listed below. In this version, argument ‘lines’ has changed to a cell array, it can accommodate as many lines as possible according to your specific graphs.

function digitize(imgfilename,xp,yp,lines)
% This function can digitize as many lines as you indicate in 'lines'
% imgfilename - the filename of image file, a string
% xp - the actual data value of data points in X axis, a row vector
% yp - the actual data value of data points in Y axis, a row vector
% lines - the string name of lines, a cell array
a = imread([imgfilename '.bmp']);
% number of data points in X axis
% number of data points in Y axis
% number of lines
nline = length(lines);
% number of times to digitize axes
naxes = 3;

Y = []; % all Ys put into one vector
YY = []; % all Ys put into a matrix
% get the data points in Y axis for several times
for i=1:naxes
    Y = [Y;y];
    YY = [YY y];
% pool the data points from three repetition together
A=[ones(size(Y)) Y];
% linear regression model for Y axis
yps = repmat(yp,1,naxes);
% calculate the earror value
ym = mean(YY,2); % mean for each data point
for i=1:size(YY,2), YY(:,i)=YY(:,i)-ym;, end
yy = reshape(YY,1,size(YY,1)*size(YY,2));
yse = std(yy)
yse = CY(2) * yse

X = []; % all Ys put into one vector
XX = []; % all Ys put into a matrix
% get the data points in X axis for several times
for i=1:naxes
    X = [X;x];
    XX = [XX x];
% pool the data points from three repetition together
A=[ones(size(X)) X];
% linear regression model for Y axis
xps = repmat(xp,1,naxes);
% calculate the earror value
xm = mean(XX,2); % mean for each data point
for i=1:size(XX,2), XX(:,i)=XX(:,i)-xm;, end
xx = reshape(XX,1,size(XX,1)*size(XX,2));
xse = std(xx)
xse = CX(2) * xse

% get the data points for all lines
% after digitization of one line, press return key to advance to next line
XLine = cells(nline,1);
YLine = cells(nline,1);
for i = 1:nline
    XLine{i}=[ones(size(xLine)) xLine]*CX;
    YLine{i}=[ones(size(yLine)) YLine]*CY;
for i = 1:nline
    hold on;
    DATA=[XLine{i} YLine{i}];
    save([imgfilename lines{i} '.txt'],'DATA','-ascii');

This version really aspired me. It is not easy to use it because if you input some thing wrong. You need stop the program and run it again until you did everything right. MATLAB provide a tool, GUIDE, to make graphic user interface. Just by trying, I started to make a UI program to digitize the graph. And I succeeded. See the following screenshot.

In the GUI version, users can do the digitization one by one. The order is not very important. You can digitize Y axis, ten X axis. After that test the perpendicular property of two axes. Then digitize lines one by one as long as you give a different name for a line. The program can save the digitized data to different files. You do not need to do it no break. You can do it pierce by pierce as long as the window is not closed. This version give user the maximum flexibility to finish the work.
Because the source code of graphic user interface is too long. I just put it into the zip file. Please download the program and run it under MATLAB 6.5. Download all source code here – digitizeit



Tags: , , , , , , ,

Using R to create brick block graphs

R provides powerful graphics functions. We can use it to draw very complicated and beautiful graphs. Here provides a complete solution to draw brick block graph in R. The function name is brickblock that takes a matrix, graph height, a vector of x-labels, and groupname as inputs. The length of vector should be same as the second dimension of the matrix. The following lists the function code.

brickblock <- function(m, height, xlabs, groupname) 
	mdim <- dim(m); # dimmension of the input matrix
	margin <- 30;  # default margin
	padding <- 5;
	# create a new matrix filled with 0
	m1 <- matrix(0, mdim[1]+1, mdim[2]) 
	## relative percentage of height
        for (i in 1:mdim[2]) { m1[1:mdim[1],i] <- m[,i]/sum(m[,i]) }
	## height of each rectangles
	m1 <- m1 * height
	## vertical position
	for (j in (mdim[1]-1):1) { m1[j,] = m1[j,] + m1[j+1,] }
	m1[mdim[1]+1,] <- rep(0,mdim[2])
	m1 <- m1 + margin
	v1 <- m1[2:(mdim[1]+1),]
	v2 <- m1[1:mdim[1],] - padding

	## width of rectangles
	rectwidth = height / mdim[2]
	## horizontal positions
	h1 <- rep(0, mdim[2])
	h2 <- h1
	for (i in 1:mdim[2]) 
	    h1[i] <- margin + (i - 1) * rectwidth
	    h2[i] <- margin + (i) * rectwidth - padding ## leave a space between two rectangles

	## an example showing colouring and shading
	plot(c(margin, margin+height), c(margin, margin+height), type= "n", xlab="", ylab="", axes=FALSE)
	for (i in 1:mdim[2])
	    for (j in 1:mdim[1])
	        rect(h1[i],v1[j,i],h2[i],v2[j,i], col="grey", border="black") # coloured
	        text(h1[i]+15, v1[j,i]+11, j, col="white");
	    text(h1[i]+15, v2[1,i]+11, xlabs[i])
	mtext(groupname, side=2, line=0, cex=1.6);

To use the function is pretty simple. First create a matrix and then call the brickblock function like the following.

brickblock(x, 500, c("2CC", "2SC", "1CC", "1SC", "Total"),"Cluster"); 

It will draw a graph similar to the following.


Tags: , , , ,

eDigitizer – a Windows application to digitize graphs

eDigitizer is a powerful tool that can help users to digitize the data points in graphs. It is extremely useful to scientists and engineers who are trying to use data from scientific literature in their study. Click here to see a screen shot. Main features of eDigitizer include the following:

  • Process multiple image formats
  • Stretch, zoom, center and rotate images
  • Test the orthogonality of axes
  • Digitize axes
  • Digitize data pointsof multiple series
  • Save digitized data to a text file

IBM Compatible PC
Intel Pentium 4 and equivalent
Hard disk space: 1 MB
OS: Windows 2000/XP or later version
.NET: Microsoft .Net Framework version 2.0 redistribution package

Free Download
eDigitizer is a freeware. You can download, use, copy and distribute the fully functional application absolutely free. Download it from here.

April 5, 2010: The length of each segment digitized is calculated and recorded. The total length of a whole line also is summed from all segments. All these information will be output in the text file and the data tab. Download the new version of eDigitier here.



Tags: , , ,