O'Reilly logo

Excel Scientific and Engineering Cookbook by David M Bourg

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

2.2. Writing Functions and Subroutines

Problem

You want to write VBA code to perform some task or calculation but you're not sure where to begin.

Solution

Open the VBA IDE, create a code module for your workbook, and then start writing your custom procedures (functions and subroutines).

Discussion

When working with Excel and VBA, you write custom code in functions and subroutines. Unlike in traditional application programming, we're not going to write a main program from which we manage the application loop, making calls to other functions and subroutines, and so on. We will, however, make heavy use of custom VBA procedures and call them from other VBA procedures. Moreover, we're going to call our VBA procedures from within Excel itself. Therefore, we're sort of attaching our custom code to Excel's main program and invoking our code within cell formulas or in response to certain events (for example, when a user presses a button). Essentially, the VBA procedures you write are extensions of Excel. This extensibility is what makes Excel, in my opinion, such a powerful computation tool.

Tip

You can actually write a subroutine and call it main, using it as a starting point for subsequent code and calls to other procedures.

Subroutines

VBA subroutines have the basic form shown in Example 2-1.

Example 2-1. VBA Subroutine

Public Sub MySubroutineName(Param1 As Integer, Param2 As Double)
    ' Your code goes here...
End Sub

Subroutines start with a scope qualifier, Public or Private, followed by the

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required