How to Make VBA Function with Arguments in Excel

Get FREE Advanced Excel Exercises with Solutions!

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.


Make VBA Function with Arguments in Excel: 4 Examples

1. Creating VBA Function with Single Argument

First, 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. 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 1 kg 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.

Read More: How to Use VBA Input Function in Excel


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 a Sub procedure was used 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. We 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.

Read More: How to Use VBA User Defined Function


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 declare a variable, NumberX as an 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 call 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 create 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 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


Download Practice Workbook

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


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.


Related Articles

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Kawser Ahmed
Kawser Ahmed

Kawser Ahmed is a Microsoft Excel Expert, Udemy Course Instructor, Data Analyst, Finance professional, and Chief Editor of ExcelDemy. He is the founder and CEO of SOFTEKO (a software and content marketing company). He has a B.Sc in Electrical and Electronics Engineering. As a Udemy instructor, he offers 8 acclaimed Excel courses, one selected for Udemy Business. A devoted MS Excel enthusiast, Kawser has contributed over 200 articles and reviewed thousands more. His expertise extends to Data Analysis,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo