How do I write a sub procedure in VBA?
|
Sub Procedures are similar to functions, however there are a few differences. Show
ExampleSub Area(x As Double, y As Double) MsgBox x * y End Sub Calling ProceduresTo invoke a Procedure somewhere in the script, you can make a call from a function. We will not be able to use the same way as that of a function as sub procedure WILL NOT return a value. Function findArea(Length As Double, Width As Variant) area Length, Width ' To Calculate Area 'area' sub proc is called End Function Now you will be able to call the function only but not the sub procedure as shown in the following screenshot.  The area is calculated and shown only in the Message box. The result cell displays ZERO as the area value is NOT returned from the function. In short, you cannot make a direct call to a sub procedure from the excel worksheet. Learn more about VBA commands What is Sub vs Function?In this article, we will discuss the key differences between a sub vs function. Both are sets of commands that are used to perform specific tasks in Microsoft Excel’s Visual Basic Application (VBA). A sub, also known as a subroutine or sub procedure, is a piece of code that is used to perform a specific task mentioned in the code but does not return any kind of value. On the other hand, a function, also known as a user-defined function procedure, is a piece of code that executes a specific task determined by the Excel user and returns a result. One of the applications of function procedures is to perform repetitive tasks. VBA Sub vs Function: Key DifferencesThe key differences between a sub and a function are as follows:
What is a VBA Sub?A sub can be described as a small program within the VBA Editor that performs a specific action in Excel. It is used to break large pieces of code into smaller parts that can be easily managed. The command is used to perform tasks that may involve updating a cell, performing a calculation, or importing a file into the Excel application. However, the outcome or results from the tasks are not returned to another sub. How to Write a Sub-Procedure in Excel?When writing a sub, the following rules should be followed:
When writing the sub, we use the “Sub” keyword and a name as the procedure name to declare the sub. The sub procedure should be followed by the task to be performed, written in VBA language. The sub should close with the statement End Sub. A sub should follow the following structure: Sub [Procedure name] (Parameters) [Tasks that need to be done] End Sub. Types of Sub Procedures in VBAA sub procedure can take two forms, i.e., private and public. The modifiers “private” and “public” allows users to use the subs differently. The private sub procedure can only be used in the current module. The public sub allows users to use the procedure in all modules present in the workbook. What is a VBA Function?A VBA function is similar to a sub procedure, only that the former can return a value whereas the latter cannot. It is a piece of code that can be called anywhere in the VBA Editor and eliminates the need to write the same lines of code every time. VBA allows users to use built-in functions, as well as user-defined functions. VBA functions work in the same way as formulas in Excel and can be used to perform repetitive tasks. Users can create custom functions for any actions and then access the functions from a cell or a direct reference from a cell. For example, a user can use functions to create a program that calculates the monthly interest payable on a motor vehicle loan. The function will include fields that accept the total loan amount and payment duration. After inputting the required values, the program will return the monthly interest value payable on the motor vehicle. How to Write a Custom Function?Follow the steps below to create a custom function:
Related ReadingsTo keep learning and developing your knowledge base, please explore the additional relevant resources below:
What is a sub procedure in VBA?A Sub procedure is a series of Visual Basic statements enclosed by the Sub and End Sub statements that performs actions but doesn't return a value. A Sub procedure can take arguments, such as constants, variables, or expressions that are passed by a calling procedure.
Can you put a sub in a function VBA?If you want Excel VBA to perform some actions, you can use a sub. Place a sub into a module (In the Visual Basic Editor, click Insert, Module). For example, the sub with name Area.
|
