How to Type and Run a VBA Sub Procedure. - VBA Tutorial

A brief review of a VBA procedure.

Macros or procedures > as they are known in VBA, are blocks that your type in a module that when run, perform some task in Excel like processing data.

When you type a procedure in a module, it is just like typing in Microsoft Word. However, merely typing a procedure in a module does nothing, it is just text, you must run it to carry out its instructions.

Procedures can be run from the VBA Editor directly; by clicking shapes or pictures on a worksheet; by event like when a user types in a worksheet cell and so on. Procedures run one at a time and only when specifically executed. You can have multiple procedures in a module just name them uniquely.

The difference between a Sub procedure and Function procedure.

As just stated, you type procedures in a module to perform your automated Excel tasks. The next question is which procedure to use, a Sub procedure or Function procedure?

This is actually very simply, Sub procedures are created when you want to automate Excel like process data, build charts, copy and paste, sort...etc. Sub procedures can also perform the same calculations as functions.

Function procedures are created when you want to perform a calculation like the Sum worksheet function does and return a value. Function procedures can be run directly from a worksheet formula allowing you to build your own custom worksheet functions.

Instructions for building and running a Sub procedure.

The following set of instructions demonstrates how to run a simple Sub procedure that commands Excel from the VBA Editor >. The procedure's task will be to remove unwanted rows and column A in a worksheet data table.

1. Close Excel than reopen it. Open a single workbook.

2. In Excel, press the Alt+F11 keys to launch the VBA Editor. When the Editor appears, this is where you type and debug your VBA code.

3. Once the Editor is visible, proceed to the Editor menu bar and select View/Project Explorer. This will launch the Project Explorer window if it is not already visible. This window is where you track your VBA projects and add to them.

Note that VBA projects are attached to workbooks automatically, therefore to save your code just save your workbook. Note that you must save a workbook that stores VBA code as a Macro Enabled Workbook when saving it or Excel will strip the code from it when it is closed.

4. Find the Explorer window on the left-hand side of the Editor window, find the VBAProject(Book1) icon, click on it, proceed to the Editor menu bar and select Insert / Module. A Standard module > will be inserted into the VBA project and will open on the right-hand side. This is where you will type your Sub procedure.

Click in the module Window and type the following code. The green lines are comments which do not have to be typed, press the Tab key to get the indents. Indents make the code more readable and lines up blocks of code with their start and end lines (statements).

How to assign a button to run a Sub procedure.

Press Alt+F11 to toggle back to Excel, activate Sheet1, name the worksheet Data so the code above can track it, create the table you see below and make sure you bold the Week column categories. On the same worksheet, insert a drawing shape, a rectangle is fine. Right-mouse click over the shape and select Format Shape from the shortcut menu.

On the Format Shape dialog box, navigate to the Size and properties tab and select Properties / Do not move or size with cells. When the code adjusts the sheet, this prevents the shape from being distorted as rows are deleted.

Right-mouse click again over the shape and select Assign Macro from the shortcut menu. Click on the delete_data procedure name in the Assign Macro dialog box and click OK. You have just made a button to run the Sub procedure code.

Click off the shape onto a worksheet cell, when you click the shape again it will run the code. Click the shape and see if it removes the bolded category rows and Column A from the table. To edit the shape, right-mouse click over it.

What's going on in the Sub procedure and the power of Excel VBA.

The procedure above uses a loop, For r = 2 to nrows...Next r, to scan rows on the Data worksheet. It is merely a counter but will loop the code inside its boundary n- times. Loops are used to represent among other things the start and stopping rows on a worksheet you want to process.

The Cells(r,1) command actually takes the counter r and uses it as an argument for its row argument. The second argument is the column index. We typed a 1 in the column index argument to represent column A on the worksheet. As the loop counts, the Cells command accesses cell's A2, A3, A4 and so forth down the sheet.

The Font.Bold commands return True/False if it encounters a cell that is bolded, that's the logic trigger, not always a number. The Rows().Delete commands lock onto the row and delete it at row r.

If you want to learn how to really use VBA, you must learn the basics of programming like loops, logic, variables, object expressions, how the code looks (syntax)...etc. Just switching on the Macro Recorder does not provide any engine or brains for your procedure to operate, the code records in an active format which makes it very twitchy and it will crash when data is in other workbooks which it always is.

You also begin to notice that VBA code can command any workbook and its data, the trick is figuring out  the paths to the data sheets when you do not know the names of the workbooks. We show you how to do all these this in our beginning Excel VBA classes.

Building upon the simple concepts above, a sophisticated UI can be quickly assembled on a worksheet using nothing more than drawing shapes, Data Validation, cells, and some VBA code.