WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Blog
  • Free Video Tutorials
  • Courses
  • All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Learn VBA » VBA Arrays Function in Excel

VBA Arrays Function in Excel

By Jeevan A YJeevan A Y | Reviewed By Dheeraj VaidyaDheeraj Vaidya, CFA, FRM

In VBA arrays are used to define the group of objects together, there are nine different array functions in VBA and they are ARRAY, ERASE, FILTER, ISARRAY, JOIN, LBOUND, REDIM, SPLIT and UBOUND, all of these are inbuilt functions for array in VBA, Array function gives us the value for the given argument.

Excel VBA Array Function

Array function is a collection of values in a single variable. We can supply an array to a Subroutine in VBA, Functions, and Properties. VBA Arrays are one of the often used techniques to store more than one value in the variable.

Examples of Excel VBA Array Function

Instead of declaring many variables and store the values, we can use the Excel VBA array to store the value in a single variable itself. For example, look at the below example

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

Code:

Sub Array_Ex()
   
  Dim x As Integer
  Dim y As Integer
  x = 1
  y = 2
  Range("A1").Value = x
  Range("A2").Value = y
End Sub

In the above example, I have declared two variables called x & y.  X holds 1 as the value, and Y holds 2 as the value.

Now, look at the Excel VBA array function example with a single variable.

Code:

Sub Array_Ex()

  Dim x(1 To 2) As Integer
  Range("A1").Value = x(1)
  Range("A2").Value = x(2)

End Sub

Now, if you run this VBA code, we would have values in cell A1 & A2.

VBA Array Example 1

Array variables returned the result as zero. This is because we have just declared variables as two, but we have not assigned any values to those variables. So we need to assign values to these variables.

Popular Course in this category
Sale
VBA Training (3 Courses, 12+ Projects)
4.6 (247 ratings)
3 Courses | 12 Hands-on Projects | 43+ Hours | Full Lifetime Access | Certificate of Completion
View Course

Code:

Sub Array_Ex()

  Dim x(1 To 2) As Integer
  x(1) = 10
  x(2) = 20
  Range("A1").Value = x(1)
  Range("A2").Value = x(2)

End Sub

Now run the code to get results.

VBA Array Example 1-1

Before we enter the values of array variables to cells, we need to assign the value to those declared array variables like we assigned the variables x(1) = 10 & x(2) = 20.

Example #1 – Insert Serial Numbers Using Static Array

Let’s look at the example of using a static array to insert serial numbers. This is much like the previous one.

Code:

Sub StaticArray_Ex()

 Dim x(1 To 5) As Integer

 x(1) = 10
 x(2) = 20
 x(3) = 30
 x(4) = 40
 x(5) = 50

 Range("A1").Value = x(1)
 Range("A2").Value = x(2)
 Range("A3").Value = x(3)
 Range("A4").Value = x(4)
 Range("A5").Value = x(5)

End Sub

Now run this code to insert serial numbers.

VBA Array Example 2

Example #2 – Insert Serial Numbers Using Dynamic Array

Now we will see the second type of array i.e., a dynamic array

Code:

Sub DynamicArray_Ex()

 Dim x() As Integer
 ReDim x(5)
 x(1) = 10
 x(2) = 20
 x(3) = 30
 x(4) = 40
 x(5) = 50
 Range("A1").Value = x(1)
 Range("A2").Value = x(2)
 Range("A3").Value = x(3)
 Range("A4").Value = x(4)
 Range("A5").Value = x(5)

End Sub

Now run this code to get the result of serial numbers. We get the same result as the previous one.

VBA Array Example 2-1

If you notice we have not supplied the length of the array while declaring the variable, instead, we have assigned the last value of the VBA array using the VBA Redim function. Redim holds the last value of the array to be passed.

Example #3 – Create a Function Insert Month Names Using Array

We have seen how to work with arrays in VBA. Now we will see how to work with an array to create a VBA function in Excel. The function is nothing but a user-defined function in VBA. Apart from using built-in functions, excel VBA allows us to create our own functions as well.

Code:

Function List_Of_Months()

 List_Of_Months = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun",

 "Jul", "Aug", "Sep", "Oct", "Nov", "Dec")

End Function

The below code will create a function that can insert months to our excel sheet.

Copy and paste the below code to your module.

Example 3

Now save this code and close the VBA Editor. After closing the VBA editor, go to the worksheet and type the formula we have just created, and you should see the formula called List_Of_Months in your worksheet.

Example 3-1

Open the formula and hit enter. We will get the first-month name i.e., Jan.

Example 3-2

If you insert the formula one more time, still we would get Jan only, not the next month, Feb.  So first select 12 columns in one row.

Example 3-3

Now open the formula in D1 cell.

Example 3-4

Since we have created the formula with the array, we need to close the formulas as an array formula only. So hold Ctrl + Shift + Enter. We would have all 12-month names.

Example 3-5

Things to Remember

  • There are two more array types available i.e., two-dimensional array & multi-dimensional array.
  • Arrays start from 0, not from 1. Zero means the first row and the first column.
  • The array is a big topic. You need to understand it to advance to the next level.
  • The array variable will be one that holds much data each time it advances to the next level.
  • Redim is used to store the last length of the array in a dynamic array type.

Recommended Articles

This has been a guide to VBA Array Function in Excel. Here we Insert serial numbers using static array and dynamic array in Excel VBA along with examples and download template. Below are some useful excel articles related to VBA –

  • VBA FileCopy
  • And Function in VBA
  • VBA StrComp Function
  • Assign Data Types in VBA
7 Shares
Share
Tweet
Share
VBA Training (3 Courses, 12+ Projects)
  • 3 Courses
  • 12 Hands-on Projects
  • 43+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download VBA Array Excel Template

Special Offer - VBA Training Course (6 courses, 35+ hours video) View More