The Tiger Lottery is an old favourite on the channel.  Did you know it was based on a real-world project?  I dug out an early version of the lottery file from back in 2011 - and was shocked by some of the Excel VBA coding. 

 

 

The differences between my Excel VBA then and now are profound.  There are great lessons here for Excel VBA beginners, and a few for me!  Here are the 10 main insights that you can use to move towards professional level Excel VBA coding.

 

CLICK HERE TO WATCH THE VIDEO

00:00 Context / Motivation

 

 

01:15 1. Module Names

My favourite module name when I was a VBA beginner?  ‘Module1’.  I didn’t know module names could be changed, nor that macros could be organised into modules by function.  Some of my favourite module names these days: ‘Functionality’ for the main functions, and ‘Programmer’ for testing macros that only I need to see.

 

01:58 2. Variable Names

Ah, good old ‘Dim X As Integer’.  This line of code is ingrained on my conscience from my university days.  But, from my perspective now, it’s not good coding practice.  Why not use an informative name such as ‘Counter’ to describe the role of the variable and make it clearer now – and easier to understand when you come back to the project in 2 months!  Oh, and declaring each variable on a new line is best practice for me these days.

 

02:45 3. Lack Of Indentation

The overall structure of the code is a clear weakness.  Indentation gives the code ‘shape’, making overall structure easier to understand.  It’s an absolute must for me these days.

 

03:37 4. Lack Of Annotations

Comments in the code are conspicuous by their absence.  I couldn’t find a single one!  I love properly annotating code these days; in fact I usually write out all the comments before trying a line of VBA.

 

04:23 5. No Option Explicit

Perhaps this is less necessary when using single-letter variable names (eg. X as Integer) but, still, including Option Explicit is good coding practice.  There’s a setting in the VBA editor to do it automatically – Tools / Editor / Require Variable Declaration.  Personally, however, I like typing it myself which means I can remind you of its importance in the videos!

 

 

05:07 6. Reference Entire Column

Did you spot the range(“E:E”) reference in the original file?  I just asked Excel to perform an operation on 2 million cells!  In this case, there’s no discernable loss of efficiency, but it’s still best avoided.  The optimal solution?  Dynamically define the existing data range to clear only cells containing data.

 

06:04 7. Not Utilizing VBA Functions eg. Rnd

This one’s not a major problem but, as you progress through your VBA career, you might develop an awareness of what’s best done in the worksheet, and what’s best done in VBA.  For me, it’s become a bit of an obsession!  It’s more efficient to use VBA’s ‘native’ random number function (=Rnd) than to input the formula to the spreadsheet with VBA, for example.

 

06:48 8. Not Utilizing Application.WorksheetFunction

Again, a harsh critique perhaps, but why not maximise efficiency where possible?  Yes, workheet formulae dovetailing with VBA can be a thing of beauty, but application.worksheetfunction shifts all the work to the VBA editor and is super slick.  The loss of transparency / traceability that a formula brings is worth it, if you’re confident in VBA.

 

07:34 9. Asking VBA To ‘Activate’ Sheets

The principle here: remote vs. direct referencing.  It’s possible to ask VBA to ‘activate’ any sheet in the file.  To do so simplifies the VBA commands (or at least shortens them), as seen in the original file.  However, assuming any sheet in the file to be active can be risky, and activating a sheet slows down the code.  These days, I ensure the routines work from anywhere in the file using remote referencing techniques.

 

08:31 10. Using ‘Selection’

The same principle applies within a worksheet.  In the original file, you can see me use ‘selection’ and ‘selection.offset(1,0)’ to work down a range of cells by, literally, selecting them one by one.  This is the way I first learned VBA.  Remote referencing is more difficult to code but ultimately more robust and efficient – give it a try, or at least try to work towards it!

 

 

Let me know in the comments below what improvements you're trying to make to your Excel VBA coding - I'll get back to you there.