Module

1
Module 5M. Weinstein CHE 116
Spring 2008

• Function Arguments
• Types of Data
• Assigning a Variables Data Type
• Debugging in the VBA Environment
• Good VBA Coding Practices

2
Function Arguments

• Many, but not all, of Excels built-in
  functions (e.g. sine, cosine, etc.) require one
  or more inputs in order to perform the
  calculation. These inputs are called function
  arguments.

The cosine function requires 1 numerical
argument. In this particular example, the
arguments value 72 radians.
In contrast, Excels RAND function does NOT
require any arguments. In cases such as this,
empty parenthesis must be used.
The above considerations also apply to
User-developed VBA Functions!
3
Function Arguments (2)

• Consider the following two user-defined VBA
  functions

This function has zero arguments and can ONLY add
six and four.
This function has two arguments. It is much more
flexible because it can add ANY two numbers.
4
Function Arguments (3)

• Function arguments allow the outside world to
  communmicate with a VBA function.
• Function arguments are utilized to pass values
  into a VBA function from an Excel Worksheet.

The values 4 and 6 have been passed into the vba
function!
In this function,, num1 6 and num2 4
There MUST be a 1-to-1 correspondence between the
parameters in the function call and the arguments
in the function that is being called!
5
Example Problems

• Write a VBA function that inputs 2 numbers and
  performs the following operations
• Multiply the two numbers together
• Raise the result from a to the 4th power
• Divide the result from b by 6.17
• Return the calculated result.

• Write a VBA function that inputs 3 numbers and
  performs the following operations (assume all
  inputs are gt 0)
• Calculate the factorial of the first number.
• Calculate the square root of the absolute value
  of the 2nd - the 3rd
• Divide the result from a by the result from b
• Return the calculated result.

6
Solutions to Example Problems
Code Comments

• Important Observations
• Variable names are created by the programmer and
  should be descriptive of what is being calculated
• Comments are included with the written code to
  ensure that the code is completely understood.
• The code has been indented and spaced to improve
  the ease of reading the code

7
Types of Data

• In addition to working with numbers, Engineers
  also work with alphanumeric data (such as names
  and dates) and logical data. The programmer
  creates and uses variables to store and operate
  on the data.
• example yearsToGoBeforeRetirement 33
• The type of data that can be stored in a
  variable depends on how the variable is defined
  in the VBA environment. Some example types of
  data include the following

8
Types of Data (2)

• How do variables get assigned a data type?
• VBA will automatically assign the data type for
  each variable if the programmer does not specify
  otherwise. Although this is how we programmed
  during Module 4, this is very bad practice and
  will make debugging of more complex code
  extremely difficult.
• The programmer should always explicitly specify
  the data type of each variable. All programming
  work from this point forward must include
  variable specification.

• Why is it important to explicitly specify a
  variables data type?
• It allows VBA to identify certain types of errors
  BEFORE they occcur.
• It is much easier to debug and fix a program.
• The computer code will run more efficiently.

9
Assigning a Variables Data Type

• VBA uses the Dim statement to define or
  Dimension a variables data type. The general
  form of the Dim statement is as follows
• Dim variable name as variable type
• The following statement specifies that the
  variable num1(defined by the programmer) is able
  to store only integer values
• Dim num1 as Integer

10
Assigning a Variables Data Type (2)
11
Assigning a Variables Data Type (3)
VBA can be set up to REQUIRE variable
declaration. This is accomplished by typing the
Option Explicit statement at the top of each VBA
module
12
Assigning a Variables Data Type (4)
To have the Option Explicit statement show up
automatically at the top of each module, first
move to the VBA editor and select Tools ? Options
Now, on the Editor tab, place a check mark next
to Require Variable Declaration
13
Assigning a Variables Data Type (5)
All variables created within a VBA function must
have a corresponding DIM statement within the VBA
function. However.. Data passed into a VBA
functions arguments from an external source
(e.g. an Excel worksheet or another VBA function)
can not have have a DIM statement within the VBA
function. Why? The functions arguments have
already been dimensioned! When called directly
from a spreadsheet function, VBA implicitly
dimensions the function arguments unless
explicitly specified by the programmer in the
function statement as follows ?function
doCalculation(num1 as Integer, num2 as
single) When called from another VBA function,
the VBA functions arguments have already been
dimensioned in the calling function.
14
VBA Programming How to Fix Problems
Up to this point, this course has focused on VBA
functions that are quite small and not very
complex. When errors occurred in these
functions, it was relatively straightforward to
identify and correct the problem(s) via quick
visual inspection of the code. The above
approach will quickly become inefficient,
ineffective, and extremely frustrating when
working with larger, more complex coding
situations. The next several slides introduce
the general concept of Debugging a program. VBA
provides several easy-to-use utilities that are
designed to help the programmer through the
debugging process.
15
Debugging within the VBA Editor

