A commonly encountered error when developing Excel VBA is the fairly cryptic "Run-time error '91': Object variable or With block variable not set".
Today I'm going to attempt to explain what it means and some common causes. Click read more...
Excel tip: you can write your own worksheet functions in VBA to make things easier!
If you've ever wanted a worksheet function to do something that isn't supported by the built-in worksheet functions, consider writing your own function using VBA.
It's very easy to do, but there are two drawbacks to be aware of: (1) the workbook must be macro-enabled; (2) your function could perform slower than an equivalent built-in function.
To find out how to write your own, click read more...
It's very easy to do, but there are two drawbacks to be aware of: (1) the workbook must be macro-enabled; (2) your function could perform slower than an equivalent built-in function.
To find out how to write your own, click read more...
Excel VBA tip: worksheet functions can be called from within VBA using Application.WorksheetFunction
Trying to do something within VBA that can be easily achieved using a formula in a cell on a worksheet? You can probably use a VBA worksheet function.
Call a worksheet function by using Application.WorksheetFunction. For example:
Call a worksheet function by using Application.WorksheetFunction. For example:
Excel VBA tip: use "VBA." to show a list of available functions
If you want to know which VBA functions are available, type "VBA." (no quotes). Provided you have Auto List Members switched on in the VBA options you will be shown a list of all of the available functions:
Excel VBA tip: press F2 to display the Object Browser
When in the Visual Basic Editor, pressing F2 displays the Object Browser. This is a really handy tool that allows you to browse through each class and view its members. Click on a class to see the members, then click on a member to see further details.
Excel VBA coding standard: do not use Type-Declaration Characters
There are a number of Type-Declaration Characters that exist in VBA as a hangover from BASIC. These are: % (Integer), & (Long), ! (Single), # (Double), @ (Currency) and $ (String). They should not be used to declare variables.
Rationale
Consider the following variable declarations. Which is easier to read and understand?
Dim x As Long
Dim x&
Rationale
Consider the following variable declarations. Which is easier to read and understand?
Dim x As Long
Dim x&
Excel VBA coding standard: declare each variable on its own line
Standard
Each variable should be declared on its own line to aid clarity and reduce the chance of unintentional behaviour. For example:
Dim x As Long
Dim y As Long
Dim ratio As Double
Rationale
VBA has a peculiar way of declaring types when multiple variables are declared on the same line. The following two pieces of code are not equivalent:
Dim x, y, z As Long
Dim x As Long, y As Long, z As Long
In the first example, x and y become Variant type and z is a Long (which is probably not what the author intended). In the second example they are all Longs. This is not consistent with many other programming languages and is likely to cause confusion for other programmers, or even the original author.
Each variable should be declared on its own line to aid clarity and reduce the chance of unintentional behaviour. For example:
Dim x As Long
Dim y As Long
Dim ratio As Double
Rationale
VBA has a peculiar way of declaring types when multiple variables are declared on the same line. The following two pieces of code are not equivalent:
Dim x, y, z As Long
Dim x As Long, y As Long, z As Long
In the first example, x and y become Variant type and z is a Long (which is probably not what the author intended). In the second example they are all Longs. This is not consistent with many other programming languages and is likely to cause confusion for other programmers, or even the original author.
Excel VBA coding standard: Always use explicitly declared variable types
Standard
VBA for Excel supports the Variant variable type and by default will not insist that variable types are declared. Whilst this makes basic coding easier, it can easily lead to very nasty bugs and slow/inefficient code, as we will now explore.
- Always use "option explicit" at the top of your modules to force variable types to be declared.
- Never use the Variant type. It is slow, not memory efficient and can hide errors, which creates potentially nasty bugs.
VBA for Excel supports the Variant variable type and by default will not insist that variable types are declared. Whilst this makes basic coding easier, it can easily lead to very nasty bugs and slow/inefficient code, as we will now explore.
Excel shortcut: Ctrl-0 and Ctrl-Shift-0 to hide or unhide columns (note: issues under Windows Vista, 7 and 8)
Ctrl-0 hides all columns in the currently selected cells.
Ctrl-Shift-0 unhides any columns that are hidden within the currently selected cells. Any columns that were previously unhidden within the selection are not changed.
There is an issue when running Windows Vista, Windows 7 and Windows 8 that means the operating system intercepts the Ctrl-Shift-0 shortcut so it does not work in Excel. Click read more to see how to overcome this.
Ctrl-Shift-0 unhides any columns that are hidden within the currently selected cells. Any columns that were previously unhidden within the selection are not changed.
There is an issue when running Windows Vista, Windows 7 and Windows 8 that means the operating system intercepts the Ctrl-Shift-0 shortcut so it does not work in Excel. Click read more to see how to overcome this.
Excel shortcut: Alt-F11 to switch between Excel and the Visual Basic Editor
This is a really useful shortcut if you're writing a lot of VBA macros. Sure, you can alt-tab between windows to achieve the same thing, but sometimes there are a lot of windows open and this shortcut guarantees you won't have to go hunting around in your alt-tab list for the correct one.
Excel shortcut: Shift-F2 to add/edit cell comments
If you want to add a comment to a cell, or edit the existing comment, press Shift-F2.
Excel tip: did you close a workbook without saving and need to get it back? Here's how to recover it!
Open the document you forgot to save. Go to File > Info and look in the Versions section towards the bottom and you should see your unsaved versions. You can then open the required version and click the Restore button that appears below the ribbon. Note: this behaviour is only supported in Excel 2010 onwards.
From Excel 2010 onwards the Autorecover feature in Excel is very good. It will save versions of files that you closed without saving, including documents you created and never saved.
If you are trying to recover a file that was never saved, click the Manage Versions button, then Recover Unsaved Workbooks. You should see the workbook in there. Workbooks are kept for 4 days.
By default Excel saves your documents in the background every 10 minutes. You can change this by heading over to File > Options > Save and adjusting the time interval. Having it set to 10 minutes means you'll lose at maximum the last 10 minutes of work should something go wrong.
From Excel 2010 onwards the Autorecover feature in Excel is very good. It will save versions of files that you closed without saving, including documents you created and never saved.
If you are trying to recover a file that was never saved, click the Manage Versions button, then Recover Unsaved Workbooks. You should see the workbook in there. Workbooks are kept for 4 days.
By default Excel saves your documents in the background every 10 minutes. You can change this by heading over to File > Options > Save and adjusting the time interval. Having it set to 10 minutes means you'll lose at maximum the last 10 minutes of work should something go wrong.
Excel tip: use Evaluate Formula to track down errors such as #NAME?, #REF! and #VALUE!
If you're having trouble working out why a formula displays an error such as #NAME?, #REF!, #VALUE!, #N/A or #DIV/0! you can use Evaluate Formula to help identify step-by-step what happened.
Select Evaluate Formula from the Formula Auditing group on the Formulas tab. Click read more for detailed more help on how to use Evaluate Formula.
Select Evaluate Formula from the Formula Auditing group on the Formulas tab. Click read more for detailed more help on how to use Evaluate Formula.
Excel tip: show formulas in cells instead of values to help troubleshooting
Having trouble with a formula or trying to learn the formulas used on somebody else's worksheet? Use "Show Formulas" to show formulas instead of values.
"Show Formulas" is a toggle that can be found on the Formulas tab in the Formula Auditing group. When in Show Formulas mode, clicking on a cell highlights the cells used in the formula for easy viewing. Turning off the toggle returns to normal viewing mode.
"Show Formulas" is a toggle that can be found on the Formulas tab in the Formula Auditing group. When in Show Formulas mode, clicking on a cell highlights the cells used in the formula for easy viewing. Turning off the toggle returns to normal viewing mode.
Excel tip: use F2 when editing a "Refers to" or "Applies to" field
Fed up of using the cursor keys whilst editing a "Refers to" or "Applies to" field and finding a cell reference appears in the middle of your formula? Pressing F2 toggles between 'point mode' and normal 'editing mode'.
Excel shortcut: F4
Use F4 to switch between relative and absolute references.
Whilst editing a formula, press F4 to toggle the reference type of the cell reference your cursor currently touches. Reference types are:
Whilst editing a formula, press F4 to toggle the reference type of the cell reference your cursor currently touches. Reference types are:
- Relative (e.g. A1)
- Absolute (e.g. $A$1)
- Row Absolute (e.g. A$1)
- Column Absolute (e.g. $A1)
Excel shortcut: Ctrl-D
Use Ctrl-D to fill down.
Highlight a range that includes the cell you want to fill down and where you want to fill down to, then press Ctrl-D. The following will fill '50' down into all of the selected cells.
Highlight a range that includes the cell you want to fill down and where you want to fill down to, then press Ctrl-D. The following will fill '50' down into all of the selected cells.
Subscribe to:
Posts (Atom)