How to Upload an Array Without a Name Vba

In this Article

• What is a VBA Array Variable?
• Static Arrays
  • Array Indexes
• Dynamic Arrays
• Variant Arrays
• Declare Module & Public Arrays
  • Declaring a Public Array
• Initialize Arrays
  • Array Function
  • Populate Array with Loop
• Re-initialize Arrays
  • Using ReDim
  • Using ReDim Preserve

This tutorial volition demonstrate how to Declare (Dim), Create, and Initialize Array Variables in VBA

What is a VBA Array Variable?

A VBA array variable tin can be thought of every bit a group of variables, stored under the aforementioned name and having the aforementioned data type. An array can store text, numbers, or objects.

You refer to an element in an array using its alphabetize number.

You can declare an array variable in the same fashion as yous would declare any other variable by using the Dim, Static, Public or Individual keyword.

Static Arrays

There are 2 types of arrays – Static and Dynamic. A Static array is alleged with it size specified when y'all initially declare the array. It is also called a Stock-still Array.

The in a higher place array is alleged using the Dim argument at a Procedure or Module level, and the size of the array is 5 as we accept not declared the LBound value of the assortment.

No, that'southward not a typo! The Array size is 5, despite entering iv into the array. This is because array indexes automatically start at zero.

Array Indexes

Assortment indexes automatically begin at cipher unless Option Base ane is alleged at the acme of your lawmaking module.

If Pick Base of operations i is declared then arrays volition automatically start at 1.

However, I find declaring variables in such a way to be problematic. Code reviewers may not be aware that arrays starting time at zero or of the Option Base 1 declaration

Instead, I prefer to explicitly declare the start and end positions of arrays:

Dim intA ( 2 to v ) every bit integer

Notice that when y'all exercise so, you tin can start the array at whatever number (not just 1 or 0).

Dynamic Arrays

A Dynamic Array variable is an array whose size can be changed at runtime. You declare dynamic variables without a size.

You can apply the ReDim statement to specify the size of the Assortment after the array has been created.

You can resize a dynamic array at any time. Nonetheless, when using the ReDim Statement, all existing values are erased. To preserve existing assortment values, use ReDim Preserve instead.

Y'all can declare a dynamic array at a Process, Module or Global level, but you can only use the ReDim statement within a Procedure.

Variant Arrays

Variant arrays are Dynamic arrays that are easier to piece of work with.

Detect that you don't demand to specify the data type (it is assumed to be variant) or the array size.

As we'll run into below, you can initialize variant arrays using the Array Function (no need to resize arrays first)!

Declare Module & Public Arrays

Equally shown above, arrays tin be declared inside procedures, for use within that procedure:

Sub StaticArray ( ) 'declare the array with an LBound value of 1 and an UBound value of iv Dim IntA ( 1 to 4 ) as Integer Cease Sub

Simply they tin can also be declared at the Module or Global level.

Option Explicit 'declare the array with an LBound value of one and an UBound value of 4 Dim IntA ( 1 to four ) as Integer Sub StaticArray ( ) Cease Sub

In this example, the array variable tin be chosen anywhere inside this code module. Instead, you lot tin declare a public assortment that can be used throughout your VBA Project (encounter next section).

VBA Coding Made Easy

Stop searching for VBA code online. Learn more virtually AutoMacro - A VBA Code Architect that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users!

Acquire More!!

Declaring a Public Assortment

You declare a Public Static assortment as you would declare a Public Variable.

Public strNames ( 3 ) equally String

This declaration would demand to go at the tiptop of your module, below Option Explicit.  It could then be used throughout your VBA project in whatsoever module or process.

If y'all declare the Assortment at the acme of the module, but with the Dim keyword, then the utilize of that assortment is restricted to that individual module.  Trying to utilize the Array in a separate module would result in an error.

Initialize Arrays

You lot can assign values to a static assortment in the following mode.

Sub StaticArray ( ) 'declare the array with an LBound value of 1 and an UBound value of iv Dim IntA ( ane to 4 ) as Integer 'initialise the assortment IntA ( one ) = x IntA ( two ) = 20 IntA ( 3 ) = 30 IntA ( iv ) = 40 'show the result of position 2 of the assortment in the immediate window Debug . Print IntA ( two ) Stop Sub

If you run the above procedure,  the value of xx would be displayed in the immediate window.

Y'all can also assign the values to a Dynamic Assortment in the same fashion

Sub DynamicArray ( ) 'declare a dynamic array only omitting the spring values Dim IntA ( ) as Integer 'initialise the array ReDim IntA ( i to 4 ) IntA ( 1 ) = 10 IntA ( ii ) = twenty IntA ( 3 ) = thirty IntA ( 4 ) = twoscore 'prove the result of position 2 of the array in the immediate window Debug . Print IntA ( two ) End Sub

Array Function

However, with the variant array only, you can use the Array Part which may be easier than using the standard method.

'populate the array intA ( ) = Array ( 10 , 20 , 30 , 40 )

VBA Programming | Code Generator does work for you lot!

Populate Array with Loop

You can too populate arrays past looping through a range of cells in Excel

ane 2 3 4 5 6 7 eight nine 10 11 12 thirteen 14 15 xvi 17

Sub TestDynamicArrayFromExcel ( ) 'declare the array Dim strNames ( ) As String 'declare an integer to count the rows in a range Dim n Equally Integer 'declare an integer for the loop Dim i As Integer 'count the rows in a the range northward = Range ( "A1" , Range ( "A1" ) . End ( xlDown ) ) . Rows . Count 'redim the array to the amount of rows in the range. ReDim strNames ( due north ) For i = 0 To due north strNames ( i ) = Range ( "A1" ) . Offset ( i + 1 , 0 ) Next i 'show the values in the array MsgBox Join ( strNames ( ) ) Cease Sub

Re-initialize Arrays

You lot can re-initialize your array at any stage of your code, but you volition and so lose the original value contained in that position in your array.

Sub StaticArray ( ) 'declare the array with an LBound value of 1 and an UBound value of 4 Dim IntA ( one to 4 ) as Integer 'initialise the assortment IntA ( 1 ) = 10 IntA ( two ) = 20 IntA ( three ) = thirty IntA ( 4 ) = 40 'show the result of position ii of the array in the firsthand window Debug . Impress IntA ( ii ) 'initialize the array again intA ( 2 )= 200 Debug . Impress IntA ( 2 ) End Sub

In the example above, the Static assortment will keep all the values, except the value in position 2 – that value will modify to 200.

Using ReDim

If y'all are using a Dynamic Array, the ReDim argument is used to prepare the size of your Array.  You can use the ReDim statement later in your lawmaking to modify the size of the Assortment equally many times as you need to.   The line of code below will re-initialize the intA Array to accept a size of 2  (Recall – an Assortment index begins at 0!)

So the code including the ReDim statement would look like the example below.

ane 2 three 4 5 6 7 8 9 10 11 12 13 14 15 xvi 17

Sub TestDynamicArray ( ) 'declare the array Dim intA ( ) As Integer ReDim intA ( 2 ) 'populate the array with numbers intA ( 0 ) = 2 intA ( 1 ) = 5 intA ( 2 ) = nine 'show the number in position one Debug . Print intA ( 1 ) 'redim the array to alter the size ReDim intA ( 3 ) intA ( 0 ) = half dozen intA ( ane ) = 8 'show the number in position 1 this fourth dimension Debug . Print intA ( i ) End Sub

If  y'all run the to a higher place procedure,  the value of 5 would be displayed in the immediate window, and then a value of 8 would exist displayed once we have resized the array using ReDim and repopulated information technology.  However, as we take not populated IntA(2), and we did not apply Re-Dim Preserve, the value in that position in the array volition exist removed and both position 3 and 4 in the array will be nothing.

Using ReDim Preserve

If nosotros utilize ReDim Preserve, it will keep the original values contained in the array.

ane 2 3 4 five vi 7 8 9 ten 11 12 13 14 15 xvi 17

Sub TestDynamicArray ( ) 'declare the array Dim intA ( ) Every bit Integer ReDim intA ( 2 ) 'populate the array with numbers intA ( 0 ) = 2 intA ( 1 ) = 5 intA ( 2 ) = 9 'show the number in position 2 Debug . Print intA ( two ) 'redim the assortment ReDim Preserve intA ( 3 ) intA ( 0 ) = 6 intA ( 1 ) = viii 'show the number in position 2 again Debug . Print intA ( 2 ) End Sub

In both message boxes displayed above, the number 9 will announced as the ReDim Preserve statement kept that value in that position.

munroeforrout.blogspot.com

Source: https://www.automateexcel.com/vba/declare-dim-create-initialize-array/

Share this post