• Error Types
• Errors identified by the compiler.
• Syntactical The VBA editor will provide a
  warning while typing the VBA code, due to errors
  in spelling, missing parenthesis, unidentified
  characters, etc.
• Run Time (while trying to run the VBA code) The
  code returns an error because it is trying to
  perform some sort of illegal operation (e.g.
  division by zero)
• Logical The code runs with no errors but the
  wrong result is returned.

16
Before Using a VBA Function

• Compile it from within the VBA Editor!

The compiler highlights certain types of problems
that need to be fixed before the code will run.
The OPTION EXPLICIT statement must be present
for the compiler to do its job effectively!
17
Reacting to Auto-Generated VBA Error Messages

• Example 1 Syntactical Error
• While writing the function below, an underscore
  was inadvertently typed instead of a negative
  sign. After hitting the return key in preparation
  for writing the next line of code, the program
  automatically displays an error message
  indicating that there is a problem with the VBA
  statement that was just written.
• For syntactical errors, VBA automatically
  highlights the erroneous VBA statement in red and
  it also highlights the specific location where
  the error occurred.
• The programmer can click OK at this time, correct
  the error, and then hit the Enter key in
  preparation for typing the next VBA statement.

18
Reacting to Auto-Generated VBA Error Messages (1)

• Example 2 Run-Time Error
• The code below is syntactically correct. When
  trying to run the program, however, the VBA code
  tries to assign a value to a variable that does
  not exist. The programmer forgot to use the Dim
  statement to specify the data type for the
  variable named result.
• For this type of error, VBA does not highlight
  the erroneous code statement in red. It does,
  however, highlight the specific location within
  the VBA statement where the error occurred (see
  below).
• The programmer can now click on the OK button to
  proceed with the debugging process.

19
Reacting to Auto-Generated VBA Error Messages (2)

• Example 2 Run-Time Error (Continued)
• After clicking on the OK button, the VBA editor
  highlights the Function statement in yellow (see
  below). This means that code execution has been
  temporarily stopped just prior to executing the
  Function statement.
• The programmer has two options at this time
• Program execution can be aborted.
• The error can be fixed and then program execution
  can be resumed, starting from the point where
  code execution has temporarily been stopped.

20
Reacting to Auto-Generated VBA Error Messages (3)
Example 2 Run-Time Error (Continued)
Debug, fix, and continue program execution.
abort
To fix the problem and continue program
execution, the programmer inserts the missing Dim
statement and then continues execution as per the
menu commands below
To abort program execution select the Run / Reset
menu commands as shown below.
21
Reacting to Auto-Generated VBA Error Messages (4)

• Example 2 Run-Time Error (Continued)
• After directing VBA to continue program
  execution, the original error has been fixed but
  another error is returned as shown below.
• Again, the programmer has two options
• Program execution can be aborted.
• The error can be fixed and then program execution
  can be resumed, starting from the point where
  code execution had temporarily been stopped.

22
Reacting to Auto-Generated VBA Error Messages (5)
Click on the End button to abort program
execution.
Example 2 Run-Time Error (Continued)
To fix the problem, click on Debug. VBA
highlights the location of the problem statement
in yellow. The programmer can now correct the
error and then continue program operation via the
Run/Continue menu selections discussed previously.
23
Debugging Errors in Programming Logic

• A logic error occurs when the VBA code runs
  through to completion but the expected result is
  not obtained.
• These types of errors are more difficult to debug
  than syntactical or run-time errors, because VBA
  does not know that there is an error and thus
  cannot provide any indication of where the
  problem might be.
• Because of this, VBA provides several debugging
  tools that can help the programmer to quickly
  track down and fix these types of errors.

