Posts Tagged function

A generic PHP function to generate navigation bar for web applications

When you write a page with a long list in a web application, you may always want to break the list to pages to show only part of the list. And further, allow user use a navigation bar with page numbers to jump to different pages. This post provide a generic PHP function. You can adapt it and use it in your web application. There are two input variables: npages = total number of pages, page = the current page you want to display. Within the function, the only thing you should change is the $_config array. It hold some information for constructing the page hyperlink. $_config[‘scirptname’] includes php file name that display the given page and $_config[‘cmd’] includes the command code in the php file. Other than that, you do not need change anything to use the function. To see live demo, please visit

// generate the page navigating string
function getNavStr( npages, page)
// get necessary values for the variables
$href = $_config['scriptname']."?cmd=".$_config['cmd']."&page={page}";

if ($npages <= 1) { return ""; }

$first = 0;
$last = 0;
// calculate first and last pages
if ($page <= 5)
$first = 1;
$last = $npages < 10 ? $npages : 10;
elseif ($page >= $npages - 5)
$first = $npages - 10 > 1 ? $npages - 10 : 1;
$last = $npages;
$last = $npages < $page + 5 ? $npages : $page + 5;
$first = $page - 5 > 1 ? $page - 5 : 1;

$navStr = "";
// create the page link prefix to the first page
if ($first > 1)
$url = preg_replace("/{page}/", "1", $href);
$navStr .= "<a href=\"$url\"><span class=\"boxedtext\">1</span></a>...";
// create the page links from the first to the last
for ($i=$first; $i<=$last; $i++)
if ($i == $page)
$navStr .= "<span class=\"outstanding\">$i</span>";
$url = preg_replace("/{page}/", "$i", $href);
$navStr .= "<a href=\"$url\"><span class=\"boxedtext\">$i</span></a>";
if ($i != $last)
$navStr .= " ";
// create the page link affix to the last page
if ($last < $npages)
$url = preg_replace("/{page}/", "$npages", $href);
$navStr .= "...<a href=\"$url\"><span class=\"boxedtext\">$npages</span></a>";

$this->_navStr = $navStr;
// return the navigation string
return $navStr;


Tags: , ,

Table with Excel – TWE 2.0


Microsoft Excel is very popular spreadsheet software. It is widely used in education, in scientific research, in managing financial activities. Nowadays, the common versions of Microsoft Excel are version 2003 and 2007. The functions of these versions are very powerful and include enormous functions and possibilities.

Scientists and engineers use Microsoft Excel to record and keep observed data. And further use Excel to summarize and analyze these data. During the data processes manipulation of tables, such as merging tables, splitting tables, eliminating duplicates, format conversion and so on, is an indispensable activity.

And more, Excel users in financial institutions use Excel to hold financial information in addition to their central database. Even they use Excel to do some financial analyses, risk evaluation and so on. All in all, Excel is one of common software package used daily in financial institutions. The users also encounter the table manipulation problem.

Table manipulation is a common issue for all Excel users. So, how to help Excel users automate the table manipulation? How can we release Excel users from the tedious activities? This is the initial motivation to develop Table with Excel (TWE). The goal is to create a running fast, easy to use, and flexible Windows .NET application.

Table with Excel (TWE) can help Excel users at all levels to deal with tables. The functions TWE provides can be broken to four groups. The first group includes four functions related to merging and splitting tables. They are Split a table, Extract a table, Stack tables and Join tables (see the screen shot). The second group includes functions related to transposition of tables, that is, conversion of horizontal tables to vertical tables and vice versa. They are Table to list, List to table, and List to counts. The third group includes a function to handle duplicates, format columns and rename worksheets in batch. The forth group includes all other functions related to open Excel files, display Excel status, open the TWE PDF help file, show TWE version and copyright information, and quit the application.


Main features

Table with Excel (TWE) can help Excel users at all levels to deal with data tables in Excel. Here is a list of TWE features:

  • Merging and splitting tables
    • Split a table
    • Extract a table
    • Stack tables
    • Join tables (screen shot)
  • Transposition of tables
    • Table to list
    • List to table
    • List to counts
  • Duplicates and more
    • Handle duplicates
    • Format columns
    • Rename worksheets in batch
  • Additional functions
    • Open Excel files
    • Open the TWE help file
    • About TWE
    • Quit TWE



  • IBM Compatible PC
  • Intel Pentium 4 and equivalent
  • Hard disk space: 5 MB


  • OS: Windows 2000/XP/Vista or later version
  • .NET: Microsoft .Net Framework version 1.1 redistribution package
  • Microsoft Excel 2000/2003/2007 or later version


