## Learn how to return values depending on udf deployment (vba)

In this vba tutorial I am going to show you how to return values from an udf, depending on where you enter the udf. All udf examples in this post remove blanks but they differ on how values are returned.

**Example 1**

The values are returned horizontally and #N/A are returned when there are no more values to show.

**User defined function**

Function BasicUDF(Rng As Range) Dim Cell As Variant 'Create an array Dim temp() As Variant ReDim temp(0) 'Process every cell in Range For Each Cell In Rng 'Check if cell is empty If Cell <> "" Then 'Copy cell value to the last position in array temp(UBound(temp)) = Cell 'Increase array size with 1 ReDim Preserve temp(UBound(temp) + 1) End If Next Cell 'Remove the last value in array ReDim Preserve temp(UBound(temp) - 1) 'Return values BasicUDF = temp End Function

You can return values vertically by changing the last line to BasicUDF = Application.Transpose(temp). See animated gif below.

Values are transposed vertically.

**Example 2**

This user defined function returns values vertically and replaces #N/A with blanks.

Application.Caller.Rows.Count returns the number of rows from the cell range where you entered the udf. This makes it possible to add blanks when the udf is out of values.

**User defined function**

Function BasicUDF2(Rng As Range) Dim Cell As Variant Dim row As Single 'Create an array Dim temp() As Variant ReDim temp(0) 'Process every cell in Range For Each Cell In Rng 'Check if cell is empty If Cell <> "" Then 'Copy cell value to the last position in array temp(UBound(temp)) = Cell 'Increase array size with 1 ReDim Preserve temp(UBound(temp) + 1) End If Next Cell 'Add remaining blanks to array For row = UBound(temp) To Application.Caller.Rows.Count temp(UBound(temp)) = "" ReDim Preserve temp(UBound(temp) + 1) Next row 'Remove last blank ReDim Preserve temp(UBound(temp) - 1) 'Transpose temp array from horizontal to vertical and return values to sheet BasicUDF2 = Application.Transpose(temp) End Function

**Example 3**

This user defined function returns values in every cell, also if you entered the user defined function in multiple columns. The remaining cells are returned as blanks. See animated gif.

Values are transposed to fill the entire selection, #N/A are replaced with blank cells.

**User defined function**

Function BasicUDF3(Rng As Range) Dim Cell As Variant Dim row As Single, i As Single 'Create an array Dim temp() As Variant 'Dim array with same size as selection ReDim temp(Application.Caller.Columns.Count - 1, 0) i = 0 'Process every cell in Range For Each Cell In Rng 'Check if cell is empty If Cell <> "" Then 'Copy cell value to the last position in array temp(i, UBound(temp, 2)) = Cell i = i + 1 'Add a new row to the array If i = Application.Caller.Columns.Count Then i = 0 ReDim Preserve temp(UBound(temp, 1), UBound(temp, 2) + 1) End If End If Next Cell 'Process remaining cells in selection Do 'Remaining value are blanks temp(i, UBound(temp, 2)) = "" 'Count current column i = i + 1 'Check if current column is equal to the number of columns in selection If i = Application.Caller.Columns.Count Then 'Start over at column 1 (0) i = 0 'Add a new row in array ReDim Preserve temp(UBound(temp, 1), UBound(temp, 2) + 1) End If Loop Until UBound(temp, 2) > Application.Caller.Rows.Count - 1 'Remove last row in array ReDim Preserve temp(UBound(temp, 1), UBound(temp, 2) - 1) 'Return values BasicUDF3 = Application.Transpose(temp) End Function

**How to create an array formula**

- Select a cell range
- Type user defined function in formula bar
- Press and hold Ctrl + Shift
- Press Enter

**Where do I copy the vba code?**

- Open VB Editor or press Alt+ F11
- Click "Insert" on the menu
- Click "Module"
- Copy/Paste vba code to code module
- Return to excel

**Download excel *.xlsm file**

### Category: User defined functions udf

UPDATE: It is not possible to count conditionally formatted cells using vba as far as I know, I recommend you […]

Comments(26) Filed in category: Conditional formatting, Count values, Excel, User defined functions (udf), VBA

Robert Jr asks: Oscar, I am using the VBA code & FilterUniqueSort array to generate unique lists that drive Selection […]

Comments(17) Filed in category: Excel, Sort values, Unique distinct values, User defined functions (udf), VBA

