Posts tagged VBA

My experiments with sparklines

Sep23
2009 4 Comments Written by admin

For the uninitiated, Sparklines are “data-intense, design-simple, word-sized graphics” according to its inventor Edward Tufte. I always wanted to include them in my trend reports. The challenge: How and Which tool to use?

The data came from this report came from a complex query using SQL server and Access that had this format:

College Major 2005 2006 2007 2008 2009
A B 10 15 14 18 25

Some options I investigated and tried (be sure to read this page, you might find many more):

  • Google’s Chart API (http://code.google.com/apis/chart/types.html): you can embed these in Google spreadsheets, create HTML pages using Excel via VBA, or embed them in Excel sheets. Alas, none would work the way I would like them to work: In-cell graphics
  • Use Sparklines for Excel add-in: this add-in will create great in-cell charts (bar, bullet graphs, sparklines, etc), but copying them down is difficult and resource intensive, and any change you make in the column size will alter the shape of that chart. In addition, I had more than 400 rows to populate–these would be too many objects in a spreadsheet for Excel to handle. My machine froze when I tried to copy it for 10 rows.
  • Use R’s implementation by Jason Dieterle (search on this page for Jason Dieterle (email), January 28, 2008. Works very good. I modified the code to print max and mins only, but the function generates a graphic, which needed to be embeded in LaTex file. It did not work nicely. Charts were too big to fit in a cell of a table.
  • Create bar charts in Excel using REPT function. I tried different font sizes and styles, but it didn’t look pretty, for the range of data varied. (Hint: make the alignment of text 90, use pipe signs, create columns and graphs for each data value, remove gridlines, keep the columns very close. It almost worked.)
  • Use spark package for LaTex, doesn’t work in pdflatex, and you have to play a lot with the settings
  • Use sparklines package for LaTex. Problem was that data needed to be normalized (or scaled) from 0 to 1, and needed extra parameters for min and max points. Solution: create a normalize function in Excel, and write a big formula to produce the exact needed string for the sparklines to work i.e.:
    \begin{sparkline}{5}
    \sparkdot 1 1 blue
    \sparkdot 0.2 0 red
    \spark 0.2 0 0.4 0.0625 0.6 0.5625 0.8 0.75 1 1 /
    \end{sparkline}

Here’s the normalize function:

Public Function Normalize(cell2Normalize As Range, WholeRng As Range)
       Normalize = (cell2Normalize.Value - WorksheetFunction.Min(WholeRng)) / (WorksheetFunction.Max(WholeRng) - WorksheetFunction.Min(WholeRng))
End Function

Here’s the big formula to convert the range for data values from 2005 to 2009 (in the range E2:I2) to the sparkline LaTex environment.

="\begin{sparkline}{5} " & "\sparkdot " & CHOOSE(MATCH(MAX(E2:I2),E2:I2,0),0.2,0.4,0.6,0.8,1) & " " & 1 & " blue " & "\sparkdot " & CHOOSE(MATCH(MIN(E2:I2),E2:I2,0),0.2,0.4,0.6,0.8,1) & " " & 0 & " red " & " \spark 0.2 " & Normalize(E2,E2:I2) & " 0.4 " & Normalize(F2,E2:I2) & " 0.6 " & Normalize(G2,E2:I2) & " 0.8 " & Normalize(H2,E2:I2) & " 1 " & Normalize(I2,E2:I2) & " / \end{sparkline}"

This last option worked beautifully. I dragged the formula down. I selected the data, and clicked on “Convert Table to LaTex” button (using this add-in). Copied the LaTex code to clipboard and pasted it in my LaTex editor. Manually merged the rows for colleges (using \multirow), and generated a beautiful looking pdf.

I was very happy. Printed it in color. Got a request back very soon that there should be total rows.

I forgot about the sparklines and created a report in Access with plain old numbers in less than 15 mins. Gave it back.

I tried to repeat this – Sparkline in Cognos -in Access with no luck.

(I later tried it one more time: Got the data in Excel using External data> Access, created a pivottable with rows and all, did some formatting, copied and pasted values and formats, inserted sparkline code, converted it to LaTex, copied and pasted in LaTex editor, and here’s the beautiful looking sample pdf of 16)

I wish there were simple reporting solutions that included awesome data visualization tools. (BTW, Excel 2010 will have sparklines: link) For this report, I did try Sweave, R, and LaTex, but because of the time constraints I could not investigate it further.

Please comment if you know any other way which meet (or don’t, Tableau is certainly one) these conditions: inexpensive (read free), efficient, and repeatable.

Posted in Uncategorized - Tagged , , , , , , ,

Access to the VB project is not trusted

Apr24
2009 2 Comments Written by admin

I was trying to enable an add-in for Excel 2007, and I kept getting this error:

Access to the VB project is not trusted

You can turn this message off by going to Developer tab in the ribbon and then clicking on the Macro security button, and checking the check box for “Trust access to the VBA project object model.”

A word of caution: be wary of the add-ins/projects that need VBA access. Don’t allow any project/add-in with VBA access, unless you know its exact purpose or the author of that project.

Posted in Error messages - Tagged ,

Student’s t-test for equal means

Oct29
2008 Leave a Comment Written by admin

If you do not wish to enter complex formula in Excel and you already have calculated average, count, and variance for your samples(Tip:use a PivotTable), then you can use these user-defined functions to calculate the t-test stat value and degrees of freedom required to do hypothesis testing. Both the functions provide an optional argument for the assumption of equal variances; by default it is set to false.

Here’s the code for t-test:

'---------------------------------------------------------------------------------------
' Procedure : TTESTM
' DateTime  : 10/29/2008 08:35
' Author    :
' Purpose   : To get the t-stat value when comparing two means with an optional input
'               for equal or unequal variances
'               by default the function assumes that the user is comparing means with unequal variances
' davg1 is the mean of first sample
' dcnt1 is the sample size of sample 1 
' dvar1 is the variance of first samaple 
' davg2 is the mean of second sample
' dcnt2 is the sample size of sample 2 
' dvar2 is the variance of second samaple 
' blnEqVar set TRUE to assume equal variance for the test
'---------------------------------------------------------------------------------------

Public Function TTESTM(davg1 As Double, dcnt1 As Double, dvar1 As Double, davg2 As Double, dcnt2 As Double, dvar2 As Double, Optional blnEqVar As Boolean = False) As Double
 
On Error GoTo TTESTM_Error
Dim dResult As Double, dNumer As Double, dDenon As Double, dPooledVar As Double
 
dNumer = davg1 - davg2
 
'if equal variances are not assumed
'http://www.itl.nist.gov/div898/handbook/eda/section3/eda353.htm
If Not blnEqVar Then
	dDenon = Sqr((dvar1 ^ 2 / dcnt1) + (dvar2 ^ 2 / dcnt2))
Else
'if equal variances are assumed, then calculated the pooled variance
	dPooledVar = Sqr((((dcnt1 - 1) * dvar1 ^ 2) + ((dcnt2 - 1) * dvar2 ^ 2)) / (dcnt1 + dcnt2 - 2))
	dDenon = dPooledVar * Sqr((1 / dcnt1) + (1 / dcnt2))
End If
 
dResult = dNumer / dDenon
 
TTESTM = dResult
Exit Function
 
TTESTM_Error:
	TTESTM = Null
 
End Function

Code for degrees of freedom

'---------------------------------------------------------------------------------------
' Procedure : DOFTTESTM
' DateTime  : 10/29/2008 08:50
' Author    :
' Purpose   : To get the degrees of freedom for the t-test when comparing two means with an optional input
'               for equal or unequal variances
'               by default the function assumes that the user is comparing means with unequal variances

' dcnt1 is the sample size of sample 1 
' dvar1 is the variance of first samaple 
' dcnt2 is the sample size of sample 2 
' dvar2 is the variance of second samaple 
' blnEqVar set TRUE to assume equal variance for the test
'---------------------------------------------------------------------------------------
'
Public Function DOFTTESTM(dcnt1 As Double, dvar1 As Double, dcnt2 As Double, dvar2 As Double, Optional blnEqVar As Boolean = False) As Double
 
Dim dResult As Double, dNumer As Double, dDenon As Double
 
On Error GoTo DOFTTESTM_Error
'if equal variances are not assumed, then use a complicated formula to compute degrees of freedom
'http://www.itl.nist.gov/div898/handbook/eda/section3/eda353.htm
If Not blnEqVar Then
	dNumer = ((dvar1 ^ 2 / dcnt1) + (dvar2 ^ 2 / dcnt2)) ^ 2
	dDenon = ((dvar1 ^ 2 / dcnt1) ^ 2 / (dcnt1 - 1)) + ((dvar2 ^ 2 / dcnt2) ^ 2 / (dcnt2 - 1))
	dResult = dNumer / dDenon
Else
	dResult = dcnt1 + dcnt2 - 2
End If
 
DOFTTESTM = dResult
 
Exit Function
 
DOFTTESTM_Error:
	DOFTTESTM = Null
 
End Function
Posted in String Operations, Uncategorized, Useful Procedures - Tagged ,

Convert Text to Uppercase

Sep17
2007 2 Comments Written by admin

If you want to convert the text to uppercase, use the following code; however, I recommend downloading ASAP Utilities, it has many functionalities, including text conversion. It doesn’t offer source code though.

Here’s my code for uppercase conversion:
(If you want to convert your text to lowercase, replace Ucase with Lcase function in the code)

‘Will convert selected range to Upper case, using array
Sub Conv2UCase()
On Error GoTo Conv2UCase_Error

Dim vDataArr As Variant
Dim lUpperBndRow As Long, lUpperBndCol As Long
Dim lRow As Long, lCol As Long

‘store selected values in an array
vDataArr = Selection
‘get the upper bound of rows
lUpperBndRow = UBound(vDataArr, 1)
‘get the upper bound of cols
lUpperBndCol = UBound(vDataArr, 2)

‘Start a loop to go through all the elements of the array
For lRow = 1 To lUpperBndRow
    For lCol = 1 To lUpperBndCol
        ‘Check if the value is text, if not don’t convert
        If WorksheetFunction.IsText(vDataArr(lRow, lCol)) Then
            ‘Convert values to upper case
            vDataArr(lRow, lCol) = UCase(vDataArr(lRow, lCol))
        End If
    Next lCol
Next lRow
‘Return the converted values to the range
Selection = vDataArr
Exit Sub

Conv2UCase_Error:
    MsgBox “Error ” & Err.Number & ” (” & Err.Description & “) in Sub:Conv2UCase”
End Sub

Posted in String Operations, Uncategorized, Useful Procedures - Tagged ,

Excel Books

Sep06
2007 Leave a Comment Written by admin

Books on my shelf:
Professional Excel Development by Stephen Bullen, Rob Bovey

This is by far the most authoritative book on Excel development from Add-ins to APIs. A word of caution though, it is not for the beginners.
Excel Advanced Report Development by Timothy Zapawa

Ever wondered on using Excel as a report development tool? This might be an answer to it; however, my problem with this book is that it focuses too much on pivottables, and because of that it doesn?t do justification to the name of the book. At any rate, this is very good book to exploit pivottable to its limits.
Excel 2007 Power Programming with VBA by John Walkenbach

Of course, it cannot happen that you talk about Excel books and don?t mention Mr. Spreadsheet himself. I am his fan on personal and professional level. On personal level, I love his blog, and on professional level, I like his lucid language in his books. I recommend reading his books and blog both.
Excel 2003 Formulas by John Walkenbach

You thought you knew formulas? Just read this book and you?ll realize, which I do almost every day, that there just so much about Excel that we don?t know. John uses simple language, and introduces the readers from basic to advanced formulas. How advance you might ask, well, here is an example, which uses a formula similar to this:
Excel 2007 Formulas by John Walkenbach
Books I have Read:
Data Analysis and Decision Making with Microsoft Excel by S. Christian Albright

I borrowed this book from the library. It is a very good for beginners.
Excel 2003 Power Programming with VBA by John Walkenbach
Integrating Excel and Access by Michael Schmalz

I borrowed this book from the library too. Excellent reference on developing applications using Access, Excel, and VBA.
Mr Excel ON EXCEL by Bill Jelen

This is my first book I read on Excel, which is written by Bill Jelen aka Mr. Excel. Fantastic book. If you have begun knowing Excel just now, obtain a copy of this book right away. This is the right start for beginners. It uses very simple and clear language, and Bill provides great examples to make the learning useful.
Guerilla Data Analysis Using Microsoft Excel by Bill Jelen

I had an e-version of this book, but for some reason it’s not working anymore. I had chewed on this book a lot. It?s a great book for starters, especially, in data crunching.
Posted in Books - Tagged , ,
« Older Entries