Archive for May, 2011

Technical Preview program MS Office 2010

May 18th, 2011 admin No comments

July 15th, 2009

Just got my invitation mail for the Technical Preview program for Office 2010.
I’m gonna dedicate a machine to Windows 7 and Office 2010 because you can’t upgrade
a 64bit Office 2007 system to a Office 2010 system.

Any post will have to wait until I get back from my 2 weeks of vacation starting next friday.


Categories: Uncategorized Tags:

Compagny Culture

May 18th, 2011 admin No comments

May 14th, 2009

Start with a cage containing five apes. In the cage, hang a banana on a string and put stairs
under it. Before long, an ape will go to the stairs and start to climb towards the banana.
As soon as he touches the stairs, spray all of the apes with cold water. After a while, another
ape makes an attempt with the same result-all the apes are sprayed with cold water.

When another ape tries to climb the stairs, the other apes will try to prevent it.

Now, turn off the cold water. Next, remove one ape from the cage and replace it with a new one.
The new ape sees the banana and wants to climb the stairs. To his horror, all of the other apes
attack him.

After another attempt and attack, he knows that if he tries to climb the stairs, he will be
assaulted. Next, remove another of the original five apes and replace it with a new one.
The newcomer goes to the stairs and is attacked. The previous newcomer takes
part in the punishment with enthusiasm.

Again, replace a third original ape with a new one. The new one makes it to the stairs and is
attacked as well. Two of the four apes that beat him have no idea why they were not permitted
to climb the stairs, or why they are participating in the beating of the newest ape.

After replacing the fourth and fifth original apes, all the apes which have
been sprayed with cold water have been replaced. Nevertheless, no ape
ever again approaches the stairs.
Why not? “Because that’s the way it’s always been around here.”

That’s how individual initiative is beaten to death and a company culture begins.

( If you pay peanuts,… you get monkeys )


Categories: Uncategorized Tags:

The Microsoft Office 2010 IT Blog

May 18th, 2011 admin No comments

May 13th, 2009
Just found a new Microsoft Blog about Office 2010.
There is almost nothing there yet but I should be great…


Categories: Uncategorized Tags:

Back from the Excel User Conference 2009 in London

May 18th, 2011 admin No comments

April 7th, 2009

Just got back from the Excel User Conference 2009 in London.
Ment to drop in to the G20 but couldn’t find the time to do so.
I had a great time talking, eating and drinking beer with the great ones….
Maybe next time there should be a developer or advanced edition.
Looking forward to blogging on all my new ideas.

Categories: Excel Tags:

Update Pivottable through swapping the pivotcache

May 18th, 2011 admin No comments

March 25th, 2009

First of all, let me start by appologizing for not blogging for xxx weeks.
Having done that I want to blog about the use of pivottables for reporting to management.
Recently I started working at a new company and discovered a giant heap of workbooks.
A lot of them are used for reporting to managent. For example there is one workbook which
contains 10 pivottables based on basically two recordsets. The size of this monster is
almost 100 MB because each pivottable has its own pivotcache.

The same workbook is refreshed every day and saved with new data on the network with
a new name. This means 2G of storage space every month. The refreshing is done by
changing a cell with the dateparameter in it and updating the pivotcaches through a macro
called by a button on the sheet.

What I dislike about this aproach is the button on the sheet, the size of the workbook(s)
and the dependancy on odbc links. So I started thinking on a new setup for this workbook.
The first abvious thing was changing to 2 main pivotcaches this would reduce the size to less
than 8 MB whitch means a 90% reduction of disk space.

Secondly I wanted to refresh the data by connecting a new pivotcache to the pivottables.
In the past I wrote some code to switch pivotcaches between pivottables and to produce a
pivotcache from a sql statement and a connectionstring. It looked liked I was almost there
but there were a couple of pitfalls. Apperently you must create a pivottable first from a
pivotcache to be able to switch the pivotcache. Also when the your pivottable uses calculated
fields in the value section switching the cache results in a blank pivottable.

After realizing these things the coding didn’t take along time. Altough everything works I would like a answer from microsoft or anybody else about the problem around using a pivotcache without pivottable and the calculated field issue. Below the code without error handling for the obvious reasons. Comments a very welcome.

