METBD 050

VBA Basics

MacroA set of instructions, typically recorded by the user, that can be played back at any time to repeat the instructions automatically. When in RECORD mode, everything the user does is recorded in the macro file. Macros are stored in VBA Modules and can be edited using the Visual Basic Editor (VBE). Recorded macros are most useful for changing the formatting of objects. Recorded macros cannot contain any structured programming elements, such as the use of variables, decision structures, or looping structures. This limits their power. Recording a macro is a good way to learn how to write the VBA code for specific tasks. Each recorded step is stored as a VBA command. Macros are sub procedures.

VBA ModuleContains the code for macros, sub procedures and functions. They are automatically generated when recording macros. Can be added in the project explorer window of the VBE. Right-click on one of the objects in the desired workbook and select Insert – Module. The name of the module can be changed selecting the Module from the project explorer window of the VBE and changing its (Name) property in the Properties window. Modules can be saved by exporting them to disk. Exported modules can be imported into other workbooks.

FunctionA set of VBA instructions that takes information from a worksheet or a sub procedure, and transforms it into a solution. Functions can only work with data. They cannot modify the formatting of any object in Excel. Functions can contain structured programming elements, such as the use of variables, loops, and decision structures. A user-defined function will recalculate each time its input arguments change. If you want a function to recalculate each time the worksheet containing it updates, the first line after the function line should be:

Application.Volatile True

Functions can be Private/Public and/or Static. Private means that only the other procedures in the module can access the function. Private functions are not available in worksheets. A public function can be accessed by the worksheet and any procedure in any module in the project. The term static allows any variables in the function to retain their values after the function runs. By default, functions are public. The syntax for the function is:

[Private/Public][ Static] Function name ([Arglist]) [As type]

[one or more instructions]

[name = expression]

End Function

To return a value to the worksheet or the calling sub procedure, set the function name equal to the value to be returned. The value returned can be a number or a text string. Arguments in the Arglist are separated by commas. The arguments in the argument list act as variables within the function. If you want an argument to be optional, type Optional and a space before the argument name. The type is the type of data that the function returns.

TransformationThe function of a computer program is to transform input data into results. The group of steps necessary to perform the transformation is called an Algorithm.

Alt+Ctrl+F9Key sequence used to recalculate the active worksheet.

IF-BlockA decision structure used in VBA function and sub procedures. This structure compares two pieces of information and performs one of two possible sets of instructions based on the result of the comparison. The syntax for the if-block is:

IFconditionThen

Statements if the condition is true

Else

Statements if the condition is false

End If

The condition is a comparison between two things. The comparison operators are the same as in Excel: =, >, <, >=, <=, >. The Else portion of the if-block is optional such that the structure can execute a series of commands only if a condition is true. The if-block must finish with End If otherwise an error will occur. If an if-block will only have one command to execute only if the condition is true, then the statement can be written in one line as follows:

IfconditionThenone_statement

ExecutionMacros and sub procedures can be executed in a variety of ways. First is by selecting Tools-Macro-Macros and selecting the appropriate macro from the list and pressingRun. This hardly seems like a time-saver. Macros and sub procedures can be assigned to a shortcut key for execution by key strokes. Pretty efficient. They can also be assigned to custom buttons on custom toolbars. This is one of the fastest ways to execute a macro. In the VBE, macros can be executed using the run button on the toolbar. The control buttons are

Custom ToolbarBuilt by the user, it contains commonly used standard buttons and/or user-defined buttons. Macros can be assigned to custom buttons on toolbars. In the computer labs, custom toolbars have to be attached to a workbook in order to save them. Right-click on the toolbar and select Customize. Select the Toolbars tab from the dialog box. Select the Attach… button. In the left hand window, select the toolbars to be attached to the workbook and pick the Copy button. The toolbar should be available the next time the workbook is opened.

Sub ProcedureA VBA program that is run within the Excel program. They can be recorded macros or they can be written from scratch. Sub procedures can contain elements of structured programming, such as the use of variables, loops and decision structures. Recorded macros can be modified to include structured programming elements.

NamesNames of functions, procedures, and variables can contain both letters and numbers. They must be less than 254 characters long. The first character must be a letter. VBA names are not case sensitive. Names cannot use spaces, periods or the following characters: #, $, %, &, or !. Names can use the underscore character (_). Names that are more than one word long are written all together in lower-case characters. The first letter of each word is capitalized to make the name more readable. For example:

numberofreddogs would be written as NumberOfRedDogs

or number_of_red_dogs

The use of the underscore character is considered to be a nuisance.

FlowchartA visual representation of an algorithm. See handout on flowcharts.

AlgorithmA set of detailed, unambiguous, and ordered instructions developed to describe the transformations necessary to go from the initial (given) situation to the final (required) situation. Algorithms have the following characteristics:

  1. It must be sufficiently detailed to describe the transformations necessary to solve the problem.
  2. It must be unambiguous, so that anyone can perform the transformations correctly every time.
  3. It must always give the same results for the same initial situation.
  4. It must give correct results in all cases.

ITO ChartThere are generally three parts to the solution of an engineering problem. The first part is the initial situation which indicates the given information. This is the information with which you are to solve the problem. It is often some kind of data and sometimes includes a formula or mathematical expression. The second part is the final situation. This is what you are to find. Often, it is a numerical answer to the problem. The third part is the transformation which is arguably the most important and difficult step in the process. The transformation is the set of steps necessary to take the given information and transform it into the solution. In computer applications, the transformation process is generally the algorithm used by the computer to solve the problem. The steps in the transformation (algorithm) have to be well know to the computer which means that the computer has to be able to understand and perform the task asked of it.

One manner of solving a problem is with the use of an ITO chart. ITO stands for Input, Transformation, and Output. These are computer terms for given, transform, and find. A typical IPO chart for finding the area of a circle might look like:

Input
(Given) / Transformation / Output
(Find)
Radius, R /
  1. Get R
  2. Compute A: multiply 3.1415 by the radius squared
  3. Print:‘The radius = ‘, R and
‘The area = ‘, A
  1. Stop
/ The radius of the circle and its area, A.

What Computers Can Do: The following are the five operations that are well known to a computer:

  1. Perform arithmetic – add, subtract, multiply and divide two numbers.
  2. Compare two pieces of information (including numbers) and select one of two alternative actions, depending upon the outcome of the comparison.
  3. Receive and put out information.
  4. Repeat any group of operations.
  5. Save any piece of information for later use.

Well Know Operation: A basic step in an algorithm. A well known operation must posses the same characteristics as an algorithm. To be established as well known, an operation must satisfy all four of the criteria given for an algorithm.

METBD 050 – VBA Basics.docPage 1 of 3

10/31/03