Author: Stephen Bullen

Book review – Excel Annoyances

Posted on by Stephen Bullen

Whether you think Excel Annoyances, written by Curtis Frye and published by O’Reilly, is wonderful or terrible will ultimately depend on one thing – does it resolve your specific issue? Essentially, it’s a ‘hints and tips’ book for using (not programming) Excel. It’s ‘angle’ is that each tip is first presented as a problem, such as “I’m so tired of entering regular sequences of data into cells. I mean, typing 1 in cell A1, then 2 in cell A2, then 3 in cell A3, then 4 in cell A4… up to 100 or 200. This isn’t a good use of my time! Isn’t there some way to extend a data series automatically so that I don’t get carpal tunnel typing row headings?”, followed by an explanation of the Excel feature that solves the problem (the Fill Handle and/or Data > Series dialog).

At 226 pages, the book is fairly easy to read through and most of the annoyances are probably relevant to most people, so you’ve either encountered them already, or are likely to in the next few years (depending on how much you use Excel). I hope, though, you’re more likely to think positively about them as features of Excel (“Great, I can zoom my worksheet in/out”) rather than the negative tone used in this book (“Why can’t I see all my data?/Why is the font too small?”).

Most of the explanations are actually quite good (and accurate), though very specific to the problem and often without any ‘background’ information. I quickly tired of the whining tone used to describe each problem (e.g. “I’ll be sacked if I don’t get this chart right before the presentation”) and the jocular nature of some of the answers (e.g. “Do x, y and z and your job is safe!”). After answering questions in the newsgroups for eight years, I was surprised that I didn’t recognise many of the annoyances. I’ve no idea whether they’re all genuine, but I frequently got the impression that the ‘problem’ was invented to introduce another tip (e.g. “My boss told me to make a pivot table out of the data, but I don’t know how…”).

I couldn’t work out who the book was targetted to – was it the Excel beginner, or someone who knows VBA (and by implication knows Excel well)? For example, page 8 includes the useful beginner tips of using Alt+Enter to put a line-break in a cell and Ctrl+Z to undo AutoCorrect changes, but page 10 includes a code snippet (from Dave Hawley) for the obscure problem of fixing up the default formatting you get when copying a table from Word and pasting it into Excel 97, with a comment of “select the list and run this macro to clean up your data”!

The index is pretty good, but I don’t think it would ever be used; the format of the book is such that if you had a problem, you’d look to see if it was mentioned in the Table of Contents, rather than scan the index. A the answer/tip given for each problem is so specific, if your problem isn’t in the TOC, it’s unlikely that an answer to a different problem is going to help you much.

[The following paragraph was changed from the original text, at the request of Dave Hawley]
One aspect of this book that I found somewhat annoying was the recommendations for the reader to purchase expensive addins to solve some of the annoyances. That’s not a problem in itself (assuming the addins prove useful), but the recommendations are heavily skewed towards the addins sold by OzGrid.com (Dave Hawley’s site), rather than the many free alternatives that could probably found on the ‘net with a little more research (and perhaps bundled into a free download). For example, the book fails to mention the three (free) addins most commonly recommended in the newsgroups – Bill Manville’s FindLink, Rob Bovey’s Chart Labeller and Jan Karel Pieterse’s Name Manager – even though the book contains annoyances that they’d easily resolve.

Bottom line: Don’t buy this book online. Have a good look through it and see if the annoyances ring true and check that you can stand the writing style. If so, you’ll love this book. If not, you’ll hate it.

Regards

Stephen Bullen

Updated FormFun example

Posted on by Stephen Bullen

Following Jamie Collins’ prompting, I’ve just released an update to my FormFun example workbook. This workbook demonstrates how to modify a userform’s window styles to make it sizable, have an icon, show in the task bar, disable the [x] close button etc. The example can be downloaded from http://www.oaltd.co.uk/Excel. It includes a class module to encapsulate the Windows API calls that can be copied straight into your projects and used with just a few extra lines. For example, to create a sizable userform with a custom icon that shows in the task bar, you’d add the class module to your project and include the following code in the form:

Private Sub UserForm_Activate()

    Dim clsFormChanger As CFormChanger
   
    Set clsFormChanger = New CFormChanger
   
    clsFormChanger.Sizeable = True
    clsFormChanger.ShowIcon = True
    clsFormChanger.IconPath = “C:MyPathMyIcon.ico”
    clsFormChanger.ShowTaskBarIcon = True
   
    Set clsFormChanger.Form = Me

