Trending September 2023 # How To Use Vba Redim Preserve? # Suggested October 2023 # Top 13 Popular

You are reading the article How To Use Vba Redim Preserve? updated in September 2023 on the website Lanphuongmhbrtower.com. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested October 2023 How To Use Vba Redim Preserve?

Excel VBA ReDim

We can adjust the number of elements in an array through VBA Redim. Redim is not a function, it is Dynamic memory allocation program through Redim statement. When we do use Redim, a specific amount of memory is kept aside to store the data.

How to Use VBA ReDim Statement?

We will discuss how to use VBA ReDim Statement by using some examples.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

You can download this VBA ReDim Excel Template here – VBA ReDim Excel Template

VBA ReDim – Example #1

When we use Dim for an array, we cannot change the value of data store in an array. But when we use Redim, we can change the stored value in an array. This process of changing the array is called re-dimensioning the array.

Suppose we have an array A of 4 cell value then it will be defined with;

Dim A(3) As Double

As we can see all the cells has “0” as stored value. Now suppose if we Redim the Dim A with 3 cell data and store the value in it then it will look like as below.

ReDim B(2) As Double

B(0) = 1; B(1) = 3, B(2) = -6

It will look like as;

Now if we want to add cells in it, we can do it. Then it will automatically assign the “0” to that cells. Now for implementing ReDim, press Alt + F11 to open VBA Window. And from the Insert menu select Module option to open a new window as shown below.

Now open Subcategory and add any name. As we are using ReDim so we have named as UsingReDim.

Code:

Sub

UsingReDim()

End Sub

Now before defining ReDim, first define any Integer. Here we have defined it as “A” with array length 3. And store some values in a created array of 3 cells as shown below.

Code:

Sub

UsingReDim()

Dim

A(2)

As Integer

ReDim

A(2) A(0) = 1 A(1) = 2 A(2) = 3

End Sub

Now printing the values stored in Integer A we need to create message box with the help of command MsgBox as shown below.

Code:

Sub

UsingReDim()

Dim

A(2)

As Integer

A(0) = 1 A(1) = 2 A(2) = 3 MsgBox A(0) MsgBox A(1) MsgBox A(2)

End Sub

Now we will use ReDim and it will store more than defined characters in the array. For this use ReDim for defined Integer A. Firstly, we will remove the defined cell count from Dim. The use of Redim function is as follows:

ReDim A(2) is used for A(0), A(1), A(2) cells array.

ReDim A(4) is used for A(0), A(1), A(2), A(3), A(4) cells with two extra dummy cells.

Code:

Sub

UsingReDim()

Dim

A()

As Integer

ReDim

A(2) A(0) = 1 A(1) = 2 A(2) = 3

ReDim

A(4) MsgBox A(0) MsgBox A(1) MsgBox A(2)

End Sub

Which is because ReDim actually has only 3 values but it did not carry stored values under defined integers. Using Redim only does not store any values and carry forward the previously stored values into it. To apply the same we need to preserve the values of defined integer A, with the help of ReDim. By using Preserve in VBA, we can store the value in ReDim. For this, we will add Preserve after ReDim in VBA Codeas shown below.

Code:

Sub

UsingReDim()

Dim

A()

As Integer

ReDim

A(2) A(0) = 1 A(1) = 2 A(2) = 3

ReDim Preserve

A(4) MsgBox A(0) MsgBox A(1) MsgBox A(2)

End Sub

Now run the complete code again. We will see 3 message boxes with all the stored values in it as show below.

Now, you must be wondering why we used A(4) with ReDim Preserve?

If we try to see the values stored in 4th and 5th position of ReDim array, then we need to insert a message box for these 2 positions as well.

Code:

Sub

UsingReDim()

Dim

A()

As Integer

ReDim

A(3) A(0) = 1 A(1) = 2 A(2) = 3

ReDim Preserve

A(4) MsgBox A(0) MsgBox A(1) MsgBox A(2) MsgBox A(3) MsgBox A(4)

End Sub

As we have added message boxed for 4th and 5th position without storing any value. Now run the code again.

As we can see in the above screenshots, for a 4th and 5th position we got message boxes but the value is showing as”0”. Which means explains the above statement. If there is no value defined then ReDim Preserve will automatically consider and fill “0” in the rest of the cells.

VBA ReDim – Example #2

Now let’s see how to use ReDim Statement with String instead of Integer.

Code:

Sub

UsingReDim()

Dim

A()

As String

ReDim

A(3) A(0) = "Customer" A(1) = "Product" A(2) = "Product Id"

ReDim Preserve

A(4) MsgBox A(0) MsgBox A(1) MsgBox A(2) MsgBox A(3) MsgBox A(4)

End Sub

Once done, then run the complete code using F5 key or manually as shown below.

As we can see in the above screenshot we are getting all the defined and stored characters in first 3 message boxes. And in 4th and 5th box we are getting nothing. As ReDim stores no value if not defined. For Integers it will reflect as “0” but with String, it will show only blank messages.

Pros Of Excel VBA ReDim

VBA ReDim allows us to redefine the dimension values.

We can define as many as dimensional arrays with no or zero value and store the values later.

ReDim meaning Re-Dimensioning gives use the allowances of adding any numbers of data arrays without increasing the size of stored data.

Things To Remember

Always remember to add Preserve after ReDim in VBA, so that it will store the previously used dimensions.

If a dimension is defined as String, then any position left blank in assigning values, will carry blank and once we print the same with the help of message box will give blank message box as well.

If we have defined the dimensions Dim with 3 characters or cell values, then in ReDim we can use any number of dimensions carrying already defined and stored values in Dim.