24
Debugging Errors in Programming Logic (1)
VBA provides a facility that allows the
programmer to create one or more temporary
stopping points in the code. These temporary
stopping points are called breakpoints.
Breakpoints apply to the entire line of code and
cannot be placed within a VBA statement.
Values passed to function arguments
To set a breakpoint, place the mouse in the
shaded border area immediately to the left of the
desired VBA statement and then left-click the
mouse.
function arguments
25
Debugging Errors in Programming Logic (2)
A breakpoint temporarily stops execution of the
computer code, just prior to the VBA statement
where the breakpoint is set. The result of
running the code is shown below.

• Code execution has stopped where the breakpoint
  was set.
• The next line of VBA code that will be executed
  is highlighted in yellow.
• result1 has been calculated but result2 has not
  yet been calculated.

26
Debugging Errors in Programming Logic (3)

• When the code is in this suspended state, the
  value stored by each variable can be checked by
  pointing the mouse on top of the desired
  variable. In the example below, the mouse was
  placed over num1 and the value stored in num1
  pops up as 6.
• This capability allows the programmer to compare
  a variables current value to that which the
  programmer would expect. This can often lead to
  quick problem resolution, especially if the value
  is not as expected.

There are several options available to the
programmer at this point
27
Debugging Errors in Programming Logic (4)

• While in the suspended program state, VBA offers
  a method to quickly step into (or evaluate) a
  single VBA statement and then wait for further
  instructions before proceeding to the next
  statement. This avoids the need to create
  multiple breakpoints in the code.
• To initiate this capability, hit the F8 key. Each
  time this key is pressed, the line of code
  highlighted in yellow is executed and then the
  next executable line of code is highlighted .

(1) Program execution has been suspended at the
indicated breakpoint.
(2) Status of program execution after pushing the
F8 key for the 1st time.
28
Debugging Errors in Programming Logic (5)

• For situations where it is desired to check the
  values of many variables each time the F8 key is
  pressed, pointing the mouse over each variable
  becomes inefficient and cumbersome.
• VBA provides an alternative to the above approach
  by providing a Locals Window.

• The Locals Window displays all of the declared
  variables, their associated values, and their
  associated data types.
• This window automatically updates each time a new
  line of code is executed.

29
Debugging Errors in Programming Logic (6)
To open the Locals Window, click on the View menu
in the VBA editor and then select Locals Window.
This is the Locals Window. The window is
currently empty.
30
Debugging Errors in Programming Logic (7)
Program execution has been suspended at a
breakpoint. View the locals window. Note that
result2 has not yet been calculated.
This is the Locals Window
Now view the locals window after hitting the F8
key. At this point in the code, result2 has been
calculated.
This is the Locals Window
31
Summary Debugging in VBA

• Basic Tools/methods for debugging
• Compile your code
• Utilize VBA error messages to ID the problem
• Continue code execution after trying to fix an
  error (Run/Reset/Continue)
• Insert and Remove Breakpoints
• View In Progress Values Point Mouse over a
  variable
• Step through code Debug/Step Into (F8)
• View more than 1 In Progress Value at a single
  time Locals Window

Dont forget about the VBA Help Facility!
32
Basic Good Coding Practices

• Use Option Explicit and Dim all variables ?
  ALWAYS!
• User-created variable names, function names,
  module names, etc., must always begin with a
  lower-case letter. These names must always be
  descriptive (e.g. numPipes, discountFactor,
  dollarsToBill, etc.) . Because VBA always
  capitalizes the first letter of its reserved
  words, using this convention will make it easy
  to see if the programmer has inadvertently
  defined a name that is a reserved word in VBA.
• User-created variable names, function names,
  module names, etc., must always contain at least
  1 capital letter. If the user has dimensioned a
  variable containing a capital letter, VBA will
  automatically show the same capitalization for
  the variable at all locations in the code, even
  if initially typed in lower case. Using this
  convention will make it easy to check whether a
  name has been typed correctly and will help to
  reduce debugging time.
• Unless self-explanatory, all lines and/or blocks
  of code must always be commented. This includes
  providing a description for all dimensioned
  variables as well as a description for all
  arguments passed to a function.
• An overall description of the function, with any
  special comments, must always appear directly
  below the function statement. Variable
  declarations should follow directly after.
• Indent and space blocks of code to improve
  organization and readability of the code. This
  facilitates code modification debugging
  activities.
• Organize VBA code into 1 or more modules. Group
  the same types of functions within a single
  module!

These will become critical as the code becomes
more complicated. All work performed in this
class MUST adhere to these practices.
33
Be Prepared for the Next Class!

• Thoroughly study the Module 5 lecture notes
• Practice, practice, practice! Use it or lose it!