Vba And Excel Object Model Quick Reference

VBA STATEMENTS QUICK REFERENCE (CONTINUED)

Program Flow (Continued)

STATEMENT

DESCRIPTION

Select Case testexpression [Case expressionlist-n

End Select

Executes one block out of a series of statement blocks depending upon the value of an expression.

[Private | Public | Friend] [Static] Sub name [(arglist)] [statements]

End Sub

Declares the name, arguments, and code that form a Sub procedure.

Wend

Executes a block of statements as long as the specified condition is true.

End With

Executes a block of statements on a single object or on a user-defined data type.

Variable Declaration

STATEMENT

DESCRIPTION

[Public | Private] Const constname [As type] = expression

Declares a constant value.

Dim [WithEvents] varname[([subscripts])] [As [New] type]

Declares variables and allocates the appropriate storage space.

Friend [WithEvents] varname[([subscripts])] [As [New] type]

Declares a procedure or variable to only have scope in the project where it is defined.

Option Compare{Binary |Text|Database}

Specifies the default comparison method to use when comparing strings.

Option Explicit

Forces declaration of all variables within the module.

Option Private

Indicates that all code within the entire module is Private. This option is used by default. You can overwrite the effects of this option by declaring a specific procedure Public.

Private [WithEvents] varname[([subscripts])] [As [New] type]

Declares variables and procedures to only have scope within the current module.

Public [WithEvents] varname[([subscripts])] [As [New] type]

Declares variables and procedures to have scope within the entire project.

VBA STATEMENTS QUICK REFERENCE (CONTINUED)

Variable Declaration (Continued)

STATEMENT

DESCRIPTION

ReDim [Preserve] varname(subscripts) [As type]

Changes the dimensions of a dynamic array.

[Private | Public] Type varname elementname [([subscripts])] As type [elementname [([subscripts])] As type]

Defines a custom data type.

End Type

1

1

VBA FUNCTION QUICK REFERENCE

Legend:

Plain courier text = required

. .. = list of items

Italics = user-defined

| = or

[] = optional

Array Functions

FUNCTION

DESCRIPTION

RETURNS

Array(arg1,arg2, arg3,. ..)

Creates a variant array containing the specified elements.

Variant

LBound(arrayname[, dimension])

Returns the smallest subscript for the specified array.

Long

UBound(arrayname[, dimension])

Returns the largest subscript for the specified array.

Long

Data Type Conversion Functions

FUNCTION

DESCRIPTION

RETURNS

Asc (string)

Returns the character code of the first letter in a string.

Integer

CBool ( expression )

Converts an expression to Boolean data type (True or False).

Boolean

CByte(expression)

Converts an expression to Byte data type.

Byte

CCur (expression)

Converts an expression to Currency data type.

Currency

CDate ( expression )

Converts an expression to a Date data type.

Date

CDbl (expression)

Converts an expression to Double data type.

Double

CDec (expression )

Converts an expression to a decimal value.

Variant

(Decimal)

Chr (charactercode)

Converts the character code to the corresponding character. Chr(9) returns a tab, Chr(3 4) returns quotation marks, etc.

Variant

CInt (expression )

Converts an expression to an Integer data type;rounding any fractional parts.

Integer

Was this article helpful?

0 0

Post a comment