Ed's Blog

Good book for Excel users wanting to learn VBA programming

ed@edferrero.com — Thu, 15 May 2008 23:37:00 GMT

Jim DeMarco's new book on Excel VBA is a great choice for users who have a good general knowledge of Excel and want to learn how to write code using class modules.

This is a very good introduction to OOP in VBA. The book covers data import, XML, buiding user forms, charting, pivot tables, and Office integration.

Worth getting.

Dermot Balson's site is worth a look

ed@edferrero.com — Thu, 15 May 2008 23:09:00 GMT

This site deserves to be seen by a few more Excel users.

http://www.westnet.net.au/balson/ModellingExcel/

Dermot is based in Perth, Western Australia. Site has some neat examples. I especially like his pages on design patterns in Excel.

Some very interesting code using encryption in Excel.

You can do anything with scatter charts!

ed@edferrero.com — Wed, 14 Nov 2007 12:00:00 GMT

Whilst preparing for Australia's first Office DevCon, I revisited a few old scatter chart samples. After playing with these for a while, I decided that I really like scatter charts. If you remove the point markers, and join the series points with a plain line, you end up with a line chart over which you have a high degree of control. It's like having your own etch-a-sketch machine in Excel - you can draw anything with a scatter chart.

I started the DevCon presentation with this simple step-by-step example on how to build a chart with wariable width columns. You can get the whole sample here, but it may be easier to follow the explanation in this blog post. First, draw a simple scatter chart, any series will do, but I picked a straight line.

Then I clicked on a couple of points and dragged them around until I had something that started to look like a bar chart. Note that you cannot drag a point in Excel 2007. If you have that version, you will need to change the series values to achieve the same effect.

After a while, it becomes easier to just edit the point values in the range that contains the data series. The chart is starting to look like a variable width column chart - actually a step chart

Then format the chart series, remove the point markers and add vertical error bars of -100%. This results in a pile of boxes that looks very much like a column chart.

Now we just need to enter some formulas in the worksheet that translate the values we wish to chart into variables that build the scatter chart series. The sample also shows how to build dynamic ranges so that any data we add will automatically be shown on the chart.

Box Plots

Perhaps not a very useful chart. This can show several entities that have a range of values over two dimensions on the one chart. Here is how to build this using a scatter chart.

Box-Whisker Diagrams

Examples of box-whisker charts have been around for a while. Even I had a sample on my old web site a few years ago.
http://peltiertech.com/Excel/Charts/BoxWhiskerV.html
http://www.mis.coventry.ac.uk/~nhunt/boxplot.htm
http://www.duncanwil.co.uk/boxplot.html

Most of these use column charts and combinations of up-down bars and high-low lines. Another way of drawing a box-whisker diagram is with a scatter chart. The box-whisker diagram is shown together with the underlying data in this chart. It can also be shown without the data. The box-whisker diagram shows outliers as a disconnected line, the maximum and minimum extent of the data without outliers, and the first and third quartiles. It is used to get a quick indication of the spread of data.

Pyramid Charts

We have all seen food pyramids. Here is my version. This is a chart where each tier in the pyramid has an area proportional to the value it represents. It is drawn using a different scatter chart series for each tier of the pyramid. You will need to look at the formulas carefully and use a little trigonometry to work out how it is built.

Waterfall Charts

I probably had the first sample of an Excel Waterfall (or cascade) chart on the web. It was done a while ago, and only worked for positive values. Some better examples have been built since then.
http://peltiertech.com/Excel/Charts/Waterfall.html
http://www.tushar-mehta.com/excel/charts/waterfall/
http://www.sccs.swarthmore.edu/users/06/adem/engin/excel/waterfall_chart/index.php

All these samples use floating column charts to create waterfalls, as did my own early example. Here is a waterfall chart that has been constructed using a scatter chart. Like all the samples in this post, it uses no VBA.

I hope the next version of Excel will have some new chart types, meanwhile - you can draw anything with a scatter chart.

Office DevCon in Sydney November 3-4

ed@edferrero.com — Fri, 31 Aug 2007 10:00:00 GMT

MVP Graham Seach is organising an DevCon for Microsoft Office Developers and power users to be held at the MS Offices in Sydney over the weekend of November 3-4. www.block.net.au/devcon/index.htm

If you are interested in making the best use of Office, I strongly urge you to attend. Check out the link for a list of speakers and topics

I will certainly be there, and will present a couple of sessions.

It is free to attend Office DevCon, but transport, accommodation and meal costs are at your own expense. Snacks and drinks will be provided at the end of Day 1, during the delegate networking event.

Interactive Balance Sheet

ed@edferrero.com — Fri, 25 May 2007 10:00:00 GMT

Today, I added a sample Balance Sheet built with a Pivot Table to the 'Excel Charts' section.

Using Pivot Tables to build such accounting reports is nice because it automatically enables drill-down capabilities.

The sample contains both the report and data in one workbook, but of course it makes more sense to link the Pivot Table to data that sits in a corporate database with read-only permissions for the majority of users. That way, only approved data is used in Excel reports.

More on Excel 2007 Conditional Formats (and Excel 2003)

ed@edferrero.com — Thu, 17 May 2007 10:00:00 GMT

One of the nice things about Excel 2007 is the new conditional formatting options.

Let’s look at this briefly. First, enter some data in Excel, like this;

Then, select the data range A2:B14 and use the ‘Format as Table’ button on the Home ribbon. Make sure the ‘My Table has Headers’ option is checked and click OK. You should be able to get something like this after a bit of formatting.

Now, select just the rainfall data in the range B3:B14, and click the “Conditional Formatting’ button on the Home ribbon. Select light blue data bars from the options. That’s it, you now have a quick and simple visualisation of the data.

Can we simulate this in Excel 2003? The answer is yes, but we need a little bit of VBA code.

The code is shown below. You need to enter this in a module in the VB Editor.

Option Explicit

Sub BuildBar()
' Builds rectangle shapes in column C

' for this sample we assume that the range inpRange
' contains numeric data that we wish to chart
' the column next to the data should be empty

Dim inpRange As Range
Dim cell As Range
Dim barLength As Double
Dim total As Double

On Error Resume Next
  Set inpRange = Application.InputBox(prompt:="Enter Data Range" & Chr(10) & _
            Chr(10) & "CAUTION:" & Chr(10) & "Results will be shown in next Row or Column " _
            & Chr(10) & "and will overwrite any existing data", _
            Title:="Conditional Bars", Type:=8)
On Error GoTo 0

  If inpRange Is Nothing Then Exit Sub

' clean up any previously built rectangles
  CleanUp

  total = Application.WorksheetFunction.Max(inpRange)

  For Each cell In inpRange.Columns(1).Cells
    barLength = cell.Value / total
    Call AddRectangle(cell.Offset(0, 1), barLength)
  Next cell

End Sub

Sub AddRectangle(dest As Range, barLength As Double)
' Adds a rectangle shape to fill the specified cell

Dim cL, cT, cW, cH As Single
Dim shp As Shape

With dest
  cL = .Left
  cT = .Top
  cW = .Width
  cH = .Height
End With

Set shp = ActiveSheet.Shapes.AddShape(msoShapeRectangle, cL, cT, cW, cH)

With shp
  ' name the shapes so that we can keep track of them
  .Name = "fcRect" & dest.Address
  ' set a fill colour
  With .Fill
    .ForeColor.SchemeColor = 10
    .BackColor.SchemeColor = 51
    .TwoColorGradient msoGradientVertical, 1
  End With
  ' size them to be proportional to barLength
  .ScaleWidth barLength, msoFalse, msoScaleFromTopLeft
End With

End Sub

Sub CleanUp()
Dim shp As Shape
  For Each shp In ActiveSheet.Shapes
    If Left(shp.Name, 6) = "fcRect" Then
      shp.Delete
    End If
  Next shp
End Sub

The ‘BuildBar’ routine calls an input box to get the data range from the user, then it calls the ‘CleanUp’ routine to delete any shapes that have a name starting with “fcRect”. It then draws a series of rectangle shapes proportional to the data in the next column. You can add a button to the worksheet to call the ‘BuildBar’ routine if you like. When the routine is run, you will be asked to select the data.

Then clicking OK will build conditional bars in the next column. As shown.

Enjoy

Excel 2007 Format Conditions

ed@edferrero.com — Tue, 15 May 2007 10:00:00 GMT

I really like the enhanced conditional formatting in Excel 2007. Today I found out that the FormatConditions property in VBA behaves slightly differently in Excel 2007 than it used to in 2003.

In particular, if the format condition is based on a cell value, then the Formula1 property of the FormatContition object in Excel 2003 returns a numeric value. i.e. if the conditional format is 'Cell Value Is' greater than 100, then the Formula1 property will eturn the number 100 in Excel 2003. In Excel 2007 this will return a string - "=100".

This presents a problem when writing code that works for both Excel 2003 and 2007. The short sample below shows how to test the Formula1 property so that it works in both versions of Excel.

Sub TestFormatCondition()
Dim rng As Range
Dim valCond1 As Double

Set rng = Worksheets(1).Range("A1")

With rng.FormatConditions(1)
' check if formula condition is numeric (Excel 2007 uses string)
If IsNumeric(.Formula1) Then
    valCond1 = .Formula1
ElseIf IsNumeric(Mid(.Formula1, 2, 1)) Then
    valCond1 = CDbl(Right(.Formula1, Len(.Formula1) - 1))
Else
    Debug.Print "Condition is Formula"
End If
End With

Debug.Print valCond1

End Sub

Need a good screen capture tool? SnagIt

ed@edferrero.com — Thu, 10 May 2007 16:29:00 GMT

I have been using SnagIt by TechSmith and can thoroughly recommend the product.

Its fast, and it works. I find that the built-in graphics editor is great for cropping, resizing and colour balance, and allows me to add text and small graphics to an image. This is all I really want in a graphics editor.

If you need to capture images from your screen and incorporate them in documents, get it.

Oops, this time it was my fault entirely

ed@edferrero.com — Thu, 10 May 2007 16:19:00 GMT

I tried to edit the back-end database for this site directly. Big mistake - I lost connection half way through copying a table and ended up corrupting quite a few things. Back up and running now. I won't try that again.

Back-up the database.
Back-up the database.
Back-up the database.
Back-up the database.
Back-up the database.
Back-up the database.
Back-up the database.
Back-up the database.

Two new books for 2007

ed@edferrero.com — Tue, 01 May 2007 16:38:00 GMT

I have had the good fortune to be Technical Editor for two books just published by Wiley. I recommend both of them.

The first is 'Excel 2007 PivotTables & PivotCharts' by Peter G Aitken. This is a good introduction to Pivot Tables for those who have not used them before. However, it is equally suited to power users who wish to increase their knowledge of Pivot Tables and how to use them more effectively.

The second is 'Excel & Access Integration' by Michael Alexander and Geoffrey Clark. This is aimed at the power user who wishes to start using Access and Excel together to leverage the unique capabilities of both these programs to build powerful office applications.