Tips for running VBA macros - VBA For Excel

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.

The improved UpdateSales macro

If we go back to the UpdateSales macro earlier in this chapter, you will recall that among other things, the month_no was incremented ready for the next month before ending the macro. The problem with the macro however, is that if it is used every month, then eventually it will contain the value 13, and this of course does not make sense as there are 12 months in a year. For this reason, this example will extend that macro so that whenever the month_no value exceeds 12, it is reset to 1.We can use the VBA If Then statement to do this as can be seen on line 11.This checks to see If a condition is met in this case month_no >12 and if it is, Then the statement(s) will be executed.That is, the month_no value is reset to 1.

The full code Notice the lines 11 to 13 have been included to implement this part. If Range( month_no ) >12 Then checks to see if the month_no is greater than 12, and if it is the action in line 12 is taken, i.e. Range ( month_no ) = 1. Line 13 will end the If block:this means the actions following the true condition are complete. Note how the statements are indented between If Range ( month_no ) >12 Then and End If.

The UpdateSales macro updated to include decision

The UpdateSales macro updated to include decision

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd Protection Status

VBA For Excel Topics