How to Return a Value in VBA Function (Both Array and Non-Array Values)

In this article, I’ll show you how you can return a value from a User-Defined Function in VBA in Excel. I’ll show you how to return an array value, a non-array value, and both types of values together.

How to Return a Value in VBA Function (Quick View)

Function Remove_Left_Characters(Str As String, Num As Integer)

Remove_Left_Characters = Mid(Str, Num + 1, Len(Str) - Num)

End Function

⧭ Note:

To return a value from a function in VBA, you have to use the syntax:

And here we’ve used:

[Remove_Left_Characters is the name of the function.]

How to Return a Value in VBA Function: 3 Easy Methods

Here we’ve got a data set with the Names and IDs of some employees of a company called Jupyter Group.

Our objective today is to create a User-Defined Function that will remove a given number of characters from the left of the IDs.

1. Return a Non-Array Value in VBA Function

First, we’ll develop a function to return a non-array value.

The function will take one string as the input, remove a given number of characters from the left of the input, and then return the string.

The VBA code for this will be:

⧭ VBA Code:

Function Remove_Left_Characters(Str As String, Num As Integer)

Remove_Left_Characters = Mid(Str, Num + 1, Len(Str) - Num)

End Function

⧭ Output:

Enter this function in any cell in your worksheet with two inputs: a string and a number.

The function will remove the given number of characters from the left of the string.

Here we’ve entered this formula in cell D4:

It has removed 1 character from the left of the string S202022 of cell C4 and returned 202022.

⧭ Notes:

• The code created a function called Remove_Left_Characters.
• It takes two inputs: One string and one integer.
• Then it returns a new string by removing the given number of characters from the left of the given string.
• To return a value, it follows the syntax:

Here it is:

Read More: How to Use VBA Input Function in Excel

2. Return an Array Value in VBA Function

This time I’ll show you how you can return an array of values in a function in VBA.

Let’s develop a function that will take a range of cell references with strings, remove a given number of characters from the left of each string, and then return the new strings.

You can use the following VBA code for this purpose:

⧭ VBA Code:

Function Remove_Left_Characters(rng As Range, Num As Integer)

Dim Output As Variant
Output = rng

For i = 1 To UBound(Output, 1)
    For j = 1 To UBound(Output, 2)
        Output(i, j) = Mid(Output(i, j), Num + 1)
    Next j
Next i

Remove_Left_Characters = Output

End Function

⧭ Output:

It’ll take a range of cell references and a number as the input.

Then it will remove the given number of characters from the left of each cell of the range, and return an array of new strings.

Here in cell D4, we’ve entered the formula:

It has removed 1 character from the left of every string of the range C4:C13.

[This is an Array Formula. So don’t forget to press CTRL+SHIFT+ENTER unless you are in Office 365.]

⧭ Notes:

• The code created a function called Remove_Left_Characters again.
• It again takes two inputs: One range and one integer.
• First, it converts the range into an array called Output.
• Then it removes the given number of characters from the left of every string of the array called Output.
• Finally, the array called Output is assigned as the return value of the function by following the syntax:

Here it is:

Read More: How to Execute VBA Function Procedure in Excel

3. Return Both Types of Values in VBA Function

The first function takes a string as the input and returns another string by removing a given number of characters from the left of it.

The second function takes a range of cells with strings and returns an array by removing a given number of characters from the left of each string.

This time we’ll develop a function that takes both a single or a range of strings, and return a single value or an array of values as output.

You can use the following VBA code for this purpose:

⧭ VBA Code:

Function Remove_Left_Characters(Inpt, Num As Integer)

If VarType(Inpt) = 8204 Then

    Dim Output As Variant
    Output = Inpt

    For i = 1 To UBound(Output, 1)
        For j = 1 To UBound(Output, 2)
            Output(i, j) = Mid(Output(i, j), Num + 1)
        Next j
    Next i

     Remove_Left_Characters = Output

Else

    Remove_Left_Characters = Mid(Inpt, Num + 1)

End If

End Function

⧭ Output:

Same as the previous two methods, it takes two inputs.

But this time the first input can be either a single string or a range of strings.

Let’s examine with a single string first.

In cell D4, we’ve entered the formula:

It has returned the first character from the string in cell C4 and returned 202022.

Again, let’s examine the function with a range of cells.

This time, in cell D5, we’ve entered the formula:

[Array Formula. So press CTRL+SHIFT+ENTER unless you are in Office 365.]

This will remove the first character from the left of each string of the range C4:C13.

⧭ Notes:

• The name of the function remains the same, Remove_Left_Characters.
• It again takes two inputs: A range or a String and an integer.
• First, if the first argument is a range, then it converts the range into an array called Output.
• Then it removes the given number of characters from the left of every string of the array called Output.
• Finally, the array is assigned as the return value of the function by following the syntax:

Here it is:

• And if the first argument is not a range, then it removes the given number of characters from the left of the argument and then assigns it as the output of the function by the syntax:

Here it is:

Things to Remember

It’s better to keep separate sections for returning array values and non-array values in every function you develop in VBA. Because, in the case when you have to apply the function on a large number of cells, it’s better to use the Array Function than the Non-Array Function.

And when you want to build an Array Function for the users, first you have to convert the range given as input into an array. Then you have to make necessary changes to the array and return it.

Download Practice Workbook

Download this practice book to exercise the task while you are reading this article.

Conclusion

Using these methods you can return a single value or an array of values from a User-Defined Function in VBA. Do you have any questions? Feel free to ask us.

Further Readings

• Excel VBA Function to Return Multiple Values
• How to Make VBA Function with Arguments in Excel
• VBA Sub Vs Function in Excel
• Difference Between Subroutine and Function in Excel VBA
• How to Create Custom Function in Excel VBA
• How to Use VBA User Defined Function