In an earlier exercise we used the Macro Recorder to record a macro which formatted the selected cells with a coloured fill and a font colour (see Step-by-Step: Record a Simple Macro on page 10). When we examined the code behind the macro (see: Viewing Recorded Code on page 29) we could see that colour was specified as a number, the ColorIndex. To change the colour used by the macro is simply a matter of changing the number, but what do the numbers mean? How can you find out what colour each ColorIndex number represents.
The colour chooser in Excel offers a choice 40 different colours, each displaying a name when the mouse is held over the colour sample (Fig. 34). But it doesn't tell you the index number of the colour and, unfortunately, you can't refer to the colour by that name in VBA.
Fig. 34 The Colour Chooser.
Create the Macro
When I find myself in this situation I usually write a short macro to create my own swatch of colours along with their index numbers. Here's how to do it...
Step 1: In Excel open a new, empty workbook then open the Visual Basic Editor by using the keyboard shortcut [Alt]+[F11].
Step 2: In the Visual Basic Editor right-click on the name VBAProject(Book1) (assuming your new workbook is called Book1, otherwise choose the appropriate entry) and choose Insert > Module.
An empty code module appears in the code window of the Visual Basic Editor.
Step 3: Click in the code window and t ype:
Sub
ColorSamplesThen press the [Enter] key.
The Visual Basic Editor fills in the rest of the Sub line for you and adds the line End Sub, placing your cursor in the empty line between them.
Your code should look like this:
In the next step you will add a line of code which will specify a fill colour for a particular cell.
Step 4: Press the [Tab] key to indent your typing then type:
Range(
U n i v e r s i t y o f G r e e n w i c h S t u d e n t s ' E d i t i o n
f o r E d u c a t i o n a l U s e O n l y
Indenting code lines makes them easier to r ead (pressing tab indents text in the Visual Basic Editor by 4 spaces).
When you type the opening bracket the Visual Basic Editor displays a kind of help called Auto Quick Info which reminds you what pieces of information (called parameters) are needed to complete the code statement...
The help is telling you that it needs the address of the first cell ( Cell1) and last cell (Cell2) of the range. The first parameter (Cell1) is in bold, showing you where you are in the code statement and indicating which piece of information the Visual Basic Editor is expecting you to supply.
Note that the second parameter is enclosed by square brackets. This means that it is
optional. If your range refers to just one cell then you need only supply the first parameter.
Cell addresses are supplied as a string (a piece of text) and strings in VBA are always enclosed in quotes.
Step 5: Type:
"A1")
When you typed the closing bracket the Visual Basic Editor knew you had finished entering parameters so the help disappeared. Your code should look like this:
You now need to indicate which property of the Range you are referring to.
Step 6: Type a dot.
When you do this the Visual Basic Editor displays another kind of help called Auto List Members. This takes the form of a list of all the items that you can type at this point...
You can, of course, simply continue and type the code if you know it but it is often quicker to let the Visual Basic Editor do it for you (this also helps avoid typos and spelling mistakes). If
U n i v e r s i t y o f G r e e n w i c h S t u d e n t s ' E d i t i o n
f o r E d u c a t i o n a l U s e O n l y
You now need to indicate which property of the Interior you are referring to.
Step 9: Type a dot.
This displays another list of possibilities.
Step 10: Double-click ColorIndex
Finally, to indicate which colour you want...
Step 11: Type:=1
Your code should now look like this:
Step 12: Make sure your cursor is somewhere within the text of the macro then press the [F5] key to run the macro.
Step 13: Use the keyboard shortcut [Alt]+[F11] to switch back to the Excel window.
The macro has applied the ColorIndex number 1 (Black) to the interior fill of cell A1...
Step 14: Return to the code window and try a few more colours by changing the ColorIndex number then running the macro. You already know that the colour chooser offers 40 different colours, so try some numbers between 1 and 40.
Let's find out if there is a limit to the number of colours available.
Step 15: Change the macro to apply the ColorIndex 100 then run it.
You will see that immediately you attempt to run the macro an error occurs and a message is displayed. Excel is telling you that it can't apply the colour you specified...
U n i v e r s i t y o f G r e e n w i c h S t u d e n t s ' E d i t i o n
f o r E d u c a t i o n a l U s e O n l y
Step 16: Click the Debug button on the message box.
The message disappears and Excel takes you back to the Visual Basic Editor and indicates with a yellow highlight the line of code that it can't execute...
Take a look the Title Bar of the Visual Basic Editor window. It shows you that Excel is currently in Break Mode which means that the macro is still running but currently paused, waiting for an action from you...
Step 17: Cancel the macro by clicking the Reset button on the Visual Basic Editor toolbar.
This stops the macro and takes Excel out of break mode. (NOTE: Whilst a macro is in break mode you can not work in the Excel window).
You need to find out how many colours are available. To do this you will make use of a code loop. Loops are used to repeat sections of code. The sort of loop we will use here is called a For Next Loop and is used to repeat code a specific number of times.
You also need to create a Variable to keep track of how many times the loop has run.
Variables are used to hold information in the computer's memory. A variable must be declared at the start of a macro so that the code engine recognises it and knows what it is for.
Step 18: Place your cursor in front of the word Range and press the [Enter] key to create an empty line. Move your cursor into the empty line and type:
dim i as integer
Then press [Enter] to make an empty line underneath.
Your code should look like this:
When you move out of a line you have just typed the Visual Basic Editor carries out a
U n i v e r s i t y o f G r e e n w i c h S t u d e n t s ' E d i t i o n
f o r E d u c a t i o n a l U s e O n l y
Press [Backspace] to move the cursor back a tab.
Type:Next i
Your code should now look like this:
As it stands the loop will repeat the line of code 100 times. But in this macro you want the code to do two different things. It must work on a different cell each time, and it must apply a different colour each time. You need to edit the code line that is inside the loop.
Step 20: Place your cursor immediately to the right of the closing bracket after "A1" and type a dot.
Then type: Offset(i - 1, 0)
Then move to the end of the line, delete the number and replace it with i. Your code should now look like this:
The loop starts with the value of i equal to 1. Each time the code reaches the line Next i the value of i is increased by 1 and the loop repeats. This continues until the value of i reaches 100. Then the loop terminates and t he code moves on to the next line (which in this example finishes the macro).
The code also makes use of the value of i, in conjunction with the Offset property, to refer to a cell one row further down column A each time. Similarly, the ColorIndex starts at 1 but increases by 1 for each iteration of the loop.
The result will be that the code will colour the interior of cell A1 with ColorIndex 1, cell A2 with ColorIndex 2 etc. until it reaches a ColorIndex that does not exist. At that point the code will produce an error.
Before running a new macro it is good practice to compile the code to check for any coding errors.
Step 21: Open the Debug menu and choose Compile VBAProject.
The compiler looks for incorrect syntax and words it does not know. If nothing h appens then the code compiler has found no errors and you can proceed.
If the compiler finds an error it will notify you with a message and highlight the offending text. In this example the word Next has been spelled wrongly...
U n i v e r s i t y o f G r e e n w i c h S t u d e n t s ' E d i t i o n
f o r E d u c a t i o n a l U s e O n l y
If the compiler finds an err or correct it then compile again.
Step 22: Place your cursor anywhere between the Sub and End Sub lines of the macro and press the [F5] key to run the macro.
As expected the macro causes an error (because the macro will try to create colours up to 100 and we know that there aren't that many).
Step 23: Click the Debug button on the error message box.
The code line that i s causing the problem is highlighted yellow.
Step 24: Point at any instance of the variable i to see a box showing its current value.
The value is 57. This is the current value of i which the macro is trying to apply to a cell but is unable to do so. This tells us that the largest number we can use for the ColorIndex property must be 56.
Step 25: Click the Reset button on the Visual Basic Editor toolbar to stop the macro.
Step 26: Edit the macro to change the statement: 1 To 100 to read: 1 To 56.
Step 27: Add a new line to the macro, inside the loop but after the current line, as follows:
U n i v e r s i t y o f G r e e n w i c h S t u d e n t s ' E d i t i o n
f o r E d u c a t i o n a l U s e O n l y
The macro is now finished.
Step 28: Compile the code as described in Step 22. Then run the macro and switch to Excel to see the result.
Starting with ColorIndex 1 the macro has applied a coloured fill to cell A1 then written the number 1 in the adjacent c ell. It repeated the process increasing the number by 1 each time until it reached the maximum value of 56.
Summary
This macro has practical value in that it very quickly creates a colour swatch of the current Excel colour palette (palettes can be modified – in Excel go to Tools > Options > Color).
It is typical of the sort of macro a VBA developer might write to provide information to help with writing a program.
The main reason for using it as an example here is that it illustrates several of the Visual Basic Editor's code writing tools...
• [Alt]+[F11] is a useful keyboard shortcut to switch between the Excel window and the Visual Basic Editor window and vice versa.
• Auto Quick Info provides help about any parameters that might be required in the code statement.
• Auto List Members shows a list of the various options available when choosing a property, method or constant as you are typing.
• [F5] is a useful keyboard shortcut to run a macro from the Visual Basic Editor.
• Errors occur when macros can't execute their code properly. When an error occurs Excel displays an Error Message.
• Clicking the Debug button on an error message box takes you to the line of code that Excel is trying to execute. The offending line of code is highlighted in yellow.
• When you pause a macro in this way it goes into Break Mode.
• To stop a macro and take it out of break mode you click the Reset button.
• A Variable is used to hold a piece of information during a macro. That information can be changed as the macro runs.
• You can read the value of a variable when the macro is in break mode by pointing at it.
U n i v e r s i t y o f G r e e n w i c h S t u d e n t s ' E d i t i o n
f o r E d u c a t i o n a l U s e O n l y
• Code loops make code repeat within t he macro.
• A For...Next loop cycles a specific number of times which you define in the code.
• Compile code before running it to check for coding errors.