I am a supporter of
St. Joseph's hospice.
 If you find this site useful or if it helped you, consider a small donation to
St. Joseph's, please.

Information on
St. Joseph's

JustGiving - Sponsor me now!

 

VBA 04 Variables

Variables are very useful to store temporary data during execution of your code. You can store different types of data:

Type

Variable Examples

Value Examples

Comment

Boolean

bProcessed

True, False

Values 0 (False) or 1 (True)

Byte

btChar

&H0F, 253

Value range 0 - 255

Currency

ccyEUR

123.01

Value range -922,337,203,685,477.5808 to
922,337,203,685,477.5807

Date

dtBirthday

#12/31/1980#

Value range 1 January 100 to 31 December 9999 and times from 0:00:00 to 23:59:59.

Decimal

decDist

123789.12

With a scale of 0 (no decimal places), the largest possible value is +/-79,228,162,514,264,337,593,543,950,335. With a 28 decimal places, the largest value is +/-7.9228162514264337593543950335 and the smallest, non-zero value is +/-0.0000000000000000000000000001

Double

dPi

3.14159265

-1.79769313486231E308 to -4.94065645841247E-324 for negative values and from 4.94065645841247E-324 to 1.79769313486232E308 for positive values

Integer

intAge

123

Value range -32,768 to 32,767

Long

lIndex

17167

Value range -2,147,483,648 to 2,147,483,647

LongLong

llBigNum

123152367765

Only available for 64 bit systems: value range
-9,223,372,036,854,775,808 to 9,223,372,036,854,775,807

LongPtr

lptr

131313123

signed 32-bit (4-byte) numbers ranging in value from -2,147,483,648 to 2,147,483,647 on 32-bit systems; and signed 64-bit (8-byte) numbers ranging in value from -9,223,372,036,854,775,808 to
9,223,372,036,854,775,807 on 64-bit systems

Object

 

 

 

Single

sngNum

1.2345

Value range -3.402823E38 to -1.401298E-45 for negative values and from 1.401298E-45 to 3.402823E38 for positive values

String

sName

"Bernd"

Codes for String characters range from 0–255

User defined

 

 

 

Variant

vA

 

Value range -1.797693134862315E308 to -4.94066E-324 for negative values and from 4.94066E-324 to 1.797693134862315E308 for positive values. Generally, numeric Variant data is maintained in its original data type within the Variant. For example, if you assign an Integer to a Variant, subsequent operations treat the Variant as an Integer. However, if an arithmetic operation is performed on a Variant containing a Byte, an Integer, a Long, or a Single, and the result exceeds the normal range for the original data type, the result is promoted within the Variant to the next larger data type. A Byte is promoted to an Integer, an Integer is promoted to a Long, and a Long and a Single are promoted to a Double. An error occurs when Variant variables containing Currency, Decimal, and Double values exceed their respective ranges

More complex types of variables are arrays.

- Ozgrid on variables (explains scope and lifetime, too)

- Leszynski naming convention

Exceptions: (no variables but also useful constructs) constants, enumerations.

An example:

sbEnum_Sample_01_Screen

Option Explicit

Const ClMaxOutputRows As Long = 1000

Enum input_columns
    ic_start = 2 'So that we can loop from ic_start + 1 until ic_end - 1
    ic_name      'One more: 3
    ic_country   'One more: 4
    ic_date      'One more: 5
    ic_end       'So that we can loop from ic_start + 1 until ic_end - 1
End Enum         'input_columns

Enum output_columns
    oc_start = 6 'So that we can loop from oc_start + 1 until oc_end - 1
    oc_name      'One more: 7
    oc_country   'One more: 8
    oc_date      'One more: 9
    oc_end       'So that we can loop from oc_start + 1 until oc_end - 1
End Enum         'output_columns

Sub test()
    Dim lInputRow As Long
    Dim lOutputRow As Long
    Dim i As Long
   
    'Simple task: from input list starting in row 5
    'we copy all entries with name "Bernd" to output
    'list starting in row 12.
    lInputRow = 5
    lOutputRow = 12
    Range(Cells(lOutputRow, oc_name), _
        Cells(lOutputRow + ClMaxOutputRows - 1, _
        oc_date)).ClearContents
    Do While Not IsEmpty(Cells(lInputRow, ic_name))
        If Cells(lInputRow, ic_name) = "Bernd" Then
            For i = oc_start + 1 To oc_end - 1
                Cells(lOutputRow, i) = Cells(lInputRow, i - oc_start + ic_start)
            Next i
            'Alternatively:
            'Cells(lOutputRow, oc_name) = Cells(lInputRow, ic_name)
            'Cells(lOutputRow, oc_country) = Cells(lInputRow, ic_country)
            'Cells(lOutputRow, oc_date) = Cells(lInputRow, ic_date)
            lOutputRow = lOutputRow + 1
        End If
        lInputRow = lInputRow + 1
    Loop
           
End Sub
 

Constants you can set to fix values at the beginning of your code. You refer to them from anywhere in your code. If you need to change them, you only need to do so in one location.

Enumerations are good for lists or to give columns subsequent numbers. If you need to alter or to enhance your list or if you need to insert or to move a column, you only need to do so in your enumeration.

 

[Previous]  [Next]

Sulprobil   Get it done   Contact   Disclaimer   Download