How to Create VBA Function to Return Array in Excel VBA?
Tags: tocreatevbafunctiont, vbamacros
Published: 2021-09-13 14:14:28

---
title: How to Create VBA Function to Return Array in Excel VBA?
published: true
date: 2021-09-08 09:51:03 UTC
tags: ToCreateVBAFunctionT,VBAMacros
canonical_url: https://geekexcel.com/how-to-create-vba-function-to-return-array-in-excel-vba/
---

In this article, you will learn  **how to create VBA Function to return an array in Excel VBA**. Let’s see them below!! Get the official version of  **MS Excel**  from the following link: [https://www.microsoft.com/en-in/microsoft-365/excel](https://www.microsoft.com/en-in/microsoft-365/excel)

## Array Function

Array functions are the functions that **return an array** when used and these functions are used with **CTRL+SHIFT+ENTER key** combinations, and this is why we prefer calling array function or formulas as **CSE function and formulas**.

## Creating a UDF array function in VBA

- Firstly, in the Excel Worksheet, you have to go to the  **Developer Tab.**
- Then, you need to ** ** select the  **Visual Basic**  option under the  **Code**  section.

![Select Visual Basic](https://geekexcel.com/wp-content/uploads/2021/09/Select-Visual-Basic-9-1024x180.png)<figcaption>Select Visual Basic</figcaption>

- Now, you have to  **copy and paste the code**  given below.

```
Function ThreeEven() As Integer()

'define array
Dim numbers(2) As Integer

'Assign values to array
numbers(0) = 0
numbers(1) = 2
numbers(2) = 4

'return values
ThreeEven = numbers

End Function

```

- You need to  **save the code**  by selecting it and then  **close the window**.

![](https://geekexcel.com/wp-content/uploads/2021/09/Save-the-Code-24.png)<figcaption>Save the Code</figcaption>

- Again, you have to go to the  **Excel Spreadsheet** , and click on the  **Developer Tab**.
- You need to choose the  **Macros option**  in the Code section.

![Choose Macro option](https://geekexcel.com/wp-content/uploads/2021/09/Choose-Macro-option-11-1024x184.png)<figcaption>Choose Macro option</figcaption>

- Now, you have to click on the  **F5** to **run the code**.
- Finally, you will receive the **output** in the Microsoft Excel.

![](https://geekexcel.com/wp-content/uploads/2021/09/Final-output-2.png)<figcaption>Final output</figcaption>

**NOTE:**

- In practice, you will not know how many cells you’re going to need. In that case, always select more cells than expected array length. The function will fill the cells with array and extra cells will show #N/A error.
- By default, this array function returns values in a horizontal array. If you try to select vertical cells, all cells will show the first value of array only.

## UDF Array Function with Arguments in Excel

- Firstly, in the Excel Worksheet, you have to go to the  **Developer Tab.**
- Then, you need to ** ** select the  **Visual Basic**  option under the  **Code**  section.

![Select Visual Basic](https://geekexcel.com/wp-content/uploads/2021/09/Select-Visual-Basic-9-1024x180.png)<figcaption>Select Visual Basic</figcaption>

- After that, you have to  **copy and paste the code**  given below.

```
Function CONCATDone(rng As Range) As Variant()
Dim resulArr() As Variant

'Create a collection
Dim col As New Collection

'Adding values to collection
On Error Resume Next
For Each v In rng
col.Add v
Next
On Error GoTo 0

'completing operation on each value and adding them to Array
ReDim resulArr(col.Count - 1, 0)
For i = 0 To col.Count - 1
resulArr(i, 0) = col(i + 1) & "-done"
Next

CONCATDone = resulArr

End Function

```

- You need to  **save the code**  by selecting it and then  **close the window**.

![](https://geekexcel.com/wp-content/uploads/2021/09/Save-the-Code1-2.png)<figcaption>Save the Code</figcaption>

- Again, you have to go to the  **Excel Spreadsheet** , and click on the  **Developer Tab**.
- You need to choose the  **Macros option**  in the Code section.

![Choose Macro option](https://geekexcel.com/wp-content/uploads/2021/09/Choose-Macro-option-11-1024x184.png)<figcaption>Choose Macro option</figcaption>

- Now, you have to click on the  **F5** to **run the code**.
- Finally, you will receive the **output** in the Microsoft Excel.

## Verdict

In the above article, you can learn the simple steps on **how to create VBA Function to return an array in Excel VBA**. Kindly, share your **feedback** in the below comment section. Thanks for visiting **[Geek Excel.](https://geekexcel.com/)** Keep Learning!

**Read Also:**

- **[How to Change Font Color in Microsoft Excel 2007?](https://geekexcel.com/how-to-change-font-color-in-microsoft-excel-2007/)**
- **[Excel Formulas to Count specific word in a range ~ Easy Tutorial!!](https://geekexcel.com/excel-formulas-to-count-specific-words-in-a-range/)**
- **[Excel Formulas to Get the Last Word of a Text String ~ Quick Method!!](https://geekexcel.com/excel-formulas-to-get-the-last-word-of-the-text-string/)**
- **[Count the Times a Word Appears in a Cell or a Range in Excel Office 365!!](https://geekexcel.com/count-the-times-a-word-appears-in-a-cell-or-a-range-in-excel-office-365/)**
- **[Excel Formulas to Remove the Last Word ~ Easy Tutorial!!](https://geekexcel.com/excel-formulas-to-remove-the-last-word-easy-tutorial/)**