This user defined function creates an unique list of words and their frequency in selected range. User defined function: =FreqWords(cell_range, […]

Comments(16) Filed in category: Count values, Excel, Frequency, User defined functions (udf), VBA

Sometimes you need to find formulas containing literals (hard coded values) in a workbook. I found this excellent UDF in […]

Comments(12) Filed in category: Excel, User defined functions (udf), VBA

This blog post describes how to create a list of unique distinct words from a cell range. Unique distinct words […]

Comments(11) Filed in category: Excel, Unique distinct values, User defined functions (udf), VBA

PRASHANT asks: i need to count unique number in a specific coloured cell for eg if there are coloured cells […]

Comments(10) Filed in category: Excel, Unique distinct values, User defined functions (udf), VBA

I received an email from one of my seven blog readers. In Excel, I have a column, say A, with some […]

Comments(7) Filed in category: Count values, Excel, User defined functions (udf)

The "Text to columns" feature in excel lets you split multiple values in a single cell using a delimiting character, […]

Comments(6) Filed in category: Excel, User defined functions (udf), VBA

Chrisa asks: Hi Oscar...this is a very interesting function and helped me a lot so far. My file though is […]

Comments(6) Filed in category: Excel, User defined functions (udf), VBA

I read a very interesting blog post about Using Custom Functions in Dynamic Ranges Gabhan Berry creates a user defined […]

Comments(5) Filed in category: Drop down lists, Excel, User defined functions (udf), VBA

### Category: Vba

This post describes how to copy values between sheets. I am using the invoice template sheet. This macro copies rows […]

Comments(40) Filed in category: Excel, VBA

This post describes how to copy selected rows using checkboxes. In a previous post I showed you how to add/remove […]

Comments(30) Filed in category: Check-boxes, Excel, VBA

UPDATE: It is not possible to count conditionally formatted cells using vba as far as I know, I recommend you […]

Comments(26) Filed in category: Conditional formatting, Count values, Excel, User defined functions (udf), VBA

Table of contents Missing numbers (array formula) Missing numbers (vba) Missing numbers (array formula) Question: I want to find missing […]

Comments(24) Filed in category: Excel, VBA

You can quickly run a macro using a drop down list. The drop down list may contain multiple macro names. […]

Comments(17) Filed in category: Drop down lists, Excel, VBA

Robert Jr asks: Oscar, I am using the VBA code & FilterUniqueSort array to generate unique lists that drive Selection […]

Comments(17) Filed in category: Excel, Sort values, Unique distinct values, User defined functions (udf), VBA

Let me show you how to create self adjusting columns in excel with a few lines of vba code. Example, […]

Comments(16) Filed in category: Excel, VBA

Rahul asks: i want to know that when we create a vlookup sheet, and in the name column we enter […]

Comments(16) Filed in category: Excel, VBA

This user defined function creates an unique list of words and their frequency in selected range. User defined function: =FreqWords(cell_range, […]

Comments(16) Filed in category: Count values, Excel, Frequency, User defined functions (udf), VBA

Question: hi all, thanks for the great formula/array formula. it works great. lately, i noticed that the array formula will […]

Comments(14) Filed in category: Advanced filter, Excel, Unique distinct values, VBA

### 4 Responses to “Learn how to return values depending on udf deployment (vba)”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

Here is an extra-credit challenge for your readers. Once this article makes sense to them, they should try to figure out how this shorter UDF (which does what your basicUDF3 does) works...

Rick Rothstein (MVP - Excel),

I am going to change this post, it is clear as mud! ;-)

Thanks for your contribution!

Hi. I am trying to make it very fast for a UDF to return its original value under certain conditions. I created a test Excel file that has close to 60,000 cells that will either return the current time or return the original caller value.

If I don't access the caller value it will calculate in 6 seconds. (About 4 seconds if I am not in debug mode). But if I access the caller value, it takes 4 to 5 times longer.

I was hoping there was another way to return the caller value that would be faster. If it's not an easy answer, I was wondering if I bought a higher version of ADX with the source code, are there any opportunities to adjust the source code to accomplish this? Speed is really everything for what I am trying to accomplish.

I am using an XLL/XLA C# project. I am open to other methods to accomplish the goal.

I can send you my test project if needed. I would have attached, but I don't know how to attach to this post.

Thanks for your help.

Dwipayan Das,

Contact Charles Williams

https://fastexcel.wordpress.com/