--- description: Array is a memory location capable of storing more than one value. The values must all be of the same data type. Let's say you want to store a list of your favourite beverages in a single variable, you can use an array to do that. title: Excel VBA Arrays: Types & How to Use with Example image: https://www.guru99.com/images/excel-vba-arrays.png --- [Skip to content](#main) **⚡ Smart Summary** Excel VBA Arrays store a group of related values of the same data type under one name, accessed by an index. This page explains static and dynamic arrays, one, two, and multi-dimensional arrays, declaration syntax, and a worked beverage-loading example. * 📦 **Array Concept:** A single named memory location that holds many values of the same data type, separated by an index. * 🎯 **Advantages:** Arrays group related data, reduce the number of variables, and make retrieving and sorting data faster. * 📌 **Static Array:** Declared with a fixed size, for example Dim ArrayMonth(12) As String, when the element count is known. * 🔄 **Dynamic Array:** Declared empty with Dim ArrayMonth() and sized later using ReDim when the element count is unknown. * 📐 **Dimensions:** An array uses one, two, or more indexes to model single lists, tables, or multi-level data. * 🧪 **Worked Example:** A command button loads four beverage names from an array into worksheet cells using Sheet1.Cells. [ Read More ](javascript:void%280%29;) ![Excel VBA Arrays]() ## What is VBA Array? An array is defined as a memory location capable of storing more than one value. The values must all be of the same data type. Let’s say you want to store a list of your favourite beverages in a single variable, you can use VBA array to do that. By using an array, you can refer to the related values by the same name. You can use an index or subscript to tell them apart. The individual values are referred as the elements of the Excel VBA array. They are contiguous from index 0 through the highest index value. This tutorial assumes you are using [Microsoft Excel](https://www.guru99.com/excel-tutorials.html) version 2013\. The knowledge still applies to other versions of Microsoft Excel as well. ## What are Advantages of arrays? Before writing array code, it helps to understand why arrays are worth using. The following are some of the benefits offered by VBA array function 1. Group logically related data together – let’s say you want to store a list of students. You can use a single array variable that has separate locations for student categories i.e. kinder garden, primary, secondary, high school, etc. 2. Arrays make it easy to write maintainable code. For the same logically related data, it allows you to define a single variable, instead of defining more than one variable. 3. Better performance – once an array has been defined, it is faster to retrieve, sort, and modify data. ## Types of Arrays in VBA VBA supports two types of arrays namely; * **Static** – These types of arrays have a fixed pre-determined number of elements that can be stored. One cannot change the size of the data type of a Static Array. These are useful when you want to work with known entities such as the number of days in a week, gender, etc.**For Example**: Dim ArrayMonth(12) As String * **Dynamic** – These types of arrays do not have a fixed pre-determined number of elements that can be stored. These are useful when working with entities that you cannot predetermine the number.**For Example**: Dim ArrayMonth() As Variant Once you know which type fits your data, the next step is the declaration syntax for each. **Syntax to declare arrays** **Static arrays** The syntax for declaring **STATIC** arrays is as follows: Dim arrayName (n) as datatype **HERE,** | Code | Action | | -------------------------- | --------------------------------------------------------------------------------------------------------------------------------------------- | | Dim arrayName (n) datatype | It declares an array variable called arrayName with a size of n and datatype. Size refers to the number of elements that the array can store. | **Dynamic arrays** The syntax for declaring **DYNAMIC** arrays is as follows: Dim arrayName() as datatype ReDim arrayName(4) **HERE,** | Code | Action | | ------------------------- | ---------------------------------------------------------------------------------------- | | Dim arrayName () datatype | It declares an array variable called arrayName without specifying the number of elements | | ReDim arrayName(4) | It specifies the array size after the array has been defined. | **Array Dimensions** An array can be one dimension, two dimensions or multidimensional. * **One dimension**: In this dimension, the array uses only one index. For example, a number of people of each age. * **Two dimensions**: In this dimension, the array uses two indexes. For example, a number of students in each class. It requires number of classes and student number in each class * **Multi-dimension**: In this dimension, the array uses more than two indexes. For example, temperatures during the daytime. ( 30, 40, 20). ### RELATED ARTICLES * [VBA Arithmetic Operators: Addition, Subtraction, Multiplication ](https://www.guru99.com/vba-arithmetic-operators.html "VBA Arithmetic Operators: Addition, Subtraction, Multiplication") * [VBA String Manipulation Functions & Operators ](https://www.guru99.com/vba-string-operators.html "VBA String Manipulation Functions & Operators") * [VBA Web Scraping to Excel ](https://www.guru99.com/data-scraping-vba.html "VBA Web Scraping to Excel") * [Top 22 VBA Interview Questions and Answers (2026) ](https://www.guru99.com/vba-interview-questions.html "Top 22 VBA Interview Questions and Answers (2026)") ## How to use Array in Excel VBA With the theory covered, we can put an array to work in a real workbook. We will create a simple application. This application populates an Excel sheet with data from an array variable. In this VBA Array example, we are going to do following things. * Create a new Microsoft Excel workbook and save it as Excel Macro-Enabled Workbook (\*.xlsm) * Add a command button to the workbook * Set the name and caption properties of the command button * Programming the VBA that populates the Excel sheet Let do this exercise step by step, **Step 1 – Create a new workbook** 1. Open Microsoft Excel 2. Save the new workbook as VBA Arrays.xlsm **Step 2 – Add a command button** **Note:** This section assumes you are familiar with the process of creating an interface in excel. If you are not familiar, read the tutorial [VBA Excel Form Control & ActiveX Control](https://www.guru99.com/vba-operators.html). It will show you how to create the interface 1. Add a command button to the sheet [](https://www.guru99.com/images/vba/062416%5F1125%5FVBAArrays1.jpg) 1. Set the name property to cmdLoadBeverages 2. Set the caption property to Load Beverages Your GUI should now be as follows [](https://www.guru99.com/images/vba/062416%5F1125%5FVBAArrays2.jpg) **Step 3 – Save the file** 1. Click on save as button 2. Choose Excel Macro-Enabled Workbook (\*.xlsm) as shown in the image below [](https://www.guru99.com/images/vba/062416%5F1125%5FVBAArrays3.jpg) **Step 4 – Write the code** We will now write the code for our application 1. Right click on Load Beverages button and select view code 2. Add the following code to the click event of cmdLoadBeverages Private Sub cmdLoadBeverages_Click() Dim Drinks(1 To 4) As String Drinks(1) = "Pepsi" Drinks(2) = "Coke" Drinks(3) = "Fanta" Drinks(4) = "Juice" Sheet1.Cells(1, 1).Value = "My Favorite Beverages" Sheet1.Cells(2, 1).Value = Drinks(1) Sheet1.Cells(3, 1).Value = Drinks(2) Sheet1.Cells(4, 1).Value = Drinks(3) Sheet1.Cells(5, 1).Value = Drinks(4) End Sub **HERE,** | Code | Action | | -------------------------------------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------- | | Dim Drinks(1 To 4) As String | It declares an array variable called Drinks. The first array index is 1 and the last array index is 4. | | Drinks(1) = “Pepsi” | Assigns the value Pepsi to the first array element. The other similar code does the same for the other elements in the array. | | Sheet1.Cells(1, 1).Value = “My Favorite Beverages” | Writes the value My Favorite Beverages in cell address A1\. Sheet1 makes reference to the sheet, and Cells(1,1) makes reference to row number 1 and column 1 (B) | | Sheet1.Cells(2, 1).Value = Drinks(1) | Writes the value of the array element with index 1 to row number two of column 1 | ## Testing our application With the code in place, the final step is to run the button and confirm the array fills the sheet. Select the developer tab and ensure that the Design mode button is “off.” The indicator is, it will have a white background and not a coloured (greenish) background. (See image below) [](https://www.guru99.com/images/vba/062416%5F1125%5FVBAArrays4.png) Click on Load Beverages button You will get the following results [](https://www.guru99.com/images/vba/062416%5F1125%5FVBAArrays5.jpg) Download Excel containing above code [Download the above Excel Code](https://drive.google.com/uc?export=download&id=1puRIDGzfX8MsqDyQC44wPV8kUp2RCf5Z) ### RELATED ARTICLES * [VBA Arithmetic Operators: Addition, Subtraction, Multiplication ](https://www.guru99.com/vba-arithmetic-operators.html "VBA Arithmetic Operators: Addition, Subtraction, Multiplication") * [VBA String Manipulation Functions & Operators ](https://www.guru99.com/vba-string-operators.html "VBA String Manipulation Functions & Operators") * [VBA Web Scraping to Excel ](https://www.guru99.com/data-scraping-vba.html "VBA Web Scraping to Excel") * [Top 22 VBA Interview Questions and Answers (2026) ](https://www.guru99.com/vba-interview-questions.html "Top 22 VBA Interview Questions and Answers (2026)") ## FAQs 📊 What is the difference between a static and a dynamic array in VBA? A static array has a fixed size set at declaration, such as Dim Arr(12). A dynamic array is declared empty with Dim Arr() and resized later using ReDim when the number of elements is not known in advance. 🔢 What does the ReDim statement do in a VBA array? ReDim sets or changes the size of a dynamic array after it is declared. Adding the Preserve keyword, as in ReDim Preserve Arr(10), keeps the existing values while resizing; without Preserve, all stored values are cleared. 📏 How do I find the size of an array in VBA? Use the LBound and UBound functions. LBound returns the lowest index and UBound returns the highest index. The number of elements equals UBound(Arr) minus LBound(Arr) plus one, which works for both static and dynamic arrays. 🤖 Can AI generate VBA array code from a plain description? Yes. AI assistants such as Copilot generate array declarations and loops from a plain request, for example load ten product names into a column. The developer pastes the code into the editor and tests it on a copy of the workbook. 🧠 Can AI detect an out-of-bounds array error in VBA? Yes. AI assistants read the “Subscript out of range” error and the code, then point to the index that exceeds the array bounds and suggest a fix, such as adjusting the ReDim size or the loop counter. The developer applies and retests it. #### Summarize this post with: ChatGPT Perplexity Grok Google AI **Stay Updated on AI** **Get Weekly AI Skills, Trends, Actionable Advice.** ##### Sign up for the newsletter Subscribe for Free You have successfully subscribed. Please check your inbox. ![AI-Newsletter]() Chosen by over **350,000+** professionals [Scroll to top ](#wrapper)Scroll to top × Toggle Menu Close Search for: Search ```json {"@context":"https://schema.org","@graph":[{"@type":"Organization","@id":"https://www.guru99.com/#organization","name":"Guru99","sameAs":["https://www.facebook.com/Guru99Official","https://twitter.com/guru99com"],"logo":{"@type":"ImageObject","@id":"https://www.guru99.com/#logo","url":"https://www.guru99.com/images/guru99-logo-v1-150x59.png","contentUrl":"https://www.guru99.com/images/guru99-logo-v1-150x59.png","caption":"Guru99","inLanguage":"en-US"}},{"@type":"WebSite","@id":"https://www.guru99.com/#website","url":"https://www.guru99.com","name":"Guru99","publisher":{"@id":"https://www.guru99.com/#organization"},"inLanguage":"en-US"},{"@type":"ImageObject","@id":"https://www.guru99.com/images/excel-vba-arrays.png","url":"https://www.guru99.com/images/excel-vba-arrays.png","width":"700","height":"250","caption":"Excel VBA Arrays","inLanguage":"en-US"},{"@type":"BreadcrumbList","@id":"https://www.guru99.com/vba-arrays.html#breadcrumb","itemListElement":[{"@type":"ListItem","position":"1","item":{"@id":"https://www.guru99.com","name":"Home"}},{"@type":"ListItem","position":"2","item":{"@id":"https://www.guru99.com/vba","name":"VBA"}},{"@type":"ListItem","position":"3","item":{"@id":"https://www.guru99.com/vba-arrays.html","name":"Excel VBA Arrays: Types & How to Use with Example"}}]},{"@type":"WebPage","@id":"https://www.guru99.com/vba-arrays.html#webpage","url":"https://www.guru99.com/vba-arrays.html","name":"Excel VBA Arrays: Types & How to Use with Example","dateModified":"2026-07-15T16:55:43+05:30","isPartOf":{"@id":"https://www.guru99.com/#website"},"primaryImageOfPage":{"@id":"https://www.guru99.com/images/excel-vba-arrays.png"},"inLanguage":"en-US","breadcrumb":{"@id":"https://www.guru99.com/vba-arrays.html#breadcrumb"}},{"@type":"Person","@id":"https://www.guru99.com/author/charlotte","name":"Charlotte Miller","description":"I am Charlotte Miller, an Excel VBA Developer with over a decade of first-hand experience.","url":"https://www.guru99.com/author/charlotte","image":{"@type":"ImageObject","@id":"https://www.guru99.com/images/charlotte-miller-author.png","url":"https://www.guru99.com/images/charlotte-miller-author.png","caption":"Charlotte Miller","inLanguage":"en-US"},"worksFor":{"@id":"https://www.guru99.com/#organization"}},{"articleSection":"VBA","headline":"Excel VBA Arrays: Types & How to Use with Example","description":"Array is a memory location capable of storing more than one value. The values must all be of the same data type. Let's say you want to store a list of your favourite beverages in a single variable, you can use an array to do that.","keywords":"vba","speakable":{"@type":"SpeakableSpecification","cssSelector":[".entry-title",".summary"]},"@type":"Article","author":{"@id":"https://www.guru99.com/author/charlotte","name":"Charlotte Miller"},"dateModified":"2026-07-15T16:55:43+05:30","image":{"@id":"https://www.guru99.com/images/excel-vba-arrays.png"},"copyrightYear":"2026","name":"Excel VBA Arrays: Types & How to Use with Example","subjectOf":[{"@type":"HowTo","name":"How to use Array in Excel VBA","description":"We will create a simple application. This application populates an Excel sheet with data from an array variable. In this VBA Array example, we are going to do following things.","step":[{"@type":"HowToStep","name":"Step 1) Create a new workbook","text":"In the first step, Open Microsoft Excel","url":"https://www.guru99.com/vba-arrays.html#step1"},{"@type":"HowToStep","name":"Step 2) Add a command button","text":"In This section assumes you are familiar with the process of creating an interface in excel. If you are not familiar, read the tutorial VBA Excel Form Control & ActiveX Control. It will show you how to create the interface","image":{"@type":"ImageObject","url":"https://cdn.guru99.com/images/vba/062416_1125_VBAArrays1.jpg"},"url":"https://www.guru99.com/vba-arrays.html#step2"},{"@type":"HowToStep","name":"Step 3) Save the file","text":"Now Click on save as button","image":{"@type":"ImageObject","url":"https://cdn.guru99.com/images/vba/062416_1125_VBAArrays3.jpg"},"url":"https://www.guru99.com/vba-arrays.html#step3"},{"@type":"HowToStep","name":"Step 4) Write the code","text":"Now Right click on Load Beverages button and select view code","url":"https://www.guru99.com/vba-arrays.html#step4"}]},{"@type":"FAQPage","mainEntity":[{"@type":"Question","name":"What is the difference between a static and a dynamic array in VBA?","acceptedAnswer":{"@type":"Answer","text":"A static array has a fixed size set at declaration, such as Dim Arr(12). A dynamic array is declared empty with Dim Arr() and resized later using ReDim when the number of elements is not known in advance."}},{"@type":"Question","name":"What does the ReDim statement do in a VBA array?","acceptedAnswer":{"@type":"Answer","text":"ReDim sets or changes the size of a dynamic array after it is declared. Adding the Preserve keyword, as in ReDim Preserve Arr(10), keeps the existing values while resizing; without Preserve, all stored values are cleared."}},{"@type":"Question","name":"How do I find the size of an array in VBA?","acceptedAnswer":{"@type":"Answer","text":"Use the LBound and UBound functions. LBound returns the lowest index and UBound returns the highest index. The number of elements equals UBound(Arr) minus LBound(Arr) plus one, which works for both static and dynamic arrays."}},{"@type":"Question","name":"Can AI generate VBA array code from a plain description?","acceptedAnswer":{"@type":"Answer","text":"Yes. AI assistants such as Copilot generate array declarations and loops from a plain request, for example load ten product names into a column. The developer pastes the code into the editor and tests it on a copy of the workbook."}},{"@type":"Question","name":"Can AI detect an out-of-bounds array error in VBA?","acceptedAnswer":{"@type":"Answer","text":"Yes. AI assistants read the Subscript out of range error and the code, then point to the index that exceeds the array bounds and suggest a fix, such as adjusting the ReDim size or the loop counter. The developer applies and retests it."}}]}],"@id":"https://www.guru99.com/vba-arrays.html#schema-30529","isPartOf":{"@id":"https://www.guru99.com/vba-arrays.html#webpage"},"publisher":{"@id":"https://www.guru99.com/#organization"},"inLanguage":"en-US","mainEntityOfPage":{"@id":"https://www.guru99.com/vba-arrays.html#webpage"}}]} ```