Book Review – Excel Macro Mastery

The book Excel Macro Mastery is an excellent book for non-programmers, willing to learn VBA. Indeed, it is written in simple English, following the 80/20 rule of Paretto in Excel, or as the author says, 99.9% of the time you will use the Workbook object for 3 things – open,  close and access a sheet.

I really enjoyed reading it and thinking how much time it may have saved, if I have read it from my start with VBA (to be honest, I think about it every time I read a beginner’s book for VBA), but in this case it is quite a lot due to the simplicity of the language and the explanations.  The book is just 70 pages and most of it is taken by code, so it is easy to read. Let’s take it step by step now.

Step 1 gives two page introduction on how to create a sub routine.

Step 2 gives some examples about the abilities of VBA. The standard “Hello World” and the way to put some data in the cells.

Step 3 introduces you to the Workbook, Worksheet and Range, named by the author “The 3 magic elements”.

Step 4 tells about cells and ranges. Pretty much Range should be used, if the cell is the same every time a macro runs and Cell should be used if the cells are variable.

Step 5 introduces variables in 3 pages with example.

Step 6 provides a useful macro for copying and pasting range from one worksheet to another.

Step 7 introduces loops. Pretty much, you may use only the for-loop and forget about the others in many of the cases. Anyway, VBA provides more than that, just to accustom to your mind set.

Step 8 introduces the IF and the Select Case operators

Step 9 introduces the possiblity to create buttons in excel and assign functions to it

Step 10 gives you a taste of programming – the usage of functions is present. In this step I have seen what is the difference between entering arguments with and without parenthesis in a function.

Step 11 provides 2 useful functions for VBA programmer – a function that opens a workbook and a boolen function checking whether a workbook is opened.

Step 12 teaches you simple error handling

Step 13 introduces collections and arrays

Step 14 is a one page chapter with a task to create a macro.

Step 15 is the last one,  introducing error handling.

Pretty much that is it. Not really in depth, but useful, if you have never worked with VBA. I recommend it, if you spend more than an hour per day in Excel. At least it will broaden your horizons.

With the written permission of the author, I share the two functions for openning workbook and checking whether a workbook is opened. I hope you enjoy them!

Option Explicit
Public Function OpenWorkbook(ByVal sFilename As String, ByVal ReadOnly As Boolean) As Workbook

    On Error GoTo Eh
    ' Check file exists
    If (Dir(sFilename) <> "") Then
        ' If workbook is already open then inform the user.
        If IsWorkBookOpen(sFilename) Then
            MsgBox ("The workbook [" + sFilename + "] is already open by user [" + Application.UserName _
                    + "]. Please close the file and run again.")
            GoTo Done
        End If
        Set OpenWorkbook = Workbooks.Open(FileName:=sFilename, ReadOnly:=True)
    Else
        ' File does not exist
        MsgBox ("The workbook: " + sFilename + " could not be found.")
    End If
Done:
    Exit Function
Eh:
    MsgBox "Error message is " + Err.Description

End Function

Function IsWorkBookOpen(FileName As String)

    Dim fileID As Long
    Dim errNum As Long
    ' Open file and check for error
    On Error Resume Next
    fileID = FreeFile()
    Open FileName For Input Lock Read As #fileID
    Close fileID
    ' Store error number and clear error
    errNum = Err
    On Error GoTo 0
    ' Determine the open state of then workbook based on error
    If errNum = 0 Then
        IsWorkBookOpen = False
    ElseIf errNum = 70 Then
        IsWorkBookOpen = True
    Else
        Err.Raise errNum
    End If
    
End Function

Last, but not least, the book costs $4.51 from Amazon, so if it would save a couple of hours of your work it has already paid back!