Tips for running VBA macros

The following tips may help relieve stress when running VBA procedures:

Always include the statement: Activesheet.Unprotect before the main body of program instructions begin. This enables changes to be made to the active worksheet. If you fail to do this, then you will probably get a runtime error in your program. You can always re-protect the sheet when the macro is complete.

Always ensure that the correct worksheet is selected by including an statement of the form: Worksheets ("mySheet").Select before the program instructions begin, where mySheet is the worksheet that is required to be the active sheet during the running of the macro. If you fail to do this and some other worksheet is active during the program run, then the macro instructions might be applied to the wrong worksheet with unpredictable and, possible very undesirable consequences.

Make sure that any named ranges that are referred to in a VBA macro exist, and are correctly spelt. This is a common source of errors, and can be avoided by checking the names of each of the named ranges by using the Excel named range box. Click the Name box at the left-hand end of the formula bar in Excel to see what Name ranges exist in your workbook. Don't try to run a macro until it is ready. If your macro refuses to run, check for obvious obstructions, i.e., make sure that you have no dialog box awaiting a response, or that you are not currently editing a cell in the worksheet that you are using, and so on.

If you are typing a VBA statement that is excessively long, you can continue from one statement to the other by using the underscore ( _ ) character. To use this, first type a space at the point where you want to finish the line, and then type the underscore character, and then continue the typing on the next line. Remember, you must have the space before, and after, the underscore character.

If you want to temporarily remove a statement in a VBA program, you can comment out the statement by inserting the comment symbol ( ' ) before the statement line begins. This circumvents the need for re-typing the line again when it is to be used.

0 0

Post a comment