Learning Center
Plans & pricing Sign in
Sign Out

VBA Programming Essentials


									VBA Programming Essentials Functions – Functions return a value. This value can be a number, a string, a Boolean. Optionally, functions require one or more parameters.  Syntax o Function functionName () as Type o Function functionName(var as Type) as Type o Function functionName(var as Type, var as Type …) as Type  Issues o Functions cannot be attached to Excel buttons. o Functions MUST be assigned a value before “returning” (End Function), that is, the name of the function MUST appear to the left of the equal sign in AT LEAST one executable statement. o Functions MUST have () even if empty, e.g., Pi(). o Functions CANNOT “read” information from a spreadsheet or “write” information to a spreadsheet.  NS o A function would appear in a NS diagram as “x = calcSomething(z)” Macros – Macros invoke an action. Macros do not return a value. Macros do not allow parameters. Macros may be executed by name (Run Macro), by assigning them to a key (Cntl-m) or assigning them to a “button” (not a “command button”)  Syntax o Sub macroName ()  Issues o Macros cannot be passed information from Excel in the argument list (parameters) but can read data from a worksheet. o Macros cannot pass information back to Excel but can change values on a worksheet. o Macros can be “recorded” from Excel (New Macro), constructed from scratch, or a previously recorded macro can serve as a template. o Macros MUST have () following the name. o The name of a macro never appears to the left of the equal sign. Subprograms – Subprograms (programs) invoke an action. Subprograms optionally can have parameters.  Syntax o Sub SubName () o Sub SubName(var as Type) o Sub subName (var as Type, var as Type…)  Issues o Subprograms can be passed information in the argument list (parameters) and can read and write data from a worksheet. o Subprograms can pass information back to VBA using ByRef or can change values on a worksheet.

o Subprograms MUST have () following the name. o The name of a subprogram never appears to the left of the equal sign.  NS o A subprogram would appear in a NS diagram as “doSomething(z)” Object Oriented Programming (VBA) Modern programming involves more than just assigning a number to a variable (x=3). Modern programming (OOP) involves working with various objects (such as a cell, a range of cells, a selection, a worksheet, a workbook, an application (Excel itself), a form, a button, a scrollbar, etc). Objects have two different sets of characteristics:  Methods (things the objects knows how to do, e.g., cells know how to “clear” themselves, worksheets know how to delete themselves).  Properties (characteristics of the object). A cell has content (value), its interior (a property of cells) has a color, border, fill pattern. Cells use fonts to display information (values) and these fonts have a face, size, color, bold/italic face, etc. A single cell, a selection, and a range all have interiors, fonts, and colors, hence share this property (they are all related).  In order to work with objects, one refers to the name of the object (“to get its attention”) and then using “.” locates the method or property. Once located, the method is performed or the property is “read” or “modified”.

To top