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

Return Value in a Function in VBA

Note:

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

Function Name=Return Value

And here we’ve used:

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

[Remove_Left_Characters is the name of the function.]


Download Practice Workbook

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


How to Return a Value in VBA Function

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

Data Set to Return a Value in VBA Function

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

VBA Code to Return a Value in VBA 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:

=Remove_Left_Characters(C4,1)

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:
Function Name = Return Value

Here it is:

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

Similar Readings:


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

VBA Code to Return a Value in VBA 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 from each cell of the range, and return an array of new strings.

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

=Remove_Left_Characters(C4:C13,1)

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

Calling the Function to Return a Value in VBA Function

[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:
Function Name=Return Object

Here it is:

Remove_Left_Characters = Output

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:

=Remove_Left_Characters(C4,1)

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:

=Remove_Left_Characters(C4:C13,1)

[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:
Function Name=Return Object

Here it is:

Remove_Left_Characters = Output
  • 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:
Function Name=Return Value

Here it is:

Remove_Left_Characters = Mid(Inpt, Num + 1)

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.


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

Rifat Hassan

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo