Skip to main content

Static statement

Table of contents
  1. Syntax
  2. Remarks
  3. Examples

Used at the procedure level to declare variables and allocate storage space. Variables declared with the Static statement retain their values as long as the code is running.

Syntax

Static varname [ ( [ subscripts ] ) ] [ As [ New ] type ], [ varname [ ( [ subscripts ] ) ] [ As [ New ] type ]] . . .

The Static statement syntax has these parts:

Part Description
varname Required. Name of the variable; follows standard variable naming conventions.
subscripts Optional. Dimensions of an array variable; up to 60 multiple dimensions may be declared. The subscripts argument uses the following syntax:

[ lowerTo ] upper [ , [ lowerTo ] upper ] . . .

When not explicitly stated in lower, the lower bound of an array is controlled by the Option Base statement. The lower bound is zero if no Option Base statement is present.

New Optional. Keyword that enables implicit creation of an object. If you use New when declaring the object variable, a new instance of the object is created on first reference to it, so you don't have to use the Set statement to assign the object reference. The New keyword can't be used to declare variables of any intrinsic data type and can't be used to declare instances of dependent objects.
type Optional. Data type of the variable; may be Byte, Boolean, Integer, Long, Currency, Single, Double, Decimal (not currently supported), Date, String (for variable-length strings), String length (for fixed-length strings), Object, Variant, a user-defined type, or an object type. Use a separate As type clause for each variable being defined.

Remarks

After module code is running, variables declared with the Static statement retain their value until the module is reset or restarted. In class modules, variables declared with the Static statement retain their value in each class instance until that instance is destroyed. In form modules, static variables retain their value until the form is closed.

Use the Static statement in nonstatic procedures to explicitly declare variables that are visible only within the procedure, but whose lifetime is the same as the module in which the procedure is defined.

Use a Static statement within a procedure to declare the data type of a variable that retains its value between procedure calls. For example, the following statement declares a fixed-size array of integers:

Static EmployeeNumber(500) As Integer

The following statement declares a variable for a new instance of a worksheet:

Static X As New Worksheet

If the New keyword isn't used when declaring an object variable, the variable that refers to the object must be assigned an existing object by using the Set statement before it can be used. Until it is assigned an object, the declared object variable has the special value Nothing, which indicates that it doesn't refer to any particular instance of an object. When you use the New keyword in the declaration, an instance of the object is created on the first reference to the object.

If you don't specify a data type or object type, and there is no Deftype statement in the module, the variable is Variant by default.

When variables are initialized, a numeric variable is initialized to 0, a variable-length string is initialized to a zero-length string (""), and a fixed-length string is filled with zeros. Variant variables are initialized to Empty. Each element of a user-defined type variable is initialized as if it were a separate variable.

Examples

This example uses the Static statement to retain the value of a variable for as long as the module code is running.

' Function definition.
Function KeepTotal(Number)
    ' Only the variable Accumulate preserves its value between calls.
    Static Accumulate
    Accumulate = Accumulate + Number
    KeepTotal = Accumulate
End Function
 
' Static function definition.
Static Function MyFunction(Arg1, Arg2, Arg3)
    ' All local variables preserve value between function calls.
    Accumulate = Arg1 + Arg2 + Arg3
    Half = Accumulate / 2
    MyFunction = Half
End Function

Leave a comment

Your email address will not be published. Required fields are marked *

Format your code: <pre><code class="language-vba">place your code here</code></pre>