Page 70 - Excel for Scientists and Engineers: Numerical Methods

CHAPTER 2 FUNDAMENTALS OF PROGRAMMING WITH VBA 47

Sub ArrayDemo20
'Another method to "work around" the row-column problem: use
Transpose.
'Note that Transpose creates a 1 -base array.
Dim TestArray( 10)
statements to populate the array
Range("E1 :El O").Value = Application.Transpose(TestArray)
End Sub

Figure 2-17. Another "work around" for the row-column problem.

Custom Functions

Chapter 1 provided an introduction to Sub procedures and Function
procedures. By now it should be clear that a Sub procedure (a command macro)
is a computer program that you "run"; it can perform actions such as formatting,
opening or closing documents and so on. A Function procedure (a user-defined
function) is a computer program that calculates a value and returns it to the cell
in which it is typed. A Function procedure cannot change the worksheet
environment (e.g., it can't make a cell Bold).
The following sections provide some examples of more advanced features of
custom functions.

Specifying the Data Type of an Argument

You can specify the data type of an argument passed to a Function
procedure by using the As keyword in the Function statement. For example,
the Function procedure MolWt takes two arguments: formula (a string) and
decimals (an integer). The statement

Function MolWt (formula As String, decimals As Integer)
declares the type of each variable. If an argument of an incorrect type is
supplied to the function, a #VALUE! error message will be displayed.

Specifying the Data Type
Returned by a Function Procedure
You can also specify the data type of the return value. If none is specified,
the Variant data type will be returned. In the example of the preceding section,
MolWt returns a floating-point result. The Variant data type is satisfactory;
however, if you wanted to specify double precision floating point, use an
additional As Type expression in the statement, for example,

Function MolWt (formula As String, decimals As Integer) As Double