Khan Muhammad Kawser
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.
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.
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.
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.
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.
- 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.
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.
- 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.
