Variables
Just as in other programming languages. You can use variables. You do not have to declare variables. Visual Basic will automatically create storage for a variable the first time you use it.
Automatically created variables are of type Variant and can contain any type of data---strings, numbers, boolen values, errors, arrays or objects.
For example, the following statement assigns the value 34 to the variable x.
x = 34
Now you may be ask when with out declaration we can use a variable the what is the need for declare it.
So the answer for safety reason we declare variables and its data types.
Declaration habits of variables is help us when we write a long program.
Using Dim
Declaring a variable is to tell the computer to reserve space in memory for later use. As a result your VBA code run much more efficiently. To declare a variable use a Dim (short for Dimension) statement.
Syntax:
Dim var_name as data_type
Data Types
When a data type is declared it means that you have define the kind of value that may be stored within the memory allocated for a variable.
There are many types of data types. which are following
Common Math Operator Used in VBA
Addition +
Subtraction -
Multiplication *
Division /
Exponential ^
Few Examples
Sub example()
Dim myint as integer
myint = 5
end sub
sub example()
Dim marks, c,d
Set marks = Range("A1:B10")
d=0
For Each c in marks
if c.value < 40 then
d = d+1
end if
Next c
end sub
Constants
Values that don't change should be set up as constants rather than variables. Tis prevents them being changed by accident.
The line Const pi as double = 3.14159
will create the constant pi that can then be used in an expression such as
rtangle = pi/2
Arrays
An array is a collection of variables that have a common name. For example, we refer a specific variable in the array by using the array name and an index number.
Arrays contain a sequence of variables. Each one is called an element of the array and is identified by an index number.
Dim can be used to declare an array without giving it any values.
a) Declaring arrays
Before you can use an array, you must declare it like a variable in VBA with a Dim or a Public statement. By default, VBA takes 0 as the lower index. You need to specify the number of elements in the array by specifying the first index number and the last index number. The following example shows how to declare the number of days in a week.
Dim NumDays(1 to 7) As Integer
When you declare an array, you can choose to specify only the upper index.
As VBA assumes that 0 is the lower index, the following code both declare the same 100 element array:
Dim MyArray(0 to 99) As Integer
Dim MyArray(99) As Integer
If you want VBA to assume that 1 is the lower index for your arrays, your need to add the following code in the Declarations section of your module:
Option Base 1
We assume the the lower index is 1 from now. The array code above, will now represent a 99 element array
b) Assigning values to elements in an array
Suppose we have an one-dimensional array called MyArray and we wish to assign the value 5 to the 3rd index slot, use the following
MyArray(3) = 5
c) Multidimensional arrays
We have look at one-dimensional arrays. Incredibly, VBA allow you to have as many as 60 dimensions. The following example declares a 50-integer array with two dimensions:
Dim MyArray(1 to 5, 1 to 5) As Integer
You can think of this array as occupying a 5-x-5 matrix. If we want to assign the value 5 to the entry in the 2nd row and 5th column of the array, use
MyArray(2, 5) = 5
In other word, the integer value 5 is stored in (row 2, column 5) of the matrix MyArray
For a three-dimensional array, think of it as a cube. Visualizing an array of more than three dimensions is more difficult. Well you don’t worry about the fourth dimension and beyond as I bet you’ll never use it.
d) Dynamic arrays
So far, we have been declaring arrays with a fixed number of elements. If you wish to declare an array without specifying its size, use
Dim MyArray() As Integer
This is how to create dynamic arrays. Notice a dynamic array has a blank set of parentheses:
To resize the array, you must use the ReDim statement to tell VBA how many elements the array has. Most of the time, the number of elements in the array is determined while your code is running. The ReDim statement can be used any number of times, to change the array’s size as often as you need.
For example if you need to resize MyArray to a 10 x 10 matrix then you
ReDim MyArray(10,10) As Integer
Now you have change the number of elements in a dynamic array. When you redimension an array by using ReDim, you clear all the values currently stored in the array elements.
Using Set
Although Most methods return values some. You cannot assign an object to a variable using an equals sign. Instead, you should use the Set statement, for example,
Set rangeoffset = Range("C1:C5").offset(1,1)
No comments:
Post a Comment