ENGG1811 VBA Reference Card VBA Language …

[Pages:4]ENGG1811 VBA Reference Card

Editor Access and Setup

Developer tab, Visual Basic button. Starts the VBE. Shortcut is Alt-F11. Select Tools ? Options menu. First few options should be like this:

Others are according to preference, including font in Editor Format (Consolas, Fixedsys are better than Courier).

VBE Layout

Top left: Project Explorer. Top right: edit window, one per object being edited. Bottom right: Properties window, F4 to unhide. Bottom right: Immediate window (Debug.Print destination), Ctrl-g to unhide.

Workbook objects

Sheets, for related event procedures and controls. Workbook, event procedures open/close etc. Modules, where most of the VBA resides; Insert ? Module to create, rename using the Properties window.

GW 1209

VBA Language Structure

' comment starts with a single quote ' (and is ignored by the VB system) identifiers are names you give to things like

variables and procedures. They start with a letter, followed by any number of letters, digits or underscores (_).

data types include Integer, Long (integer with wider range), Double (for normal calculations), String, Boolean and Variant (= any type).

number formats include integer, decimal or scientific (also hexadecimal prefixed by &H)

string literals are enclosed in double quote characters, type two of them to represent a double quote symbol: "He said, ""Hi!""".

each module consists of optional constant definitions, optional variable declarations, then procedures.

Declarations

Constant definitions are of the form

Const name = value where name is by convention ALL_CAPS and value is a literal or constant expression, for example:

Const ROW_START = 4 ' first data row Const PLANCK = 6.626068e-34 'in J s

Variable declarations are of the form

Dim name As type or

Dim name1 As type1, name2 As type2, ...

Variable names may be simple algebraic (x, k), or indicate counting purpose (row, col), or hold significant values, using initial lower case but then Title Case (wordsJoinedWithInitialCaps).

Part A Examples:

Dim longitude As Double Dim numCells As Integer Dim e As Double ' surface roughness Dim stressMildSteel As Double

Procedures Only two types, subprograms and functions. Sub name(parameter-list...) End Sub Function name(parameter-list...) As type

name = ... ' assign result to func name End Function Procedures contain local variable declarations (normally occur first), and statements. parameter-list is a comma-separated list of local variables associated with the arguments passed to the procedure. Simplest format is

name As type A subprogram with no parameters is called a macro, and can be initiated by the user (Alt-F8). See also Parameters and Procedure Calls overleaf.

Procedure Conventions Subprograms perform general tasks, should be named using a verb phrase in Title Case (for example, ProcessCells. Functions should just calculate and return a value, not change the workbook directly. Their names should be nouns or noun phrases, such as Pythagoras, LastCell, IsEmpty, RelativeBearing.

1

Formatting Conventions (Rules)

Always indent lines that belong to an outer structure, such as procedure contents and selected or iterated statements.

Leave an empty line before each group of related statements.

Use a comment before each procedure, before a statement that does something non-obvious and on important variable declarations.

Long lines should be split by inserting a space, underscore and Enter, increase indent by half.

Assignment Statement

Statements perform actions by changing the program state. Simplest is assignment:

variable = expression

expression combines literals, named constants,

variables and function calls, combined using

operators with this precedence:

( )

parentheses

^

exponentiation

+ ?

unary: sign operators

* /

multiplication, division

\

integer division

Mod

remainder

+ ?

binary: add, subtract

&

string concatenation

= < > = comparisons

Like

Not And Or Xor Boolean operators

Like patterns:

* [abcx-z] [!xyzE-G] # ?

string of any length any single character any char except these digit = [0-9] any single character

GW 1209

Selection Statements

If Boolean-expression Then

statement

Else statement

optional

End If

Chained form:

If Boolean-expression Then

statement

ElseIf Boolean-expression Then

statement

Else statement

optional

End If

repeat as needed

A single expression can be matched this way (not required for ENGG1811):

Select Case expression

Case value1 statements

Case value1, value2, ... statements

Case value1 To value2, ... statements

Case Is > value ' or any comparison statements

Case Like pattern statements

Case Else ' if no match, optional statements

End Select

Early exit from procedure:

If exception-condition Then Exit Function ' or Exit Sub

End If

Iteration Statements

Set variable var (usually Integer or Long) to each element of arithmetic progression:

For var = start To finish Step amount

statements

optional

Next var

Continue as long as Boolean-expression is true:

While Boolean-expression statements

Wend

Also general form (not required for ENGG1811):

Do ... Loop with Exit Do and/or While conditions at either end.

Parameters and Procedure Calls

Each parameter is a local variable, initialised from the corresponding argument.

ByVal prefix means copy argument value only

ByRef prefix means associate argument variable (if it is a variable) with parameter, this is default.

Example:

Sub DoIt(ByVal count As Integer, _

ByRef result As Double)

If parameter names are known, can associate in any order using the := notation:

DoIt result:=myAnswer, count:=22

Functions always require parentheses around

arguments (if any), subprograms don't, but can be

forced with the Call statement:

Call DoIt(22, myAnswer) each of these

DoIt 22, myAnswer

is equivalent

to the above

2

Excel Objects Hierarchy

Application ActiveWorkbook ActiveSheet ActiveCell

Most common objects are ranges: ActiveSheet.Cells(row,column) ActiveSheet.Range(name) ' "max" etc ActiveSheet.Range(address) ' "A4" etc Selection ' created by .Select

Objects have properties (attributes, some of which can be set), and methods (associated procedures). Notation is the same:

object.property used in an expression

object.property = newvalue

object.method arguments... if a subprogram

object.method(arguments...) if a function

Range properties include

Value (default if omitted) Formula

Interior

Font

Borders

NumberFormat

Range methods include

ClearContents AutoFit

FillDown Merge Offset(row, col)

With Statement Can factorise common prefix object reference:

With ActiveSheet.Cells(1,4).Interior .Pattern = xlSolid .Color = RGB(100,50,100) ' purple

End With

GW 1209

Object Variables Can declare variables of specific or generic object types:

Dim rng As Range Dim wks As Worksheet Dim obj As Object Must use the Set keyword when assigning object variables (because the var "points" to the object). Set rng = ActiveSheet.Columns(5)

Collections Group (usually related) objects under a single name Can be indexed by position (1..n) or name

ActiveWorkbook.Sheets.Add _ after:=Sheets(3)

ActiveWorkbook.Sheets("Temp").Delete For Each wks In ActiveWorkbook.Sheets

Debug.Print wks.Name Next wks

ActiveSheet.Shapes Collection Represent drawing objects, coords are X and Y (down is positive) in pixels (approximately).

Dim shp As Shape Set shp = _ ActiveSheet.Shapes.AddShape(type, _

left, top, width, height) type includes many predefined constants such as

msoShapeRectangle, msoShapeOval msoShapeRightArrow, msoShapeHeart (see MsoAutoShapeType in Excel Help) other arguments define rectangular bounding box.

Part B Other Shape methods (all return a shape):

AddLine(beginX, beginY, endX, endY) AddTextBox(orientation, left, top, _

width, height) BuildFreeform see VBA Drawing document. Shape properties include Line, Fill; use these to change appearance:

With shp.Line .Weight = 1.5 .DashStyle = msoLineDashDot .Forecolor.RGB = _ RGB(128,0,0) ' dark red

End With With shp.Fill

.Forecolor.RGB = vbYellow .Transparency = 0.5 ' translucent .Visible = True ' False = hide End With

Colours

Define Long constants, or use the RGB function.

If you know the hexadecimal code as used on web pages, can also create meaningful Consts:

web : #00CC99

= RGB(0,204,153)

VBA: Const BLUE_GREEN = &H99CC00

Note the reversed red and blue order.

Built-in constants: vbBlack, vbWhite,

vbRed, vbGreen, vbBlue,

vbCyan, vbMagenta, vbYellow.

3

Standard Functions Use Excel Help for more info. Here num is a Double or Integer, i is an integer, str is a string, a is an angle in radians. Conversions:

Int(num), Fix(num), Round(num,i) Abs(num), Val(str), CStr(num), CInt(str) Real mathematics: Sqr(num) (square root) Exp(num), Log(num) (both base e, not 10) Trigonometry: Sin(a), Cos(a), Tan(a) Atn(num) (arctangent) Others: IsNumeric(str) (looks like a number?) RGB(red,green,blue) (integer colour values) Rnd() (random number 0..1) Functions used in formulas: WorksheetFunction.functionname(...)

Error Codes and possible causes 6: Overflow: calculated integer value is too large (> 32767) or too small (< ?32768) 13 Type mismatch: expression mixes up numbers and non-numeric values 438 Object doesn't support this property or method: you may have misspelled something after a dot, such as Activesheet.Culls(row,col) 1004 Unable to get the property [...] of the WorksheetFunction class: probably incorrect argument such as Asin with arg > 1

VBA Examples Function to calculate Pythagorean distance given the differences in X and Y values: Function Pythag(xDiff As Double, _

yDiff As Double) As Double Pythag = Sqr(xDiff ^ 2 + yDiff ^ 2) End Function

Snippet to process column in variable col: Dim row As Integer

row = ROW_START While Activesheet.Cells(row,col) ""

' do something with the cell row = row + 1 Wend

Subprogram calculating two values, passed back to the caller using ByRef parameters. Cartesian to polar conversion: Sub Polar(x As Double, y As Double, _

ByRef mag As Double, _ ByRef angle As Double)

mag = Pythag(x, y) angle = WorksheetFunction.Atan2(x, y)

' Atan2 works for x=0, Atn doesn't End Sub

Notes

GW 1209

4

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download