Sub UpdateWorkbook()

   Dim sSQL1 As String: Dim sSQL2 As String: Dim sDate As String

   sDate = InputBox(”Enter the date (yyyymmdd)”)

   sSQL1 = “SELECT *, (new-old) AS change FROM table1 WHERE date=’” & sDate & “‘”
   sSQL2 = “SELECT * FROM table1 WHERE date = ‘” & sDate & “‘”

   SwapPivotCaches sSQLServer, sSQL1, “PivotTable1″
   SwapPivotCaches sSQLServer, sSQL2, “PivotTable2″

End Sub
Sub SwapPivotCaches(sConnection As String, sSQL As String, sPivotTable As String)

   Dim pcTemp As PivotCache: Dim ptTemp As PivotTable
   Dim ws As Worksheet: Dim pt As PivotTable

   Application.ScreenUpdating = False

   Sheet0.Visible = xlSheetVisible

   Set pcTemp = PivotCacheFromSQL(sSQL, sConnection)
   Set ptTemp = pcTemp.CreatePivotTable(TableDestination:=Sheet0.Range(”B5″))

   For Each ws In ActiveWorkbook.Worksheets
      For Each pt In ws.PivotTables
         If pt.Name = sPivotTable Then pt.CacheIndex = ptTemp.CacheIndex
      Next pt
   Next ws

   Set ptTemp = Nothing
   Set pcTemp = Nothing

   Sheet0.Visible = xlSheetVeryHidden
   Application.ScreenUpdating = False

End Sub
Function PivotCacheFromSQL(sSQL As String, sConnection As String) As PivotCache

   Dim cConnection As ADODB.Connection
   Dim rsRecordset As ADODB.Recordset
   Dim cmdCommand As ADODB.Command
   Dim pcPivotCache As PivotCache
   Dim iErrorCounter As Integer

   iErrorCounter = 0

   Set cConnection = New ADODB.Connection
   cConnection.Open sConnection

   Set cmdCommand = New ADODB.Command
   Set cmdCommand.ActiveConnection = cConnection

   With cmdCommand
      .CommandText = sSQL
      .CommandType = adCmdText
   End With

   Set rsRecordset = New ADODB.Recordset
   Set rsRecordset.ActiveConnection = cConnection

   rsRecordset.Open cmdCommand

   Set pcPivotCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)
   Set pcPivotCache.Recordset = rsRecordset

   Application.StatusBar = False

   Set PivotCacheFromSQL = pcPivotCache
   Set rsRecordset = Nothing
   Set cmdCommand = Nothing
   Set cConnection = Nothing

End Function
Categories: Excel, Pivot table Tags: ,

Lehman Excel snafu could cost Barclays dear

May 17th, 2011 admin No comments

October 22nd, 2008

A formatting fubar involving an Excel spreadsheet has left Barclays Capital with contracts involving collapsed investment bank Lehman Brothers than it never meant to acquire.

Working to a tight deadline, a junior law associate at Cleary Gottlieb Steen & Hamilton LLP converted an Excel file into a PDF format document. The doc was to be posted on a bankruptcy court’s website before a midnight purchase offer deadline on 18 September, just four hours after Barclays sent the spreadsheet to the lawyers. The Excel file contained 1,000 rows of data and 24,000 cells.

Some of these details on various trading contracts were marked as hidden because they were not intended to form part of Barclays’ proposed deal. However, this “hidden” distinction was ignored during the reformatting process so that Barclays ended up offering to take on an additional 179 contracts as part of its bankruptcy buyout deal, Finextra reports.

The error was discovered on 1 October, after US Bankruptcy Judge James Peck approved the deal, prompting a legal motion (pdf) from Barclays to amend the deal, excluding the scores of contracts it states were mistakingly included in the agreement. The story was broken by legal tabloid Above The Law.

Lehman Brothers sought bankruptcy protection on 15 September, a move that set off a chain of events that have shaken confidence in global financial institutions and the collapse of stock markets across the globe that has only been partially reversed by unprecedented government bail-outs to the banking sector. Barclays first offered to buy a “stripped clean” portion of Lehman for around $1.75bn on 16 September.

