Excel Functions Tutorials

- VBA
- VBA Tutorial
- VBA Functions in Excel
- VBA ArrayList
- VBA Arrays Function in Excel
- VBA Activate Sheet
- VBA Break
- VBA Borders
- VBA Boolean
- VBA ByRef
- VBA Code
- VBA Const
- VBA Class Modules
- VBA Count
- VBA COUNTA
- VBA COUNTIF
- VBA Comment Block
- VBA Match Function
- VBA LEFT Function
- VBA Right Function
- VBA Like
- VBA LEN
- VBA Long
- VBA Today
- VBA Now
- VBA Time Function
- VBA Timer
- VBA TimeValue
- VBA Weekday
- VBA ROUND
- VBA RoundUp
- VBA Random Numbers
- VBA ReDIM Function
- VBA Rename Sheet
- VBA Protect Sheet
- VBA Remove Duplicates
- VBA Concatenate
- Copy Paste in VBA
- VBA Paste
- VBA Print
- VBA Date Function
- VBA DateDiff Function
- VBA DateAdd Function
- VBA DatePart
- VBA Data Type
- VBA Dictionary
- VBA Debug Print
- VBA Charts
- VBA CDBL
- VBA CSTR
- VBA Chr
- VBA ChDir
- VBA ENUM
- VBA RegEx
- VBA Mid Function
- VBA Max
- VBA Find Function
- VBA Find and Replace
- VBA Trim Function
- VBA Text
- VBA OFFSET Function
- VBA MOD Function
- VBA Split Function
- VBA UBound Function
- VBA Union
- VBA Transpose
- VBA INT
- VBA InStr
- VBA INSTRREV
- VBA Intersect
- VBA Integer
- VBA DIR Function
- VBA OR Function
- VBA AND
- VBA Operators
- VBA Not Equal
- VBA Worksheet Function
- VBA Workbook
- VBA ThisWorkbook
- VBA Worksheets
- VBA Write Text File
- VBA Hyperlinks
- VBA String Functions
- VBA StrComp
- VBA StrConv
- VBA Sub
- VBA Call Sub
- VBA End
- VBA Wait
- VBA Option Explicit
- VBA SubString
- VBA Subscript Out of Range
- VBA IIF
- VBA IF OR
- VBA IFERROR
- VBA On Error
- VBA OverFlow Error
- VBA 1004 Error
- VBA Error Handling
- VBA Type
- VBA Type Mismatch Error
- VBA IsEmpty
- VBA ISNULL
- VBA Input Box
- VBA MsgBox
- VBA Text Box
- VBA Format
- VBA Format Number
- VBA Conditional Formatting
- VBA AutoFill
- VBA AutoFilter
- VBA Color Index
- VBA Font Color
- VBA Clear Contents
- VBA Collection
- VBA Paste Special
- VBA Progress Bar
- VBA GoTo
- VBA Userform
- VBA Close UserForm
- User Defined Function in Excel VBA
- VBA Outlook
- VBA JOIN
- VBA LCase
- VBA UCase
- VBA Select Case
- VBA Select Cell
- VBA Selection
- VBA Active Cell
- VBA Set
- VBA Sleep
- VBA Pause
- VBA Range Objects
- VBA Range Cells
- VBA UsedRange
- VBA Loop
- VBA Break For Loop
- VBA For Each Loop
- VBA For Next Loop
- VBA Do Until Loop
- VBA File Copy
- VBA FileDialog
- VBA FileSystemObject (FSO)
- VBA Cells
- VBA Last Row
- VBA Insert Row
- VBA Hide Columns
- VBA New Line
- VBA GetOpenFilename
- VBA GetObject
- VBA Delete File
- VBA Delete Row
- VBA Insert Columns
- VBA Delete Column
- VBA Val
- VBA Value
- VBA Variant
- VBA Variable Declaration
- VBA Global Variables
- VBA Pivot Table
- VBA Refresh Pivot Table
- VBA PowerPoint
- VBA Send Email from Excel

- Financial Functions in Excel (17+)
- Logical Functions in Excel (15+)
- TEXT Functions in Excel (29+)
- Lookup Reference in Excel (44+)
- Maths Functions in Excel (52+)
- Date and Time Function in Excel (22+)
- Statistical Function in Excel (50+)
- Information Functions in Excel (5+)
- Excel Charts (48+)
- Excel Tools (98+)
- Excel Tips (178+)

Related Courses

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

The Excel, VBA array function is a collection of values in a single variable. We can supply an array to a Sub Procedure, 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

**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.

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.

4.6 (247 ratings)

**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.

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.

#### Example #2 – Insert Serial Numbers Using Dynamic Array

Now we will see the second type of array i.e. 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.

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 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.

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.

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

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.

Now open the formula in D1 cell.

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

### Things to Remember

- There are two more array types are 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.
- Array variable will be one that holds much data each time it advances to the next level.
- Redim is the function used to store the last length of the array in dynamic array type.

### Recommended Articles

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

- Excel VBA File Copy
- Activate Sheet in Excel VBA
- And Function in VBA
- What is VBA FileDialog?
- VBA StrConv Function
- Find and Replace using VBA code
- VBA StrComp Function
- Assign Data Types in VBA

- 3 Courses
- 12 Hands-on Projects
- 43+ Hours
- Full Lifetime Access
- Certificate of Completion