Excel VBA Array in Office 365 ~ One/Two Dimensional Arrays!!
Tags: excelvbaarrayinoffic, vbamacros
Published: 2020-12-24 03:46:20

---
title: Excel VBA Array in Office 365 ~ One/Two Dimensional Arrays!!
published: true
date: 2020-12-23 12:33:49 UTC
tags: ExcelVBAArrayinOffic,VBAMacros
canonical_url: https://geekexcel.com/excel-vba-array-in-office-365-one-two-dimensional-arrays/
---

As we know, an array is a collection of items of the same data type. If a single variable contains more than one value, then it is known as an array variable. Here, we are going to see how to create and use Excel [VBA](https://geekexcel.com/excel-office-365-vba-overview-definition-and-examples/) Array in Office 365. There are two types of array available in Excel, they are **VBA One dimensional Array** , and **VBA Two dimensional Array**. Let’s see one by one.
## VBA One Dimensional Array:

### One Dimensional Array:

It exists in a **single row** or a **single column** in Microsoft Excel. Refer to the below example image.

![Example for 1-D Array](https://eadn-wc04-371788.nxedge.io/cdn/wp-content/uploads/2020/12/EXCEL_AQLeYTtUmH.png)<figcaption id="caption-attachment-20299">Example for 1-D Array</figcaption>

### Array Declaration:

You can declare an [array](https://geekexcel.com/simple-shortcut-to-enter-array-formula-in-microsoft-excel-365/) in the following three types.

- **Dim array1()**
- **Dim array3(2 to 20)**
- **array4 = Array(“blue”, “red”, “black”)**

### Array Assignation:

The following syntax are the examples to know how to assign value to a VBA array.

**Dim array7(1 to 5) As String**

**array7(1) = “alice”**

**array7(2) = “loves”**

**array7(3) = “to”**

**array7(4) = “read”**

**array7(5) = “books”**

### Example:

- You need to [**open** the **Visual Basic Editor**](https://geekexcel.com/shortcut-to-open-vba-editor-in-microsoft-excel-365/) by clicking the shortcut [**ALT** + **F11**](https://geekexcel.com/shortcut-to-open-vba-editor-in-microsoft-excel-365/) Keys.
- Then, on the **Insert Tab, select** the **Module** option from the menu.

![Insert Tab](https://eadn-wc04-371788.nxedge.io/cdn/wp-content/uploads/2020/12/Insert-Tab-20.png)<figcaption id="caption-attachment-19831">Insert Tab</figcaption>

- Then, you need to **enter the below given VBA code** in the code window.

VBA Code for OneDimArray

Sub OneDimensionalExample()  
Dim array7(1 To 5) As String  
array7(1) = “alice”  
array7(2) = “loves”  
array7(3) = “to”  
array7(4) = “read”  
array7(5) = “books”  
MsgBox “the value of array7(5) is ” & array7(5)  
End Sub

- Finally, you need to **save the code.**

![Enter the Code](https://eadn-wc04-371788.nxedge.io/cdn/wp-content/uploads/2020/12/Enter-the-Code-1.png)<figcaption id="caption-attachment-20228">Enter the Code</figcaption>

- Now, you need to **close** this **window.**
- Then, go back to the workbook and **run** the **macro** by **clicking** the **Macros** option under the **Developer Tab.**

![Click Macros option](https://eadn-wc04-371788.nxedge.io/cdn/wp-content/uploads/2020/12/Click-Macros-option.png)<figcaption id="caption-attachment-19833">Click Macros option</figcaption>

- Then, it will display the **Macro Dialog Box** , where you need to **select** the **macro name** and **hit** the **Run** button.

![Macro Dialog Box](https://eadn-wc04-371788.nxedge.io/cdn/wp-content/uploads/2020/12/Macro-Dialog-Box-2.png)<figcaption id="caption-attachment-20229">Macro Dialog Box</figcaption>

- Then, it will display the output as shown in the below screenshot.

![Output](https://eadn-wc04-371788.nxedge.io/cdn/wp-content/uploads/2020/12/Output-23.png)<figcaption id="caption-attachment-20230">Output</figcaption>

## VBA Two Dimensional Array:

### Two Dimensional Array:

- The two-dimensional array is **arranged in rows and columns**. Each row is separated by a semicolon ( **;** ) and each column is separated by a comma ( **,** ).
- For example, arr = **{“Alice”, “Lisa”, “Luis”; 10, 89, 50}**
- Refer to the below example.

![2-D Array Example](https://eadn-wc04-371788.nxedge.io/cdn/wp-content/uploads/2020/12/EXCEL_az6sb200cd.png)<figcaption id="caption-attachment-20307">2-D Array Example</figcaption>

### Array Declaration:

You can declare a two-dimensional array in the below-given ways.

To declare an array with two rows and two columns, use the below syntax.

- **Dim array1(2,2) as String**
- **Dim array1(1 to 2, 1 to 2) as String**

### Array Assignation:

This section will show how to assign value to the VBA two-dimensional array.

**Dim array7(1 to 2, 1 to 2) as String**

**Array7(1,1) = “Welcome”**

**Array7(1,2) = “to”**

**Array7(2,1) = “Geek”**

**Array7(2,2) = “Excel”**

### Example:

- You need to [**open** the **Visual Basic Editor**](https://geekexcel.com/shortcut-to-open-vba-editor-in-microsoft-excel-365/) by clicking the shortcut [**ALT** + **F11**](https://geekexcel.com/shortcut-to-open-vba-editor-in-microsoft-excel-365/) Keys.
- Then, on the **Insert Tab, select** the **Module** option from the menu.

![Insert Tab](https://eadn-wc04-371788.nxedge.io/cdn/wp-content/uploads/2020/12/Insert-Tab-20.png)<figcaption id="caption-attachment-19831">Insert Tab</figcaption>

- Then, you need to **enter the below given VBA code** in the code window.

VBA Code for arrays

Sub TwoDimensionalExample()  
Dim array7(1 To 2, 1 To 2) As String  
array7(1, 1) = “Welcome”  
array7(1, 2) = “to”  
array7(2, 1) = “Geek”  
array7(2, 2) = “Excel”  
MsgBox “The value of array7(2,2) is ” & array7(2, 2)  
End Sub

- Finally, you need to **save the code.**

![Save the Code](https://eadn-wc04-371788.nxedge.io/cdn/wp-content/uploads/2020/12/1-30.png)<figcaption id="caption-attachment-20232">Save the Code</figcaption>

- Now, you need to **close** this **window.**
- Then, go back to the workbook and **run** the **macro** by **clicking** the **Macros** option under the **Developer Tab.**

![Click Macros option](https://eadn-wc04-371788.nxedge.io/cdn/wp-content/uploads/2020/12/Click-Macros-option.png)<figcaption id="caption-attachment-19833">Click Macros option</figcaption>

- Then, it will display the **Macro Dialog Box** , where you need to **select** the **macro name** and **hit** the **Run** button.

![Macro Dialog Box](https://eadn-wc04-371788.nxedge.io/cdn/wp-content/uploads/2020/12/2-20.png)<figcaption id="caption-attachment-20233">Macro Dialog Box</figcaption>

- Then, it will display the output as shown in the below screenshot.

![Result](https://eadn-wc04-371788.nxedge.io/cdn/wp-content/uploads/2020/12/Result-10.png)<figcaption id="caption-attachment-20234">Result</figcaption>

## Closure:

In this article, we have illustrated how to create and use **Excel VBA Array in Office 365. ** The two types of arrays with their syntax and examples help you understand this concept well. Kindly, give your **feedback/suggestions** in the below comment section. Thanks for visiting **[Geek Excel.](https://geekexcel.com/)** Keep Learning!

### Keep Reading:

- [Excel VBA Date And Time Function in Office 365 ~ Easy Tutorial!!](https://geekexcel.com/excel-vba-date-and-time-function/)
- [Excel Office 365 VBA Variables & Data Types – A Complete Guide!!](https://geekexcel.com/excel-office-365-vba-variables-data-types-a-complete-guide/)