Writing a Macro in the VBE

In this example, you'll create a macro by typing code directly in the VBE.

Open the file 1-MacroExample01.xlsx (shown in Figure 1-21), and open the VBE.

Note You will find all the example files and source code for this book at www.apress.com in the Downloads section of this book's home page.

Figure 1-21. Sales data for the first quarter of the year

We see tour sales for the fictitious band "VBA," which are received quarterly by their management office and need to be totaled. Using R1C1 notation, we'll create one subroutine that will total these numbers, and since it is a relative reference to the cells, we'll see that we only need to create one formula.

First, we'll add a standard code module to the project. In the VBE (Alt+F11 from an Excel workbook), in the Project Explorer section (top-left pane), choose the top-level item, named VBAProject (1-MacroExample01.xlsx), right-click it, and choose Insert > Module, as shown in Figure 1-22.

¿ffjl File Edit View Insert Format Debug Run Tools Ac

Project - VBAProject a a 151

B ^ VBAProject (lMacroExampleOl.xIsm)

B §S Microsoft Excel Objects ® Sheetl {Sheet 1) |P| Sheet2 (Sheet2) (f| Sheet3 (Sheet3) ® ThisWorkbook


Alphabetic | c

Vrew Code i=H View Object

VBAProject Properties... Insert

Import File... Export Fife... Remove i^] Print... v Dockable Hide l§|] Us erForm

Module © class Module

Figure 1-22. Inserting a standard code module (shortcut menu)

The Project Explorer shows our new module, named Module1 by default, as shown in Figure 1-23.

- ^ VBAProject (1-MacroExampleOl.xlsm}

S Microsoft Excel Objects g] Sheetl (Sheetl)

I (Q SheetZ (SheetZ)

I Sheet3 (Sheet3)

^T] ThisWorkbook S Modules »8 HodJei;

Figure 1-23. New standard code module added

More Macro Security

In the code pane, create a new empty subroutine called TotalSales, as shown in Listing 1-2, and save the file.

Listing 1-2. Empty TotalSales Subroutine Sub TotalSales()

End Sub

The prompt shown in Figure 1-24 will appear.

Microsoft Office Excel

The following features cannot be saved in macro-free workbooks: ■ VB project

To save a file with these featuresr dick Nor and then choose a macro-enabled file type in the File Type list. To continue saving as a macro-free workbookr dick Yes.


Figure 1-24. Macro-free workbook warning

The file you opened has an extension of .xlsx, which is the default file format for any new Excel workbook. This format is not macro-enabled and cannot be macro-enabled. To use macros in Excel 2007, you must choose a macro-enabled format from the list of file types in the Save As dialog box.

Choose No from this dialog to display the Save As dialog box. In the "Save as type" drop-down list, choose Excel Macro-Enabled Workbook (*.xlsm), as shown in Figure 1-25, and click OK.

Figure 1-25. Selecting a macro-enabled file type (*.xlsm)

Figure 1-25. Selecting a macro-enabled file type (*.xlsm)

Other macro-enabled file types available are listed in Table 1-3.

Table 1-3. Macro-Enabled File Types

File Type


Macro-enabled template


Macro-enabled add-in


Non-XML Excel binary workbook


Our TotalSales method will create a formula to insert in the first cell in the Totals section (B8). That formula will be reused in the rest of the cells in the Totals row on the worksheet.

Let's determine the R1C1 coordinates of our formula. Once that's done, we'll assign that to a variable so we don't have to type it multiple times or copy and paste it.

The first cell in the Totals row is cell B8. On the worksheet, put the cursor in cell B8. For illustrative purposes, arrow key up until the cursor is in B3 (the first cell in the data range for that column), counting rows as you move. Of course, it's much simpler to just subtract the row numbers (8 - 3 = 5 in this case). Now we have our starting row, R[—5], five rows above our formula's cell location. Since we're working in the same column as our formula, the column reference will be C. This gives us the starting cell in our formula range of R[-5]C. Use the same technique to determine the last cell location (I've used the cell above the formula even though it does not contain any data; this is how Excel's AutoSum command works). Our finished range reference is R[-5]C:R[-1]C.

Add a string variable to hold the formula:

Dim sFormula As String

Once we've done this, we can assign the variable to each cell in the Totals data row individually.

The finished TotalSales code should look like Listing 1-3.

Listing 1-3. Completed TotalSales Macro

Sub TotalSales() 'Author: Jim DeMarco 'Date: 6/24/07

'Purpose: Adds total sales for all regions Dim sFormula As String sFormula = "=SUM(R[-5]C:R[-1]C)" Range("B8").Select ActiveCell.FormulaR1C1 = sFormula Range("C8").Select ActiveCell.FormulaR1C1 = sFormula Range("D8").Select ActiveCell.FormulaR1C1 = sFormula Range("E8").Select ActiveCell.FormulaR1C1 = sFormula

End Sub

As you can see, we created the formula once, assigned it to the sFormula variable, and then selected each target cell and inserted the formula. Of course, this is not the most efficient method we can use to achieve this.

Using Excel's Range object, we can walk through the cells in a given range and set the formula. Add a second subroutine to Modulel as follows:

Sub TotalSales2() 'Author: Jim DeMarco 'Date: 6/24/07

'Purpose: Adds total sales for all regions by looping through cells in a range Dim sFormula As String Dim cell As Range sFormula = "=SUM(R[-5]C:R[-1]C)" For Each cell In Range("B8:E8") cell.FormulaR1C1 = sFormula Next cell

End Sub

We've added a variable called cell which is of type Range. You'll recall that a range in Excel can be anything from one to multiple cells. We then walk through the range B8:E8 using a For...Each statement, visiting each cell in the referenced range. This is much more concise, easier to read, and easier to maintain. Of course, like in the first example it also assumes you know the addresses of the cells in the range to receive the formula.

Let's look at one last example that, while not completely dynamic, will show you a method whereby you could easily adapt it to determine the locations for your formula. Add one more subroutine to Module1:

Sub TotalSales3() 'Author: Jim DeMarco 'Date: 6/24/07

'Purpose: Adds total sales for all regions by moving across columms Dim sFormula As String Dim i As Integer sFormula = "=SUM(R[-5]C:R[-1]C)" For i = 2 To 5

Cells(8, i).Select ActiveCell.FormulaR1C1 = sFormula Next i

End Sub

This time we're using a counter variable, i, to loop through columns 2 through 5. We select each cell in turn and apply the formula to it. Using this method, it becomes apparent that if we can use code to determine our start and end points for the For loop, we can very easily create a dynamic method of adding our formula to a variable number of columns or rows.

+1 0

Post a comment