A revised version of the deal was eventually agreed on 18 September, largely focusing on the New York arm of Lehman’s business (including its $910m-valued headquarters) and responsibility for 9,000 former employees. Barclays paid $1.35bn for these assets as well as taking on responsibility for some of Lehman’s trading positions which, because of the formatting error, became more numerous than it intended.

It’s unclear what the financial ramifications of the formatting error might be. Excel spreadsheets might seem a fairly unsophisticated method of logging multi-billion pound trading positions, but they are quick to produce and easy to understand – vital consideration in a financial market – which makes them widely used.

A hearing on Barclays Capital’s attempts to extricate itself from the mess created by the dodgy Excel conversion is pencilled in for 5 November.
By John Leyden 15th October 2008

Categories: Excel Tags:

Pivot Table based on CSV file.

May 17th, 2011 admin No comments

July 31st, 2008

It’s been a while since my last post.
Due to my new job and rebuilding my house free time was scarce.
In my line of business we work a lot with data from numerous suppliers.
Some of the data is provided in csv files which can be imported in excel to base a pivot table on.
But what if the number of records / lines exceeds 65536 in excel 2003 or 1048576 in excel 2007.
You can cut the csv file in parts and base the pivot table on multiple consolidation ranges.
This was too much of a hassle for me and I started thinking about a more structured way to
create a pivot table on a csv file, with this result…
What is does is in fact simple but beautiful.
Select CSV File, create a recordset from it and attach it to the pivot cache to base a new
pivot table on this cache.

Option Explicit

'Set Reference to Microsoft ActiveX Data Objects 2.7 Library

Const sConnStrP1 = "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq="
Const sConnStrP2 = ";Extensions=asc,csv,tab,txt;Persist Security Info=False"
Const sFilter = "CSV File, *.csv"

Sub CreatePivotTableFromCSV()

  Dim sFileName As String
  Dim sFilePath As String

  sFileName = Application.GetOpenFilename(sFilter, 1, "Select File", , False)
  sFilePath = Left(sFileName, InStrRev(sFileName, "\"))
  sFileName = Replace(sFileName, sFilePath, "")

  TestCSV sFilePath, sFileName

End Sub
Sub TestCSV(ByVal sFilePath As String, ByVal sFileName As String)

  Dim cConnection As ADODB.Connection
  Dim rsRecordset As ADODB.Recordset
  Dim pcPivotCache As PivotCache
  Dim ptPivotTable As PivotTable
  Dim SQL As String

  Set cConnection = New ADODB.Connection
  cConnection.Open sConnStrP1 & sFilePath & sConnStrP2

  SQL = "SELECT * FROM " & sFileName

  Set rsRecordset = New ADODB.Recordset
  Set rsRecordset = cConnection.Execute(SQL)

  'For Excel 2003 Use
  'Set pcPivotCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)
  'For Excel 2007 Use

  Set pcPivotCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlExternal)
  Set pcPivotCache.Recordset = rsRecordset
  Set ptPivotTable = pcPivotCache.CreatePivotTable(TableDestination:=Range("B5"))


  Set rsRecordset = Nothing
  Set cConnection = Nothing

End Sub


Comments are welkom

Categories: CSV, Excel, Pivot table Tags: , ,

Who’s da mastah?…….Sho’nuff!

May 17th, 2011 admin No comments

June 1st, 2008

Last Friday I was making dinner for a company of 12 at my grandmother’s house and happened to be sitting next to the CTO of the major social networking site. He told me a story about a question he asked every programmer he interviewed for a job. The question was how to program the Fibonacci sequence. Just because he left me a scrap in which he makes fun of me I’m going to post a little array UDF for this problem.

Function FIBONACCI(n As Long) As Variant

   Dim sFibonacci() As Variant
   Dim CallerSize As Long
   Dim i As Long: Dim j As Long: Dim k As Long

   Const sqrt5 = 2.23606797749979

   With Application.Caller
      ReDim sFibonacci(1 To .Rows.Count, 1 To .Columns.Count)
      CallerSize = .Cells.Count
      For i = 1 To .Rows.Count
         For j = 1 To .Columns.Count
            k = (i - 1) * .Columns.Count + j + n
            sFibonacci(i, j) = Round(((1+sqrt5)^k - (1-sqrt5)^k) / (2^k*sqrt5),0)
         Next j
      Next i
   End With

   FIBONACCI = sFibonacci

End Function

p.s. I’m going to win a case of champagne and a public apology soon…..

Categories: Excel, UDF Tags: ,

Standard Deviation of Counts

May 17th, 2011 admin No comments

May 20th, 2008

After reading and commenting on the Standard Deviation of Counts at Methods in Excel I decided to award the topic a post on my own blog ( with the blessing of Ross )
The problem is to get the Standard Deviation for a number of counts of scores. You can achieve this by coding an UDF or by a combination of worksheet functions. Each with the obvious drawbacks.

The test example consists out of the following data:

The formula for the Standard Deviation I used was:

I named the two main ranges “values” and “scores”, defined:
N as SUM(counts),
the first summation as SUM(MMULT(values^2,TRANSPOSE(counts))),
and the second summation as SUM(MMULT(values,TRANSPOSE(counts))))^2
The combination of these parts resulted in:
If the scores represent the entire population instead of a sample you should change it to

