1. Introduction

This problem set will introduce the use of Visual Basic 'macros', or programming modules, within a spreadsheet environment. Programming modules are easier to write and maintain for certain types of problems, particularly those involving logic statements, than the straight use of formulas in spreadsheet cells.

This problem set will also serve as a bridge to the solution of computational physics problems using programming language 'code' on its own.

1. Programming techniques

The programming language packaged with Excel is a variant of Basic called Visual Basic for Applications (VBA). VBA has most of the constructs from the full package of Visual Basic, but lacks some of the features associated with objects. Nevertheless, VBA allows you to create dialog boxes, action buttons, and graphics elements within your spreadsheet. Very sophisticated 'applications' can be created this way.

The VBA package contains a tool-box of 'controls', which are the graphical programming objects like dialog boxes, radio buttons (or action buttons), edit boxes, and so on.

There are two main types of VBA module, the 'function' and the 'subroutine'. The only difference is that a 'function' can be used to return a value just like the built-in functions (sin, cos, exp), while a subroutine only performs operations on arguments.

Here is a simple function, showing some of the things you need to know about programming in VBA.

Function AddItTwice(CellRef1, CellRef2)

Dim I As Integer

For I=1 to 2

AddIt=CellRef1 + CellRef2

Next I

End Function

This example shows the definition of a Function (with the necessary End statement), the Dim (dimension) statement (which is sometimes optional and sometimes required), the structure of a 'do loop', called a For-Next statement in Basic, and the method of returning a value for the function.

An important thing to know is how to label cells and functions, and in fact everything in your spreadsheet, so that your program is easy to de-bug and doesn't have unwanted 'features'. The naming convention is like that used for filenames in Unix, or email addresses everywhere. It sets the 'scope' of your modules, functions and subroutines, and variables.

Suppose you have a spreadsheet named 'Data', a module named 'Custom', and a function named 'AddComplex(z1,z2)' written on the module Custom.

Then, on your spreadsheet Data, you could call the function 'Custom.AddComplex(z1,z2)', and there would be no mistake about which function you meant. Within the function AddComplex, you could have a statement like Data.Cells(1,1).Value=z1, which would output the value z1 to your spreadsheet cell. You can also get input in the same way, though it is generally better to pass values as input and output in the arguments, rather than by direct cell references (but it's up to you).

1. Tasks

• Using spreadsheet formulas only, make a graph of a 'square-wave' function. Then, create a 'squarewave' subroutine as a VBA module, and use it to graph the square-wave function. Your square-wave function, in both cases, must have the period of the wave as a change-able parameter.
• Make a 'dialog box' which has two 'edit boxes', and label one 'Input' and the other 'Output'. Link the input and output edit boxes to cells on your spreadsheet. Make the ouput equal to the square of the input. Test your dialog box and make sure it works.
• Write a VBA subroutine to calculate the factorial of an integer. Create a dialog box, which has two 'edit boxes' and one 'action button'. One of the edit boxes should be labelled "Input", the other "Output". The button should be labelled "Calculate" [Note: you can use reasonable synonyms of your choice]. You enter a number as Input, push Calculate, and receive the answer as Output.
• [Extra Credit] There is a generalization of the factorial function, which is valid for positive real numbers, called the gamma function, G(x). Actually, it works for complex arguments as well, but we will work with real numbers only for simplicity. The gamma function has the following crucial property:

This is a recursion relation, and when x is an integer it is the definition of the factorial function, x! . The difference is that now x need not be an integer. You can get a good approximation to the gamma function (accurate to 5 x 10^-5) from the expansion

where the sum is over k=1 to 5, and the coefficients have the values

a₁= -0.5748646; a₂=0.9512363; a₃= -0.6998588; a₄=0.4245549; a₅= -0.1010678.

Make a subroutine, Gamma5, which calculates the gamma function for any positive real number, and plug it into your 'factorial' program.