Lesson 2: Programming Basics

Lesson Objectives

Upon completion of this lesson, the participant will be able to:

• Use procedures to structure and segment code for effectively designed applications.
• Describe the difference between Sub and Function procedures and explain when the use of each type is appropriate.
• Customize the programming environment by changing the size and color of code text and the tab width.
• Explain the various data types available in Visual Basic and utilize them when declaring data.
• Declare variables that are accessible within a procedure, within a module or within a workbook.
• Describe the difference between variable scope and lifetime.
• Describe why explicit variable declarations make code more efficient and less error prone.
• Practice effective variable declaration using the Require Variable Declaration option.
• Explain the difference between variables and constants.
• Create arrays and use them to store different types of data.
• Use the conversion functions to change the data type of a variable.
• Manipulate text strings using Visual Basic functions such as Left, Mid, Right and others.
• Accept user input using the MsgBox and InputBox functions and use the values returned from these function in VB code.
• Describe the Auto_Open and Auto_Close sub procedures.

Some Topics to be introduced in this lesson include:

• Procedures
• Sub vs. Function Procedures
• Auto_Open and Auto_Close
• Creating and calling a procedure
• The Module Sheet
• Customizing the programming environment
• Declaring data - Data types
• Scope and lifetime
• Variables and constants
• Converting data types
• Text string manipulation
• Directory and file control
• Interaction (MsgBox,InputBox)

Programming Basics

Module Structure

Code within a Module is broken up into a series of procedures. Procedures are blocks of code that perform a task or tasks which together make up a full application An application is usually written using a series of procedures, dividing the code into more manageable units. This code is more easily shared with other procedures and is easier to debug. Procedures can also be defined to perform a specific task based on a particular event (event procedures).

There are three types of procedures in Visual Basic for Applications:

• Sub
• Function
• Property

Sub Procedures

A Sub procedure can accept arguments, perform a series of statements and change the value of its arguments. These are used most often when automating tasks and performing commands. A Sub procedure is defined using the keyword Sub followed by the name of the procedure:

Sub PrintReport()

' Procedure code goes here

End Sub

Sub procedures are the most common procedure type and will be used in code examples which introduce the rest of the topics discussed in this Module.

Auto_Open and Auto_Close

Only two type of auto-execute macros are supported in Project 4.0: Auto_Close and Auto_Open.

Any sub procedure named Auto_Open will execute automatically when the project file is first opened. Holding down the Shift key while opening the file will prevent the macro from executing. Note that if the Open Last File on Startup option is selected, an Auto_Open macro in the last saved file will be executed every time Project is booted.

Any sub procedure named Auto_Close will execute automatically when the project file is closed. Holding down the Shift key while closing the file will prevent the macro from executing. Note that if a number of files are closed at once (File Close All, or File Exit), all Auto_Close procedures will be executed for these files, if they exist.

If procedures with the same name appear in both the local project file and the global file, the local procedures will be executed.

Sub Auto_Open

{Code to execute when the workbook is opened}

End Sub

Sub Auto_Close

{Code to execute when the workbook is closed}

End Sub

Function Procedures

A Function procedure, sometimes referred to as a user-defined function, can also accept arguments, execute a series of statements and change the value of its arguments. Unlike a Sub procedure, however, a Function procedure may also return a value. They most often are used to perform some sort of calculation. Function procedures are defined using the keyword Function:

Function SalesFactor(MarkUp, Region)

' Function procedure code goes here

End Function

Function procedures will be discussed in more detail in Lesson 4.

Defining and Running a Procedure

When a procedure is created using the Sub, Function or Property statement (followed by the corresponding End statement), the procedure is available for use. Depending on the type of procedure, the procedure can be run from the Tools Macro command, while debugging or from another procedure, without any special "defining" process. A quick way to run a procedure is from within the module editor. Click within the procedure, and press the Run button on the Visual Basic toolbar.

To run a procedure from within another procedure, simply type the name of the procedure:

PrintReport

or, if the procedure resides on another module sheet, you can proceed the name with the module sheet name (although this isn’t absolutely necessary):

Module1.PrintReport

Procedures are, by default, available to all other procedures within the same library. To limit accessibility to a procedure, place the word Private before the Sub or Function statement and the procedure can only be called from within the current module:

Private Sub PrintReport()

General Declarations Section

All modules contain an area at the top of a module which falls before the first procedure in that module. This section is called the general declarations section. This area is used to define options and declare data that apply to the entire module sheet.

Using Comments and Line Labels to Document Code

Line labels can be used to separate the code within a procedure and can then be used by certain commands to indicate branching locations. A line label can contain any combination of characters but must begin with a letter and end with a colon:

Sub PrintReport()

PrintMySelection:

'Procedure code goes here

End Sub

Comments can be added to code placing either an apostrophe (') or the Rem statement at the beginning of the comment. Comments can appear on their own lines or at the end of code strings:

Sub PrintReport()

Selection.PrintOut 'Prints all pages of the selection

End Sub

Making Code Easier to Read

At times, code in a procedure can become so long that it extends past the edge of the screen. This can make reading the code difficult. The line-continuation character or the underscore (_) is used to extend a single line of code to more than one physical lines. There are two rules to using the line continuation character. It must immediately follow a space and it cannot appear within a text string.

Declaring and Storing Data

An essential element in many programs is storing data for later use. In VB, values are stored as variables or constants. This form of data is flexible because the type of data stored and the accessibility of the data from other procedures can be controlled.

Terminology and Concepts

Variables vs. Constants

The decision to create a variable or a constant depends on how that data will be used. A variable stores values that will change at some point in the application. For example, if the application stores a value that increments each time a particular task is completed, a variable should be used. A constant is used to store a value that does not change.

Data Types

In Visual Basic, the programmer has a great deal of control over how information is stored. The different data types available include:

Besides providing (in some cases) built-in error checking, assigning the correct data type to a variable or constant also saves memory. The storage size listed above is important because it demonstrates how the programmer can save memory by assigning the most appropriate data type to a variable or constant. For example, if a declared variable is assigned the Double data type (8 bytes) but will only actually store integer values (2 bytes), the programmer wastes 6 bytes of storage space. This may not be significant when there are only a few variables but can greatly decrease performance if many variables are involved.

A variable or constant that is not assigned a particular data type defaults to the variant data type. The variant data type, while useful because it can store virtually any type of data, can also compound the problem described above by requiring 16 bytes of storage space plus 1 byte for each character (when a string is stored).

Data Accessibility and Lifetime

The accessibility or the scope of a variable or constant defines how that variable is used by procedures in the application. There are three levels of scope in Visual Basic for Applications:

Public - Data declared as public in scope is directly available to all procedures within the same library.

Module - Data declared as module in scope is directly available only to procedures within the current module.

Local - Locally declared data is available only within the procedure where it is declared.

The lifetime or the length of time that a variable or constant maintains its value, is determined by the scope of the data. By default, data that is declared with Public or Module level scope maintains its value as long as Visual Basic is running. These variables will be reset when the library is closed and reopened. They are also reset if the changes are made to the library. You can manually reset them by choosing Reset from the default Run menu. Locally declared data maintain their values while the host procedure is running.

Declaring Data

Data declarations in Visual Basic can be either implicit or explicit. An implicit declaration can also be referred to as an "on-the-fly" declaration because the programmer declares the variable simply by assigning to it a value. For example, the following code creates a variable called "X" and assigns to it the value 100:

X = 100

While this method is quick and requires little prior thought or planning on the part of the programmer, there are some reasons why this type of declaration is not the preferred method:

Implicitly declared data always uses the variant data type so regardless of the type of data stored there, 16+ bytes of memory are always allocated.

Implicit variables are always local in scope and cannot be made available to other procedures.

Explicitly declared data is defined at design time using one of the following statements: Dim, Public, Private, Static or Const. The statement used is determined by the desired scope of the variable or constant.

To force Visual Basic to always require explicitly declared variables, use the "Require Variable Declaration" option in the Module General panel of the Tools Options dialog. This places the statement Option Explicit in the general declarations section of each new module. With this option enabled, VB will always perform a pre-runtime validity check each time the name is used in the procedure, decreasing the chance that the programmer will type the name incorrectly.

Declaring Data that is Public

To define a variable that is available to all procedures within the current library, use the Public statement and place the declaration in the General Declarations section of a module sheet:

Public VariableName {as Type}

It is not possible to set the value of a variable in the same line that it is declared. Also, the value of a variable cannot be defined in the General Declarations section. It must be established within a procedure.

To define a public constant, use the Public and Const statements in the General Declaration section:

Public Const ConstantName {As type} = expression

Since the value of a constant never changes, the value of the constant is part of the declaration statement.

Declaring Data that is Module in Scope

Variables that are module in scope are available to all procedures that reside in the module where the variable is declared. These are declared in the General Declarations section using the Dim or Private statements. Again, values must be assigned within a procedure:

Dim VariableName {as type}

or

Private VariableName {as type}

Module level constants use the Private statement and are located in the General Declarations section:

Private Const ConstantName {As type} = expression

Declaring Local Data

Variables that are local in scope are recognized only within the procedure where they appear and are defined within the procedure using either the Dim statement or a Static statements:

Dim VariableName {as type}

or

Static VariableName {as type}

The Static statement causes a local variable, which normally loses its value after the procedure finishes executing, to have a "permanent" lifetime like that of a public or module variable. The value of the local variable will be maintained until the library is closed.

A constant may also be defined locally, using the Const statement:

Const ConstName {as type} = expression

Using Declared Data

The following code shows how the various data declarations might appear:

' General Declarations Section

Option Explicit

Public Counter as Integer ' Public variable

Dim State as String ' Private variable

Public Const Workdays as Integer = 4 ' Public constant

Private Const City as String = "Seattle" ' Private constant

Sub MySub()

Dim Birthday as Date ' Local variable

Const StartDate as Date = #01/01/94# ' Local constant

Counter = 1

State = "Washington"

Birthday = #02/27/64#

End Sub

Try This: Using Declared Data

1. Assuming that MySub (code above) is located in Module1, what happens if the following procedure is executed from Module2? What willcorrect the problem?




The error "Variable Not Defined" appears - to correct the problem, change State to a public variable using Public in place of the Dim statement.

Sub MySub2()

Module1.MySub ' Runs the procedure MySub

MsgBox State ' Displays

End Sub

2. When a public or module level variable exists and a local variable with the same name is declared, which takes precedence? Run the code below for the:




Local takes precedence

Sub MySub3()

MySub ' Runs the procedure MySub

Dim Counter As Integer ' Declares local variable Counter

MsgBox "MySub3 Counter = " & Str(Counter)

' Shows value of Counter in MySub3

MySub4 ' Runs MySub4 (code on next page)

End Sub

Sub MySub4()

' Shows value of Counter in MySub4

MsgBox "MySub4 Counter = " & Str(Counter)

End Sub

Arrays

Arrays are another type of declared data, but instead of storing a single value, arrays can store many values. By declaring data as an array, it is possible to refer to a series of variables by the same name and use an index to distinguish each one. To define an array, follow the variable or constant name with a set of parenthesis and the element range. The following declaration creates a 5 by 100 array of integers:

Dim MyArray(1 to 5, 1 to 100) as Integer

Once declared, indexing can be used to refer to individual elements within the array:

Temp = MyArray(1, Counter) ' Assign the indexed value to the variable Temp

or,

MyArray(1, Counter) = (SomeVariable*15)/.05 ' Assign a value to an element

' in an array

Arrays in Visual Basic use a zero base, by default, if a lower boundary is not specified (e.g., "100", instead of "1 to 100"):

Dim MyArray(100) as Integer

The array will actually contain 101 elements, with indexing starting with 0. VB can be instructed to use a one base by placing the line Option Base 1 in the general declarations section of the module. This may be prone to errors, however, if the code from the procedure is copied to a module which does not use the Base 1 option.

Arrays and Memory

Assigning the proper data type to an array is even more important than with regular variables and constants because the number of bytes used is multiplied by the number of elements. The first example above reserves 1,000 bytes of storage space (5 * 100 * 2), whereas the same array declared as a variant:

Dim MyArray(1 to 5, 1 to 100)

requires at least 8,000 bytes (5 * 100 * 16).

The maximum size of an array varies, based on the operating system used and how much memory is available. Encountering this limit is unlikely, but using arrays that exceed the amount of random-access memory available on the system are slower because the data must be read from and written to disk.

Assigning Many Values to an Array

The Array function is useful when assigning many values to an array. Instead of using the following command to declare a 10 element array and assign the letters "A" to "J",

Dim MyArray (1 to 10) as String

MyArray(1) = "A"

MyArray(2) = "B"

MyArray(3) = "C"

...

MyArray(10) = "J"

declare the array as a variant and then use the Array function to assign the letters.

Dim MyArray

MyArray = Array("A", "B", "C", "D", "E", "F", "G", "H", "I", "J")

The downside to this method is that there is no way to control the data type for the array as it must use the variant data type. For very large arrays this may pose memory problems. Also, unless the Option Base 1 statement is used, the array uses a zero base by default. The Array function has other uses, which will be discussed later in the course.

Expressions

An expression is a combination of keywords, operators, variables, and constants that yield a string, number, date or object. Expressions can perform a calculation, manipulate characters, or test data to return the desired data type. A variety of functions and other tools are available to use with expressions.

Operators

Operators are used in VB to perform calculations and comparisons within expressions. Below are definitions for some of the operators used in VB. Other operators not listed include: And, Or, -, *, and &

Converting Data Types

It is sometimes necessary to change the data type of an expression before assigning it to a variable or using it to set an argument. For example, the following variable X is defined as a string. However, when the value 10000 is assigned to the variable, the error "Type Mismatch" appears:

Dim X as String

X = 10000

The problem can be corrected by converting the value 10000 to a text string using one of the conversion functions:

X = Str(10000)

or

X = CStr(10000)

Other conversion functions include:

Dim x as Integer : x=30000

x = x * 2

Will generate an error since the Integer data type has an upper limit of 32,767.

Using Functions

Visual Basic has a host of functions available for a variety of uses:

• Text string manipulation
• Date and time
• File system
• Interaction
• Math

Try This: Working with Expressions and Functions

• Choose the View Object Browser command (with a module sheet active). Select "VBA" under Libraries/Workbooks. Write down the names of 3 functions within each of these categories:
• File System
• Information
• Strings

Working with Arguments

When working with functions (and methods, discussed in Module 2), there are several different ways to enter arguments.

The first way is very similar to using functions in Excel, where arguments are entered with a comma separating each one. Assuming the InputBox function:

InputBox prompt[,title][,default][,xpos][,ypos][,helpfile,context]

Some arguments are required and others are optional (shown in square brackets). Optional arguments can be left blank with a comma serving as the place holder. For example, the InputBox function with the prompt and default arguments appear as follows:

InputBox "Enter file name",,"?.MPP"

While this method is quick, it is not always obvious what the arguments represent. Another method is to use named arguments. Every function has a set of argument names and these can be used directly within the function. The same command above using named arguments looks like this:

InputBox prompt:="Enter file name", default:="?.MPP"

Note that the argument name is followed by ":=" and, since the name identifies the argument, the extra commas are removed because the relative position of the argument is no longer important.

A quick way to enter the named arguments for a function is to find the function in the Object Browser and use the Paste button.

If the function returns a value and the value is to be used in an expression or assigned to a variable, parenthesis must be placed around the arguments. Otherwise the parenthesis may be left off. To return the result of the input box to the variable myFile, the code would be:

myFile = Application.InputBox(prompt:="Enter file name", default:="?.MPP")

Text String Manipulation

VB includes several functions for manipulating text strings. These include: Instr, Left, Ltrim, Mid, Right, Rtrim, and Trim.

Instr()

This function returns the position of the first occurrence of one string within another.

SearchString = "XXpXXpXXPXXP"

SearchChar = "P"

MyPos = Instr(4,SearchString, SearchChar,1) ‘Returns 6

Left()

This function returns the specified number of characters from the left side of a string.

AnyString = "Hello World"

MyStr = Left(AnyString,1) ‘Returns "H"

Mid()

This function returns the specified number of characters from a string.

MyString = "Mid Function Demo"

FirstWord = "Mid(MyString,1,3) Returns "Mid"

LastWord = "Mid(MyString,14,4) Returns "Demo"

MidWords = Mid(MyString,5) Returns "Function Demo"

Right()

Returns the specified number of characters from the right side of a string.

AnyString = "Hello World"

MyStr = Right(AnyString,1) Returns "d"

MyStr = Right(AnyString,6) Returns " World"

Controlling User Input

Two functions and one method are provided to control user input for VB modules Microsoft Project: the MsgBox function, the InputBox function, and the Message method.

InputBox Function

This function displays a dialog box that waits for the user to input text or choose a button and then returns the contents of the text box.

Message = "Enter a value between 1 and 3" ' Set prompt.

Title = "InputBox Demo" ' Set title.

Default = "1" ' Set default.

' Display message, title, and default value.

MyValue = InputBox(Message, Title, Default)

' Use helpfile and context. The help button is added automatically.

MyValue = InputBox(Message, Title, , , , "DEMO.HLP", 10)

' Display dialog at position 100,100

MyValue = InputBox(Message, Title, Default, 100, 100)

MsgBox Function

This function displays a message dialog box to the user and waits for the user to choose a button. It then returns a value indicating which button the user chose.

Msg = "Do you want to continue ?" ' Define message.

Style = vbYesNo + vbCritical + vbDefaultButton2 ' Define buttons.

Title = "MsgBox Demonstration" ' Define title.

Help = "DEMO.HLP" ' Define help file.

Ctxt = 1000 ' Define topic

' context.

' Display message.

Response = MsgBox(Msg, Style, Title, Help, Ctxt)

If Response = vbYES Then ' User chose Yes

' button.

MyString = "Yes" ' Perform some action.

Else ' User chose No

' button.

MyString = "No" ' Perform some action.

End If

Message Method

The message method is a hold over from the Project 3.0 macro language. It offers one significant advantage over the MsgBox function. It allows you to specify the text that appears on the yes and no buttons.

Application.Message message:="Is this OK?", type:=pjYesNo, _

yesText:="You bet", noText:="No way"

Lesson 2 Exercises

1. You need to store an integer value for use in your application. It must be available to all the procedures in Module1 and its value is determined by some condition that occurs when a certain procedure is executed. Once the value is established, it will not change. Should you use a variable or a constant? Why? How might this appear in your module sheet?

   A variable should be used. It will be declared in the General Declarations section using either the Private or Dim statement. The value will be set in the procedure where the condition is tested. While the value does not change, it is not possible to declare a constant without assigning it a value and once it is declared, it cannot be modified.



Dim VariableName as Integer

Sub Sample()
VariableName = expression
End Sub
2. I have a procedure written in Visual Basic for Applications and it runs very slowly. All it does is assign values to several public arrays. What might be the problem?

The customer may not be using the appropriate data type for the arrays. For example, the values may be integers but the arrays are declared using the variant data type. This can significantly slow execution if very large arrays (or many variables) are involved.

3. Write code which accepts a number from the user, multiplies the number by 100 and displays the result in a message box.




Sub Sample()
MsgBox Val(InputBox("Enter number")) * 100
End Sub

4. Create a macro that runs whenever the file is opened that displays a message box stating the current date and time.




Sub Auto_Open()

MsgBox Now()

End Sub

5. Input a procedure that prompts the user to enter 5 words separated by spaces, remove the last two words, clear off any extra spaces on the end and front of the string, and then display the following information on separate lines in a message box:


--The first word
--The middle word
--The last word

Sub StringFun()

Dim daString As String

Dim vFirstWord, vMiddleWord, vLastWord As String

Dim v3rdSpace, vSpacePos1, vSpacePos2 As Integer

daString = InputBox("Enter 5 words separated by spaces")

'Finds position of 3rd space and deletes everything to the right

v3rdSpace = InStr(InStr(InStr(1, daString, " ") + 1, _

daString, " ") + 1, daString, " ")

daString = Trim(Left(daString, v3rdSpace - 1))

'Determines positions of the two spaces between the three words

vSpacePos1 = InStr(1, daString, " ")

vSpacePos2 = InStr(vSpacePos1 + 1, daString, " ")

'Pulls out words

vFirstWord = Left(daString, vSpacePos1 - 1)

vLastWord = Right(daString, Len(daString) - vSpacePos2)

vMiddleWord = Mid(daString, vSpacePos1 + 1, _

vSpacePos2 - vSpacePos1 - 1)

'Concatenates message with linefeeds

MsgBox vFirstWord & Chr(10) & vMiddleWord & Chr(10) & vLastWord

End Sub