Run Code from a Module

 

As a beginner to Excel VBA, you might find it difficult to decide where to put your VBA code. The Create a Macro chapter illustrates how to run code by clicking on a command button. This example teaches you how to run code from a module.

1. Open the Visual Basic Editor.

2. Click Insert, Module.

Insert Module

3. Create a procedure (macro) called Cyan.

Sub Cyan()

End Sub

Note: a procedure is either a sub or a function. Learn more about functions and subs here, if you like.

4. The sub changes the background color of your worksheet to cyan. To achieve this, add the following code line.

Cells.Interior.ColorIndex = 28

Note: instead of ColorIndex number 28 (cyan), you can use any ColorIndex number.

To run the procedure, execute the following steps.

5. Click Macros.

Click Macros

6. Select Cyan and click Run.

Run

Result:

Different Background Color

Note: code placed into a module is available to the whole workbook. That means you can select Sheet2 or Sheet3 and change the background color of these sheets as well. The Add a Macro to the Toolbar program illustrates how to make a macro available to all your workbooks (Excel files). Remember, code placed on a sheet (assigned to a command button) is only available for that particular sheet.