My choice for coding a UDF would be:

Function STDEVCOUNTS(Counts As Range, Values As Range, bPartial As Boolean)

   Dim N As Long: Dim i As Long
   Dim dSum As Double: Dim SValues() As Double

   ReDim SValues(1 To Values.Cells.Count)

   For i = 1 To Values.Cells.Count

      SValues(i) = Values(1, i) * Values(1, i)

   Next i

   With WorksheetFunction

      N = .Sum(Counts)
      dSum = N * .Sum(.MMult(SValues, .Transpose(Counts)))
      dSum = dSum - .Sum(.MMult(Values, .Transpose(Counts))) ^ 2
      dSum = dSum / (N * (N + bPartial))

   End With

   STDEVCOUNTS = dSum ^ 0.5

End Function

Slightly slower ( about 5% ) but less code is my second attempt with a little help from Bob Phillips from the Excel User Group.

Function STDEVCOUNTS(Counts As Range, Values As Range, bPartial As Boolean)

   Dim N As Long: Dim i As Long: Dim dSum As Double

   With WorksheetFunction

      N = .Sum(Counts)
      dSum = N * Evaluate(”SUM(MMULT(” & Values.Address(, , , True) & “^2,TRANSPOSE(” & Counts.Address(, , , True) & “)))”)
      dSum = dSum - .Sum(.MMult(Values, .Transpose(Counts))) ^ 2
      dSum = dSum / (N * (N + bPartial))

   End With

   STDEVCOUNTS = dSum ^ 0.5

End Function

Thoughts on this code or on the functions….?



Categories: Excel, UDF Tags: ,

How Excel solves all my problems

May 17th, 2011 admin No comments

May 8th, 2008

Last week I was on holliday and we had to divide 3 bottles of wine among ten friends.
We could have written our names on little papers and draw 3 names from the lot,
but why do something like that when you have a computer with excel.
So I wrote a array function that returns a permutation from a set of data.

I put the 10 names in A1:A10 and then selected C1:C3 and inserted =PERMUTATION(A1:A10)
finally I pressed shift ctrl enter

and voila ( sorry for the french ) a random permutation of 3 names…….

the source for this small miracle is below

Function PERMUTATION(ByRef rSource As Range) As Variant

   Dim PermCol As Collection: Dim Cell As Range: Dim Result() As Variant
   Dim iIndex As Long: Dim i As Long: Dim j As Long

   ReDim Result(1 To Application.Caller.Rows.Count, 1 To Application.Caller.Columns.Count)

   Set PermCol = New Collection

   i = 1

   For Each Cell In rSource

      PermCol.Add CStr(Trim(Cell.Value)), CStr(i): i = i + 1

   Next Cell

   For i = 1 To Application.Caller.Rows.Count

      For j = 1 To Application.Caller.Columns.Count

         iIndex = WorksheetFunction.RandBetween(1, PermCol.Count)
         Result(i, j) = PermCol(iIndex)
         PermCol.Remove (iIndex)

      Next j

   Next i


End Function

I don’t know any real life use for this function besides the dividing of winebotles I told you about.
But if somebody has any use for this beautiful function please let me know…



link to this post on the excel user group with comments and other solution


Categories: Excel, UDF Tags: ,