Updated: 2020
	        Visual Basic Functions and Parameters/Arguments


0) Introduction:
1) Functions, Parameters, and Arguments:
2) Quick Summary of Parameters and Arguments:
3) More Functions in VB:
4) Two Other Ways to Call/Test VB Functions:
5) Functions for the Cannon Shell Worksheet:
6) Ranges as Parameters:
7) Subroutine Parameters:
8) Summary:

--------------------------------------------------------------------------------

0) Introduction:

In this lecture we will discuss how to define and call VB functions in Excel.
We will also see the difference between functions and subroutines and start
exploring how to define "parameters" for both (the things in-between the
parentheses in the function/subroutine header), and how they are both called
with "arguments" (typically numeric or text values, sometimes values from cells
and sometimes even ranges).

--------------------------------------------------------------------------------

1) Functions, Parameters, and Arguments:

We can write functions in VB that calculate results and then we can call these
functions directly from Excel worksheets (as well as from other functions or
subroutines that we write). Functions are similar to subroutines, but instead of
performming actions, they only can calculate values: so, for example, they 
CANNOT change any cells in the spreadsheet: if we try to change a cell, the cell
won't change and the computed value of the function will be #VALUE!

Also, while some subroutines (all the ones we've seen so far) define no
parameters, most functions define parameters. PARAMETERS are just variables
(names refering to values) that are initialized when a function or subroutine
is called. They are initialized by values calculated by ARGUMENTS. We can refer
to the values of arguments by using the names of their matching parameters. To
be more concrete, let's look at one simple example of a function and use the
parameter/argument terminology to describe it.

We can write a simple VB function to square a value. Such a function is not of
much use in Excel, because we can always write ^2 in a worksheet to square any
value. But, by writing this simple function first, we can concentrate on the
syntax of functions and the use of parameters and arguments. We write the
function as

Function Square(x)
  Square = x ^ 2    'Must put spaces around ^
End Function

Similarly to a subroutine, each function starts with a line Function name(...)
and ends with a line End Function. If the function needs any information to
calculate its value (here we must send the function a value to square) we write
one or more parameters inside the parentheses of the first line. The Square
function requires one parameter, which we name x (we could name it anything:
z, ToSquare, etc.) and then use x in the functions body.

We use x just like a variable inside the function. When VB reaches the line End
Function, it takes whatever value is stored in the function name (here we've
calculated x ^ 2 and stored it into Square) and returns it as the value of the
function. For simple functions, we can write them in a single line, which stores
into the function's name the value calculated from the parameters. But we can
also use all the VB statements we know to write multi-line functions.

Once we have defined a function in VB, we can call it from a worksheet by
writing the name of the function followed by values for all its parameters,
again specified in parentheses. These values are called arguments. So, the
functions that we write in VB are similar to functions that already exist in
Excel. 

-->Write the function Square (shown above) in the lecture22start workbook inside
-->  Module1 (create it as you did in the previous lecture)
-->In cell B2 on the Function worksheet, write the formula
-->   = Square(A2)
-->Fill this formula into cells B3 and B4 as well

So, in cell B2 we call the Square function with the argument A2. VB calculates
the value of A2 (here 1) and uses this value to initialize the parameter x
defined in the Square function. The Square function squares whatever value
is stored in the parameter x (1 squared is 1) and returns the square, where its
value (1) appears in cell B2. Likewise for cells B3 (using A3 as a argument)
and B4 (using A4 as an argument). Examine these formulas and values.

-->In cell B2 on the Function worksheet, write the formula
-->   = 3*Square(A2+1)
-->Fill this formula into cells B3 and B4 as well

Now in cell B2 we call the Square function with the argument A2+1. VB
calculates the value of A2 (here 1), adds 1 (here 2) and uses this value as the
value of the argument, using the argument to initialize the parameter x defined
in the Square function.

The Square function squares whatever value is stored in the parameter x (2
squared is 4) and returns the square; the formula in cell B2 specifies 3 times
Square, so the value 12 appears in the cell B2. Likewise for cells B3 and B4.
Examine their formulas and values. Use the Evaluate Formula icon for a more
detailed description of how the formula in this cell is evaluated.

-->Change the formula in B2 to be = Square("Hello"); we cannot square text,
-->  so the function returns #VALUE!

--------------------------------------------------------------------------------

2) Quick Summary of Parameters and Arguments:

PARAMETERS are variables that are defined between the parentheses in the
first line of a Function (or Subroutine). We can use these variables inside
the Function to compute with. We can use the value originally assigned to them,
or even change what value they store.

ARGUMENTS are formulas (as simple as numric/text values, or worksheet cells,
or more complicated formulas) that we specify when we want to call the Function
to  calculate a value. We communicate these arguments to the function via the
function's parameters.

In programming we speak about "calling" a function. Think about calling some
smart person who knows how to calculate a value. After they pick up the phone,
we tell them the values of the arguments (which they store in their parameter
variables) and then they call us back and tell us the value of the function.
So, calling a function results in the calculation of a value of the function
for specific arguments.

VB first calcuates the values of the arguments and then uses these values to
initialize their matching parameters. If a function has multiple parameters,
the arguments are matched to the parameters sequentially: the value of the first
argument is stored in the first parameter, the value of the second argument is
stored in the second parameter, etc. There must be a matching argument for each
parameter.

-->In cell B2 on the Function worksheet, enter the formula = Square() leaving
-->  out the argument; observe that Excel will mark the cell as #VALUE
-->Likewise, enter the formula = Square(1,2) writing too many arguments; again
-->  observe that Excel will mark the cell as #VALUE

We will now define and examine more functions and subroutines.

--------------------------------------------------------------------------------

3) More Functions in VB:

Let's look at a more complicated function: one that defines multiple
parameters and one that is actually useful in Excel. We define the Distance
function below to calculate the euclidean distance between two points on the
plane. The first is given by the coordinates (x1,y1) and the second is given by
the coordinates (x2,y2).

Function Distance(x1, y1, x2, y2)
  dx = (x1 - x2)
  dy = (y1 - y2)
  Distance = Sqr(dx ^ 2 + dy ^ 2)  'Sqr is the square root in VB
  ' or just Distance = Sqr((x1 - x2) ^ 2 + (y1 - y2) ^ 2)
End Function

So this function has 4 parameters (separated by commas), and therefore must be
called with 4 arguments (also separated by commas). It also uses two more
variables, dx and dy, inside the function just to make the calcuation
"simpler". These are called "local variables" in programming terminology. They
are local to the function. We have seen these before in subroutines. As the
comment in the last line shows, we can avoid these extra variables in this
function and just calculate Distance directly from x1, x2, y1, and y2. But, as
we have seen before, sometimes additional names makes formulas easier to
understand. No matter how we write this function, we must write Distance = 
something, to calculate what result to return when we call this function.

The complete list of simple mathematical functions availab in VB are Abs, Atn,
Cos, Exp, Log, Round, Sgn, Sin, Sqr, and Tan. Will we use Sin and Cos in part
5 below.

-->Write the function Distance (shown above) in the lecture22start workbook in
-->  Module1
-->In cell E8 on the Function worksheet, write the formula
-->   = Distance(A8,B8,C8,D8)

So, in cell E8 we call the Distance function with the four argument A8, B8, C8,
and D8. VB calculates the value of each of these cells and uses these values
to initialize the parameters x1, y1, x2, y2 in the Distance function. The
Distance function then calculates dx and dy and uses these values to compute
Distance. The result returned by this function appears in cell E8.

Try changing the values store in these cells and observe how the value is cell
E8 is recomputed.

But note that if we edit a function in VB and change it, when we return to the
Excel worksheet all the values will remain the same until we press F9 (or click
the Calculate Now icon.

-->Edit the body of the Square function to make it cube its parameter (x^3)
-->Return to the Excel worksheet and press the F9 key; observe the values in
-->  the range B2:B4 update
-->Edit the body of the Square function to make it square its parameter
-->Return to the Excel worksheet and press the F9 key; observe the values in
-->  the range B2:B4 update

-->Edit the body of the Distance function to include the statements
-->  MsgBox "dx = " & dx
-->  MsgBox "dy = " & dy
-->Return to the Excel worksheet and press the F9 key; observe the values
-->  displayed in the message boxes.
-->Edit the body of the Distance function to remove these MsgBox statements

--------------------------------------------------------------------------------

4) Two Other Ways to Call/Test VB Functions:

Besides calling a VB function by just writing its name in a worksheet cell,
there are two other ways to call a function. Both direct Excel to prompt (or
remind) us about it parameters, which helps us to match the arguments with the
parameters correctly. They also show us the value of the function for the
arguments we specify, which can help us test/undestnd the function even before
we use it in the worksheet.

-->Delete the contents of cell E8
-->Select cell E8 and click the "fx" (in the line beneath the ribbon)
-->After the title "Or select a category:" select "User Defined" (the last
-->  option in the pull-down list)
-->Ensure the word Distance is highlighted in "Select a Function:"
-->Double-click it or Click OK

We will see a pop-up window with the header "Function Arguments" and boxes
for the parameter names we used when we defined this function: x1, y1, x2, y2
(appearing in the same order as in Function Distance(x1, y1, x2, y2).

-->Click in the x1 box and then select the cell A8

Notice that the value of this argument is calculated and displayed to the right
of the box (e.g., " = 0")

-->Click in the y1 box and then select the cell B8
-->Click in the x2 box and then select the cell C8
-->Click in the y2 box and then select the cell D8

Notice that once we specify all the arguments, the pop-up window shows the
value of the function beneath the section containing the argument boxes. Using
this form, we can easily test the function for a variety of arguments without
leaving the pop up window.

-->Put the values 1, 5, 10, and 12 in the boxes for x1, y1, x2, y2 respectively
-->As we enter each new argument, Excel recalculates and displays the value of
-->  the function

If a function is already in a cell, we can bring up this "Function Arguments"
window by selecting the cell and clicking fx.

Finally, if we type the name of a function in a cell and press ctrl/a (think
a for arguments) instead of pressing "fx" and choosing the function we want to
call from a list, Excel will also pop-up the Function Arguments window for the
function, and allow us to fill in this information just as described above.

-->In cell B3, retype the formula =3*(Square and then press ctrl/a
-->Put A3+1 where it specifies the X parameter
-->Notice that Excel shows the value of the parameter (3), the value of the
-->  function (9) undeneath it, and the value of the formula in the cell (27)
--   at the bottom after "Formula result ="
-->Click OK

--------------------------------------------------------------------------------

5) Functions for the Cannon Shell Worksheet:

The formulas for calcuating the new X and Y positions of a cannon shell are
fairly complex. In the X direction, it involves the old position, the speed,
the angle, and the time increment (dT). In the Y direction, it involves the
old position, the speed, the angle, gravity, the total time, and the time 
increment.

Instead of entering this information into a cell directly, we can define
VB functions for these calculations (using nice names for parameters) and
then call these functions by their names in the worksheet (passing them the
correct arguments). Here are these two functions, each written in a single
line:

  Function NewX(oldX, speed, angle, dT)
    NewX = oldX + speed * Cos(WorksheetFunction.Radians(angle)) * dT
  End Function

  Function NewY(oldY, speed, angle, gravity, time, dT)
    NewY = oldY + (speed * Sin(WorksheetFunction.Radians(angle)) - gravity * time) * dT
  End Function

Note that the function Sin is built into VB, but we need to use the Excel
function Radians, so we must write it as WorksheetFunction.Radians. Everything
else is just a mathematical operator.

With these functions defined, we can simplify the cells in the B and C columns
on the Cannon Shell worksheet. For example, we can enter in B17 the formula

  =IF( C17<0,B16, NewX(B16,$B$10,$B$7,$B$2) )

Here the argument B16 is matched with the parameter oldX; the argument $B$10
is matched with the parameter speed; the argument $B$7 is matched with the
parameter angle; and the the argument $B$2 is matched with the parameter dT.

Without functions this cell would contain

  =IF( C17<0,B16, B16+$B$10*COS(RADIANS($B$7))*$B$2)

Likewise, we can enter in C17 the formula

  =IF(C16<0,-1, NewY(C16,$B$10,$B$7,$B$3,A17,$B$2) )

We can then fill these two cells down to the bottom.

Of course, we can make names for the absolute addresses, which would make these
formulas even easier to understand. We could create Excel names for the
arguments with the same names as the parameters: speed, angle, dT and write

  =IF( C17<0 ,B16, NewX(B16,speed,angle,dT) ) 

and

  =IF (C16<0 ,-1,  NewY(C16,speed,angle,Gravity,A17,dT) )

Examine the lecture22finish workbook to see these changes. Duplicate them in
the lecture22start workbook

--------------------------------------------------------------------------------

6) Ranges as Parameters:

So far we have seen parameters/arguments that are numbers. Some functions, like
WorksheetFunction.Sum, require a range of cells. We can to write a MySum1
function that takes a range as an argument as follows

Function MySum1(range)
  MySum1 = WorksheetFunction.Sum(range)
End Function

note that there is nothing special about the word range in the first line of
this method. I could have used any word for the parameter so long as I used
the same word as the argument when calling thee WorksheetFunction.sum function.

-->Write the function MySum1 (shown above) in the lecture22start workbook in
-->  Module1
-->In cell A17 on the Function worksheet, write the formula =MySum(A14:J14);
--> notice the value 55

Let's write a more interesting function that uses a For loop. Suppose that we
wanted to compute the sum of the largest n values in a range. We could write
the following VB function

Function TopSum(range, n)
  Sum = 0
  For i = 1 To n
    Sum = Sum + WorksheetFunction.Large(range, i)
  Next i
  TopSum = Sum
End Function

Here, the call to WorksheetFunction.Large(range,i) is done with the same
range, but i goes from 1, 2, ..., n: so it adds up in the local variable
Sum WorksheetFunction.Large(range,1) + Sum WorksheetFunction.Large(range,1) + 
... Sum WorksheetFunction.Large(range,n). This pattern of summing frequently
occurs in programs.

-->Write the function TopSum (shown above) in the lecture22start workbook in
-->  Module1
-->In cell B23 on the Function worksheet, write the formula
-->  =TopSum($A$20:$J$20,A23)
-->To explore the workings of this function further, put the statement
-->    MsgBox "Adding " & WorksheetFunction.Large(range, i) & " to sum"
-->  right before the Sum = Sum + ... statement and put the statement
-->    MsgBox "Returning the value " & TopSum
-->  right before the End Function line
-->Press F9 to recompute the value in cell B23
-->Instead of removing these MsgBox statements, comment them out by putting
-->  the ' character right before MsgBox
-->Fill the B23 cell into cells B24:B27; notice what it computes for
-->  1, 2, 3, 4, and 10 used as the argument matching parameter n
-->Hand simulate by hand what happens in the case of n being 3 for this range
-->   to understand this function better

We can write a similar function that computes the sum of the smallest n values'
in any range. It is mostly the same, with the name BottomSum substituting for
TopSum and WorksheetFunction.Small replacing WorksheetFunction.Large

Function BottomSum(range, n)
  Sum = 0
  For i = 1 To n
    Sum = Sum + WorksheetFunction.Small(range, i)
  Next i
  BottomSum = Sum
End Function

-->Write the function BottomSum (shown above) in the lecture22start workbook in
-->  Module1
-->In cell C23 on the Function worksheet, write the formula
-->  =BottomSum($A$20:$J$20,A23)
-->  and fill this function into cells B24:B27; notice what it computes for
-->  1, 2, 3, 4, and 10 used as the argument matching parameter n

Given these functions, we can write a function SumMiddle, which excludes the 
top n and bottom n values in a range (Olympic ice skating used to use 10
judges and throw out the top 2 and bottom 2 scores). The MiddleSum function
below subtracts the top n and bottom n values from the entire sum of the range.

Function MiddleSum(range, n)
 MiddleSum = WorksheetFunction.Sum(range) - TopSum(range,n)- BottomSum(range,n)
End Function

-->Write the function MiddleSum (shown above) in the lecture22start workbook in
-->  Module1
-->In cell D23 on the Function worksheet, write the formula
-->  =BottomSum($A$20:$J$20,A23)
-->  and fill this function into cells D24:D26; notice what it computes for
-->  1, 2, 3, and 4 used as the argument matching parameter n