Table with Excel (TWE) is a free ware. You can download, use, copy and distribute the fully functional application absolutely free. If you like it please put comments on the website and recommend it to your friends or team members. Your support will encourage us to improve the product further. If you have specific requests, please do not hesitate to contact us. We can discuss your requests and further improve TWE.

Download TWE 2.0 now – twe
Download TWE 2.0 user manual to learn more – Table with Excel


Tags: , , , , , , , , , , , , , , , ,

Search maximum and minimum of a function in a given range and its corresponding value of independent variable

More often, we need find the maximum or minimum value of a function within a given range. In Excel, we can develop a user-defined worksheet function to fulfill this goal. Here provides a user-defined function called minmax. This function is very powerful because it can be applied to any polynomial equation.

The definition of the function is:

MinMax(n As Integer, X As Object, b As Object, Optional isMax As Boolean = True)

Where, n stands for the number of independent variables, X includes the range and step size of each independent variables, isMax is a boolean variable with default value of TRUE for searching maximum and with a value of FALSE for searching minimum.

The return value of the function is an row array. The number of cells is equal to the number of independent variables plus 1. The first returned value is the maximum of minimum of the function and the rest include values of independent variables at this point.

Here lists VBA source code.

Option Explicit
Option Base 1

' search the minimum or maximum value of an equation in a specific range
Function MinMax(n As Integer, X As Object, b As Object, Optional isMax _
	 As Boolean = True)
' n - the number of variables
' X - is a n-by-3 matrix, first column is the initial values for each variable
'                         second column is the end values for each variables
'                         third column is the step size for each variables
' b - n+1-by-n+1 matrix, the coeficients for polynomial equation
'     column 1 - constant, column 2 - x1, and so on
'     row 1 - constant, row 2 - x1, ans so on
' isMax - a boolean variable to indicate calculate the maximum or minimum
' output variable - a row vector with n+1 values
' first value - maximum or minimum value of the equation
' the remaining values - the values for all xs at maximum or minimum of y
    Dim i, j, l, y
    ReDim myMinMax(n + 1)
    If isMax Then
        myMinMax(1) = -1E+100
        myMinMax(1) = 1E+100
    End If
    Dim xtemp, TotalN
    ReDim tempint(n), xn(n), xminmax(n)
    TotalN = 1
    For i = 1 To n
        tempint(i) = 1
    For i = 1 To n
        xn(i) = Round((X(i, 2) - X(i, 1)) / X(i, 3)) + 1
        TotalN = TotalN * xn(i)
    For j = 1 To n
        For i = j + 1 To n
            tempint(j) = tempint(j) * xn(j)
    ReDim gridx(TotalN, n)
    For i = 1 To TotalN
        j = 1
        Do While j <= n
         gridx(i, j) = X(j, 1) + X(j, 3) * (((i - 1) \ tempint(j)) Mod xn(j))
         j = j + 1
    For l = 1 To TotalN
        y = 0
        For i = 1 To n + 1
            For j = i To n + 1
                If i = 1 Then
                    If j = 1 Then
                        y = y + b(i, j)
                        y = y + b(i, j) * gridx(l, j - 1)
                    End If
                    y = y + b(i, j) * gridx(l, j - 1) * gridx(l, i - 1)
                End If
        If isMax Then
            If y > myMinMax(1) Then
                myMinMax(1) = y
                For i = 1 To n
                    myMinMax(i + 1) = gridx(l, i)
            End If
            If y < myMinMax(1) Then
                myMinMax(1) = y
                For i = 1 To n
                    myMinMax(i + 1) = gridx(l, i)
            End If
        End If
    MinMax = myMinMax
End Function

Download sample Excel file - minmax. Please notice that the sample Excel file includes macro. You should enable macro when you open the file. Otherwise, you cannot run the macro in the Excel sheet.

Suppose that we have polynomial like the following,

Y= - 14.8 + 0.232 X1+ 0.159 X2- 0.129 X3- 0.135X1X2- 0.00070 X1X1+ 0.00120 X2X2+ 0.00113 X3X4- 0.00149 X4X4

First we have to rearrange the formula and put the coefficients into a matrix format like the following,

Then arrange the boundary and step size of each independent variable like the following,

The trick part is the return values. You should select the number of cells equal to the number of independent variables plus one within a single row first, enter the following formula,


Attention now! Then, press down "Ctrl" + "Shift" first and press "Enter" key to make the formula work correctly. This is the method to enter a function returning an array or matrix. Remember, it may take a while to search the maximum or minimum value based on the function and the size of searching boundaries.


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: , , , ,