vbafasttrack.com provides the best Excel VBA courses, tutorials, tools and training. Period.
Our pledge to our clients ...
Over 70% of Fortune 500 firms use Excel VBA based tools / solutions. Let us show you what we can do for your business ...
Finally the new site is up and running. With a fresh new look, improved navigation, more code and a face-lift for our favourite coding tool CodeLib. We hope you enjoy your time here and find what you're looking for..
If you are considering investing in an Excel VBA course, we have a list of the questions that you should ask before signing up. As a hint, not many course providers will be able to answer yes to these questions..Read more...
One of our most popular and recommended features is our very own, world famous, 10 Commandments of Excel VBA. Do yourself a favor and have a read.Read more...
This article is the result of many years of experience and (in some cases) frustration. It has been written by a professional developer who has made all of the mistakes there are but is here to tell the tale on how to avoid those same pitfalls...
1. Always use Option Explicit
Automatically have Microsoft Office include the keywords Option Explicit at the top of all code modules. This ensures that you must explicitly declare any and all variables in your code. This means that an accidental mis-spelling of a variable name (eg Salse versus Sales) will be picked up and your code will not be using an erroneous value. In large code modules, finding such an error can take quite some time. You can set this option to be automatically enabled and inserted by Office products in the VBA Editor by selecting:
Tools -> Options -> Editor and checking the Require Variable Declaration option.
2. Use the With Keyword
When dealing with objects (either built-in or custom) or UDT's (User Defined Types - see Types below) your code becomes more readable and less convoluted if you use the With keyword when addressing members. Compare the below two code snippets which achieve the same net end result:
The following code:
Selection.Font.Bold = True Selection.Font.Italic = True Selection.Font.Underline = xlUnderlineStyleSingle Selection.Font.Color = -16776961
Is better written as:
With Selection.Font .Bold = True .Italic = True .Underline = xlUnderlineStyleSingle .Color = -16776961 End With
3. Use Built In Functions
Unless there is a very good reason (ie different calculation methodology required) then it is definitely best to utilise Excel's built-in functions wherever possible. After all they are very fast (compared to VBA code) and are tried, tested, trusted and accurate. You can use Excel's built-in functions in your VBA code through the use of the WorksheetFunction method of the Application object. An example is shown below:
Application.WorksheetFunction.Average(3, 4, 5) 'Returns the value 4
Application.Average(3, 4, 5) 'Can drop the 'WorksheetFunction specification
Naturally the numbers in the above example can be replaced by variables.
4. Use Ctrl-Space Combination
When entering code, use the Ctrl-SPACE key combination to bring up the editor's intellisense auto-complete window from which you can select (either by pressing the ENTER key or the SPACE key) the full term that you require. This can save a lot of typing and re-typing in of variable names for instance.
5. Use a Coding Convention
It is a good idea to use a coding convention in relation to variable names, particularly for variable scope. Whilst the famous Hungarian notation has been around for a while, it is quite difficult to get used to. At the very least use a scope convention of some description. For example:
6. Use Types
Use UDT's (User Defined Types) wherever possible in order to make code more readable, contained and efficient. Consider the following
Using the below::
Private Type Individual Name As String Address As String PhoneNum As String Salary As Currency End Type Private Individuals() As Individual
Is a lot more efficient and contained than four separate arrays and having to re-dimension each for example.
7. Use Meaningful variable names
Excel VBA imposes a limit of 255 characters for variable names, so there is more than enough allowance to make variable names meaningful. For example:
Sales is a poor choice when compared to
8. Use ScreenUpdating
Where your code results in a number of changes (of whatever type) to the display in the Excel application (ie the worksheets), it is advisable to turn screen updating off prior to performing such code and back on again after the code has completed. This results in faster code execution and a more professional look (the screen isn't flickering many times during updating). This is achieved by the following:
Application.ScreenUpdating = False 'Turn screen updating off
Application.ScreenUpdating = True 'Turn screen updating back on!
9. Create Stand-Alone functions / subs
Wherever possible, try to create functions and subs which are self-contained / stand-alone. In other words, they can be readily copied into other projects. This supports one of the key principles of professional development - reuse.
10. Create / Buy a Code Library
As you create more and more code, it is strongly advisable to either build or buy a code library application such that you can manage all of your code snippets in one centralised location. Some key requirements for a good code library application would be:
In addition to the production of coding tools and provision of courses, vbafasttrack.com also offers custom development services using Excel VBA as the core tool / environment. Solutions developed by us are currently in use in many companies, including:
Contact us today for a quote. We offer the best service and, if required, ultra-fast turnaround.
If your company has specific training needs that aren't met by our standard course content, enquire about having bespoke training materials produced for your firm.