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!
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 |
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!