Sunday, 7 October 2012

What Is the difference between Dim And Redim Statements in VBA

How to use Dim and Redim Statements in VBA
Dim Statement:
Variables declared with Dim at the script level are available to all procedures within the script. At the procedure level, variables are available only within the procedure

Dim VarName[([subscripts])][, VarName[([subscripts])]] .. 

You can also use the Dim statement with empty parentheses to declare a dynamic array. After declaring a dynamic array, use the ReDim statement within a procedure to define the number of dimensions and elements in the array. If you try to redeclare a dimension for an array variable whose size was explicitly specified in a Dim statement, an error occurs.

Dim x as Integer                                    'Declaring a Variable  or Array
Dim y(10) as String

ReDim Statement :
Declares dynamic-array variables, and allocates or reallocates storage space at procedure level.

ReDim [Preserve] VarName(subscripts) [, VarName(subscripts)] . . .

The ReDim statement is used to size or resize a dynamic array that has already been formally declared using a Private, Public, or Dim statement with empty parentheses (without dimension subscripts). You can use the ReDim statement repeatedly to change the number of elements and dimensions in an array.

Dim x As String
Dim y ()  As String

ReDim y(20)                             ‘ReSizing or Reinitialization of Array Size /Variable Value
ReDim x =" TPR"

If you use the Preserve keyword, you can resize only the last array dimension, and you can't change the number of dimensions at all. For example, if your array has only one dimension, you can resize that dimension because it is the last and only dimension. However, if your array has two or more dimensions, you can change the size of only the last dimension and still preserve the contents of the array.

The following example shows how you can increase the size of the last dimension of a dynamic array without erasing any existing data contained in the array.

ReDim X(20, 20, 20)
. . .
ReDim Preserve X(20, 20, 25)

Caution : 
If you make an array smaller than it was originally, data in the eliminated elements is lost.

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

No comments:

Post a Comment

Hi User, Thank You for visiting My Blog. Please post your open Feedback only related to this Blog Posts. Please note that I cannot respond to the Anonymous Comments.

Subscribe to Blog Posts by Email

ExcelKingdom-Popular Posts