Subroutines and Functions
If you have a certain set of commands that never change, and need to be executed several times, at several different points of a macro, or need to be easily skipped sometimes and not others, one way of doing this is with a subroutine, better known as the Sub command. You've already done this to a limited extent with the sub main...end sub statements in every macro you write; this is merely an extension of that.
Declare Sub Hello()
Sub Main
Dim CS as Object
Set CS = CreateObject("Connex.Client")
bool = CS.AddField(1, "949 Starting")
Call Hello
bool = CS.AddField(3, "949 Done")
End Sub
Sub Hello()
Dim CS as Object
Set CS = CreateObject("Connex.Client")
bool = CS.AddField(2, "949 Hello, world!")
End Sub
In this case, the Declare statement tells the macro that there are other subroutines besides the main one, included at the end of the macro. Then the Call statement tells the computer to go to that subroutine and execute what it finds there. Since this is a totally separate subroutine from "main", definitions established inside of "main" don't carry over, so the "CS" short-cut for "Connex.Client" has to be redefined to be used. When the subroutine is done, the computer returns to the line after the one that called the subroutine and starts executing commands normally.
Note that using the word Call is optional; you can just include the subroutine name on a line by itself and the computer will figure out what's going on. However, that can make it very easy for another programmer to look at your macro and confuse a subroutine call with a plain old variable. On the other hand, there are times when you cannot use Call; this situation will be discussed later on. In general, however, it is probably best to always use Call unless absolutely necessary; and it should always be used when there are not any variables being sent along to the subroutine.
Functions and subroutines work similarly. The difference is that a function normally returns to the program with a value attached as if it were a variable, and thus can be part of an equation--in this, functions work exactly like functions in algebra do. Subroutines may alter a variable's value, but do not return with any specific value attached to the subroutine itself; the computer just executes the commands inside the subroutine and then returns to the program that called the subroutine.
Declare Function Addition()
Sub Main
x = 1
x = x + Addition
End Sub
Function Addition()
Addition = 1
End Function
Since the name of the function was set equal to something between the Function and End Function statements, that is the value the function returns to the command that called it. It is also possible to pass variables from the main program to a subroutine or function when it is called; for that, data is included in the parentheses.
Function Addition(x)
Addition = x + 1
End Function
Sub Main
y = 1
y = Addition(y)
z = Addition(2) + 2
x = y + Addition(z)
End Sub
As you can see, the Function...End Function statement blocks can be either before or after the main subroutine; the computer will still find them and run them only at the proper time. If you put them after the main subroutine, then you must include a corresponding Declare statement before the main subroutine; if you put them before the main subroutine, then a Declare statement is not needed.
In this program, the value of y (namely, 1) is sent to the function. While inside the function, this value becomes the variable x. The function adds one to the value given, then returns the new value (that is, 2), and the main subroutine sets y equal to that value. Then z takes a value of 2, runs it through the function, and adds 2, for a total equaling 5. Finally, x (which is not related in any way to the x used inside the function, other than as a source of possible confusion) runs z through the function and adds the result (which is 6) to y (which is 2), for a grand total equaling 8.
For those who remember high school- and college-level algebra, the above program could be represented in mathematics terminology as:
Given: f(x) = x + 1 y = f(1) z = f(2) + 2 x = y + f(z)
Once again, y = (1 + 1) = 2, z = (2 + 1) + 2 = 5, and x = 2 + (5 + 1) = 8.
For subroutines, passing variables in parentheses doesn't always do what you want it to do. As with functions, the Declare and Sub statements must include the variable to be passed in parentheses, but it is with the calling of the subprogram that things change. If parentheses are used when calling the subprogram, the program only passes a copy of the variable, which can then be modified inside the subroutine without modifying the original. If the subroutine is called without using parentheses, the original is passed along to the subroutine, and if the original variable is modified in the subroutine, it is still modified when control returns to the main program.
Unlike functions, where the value passed is the important part, subroutines pass the specified variable name, along with that variable's current value. (Variable name doesn't matter for functions because functions are almost always called using parentheses, so changes inside the function do not affect the variable's value when control is returned to the main subroutine.)
Sub Addition(x)
x = x + 1
MsgBox x
End Sub
Sub Main
x = 1
Addition(x)
y = x
Addition x
z = x
End Sub
What this program does is define x, then pass a copy of that along to the subroutine. The subroutine adds 1 to x, displays the new value (2) in a message box, and returns to the program. Since parentheses were used with the Call, a copy of x was sent to the subroutine, the actual value of x has not really changed in the main subroutine, so when y is set equal to x, y then equals 1. Then the subroutine is called without parentheses--and without the Call statement. The subroutine adds 1 to x, displays the new value of x, and returns to the main program--but now the value for x has actually changed, so that when z is set equal to x, z equals 2.
Note that the subroutine can be located either before or after the main program, and that when it is located before the main program, a Declare statement is unnecessary. There are various reasons for placing a subroutine or function before or after the main subroutine, but they all boil down to potential readability & understandability by future programmers that see your macro; as long as you remember to use or not use the Declare statement as needed, the computer can't tell the difference.
Subroutines and functions become very powerful tools when used along with the flow control commands introduced in the previous lesson. By separating the program into various modules, each with a specific purpose, the macro becomes much easier to read, understand, and error-check.
Declare Function Absolute(a%)
Declare Sub Odds(a%)
Declare Sub Evens(a%)
Declare Sub Equal
Declare Sub Skip
Declare Sub Greater(a%)
Sub Main
Num1$ = InputBox("Enter first number:")
Num2$ = InputBox("Enter second number:")
x = Val(Num1$) : y = Val(Num2$)
z = x - y
If z < 1 Then z = Absolute(z)
Select Case z
Case 0
Call Equal
Case 2, 4, 8, 10
Call Evens(z)
Case 1, 3, 5, 7, 9
Call Odds(z)
Case 6
Call Skip
Case Else
Call Greater(z)
End Select
End Sub
Function Absolute(a%)
Absolute = ABS(a%)
End Function
Sub Odds(a%)
MsgBox Str(a%) & " is Odd."
End Sub
Sub Evens(a%)
MsgBox Str(a%) & " is Even."
End Sub
Sub Equal
MsgBox "Numbers are equal."
End Sub
Sub Skip
MsgBox "There is no number 6."
End Sub
Sub Greater(a%)
MsgBox Str(a%) & " is larger than 10."
End Sub
Given two numbers input by the user, this macro subtracts one from the other, and jumps to various subroutines based on what the result is. In this particular example, the macro could be tightened up a lot by simply incorporating the function and subroutines into the main program. However, given a more complex program, especially one that calls the same function or subroutines several times in various points in the program, this layout can make a program much easier to read and understand than one that repeats all of the commands in each place they are needed. Also, if you write another macro that needs a set of commands you've used before, it is easier to find and copy a single subroutine into the new macro than to try to remember where those commands are located in the original program.
As a matter of fact, this is exactly how the RunMacro command operates, except instead of segregating commands into a single subroutine and then calling that, an entire program's worth of commands are segregated out into a different macro and then called when needed.
Next time, arrays...