METBD 050VBA BasicsMacro A 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 forspecific tasks. Each recorded step is stored as a VBA command. Macros are sub procedures.VBA Module Contains 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.Function A 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 TrueFunctions 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 FunctionTo 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.Transformation The 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.METBD 050 – VBA Basics.doc Page 1 of 310/31/03Things in brackets[ ] are optional!Black BoxInput OutputTransformationAlt+Ctrl+F9 Key sequence used to recalculate the active worksheet.IF-Block A 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:IF condition ThenStatements if the condition is trueElseStatements if the condition is falseEnd IfThe 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:If condition Then one_statementExecution Macros 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 pressing Run.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 Toolbar Built 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 Procedure A 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. Names Names 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_dogsThe use of the underscore character is considered to be a nuisance. METBD 050 – VBA Basics.doc Page 2 of 310/31/03OptionalRun Break ResetFlowchart A visual representation of an algorithm. See handout on flowcharts.Algorithm A set of detailed, unambiguous, and ordered instructions developed to describe the transformations necessary to go from the initial (given) situation to the final