End Sub

Private Sub UserForm_Resize()
    ‘Some code to reposition all the controls
    ‘according to the form’s height and width,
    ‘given by Me.InsideHeight and Me.InsideWidth

End Sub

Enjoy

Stephen Bullen

Functions that do things

Posted on by Stephen Bullen

You’ll often read that user-defined-functions (UDFs) can’t do anything – they can only return a value to the cell that called them. While that’s true, they can schedule a task to be performed when the sheet is next calculated. The general method is shown below, where I’ve created a function to update a chart’s Y axis scale.

We start with a global variable declared in a standard module to hold the set of scheduled actions to perform:

‘A public collection to hold our scheduled actions
Public goFunctionCommands As Collection

We then have a function in a standard module that adds an item to that collection. In this example, we’ll add an array, where the first element identifies the action to perform and subsequent elements provide any extra information we need to perform the action:

‘Function to change the Y axis maximum value on a chart
‘rngCell is a reference to a range on the worksheet containing the chart
‘sChart is the name of the chart object to update
‘dNewMax is the value for the Y axis maximum value.
Public Function SetChartYAxisMax(ByRef rngCell As Range, ByVal sChart As String, _
                                 ByVal dNewMax As Double) As Double
 
    ‘Instantiate the collection if cleared
    If goFunctionCommands Is Nothing Then
        Set goFunctionCommands = New Collection
    End If
     
    ‘Add a command to the collection, as an array of
    ‘all the information we need to do the action.
    ‘In this case, we need the sheet, the name of the
    ‘chart object and the new Y axis value
    goFunctionCommands.Add Array(“SetChartYAxisMax”, rngCell.Parent, sChart, dNewMax)
 
    ‘Return the name being set to
    SetChartYAxisMax = dNewMax
 
End Function

That is the function we’ll use from the worksheet, such as the following, where B10 might contain a formula to calculate the new maximum:

=SetChartYAxisMax($A$1,”chtTheChart”,B10))

Lastly, we need to respond to the _Calculate event in the ThisWorkbook code module. In this procedure, we iterate through all the scheduled tasks, identify which they are and perform them:

‘Perform the scheduled tasks when the workbook is calculated
Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
 
    Dim vInfo As Variant
 
    On Error Resume Next
 
    If Not goFunctionCommands Is Nothing Then
     
        ‘Loop through all the actions to perform
        For Each vInfo In goFunctionCommands
         
            ‘Which action should we perform?
            ‘Add a new Case for each ‘command’ function
            Select Case vInfo(0)
            Case “SetChartYAxisMax”
   
                ‘For this action,
                ‘vInfo(0) is the name of the action
                ‘vInfo(1) is the worksheet containing the embedded chart
                ‘vInfo(2) is the name of the chart object
                ‘vInfo(3) is the new axis scale
                vInfo(1).ChartObjects(vInfo(2)).Chart.Axes(xlValue) _
                    .MaximumScale = vInfo(3)
            End Select
        Next
   
        ‘Clear the collection once we’ve processed all the commands
        Set goFunctionCommands = Nothing
    End If
   
End Sub

The only caveat with this technique is that the tasks to perform shouldn’t trigger another recalc. To implement additional functions, you just need to add the new function to schedule the task, then add another Case in the Worksheet_Calculate event.

Happy Holidays!

Stephen Bullen
http://www.oaltd.co.uk

International Issues

Posted on by Stephen Bullen

It looks like I get to be the first guest speaker to blog here, so thanks to Dick for opening up his Daily Dose.

When we write code, we can generally trust that it will work just as well on our users’ computers as on our own. Given the same source data, bugs are generally reproducible. Unfortunately, that is not the case if our users are overseas.

With a little care and foresight, it is possible (and quite easy) to write VBA code that will work wherever it’s used, with any choice of Windows or Excel UI language and any combination of Regional Settings (such as the date order and the characters used for the thousands, decimal and list separators).

I wrote a chapter about this in John Green’s “Excel 2002 VBA Programmer’s Reference” and Wrox Press have recently agreed for me to include it on my web site. I urge you to read the chapter and incorporate its recommendations into your standard coding habits.

You can read the chapter at http://www.oaltd.co.uk/ExcelProgRef/Ch22.

Regards

Stephen Bullen