By Ed Ferrero on Thursday, 15 May 2008 11:47 PM

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.

By Ed Ferrero on Thursday, 15 May 2008 11:19 PM

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.

By Ed Ferrero on Wednesday, 14 November 2007 12:10 PM

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 poin ... Read More »

By Ed Ferrero on Friday, 31 August 2007 10:10 AM

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.

By Ed Ferrero on Friday, 25 May 2007 10:10 AM

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.

By Ed Ferrero on Thursday, 17 May 2007 10:10 AM

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 ... Read More »

By Ed Ferrero on Tuesday, 15 May 2007 10:10 AM

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 = .Formul ... Read More »

By Ed Ferrero on Thursday, 10 May 2007 4:39 PM

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.

By Ed Ferrero on Thursday, 10 May 2007 4:29 PM

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.

By Ed Ferrero on Tuesday, 1 May 2007 4:48 PM

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.

Read More »