How to Make VBA Function with Arguments in Excel

Excel has a lot of built-in functions, but we can make our own functions according to our recruitment or applications. In this article, we’ll introduce how to make VBA function with different types of arguments with simple codes and vivid illustrations.


Download Practice Workbook

You can download the free Excel workbook from here and practice independently.

4 Examples to Make VBA Function with Arguments in Excel


1. Creating VBA Function with Single Argument

Firstly, we’ll learn how to create a user-defined function with a single argument. We’ll make a function to convert Kg to Gram. The function name is Kgtog.

Steps:

  • Insert the following codes in a module-
Option Explicit
Sub Kgtog()
Dim k As Double
k = ConvertKgtog(20)
Debug.Print k
End Sub
Function ConvertKgtog(CKG As Double) As Double
   ConvertKgtog = CKG * 1000
End Function
  • Then go back to your sheet.

How to Make VBA Function with Arguments

Code Breakdown:

  • First, we created a Sub procedure- Kgtog.
  • Then declared k as Double. And we’ll use this k to call the function.
  • In the second section of the code, we defined a function name- ConvertKgtog. And again declared a variable CKG as Double.
  • As 1Kg is equal to 1000g, so we multiplied CKG by 1000.

  • Now to convert 5kg to gram, insert the following formula using our function-
=ConvertKgtog(E4)
  • Then just hit the ENTER button to get the output.


2. Making Function with Multiple Arguments in Excel VBA

Now we’ll make a function that contains multiple arguments. This time, we’ll make a function to calculate the difference between two numbers named CalculateNum_Difference.

Steps:

  • In a new module type the following codes-
Option Explicit
Function CalculateNum_Difference(Number1 As Integer, Number2 As Integer) As Double
   CalculateNum_Difference = Number2 - Number1
End Function
Sub Number_Difference()
Dim Number1 As Integer
Dim Number2 As Integer
Dim Num_Diff As Double
Number1 = "5"
Number2 = "10"
Num_Diff = CalculateNum_Difference(Number1, Number2)
Debug.Print Num_Diff
End Sub
  • Next, go back to your sheet.

Making Function with Multiple Arguments in Excel VBA

Code Breakdown:

  • First, we defined a function named CalculateNum_Difference using the Function statement and declared two variables Number1 and Number2 as Integer within it.
  • Then used a Sub procedure to call the function- Number_Difference.
  • Later, declared the arguments of the functions Number1 and Number2 as Integer.
  • Finally, declared Num_Diff as Double which is used to arrange the function.

  • Insert the following formula in Cell E6 using the function-
=CalculateNum_Difference(E4,E5)
  • Hit the ENTER button to finish.

And see, it’s returning the difference between the two numbers.


3. Making VBA Function with Optional Argument

In this section, we’ll make a function with an optional argument. That means, if we skip the argument then it will choose a fixed value for the argument.

Steps:

  • Write the following codes in a new module-
Option Explicit
Function CalculateNum_Difference_Optional(Number1 As Integer, Optional Number2 As Integer) As Double
If Number2 = 0 Then Number2 = 100
   CalculateNum_Difference_Optional = Number2 - Number1
End Function
Sub Number_Difference_Optional()
Dim Number1 As Integer
Dim Number2 As Integer
Dim Num_Diff_Opt As Double
Number1 = "5"
Num_Diff_Opt = CalculateNum_Difference_Optional(Number1)
Debug.Print Num_Diff_Opt
End Sub
  • After that, go back to a sheet to apply the function.

Making VBA Function with Optional Argument

Code Breakdown:

  • This code will work like the previous code just for the second argument, we declared the Number2 variable with the Optional statement. And used the If statement to set the fixed value for the second argument- 100 when it is not selected.

  • In Cell E6, apply the following formula using our user-defined function-
=CalculateNum_Difference_Optional(E4)
  • Then hit the ENTER button.

As we didn’t set the second argument, it took the fixed number 100 as its second argument.


4. Creating VBA Function with Default Argument

We can set a default argument for a function, which means we’ll never select that argument, a default value will be chosen always.

Steps:

  • Insert the following formula in a new module-
Option Explicit
Sub Number_Difference_Default()
Dim NumberX As Integer
NumberX = CalculateNum_Difference_Default(Number1)
MsgBox NumberX
End Sub
Function CalculateNum_Difference_Default(Number1 As Integer, Optional Number2 As Integer = "100") As Double

CalculateNum_Difference_Default = Number2 - Number1

End Function
  • Now go to your sheet to apply the function.

Creating VBA Function with Default Argument

Code Breakdown:

  • First, we declared a Sub procedure- Number_Difference_Default.
  • Then declared a variable, NumberX as Integer which we’ll use to call the function.
  • In the second section of the codes, we created the function named CalculateNum_Difference_Default and declared two variables within it for the argument.

  • To try the function, insert the following formula in Cell E6
=CalculateNum_Difference_Default(E4)
  • Finally, just press enter to finish.

Have a look, we chose only one argument and the second argument is the default value- 100. So the formula subtracted 5 from 100.


How to Create a VBA Function Without Arguments

Now we’ll make a function that will need no argument. That means, we’ll just apply the function and it will always return a default value always.

Steps:

  • In a new module, type the following codes-
Function Return_Value() As Integer
   Return_Value = 100
End Function
  • Soon after, you will get the function available in the worksheets.

How to Create a Function Without Arguments

  • Insert the following formula using our function-
=Return_Value()
  • Hit the ENTER button and it will return 100 always.


How to Call a VBA Function from a Sub Procedure

Now we’ll learn how to call a Function from a Sub procedure. Here, we’ll make a function that whenever we’ll call it then it will bold the cell range E4:E45.

How to Call a Function from a Sub Procedure

Steps:

  • Insert the following codes in a new module-
Function mReturn_Value() As Range
   Set mReturn_Value = Range("E4:E8")
End Function
Sub Test_Return_Value()
Dim miRg As Range
Set miRg = mReturn_Value
miRg.Font.Bold = True
End Sub
  • After that, just run the codes.

Code Breakdown:

  • First, we created a function named, mReturn_Value and declared it as Range, which will select the specified range.
  • Then created a Sub procedure – Test_Return_Value to call the function.
  • Declared another variable miRg as Range.
  • Used the Set statement to connect with the function.
  • Finally, Font.Bold will make bold the cell range.

Now see, the range has got bold.


How to Use ByVal and ByRef Arguments in a VBA Function

While using VBA, it’s quite necessary to understand the use of ByVal and ByRef Keywords. Before applying them you should have the proper knowledge about them. In this section, we’ll show their application.

Steps:

  • Insert the following codes in a module-
Option Explicit
Sub Using_ByRef()
Dim grandtotal As Long
grandtotal = 1
Call Det(grandtotal)
End Sub
Sub Det(ByRef n As Long)
n = 100
End Sub
  • Then press F8 to see the output line by line.

We called the second Sub in the first section by using ByRef. ByRef always passes the original value so it’s returning 100.

How to Use the ByVal and ByRef Arguments in a Function

  • But have a look at these codes, we used ByVal here and ByVal always passes the value only not the reference or original one, then the value can be replaced from the calling procedure or method. So now it’s returning 1 instead of 100.
Option Explicit
Sub Using_ByVal()
Dim grandtotal As Long
grandtotal = 1
Call Det(grandtotal)
End Sub
Sub Det(ByVal n As Long)
n = 100
End Sub


Conclusion

That’s all for the article. I hope the above procedures will be good enough to make VBA function with arguments in Excel. Feel free to ask any questions in the comment section and give me feedback. Visit our site to explore more.

Kawser

Kawser

Hello! Welcome to my Excel blog! It took me some time to be a fan of Excel. But now I am a die-hard fan of MS Excel. I learn new ways of doing things with Excel and share them here. Not only how-to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned! You can checkout my courses at Udemy: https://www.udemy.com/user/exceldemy/

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo