In this post I will teach you how to return a result from a custom VBA function. You should have used in-built Excel functions such as Sum function, CountIf function etc. These functions can be used either in the Excel sheets or in the VBA projects. Like that we can also create our own custom VBA functions according to various requirements. These functions also can be used either in Excel sheets or VBA projects. So now let’s look at how to return a result from such a custom VBA function.
Let’s create a simple VBA function which doesn’t have any parameters. Suppose that we need to output a certain name when we call a function. We can create a very simple function for that as follows.
|
Function MyName() As String MyName = "John Hurt" End Function |
In the first line we have used the term“As String”. It is the data type of the result returned by the MyName function. However the data type of the value returned by the function is optional. So we can rewrite the above function like this as well.
|
Function MyName() MyName = "John Hurt" End Function |
In the above examples we used the name of the function again inside the same function to return the value. So that’s how you can output the result from the function. When you obtain the final result in the function then you need to write the name of the function again and assign that final result to it. In the above examples you see only one line inside the function. But in real life it will be very different. You will have lots of lines inside the function. But no matter how long your code is, you need to use the same technique to return the result from the function. So a real life function will look more like this.
|
Function MyName() As String ----------------------------------- Lots of lines here ----------------------------------- MyName = "John Hurt" End Function |
Now we learnt how to output a result from a custom VBA function. But do you know how to use this kind of function inside a subroutine/macro? It is simple. You can call the function in one line. Following example subroutine shows you how to use the above created function inside a VBA Project.
|
Sub Test() Dim x As String x = MyName() MsgBox x End Sub |
This is what we get if we run the above code.
Also we can use this custom VBA function inside the Excel sheets as well.
Custom VBA function we created above has no parameters. Now let’s create another simple function which has some parameters. Assume that we want to input two values to a function and want to get the product of those two numbers. Yes we don’t need to create a function to do this. But I selected this example because our goal here is to understand the concept of outputting results from the functions. Once you understand it then you can apply it to any complex situation. So this is how you can create the function for the above requirement.
|
Function Multiplication(x As Double, y As Double) As Double Multiplication = x * y End Function |
Then we can use this function in subroutines as follows.
|
Sub Example() Dim x As Double x = Multiplication(2, 42) MsgBox x End Sub |
Program will show the result in a message box.
