• Breaking News

    What are variables or data types in VBA Excel


    A named storage location that can contain data that can be modified during program execution. Each variable has a name that uniquely identifies it within its scope. A data type can be specified or not.

    Variable names must begin with an alphabetic character, must be unique within the same scope, can't be longer than 255 characters, and can't contain an embedded period or type-declaration character.

    There are quite a few VBA data types or VBA variables

    ·       Integer: Integer variables are used to store whole numbers. No Decimal form.

    Dim i As Integer
    i = 100
    Range("A1").Value = i

    ·       Single: Used to store number values that may take on decimal form. Can also contain integers.

    ·       Double: A variable of type Double is more accurate than a variable of type Integer and can also store numbers after the comma.

    Dim i As Integer
    i = 15.5
    MsgBox "value is " & i

    It will give value 16

    Dim i As Double

    i = 15.5
    MsgBox "value is " & i

    It will give value 15.5

    ·       Date: Stores date values.

      Dim BirthDay As Date

       BirthDay = DateValue("25 / 08 / 1996")

    ·       String: String variables are used to store text.

    Dim book As String
    book = "Ramayana"
    Range("A1").Value = book

    ·       Boolean: Use a Boolean variable to hold the value True or False.

    Dim India As Boolean
    India = True

    If India = True Then MsgBox "Amazing place to leave"


    The following table shows the supported data types, including storage sizes and ranges.

    Data type

    Storage size



    2 bytes

    True or False


    1 byte

    0 to 255

    Currency (scaled integer)

    8 bytes

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


    8 bytes

    January 1, 100, to December 31, 9999


    14 bytes

    +/-79,228,162,514,264,337,593,543,950,335 with no decimal point

    +/-7.9228162514264337593543950335 with 28 places to the right of the decimal

    Smallest non-zero number is+/-0.0000000000000000000000000001

    Double (double-precision floating-point)

    8 bytes

    -1.79769313486231E308 to -4.94065645841247E-324 for negative values

    4.94065645841247E-324 to 1.79769313486232E308 for positive values


    2 bytes

    -32,768 to 32,767

    Long (Long integer)

    4 bytes

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

    LongLong (LongLong integer)

    8 bytes

    -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807

    Valid on 64-bit platforms only.

    LongPtr (Long integer on 32-bit systems, LongLong integer on 64-bit systems)

    4 bytes on 32-bit systems

    -2,147,483,648 to 2,147,483,647 on 32-bit systems

    -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 on 64-bit systems


    4 bytes

    Any Object reference

    Single (single-precision floating-point)

    4 bytes

    -3.402823E38 to -1.401298E-45 for negative values

    1.401298E-45 to 3.402823E38 for positive values

    String (variable-length)

    10 bytes + string length

    0 to approximately 2 billion

    String (fixed-length)

    Length of string

    1 to approximately 65,400

    Variant (with numbers)

    16 bytes

    Any numeric value up to the range of a Double

    Variant (with characters)

    22 bytes + string length (24 bytes on 64-bit systems)

    Same range as for variable-length String

    User-defined (using Type)

    Number required by elements

    The range of each element is the same as the range of its data type.


    No comments

    View My Stats