The current article reviews a VBA book for MS Excel.
First things first – I am not sure what should be done to pursue the non-IT people, that VBA is not something difficult and every hour you spend, trying to master it would pay out by saving 20 others, pressing ctrl+arrows and copying endless ranges in Excel. That is why I am always eager to take a look into the VBA books and to review them in my site. As far as this one was for non programmers and I am not exactly a non-programmer, I was really interested what it can teach a person with no idea in VBA and I tried to find at least 3 VBA tricks I did not know. So here they are.
The things I did not know before reading:
- The fact that if you record a macro in excel in the Personal Macro Workbook, it would be recorded into a Personal.xlsb file, loaded when Excel starts and available for all workbooks, as shown in the next two screenshots:
- The “Use Relative References” possibility for recording a macro – a good option, if you want to avoid hardcoding of the range. Thus, the code is recorded with offsets, based on the current selection. Quite useful for a beginner.
- Creating a “switch for value changes – this is simply a macro, which takes into account the current state of a given TRUE/FALSE feature and returns it with the other one. E.g. if a column is hidden, it makes it not hidden and vice versa. I was doing this with if, but the current code is way better and faster. It simply takes the status of the column and makes it reverse. Here is what it looks like:
Sub <span class="hiddenSpellError">HideUnhideColumnB</span>()
Dim <span class="hiddenSpellError">bStatus</span> As Boolean
<span class="hiddenSpellError">bStatus</span> = Columns("B:B").EntireColumn.Hidden
Columns("B:B").EntireColumn.Hidden = Not <span class="hiddenSpellError">bStatus</span>
Lets start with the real review now 🙂
The screenshots of the Kindle edition were clear and easy to understand. I liked the way the range selection was thoroughly explained. It was explained with the Excel shortcuts and the VBA code, which actually runs them.
E.g. Ctrl+Shift+8 => ActiveCell.CurrentRegion.Select. This was worthy! Some valuable tips for beginners were given, that save a lot of time – e.g. “do not copy a single cell value to another cell, but use Range(“a4“).Value – Range(“a3“).Value”. Local and watch window are explained. If you are new to Excel VBA, try to make a habit to use the Watch window, it would be of use later. I myself do it rarely, but if I was accustomed to it from the beginning my performance would have been higher. At a later stage, the variables are explained, with some good explanation for Date and Time, which is a key variable in VBA (and is not mistaken as in Excel, good work for Microsoft there). At a later stage I liked the way the message box and the conditional operators were explained together. This saves time for the reader 🙂 User defined functions, error handling and events were also explained on a level that would be understandable for a beginner.
What I liked really much in the book, was the last two chapters – the last one was concerning some useful codes, that may increase your interest into VBA development. (The switch for value changes from above is from there.). The last chapter is with exercises. You can solve them with your knowledge from the book, the correct code is not provided.
So, long story short – the book is for beginners in programming. If you are working with Excel on a daily basis, you will profit from the book, if you have no VBA experience. If you are an experienced VBA user, then look for something else. And just do not be afraid to try the code as it goes.
That’s all from me! 🙂