More on Variables and Subroutines

1. More on Variables and Subroutines

2. Introduction • Discussion so far has dealt with self-contained subs. • Subs can call other subs or functions. • A module or macro may have many subs or functions. • Variables are not always created in a particular sub or function but may be used by these procedures. • Where a variable is created will be the scope of that variable.

3. Scope of Variables and Subroutines • A variable created with a Dim statement inside a sub is called a procedure-level variable. • A variable created a the top of a module with a Dim statement is a module-level or global variable. • Every sub in the module has access to this variable. • Instead if Dim, you can use Private which also has a module-level scope. • Another option is to use Public which is a project-level or global scope. This is often used with two or more modules in a project. • Variables declared module or project level scope should not be declared in a sub with a Dim statement. • Subroutines by default have a Public scope. When you define a Public sub then any other sub in the entire project can call this sub. • Subroutines created using Private are only callable by subs within its module.

4. Passing Control of Subs • The main sub is used to call or invoke the other subs or functions. • Subs are called or invoked using the Call statement. The arguments used by the sub are listed in parenthesis after the sub procedure’s name. • The use of sub and function procedures is an attempt to modularize a project. • Allows you to break up code in smaller parts.

5. Passing Arguments • There are two ways sub procedures can access external variables: • 1. by using module-level variables • 2. by passing arguments • Example 1: Const PI As Double = 22/7 • Sub Main() • Dim R as Integer, A as Double • R = Inputbox(“Enter the radius of a circle”,”Input”) • A = Area(R) • Range(“A1”).Value = “The Area of the Circle is “ & A • End Sub • Private Function Area(R as Integer) As Double • Area = PI * R^2 • End Sub • Example 2: • Sub Main() • Dim R as Integer, A as Double • R = Inputbox(“Enter the radius of a circle”,”Input”) • A = Area(R) • Range(“A1”).Value = “The Area of the Circle is “ & A • End Sub • Private Function Area(R as Integer) As Double • Const PI As Double = 22/7 • Area = PI * R^2 • End Function

6. Passing Arguments By Reference and By Value • Arguments passed by Reference is the default method and passes a reference to the variable being passed. • When variables are passed by Reference, the original value of the variable can be changed permanently. • When variables are passed by Value only a copy of the original variable’s contents are available to the calling procedure. • Arrays are passed to procedures (Sub or Function) by Reference since the elements are generally manipulated. The physical address in memory cannot be changed and passed by Value. • Function subroutines always return either a value, Boolean, or string result.A = Area(R) is an example of invoking a Function. The variable A will also receive the result passed from Area.

7. Concatenating Names And Random Numbers • You can concatenate names two ways: • 1. You can use the + sign, “Joe ” + “College” • 2. You can use the & symbol, “Joe “ & “College” • The second rendition is the preferred style. • Random numbers are generated using the function Rnd. You can use Int function to retrieve the integer portion generated. The actual function generates numbers between 0 and 1. • The function Randomize is a seed function that assures the numbers generated each time are random. • Example: number = 1 + Int (Rnd * 100) will generate numbers between 1 and 100.

8. Passing Arguments • There are two ways sub procedures can access external variables: • 1. by using module-level variables • 2. by passing arguments • Example 1: Const PI As Double = 22/7 • Sub Main() • Dim R as Integer, A as Double • R = Inputbox(“Enter the radius of a circle”,”Input”) • A = Area(R) • Range(“A1”).Value = “The Area of the Circle is “ & A • End Sub • Private Function Area(R as Integer) As Double • Area = PI * R^2 • End Sub • Example 2: • Sub Main() • Dim R as Integer, A as Double • R = Inputbox(“Enter the radius of a circle”,”Input”) • A = Area(R) • Range(“A1”).Value = “The Area of the Circle is “ & A • End Sub • Private Function Area(R as Integer) As Double • Const PI As Double = 22/7 • Area = PI * R^2 • End Function

9. Passing Arguments By Reference and By Value • Arguments passed by Reference is the default method and passes a reference to the variable being passed. • When variables are passed by Reference, the original value of the variable can be changed permanently. • When variables are passed by Value only a copy of the original variable’s contents are available to the calling procedure. • Arrays are passed to procedures (Sub or Function) by Reference since the elements are generally manipulated. The physical address in memory cannot be changed and passed by Value. • Function subroutines always return either a value, Boolean, or string result.A = Area(R) is an example of invoking a Function. The variable A will also receive the result passed from Area.

10. Concatenating Names And Random Numbers • You can concatenate names two ways: • 1. You can use the + sign, “Joe ” + “College” • 2. You can use the & symbol, “Joe “ & “College” • The second rendition is the preferred style. • Random numbers are generated using the function Rnd. You can use Int function to retrieve the integer portion generated. The actual function generates numbers between 0 and 1. • The function Randomize is a seed function that assures the numbers generated each time are random. • Example: number = 1 + Int (Rnd * 100) will generate numbers between 1 and 100.