VBA - Variables


Variable is a named memory location used to hold a value that can be changed during the script execution. Following are the basic rules for naming a variable.

Variable Naming Rules


  • You must use a letter as the first character.
  • You can't use a space, period (.), exclamation mark (!), or the characters @, &, $, # in the name.
  • Name can't exceed 255 characters in length.
  • You cannot use Visual Basic reserved keywords as variable name.
Syntax:-

Dim variable_name As variable_type

Data Types

There are many VBA data types, which can be divided into two main categories, namely numeric and non-numeric data types.

Numeric Data Types


Type Range of Values
Byte 0 to 255
Integer -32,768 to 32,767
Long -2,147,483,648 to 2,147,483,648
Single -3.402823E+38 to -1.401298E-45 for negative values
1.401298E-45 to 3.402823E+38 for positive values.
Double -1.79769313486232e+308 to
-4.94065645841247E-324 for negative values
4.94065645841247E-324 to
1.79769313486232e+308 for positive values.
Currency -922,337,203,685,477.5808 to
922,337,203,685,477.5807
Decimal +/- 79,228,162,514,264,337,593,543,950,335 if no decimal is use
+/- 7.9228162514264337593543950335 (28 decimal places).

Non-Numeric Data Types


Type Range of Values
String (fixed length) 1 to 65,400 characters
String (variable length) 0 to 2 billion characters
Date January 1, 100 to December 31, 9999
Boolean True or False
Object Any embedded object
Variant (numeric) Any value as large as double
Variant (text) Same as variable-length string

VBA - Constants


Constant is a named memory location used to hold a value that CANNOT be changed during the script execution. If a user tries to change a Constant value, the script execution ends up with an error. Constants are declared the same way the variables are declared.

Constant Naming Rules


  • You must use a letter as the first character.
  • You can't use a space, period (.), exclamation mark (!), or the characters @, &, $, # in the name.
  • Name can't exceed 255 characters in length.
  • You cannot use Visual Basic reserved keywords as variable name.

Syntax:-

Const constant_name As constant_type = constant_value

How to make operation in cells using VBA Buttons


Example:-

Private Sub Sum()
Range("A3").Value = Range("A1").Value + Range("A2").Value
End Sub

How to make operation in cells using VBA Buttons and variables


Example:-

Private Sub Sum()
a=Range("A1").Value
b=Range("A2").Value
c=a+b
Range("A3").value=c
End Sub