This example shows that functions can call other functions. In addition,
subroutines can call subroutines and subroutines can call functions. But
FUNCTIONS CANNOT CALL SUBROUTINES (because functions cannot change the
worksheet and typically subroutines change the worksheet). So, once we
understand subroutines and functions, we can build up complicated
interconnections among them, often building more complicated functions out of
simpler ones.

We can also write a MySum2 function that takes a range (as text, e.g., "A1:A15")
as an argument and use this text to build a range and then sum all values over
the range. We can write this VB function as follows

Function MySum2(rangeAsText)
  MySum2 = WorksheetFunction.Sum(range(rangeAsText))
End Function

-->Write the function MySum2 (shown above) in the lecture22start workbook in
-->  Module1
-->In cell C31 on the Function worksheet, write the formula
-->  = MySum2(A31 & ":" & B31)
-->  and notice the value 12 (3+4+5)

Notice that the argument concatenates the text in A31 with the text in B31,
putting a colon (":") in between them. Note that cell A31 contains the text
C14 and the cell B31 contains the text E14, so the call to MySum2 has the
argument "C14:E14". The sum of the cells in that range is 12. Change
the values in cells A17 and B17 and observe how cell C31 changes.

-->In cell C32 on the Function worksheet, write the formula
-->  = MySum("A20:E20")
-->  and notice the value 22 (10+5+4+1+2)

So, there are at least two different ways to write functions that have range
parameters: one with a regular range and one with the range specified by
text, that uses the range function to compute the actual range from the text.

--------------------------------------------------------------------------------

7) Subroutine Parameters:

We can also generalize subroutines by using parameters in them. For an example,
let's retun to the Life calculation. Suppose that we wanted one button that
always updated by 1 generation (Update1) and one button that updated by the
number of generations specified in the cell S8 (UpdateN).

-->See the Life macros and the Life worksheet in lecture22finish workbook in
-->  Module2

We can parameterize the LifeUpdate subroutine with the parameter "times". We
use this parameter as the upper-bound of the For loop in that subroutine. Now,
when we call this subroutine we must specify a numeric argument telling the
subroutine how many times to go around the loop.

IMPORTANT: When a subroutine has a parameter, we CANNOT have it executed by
a button press. Buttons work only when connected to parameterless subroutines!

So, we need to connect the "Update1" button to the following simple subroutine
that we write (we do not record it).

  Sub LifeUpdate1()
    LifeUpdate(1)
  End Sub

Likewise, we connect the "UpdateN" button to the following simple subroutine
that we write (we do not record it).

Sub LifeUpdateN()
  LifeUpdate Range("S8").Value
End Sub

So, both these subroutines have no parameters, and therefore can be called
by pressing buttons. Each calls the general LifeUpdate subroutine, passing it
the value 1 or the value Range("S8").Value, depending on from which subroutine
the general LifeUpdate subroutine is called.

Note on Form:

we can write either:
  LifeUpdate Range("S8").Value
or 
  LifeUpdate (Range("S8").Value)

inside the LifeUpdateN subroutine. That is, we can write this argument after
the name of the subroutine (which is a bit easier to read) or write the
argument inside parentheses, which makes it look more like a function call,
with the argument in the parentheses of the subroutine call matching the
parameter in the parentheses of the subroutine definition.


Note on Debugging:

If in the VB editor, you see an error (some code outlined in yellow), you
can press the Stop icon (blue square after the green right-pointing triangle)
to reset VB (then change some code and try over).

--------------------------------------------------------------------------------

8) Summary:

Here is a quick summary of skills to acquire from working on this lecture.
You should be able to discuss each of these topics a bit, but more importantly
know HOW TO DO something with them in Excel.

Understand the terms Function, Subroutine, Parameter, and Argument; understand
how functions/subroutines are called: evaluating arguments, then storing their
values in their corresponding parameter local names.

Be able to write and call/test simple functions in VB.

Know how to use ranges in VB functions and subroutines.

Be able to add parameters to a subroutine (and know you cannot call a
parameterized subroutine by directly clicking a button, but can call it
indirectly).

Bottom Line: The purpose of reading these notes is not just to follow the
instructions and see the results; it is to understand what you did and the
connection between what you can do and what the results will be, in general.
So, in new situations where you need to apply the skill, you'll know what to do
because you actually learned the skill while following the instructions in
these lectures and thinking about what you did.