A sub performs a task but does not return a value. A function returns a value of the tasks performed. Subs can be recalled from anywhere in the program and in multiple types. Functions are called by a variable.
Both of those sections are for writing code, but a function must return a value. For instance, if you had a program that required repeated execution of a challenging mathematical procedure, you could create a function that contained the challenging mathematical code, and then call the function whenever you needed to perform the calculation. Hope this helped not sure if I explained it well.
Explanation of Functions, Private Subs, and Public Subs in Excel VBA
What is a sub procedure in VBA?
A small program known as a “sub”—short for “subroutine” or “sub procedure”—performs a specific task, such as retrieving data or executing a program. This might entail changing a cell’s contents, obtaining information from another application, or adding a program to Excel. Subs may reduce redundant code or simplify particular actions. The program’s sub procedures are flexible and easy to use because you can call them from anywhere. There are two types of subs:
What is VBA?
Visual Basic for Applications is a domain-specific scripting language. Coding languages known as domain-specific languages are tailored to a particular program or class of programs. VBA runs on Microsoft applications, particularly Excel. In any Microsoft Office program, macros can be created and modified using VBA. Press Alt+F11 in the application you want to modify to open the VBA interface. By doing so, you can create and modify your macros in the code window.
What is a function in VBA?
A function is another type of VBA program. A function, however, has the capacity to return results, in contrast to a sub. This ability enables it to finish repetitive tasks like automating a particular computer process or calculating the monthly payable interest on a car. There are many built-in functions in VBA, including “sum” and “average,” and you can also make your own functions.
Parameters for writing a sub in VBA
Use the keyword “sub” at the beginning and end of the code when writing a sub procedure. When you name the sub procedure, it should:
List the parameters and the task you want the sub to complete after selecting a name. By doing this, the sub is finished and can carry out its assignment.
Parameters for writing a function in VBA
Writing a function is similar to writing a sub. Use the word “function” as the first word in the code’s name, just as you would for a sub procedure.
In order for a function to return a specific value, it also requires a set of variables. Put your chosen variables in parentheses and add any necessary parameters to assign a set of variables. Put the word “function” at the end of the statement to complete writing the function.
Sub vs. function in VBA
Given that both subs and functions are dynamic pieces of code that enable you to perform specific tasks, they are quite similar. Each one needs a keyword and a program name, and they all adhere to a similar coding structure.
However, there are some differences between these two techniques. Here are the main differences between subs and functions:
Returning value
The ability of functions to return values as opposed to subs to do so is the biggest distinction between the two. A variable that the procedure sends back to the user is known as a return value. The amount you owe would be a return value, for instance, if you wanted a function to figure out the interest on a loan. There are four main types of return value:
Using the ByRef or ByVal tools, which draw data from a variable in a different program or function, a sub can simulate the effects of a return value. As a result, a sub can provide information from a secondary source.
Calling subs and functions
You can specify the timing of the activation of these procedures when you have more than one sub or function. You can accomplish this by adding a call keyword or by starting a new line with the subroutine name. The execution of a function depends on a particular variable, whereas you can call sub from anywhere in your program. A function is not readily visible in the “Macros” section of the VBA code window as opposed to subs. You can use subs to call functions because they are simpler to call.
Formulas
Functions have the advantage of being directly usable on an Excel worksheet. They function like formulas, and the formula tools let you interact with them. Excel allows you to use sub procedures, but not directly on worksheets. Subs cannot function like formulas because they do not return data.
Repeatability
You might occasionally want to use a function or subroutine more than once. Technically, a sub can be repeated, but doing so necessitates entering a value into a cell each time you run it. A function does not need individual input because it can return a value. This makes it simple to repeat a function with various values.
Uses
Tasks that require a return value or that are repetitive benefit most from the use of functions. Functions can be used to perform calculations, estimate file sizes, or categorize particular objects. Since you can call functions from anywhere in the program, subs call and execute them. Because they don’t need a second sub to function, you can also use them for simpler tasks without a return value.
FAQ
What is difference between sub procedure and function?
When certain tasks need to be performed, a subprocedure is called. It completes a task and then returns control to the calling code. Every time a value needs to be returned to the calling code after completing a task, a function is called. A subprocedure does not give the calling code a value.
Can you call a sub from a function in VBA?
In VBA, you can use the word “Call” with the name of the Sub or just the Sub’s name to call it from another Sub or a User-Defined Function. You must call the Sub with any value of the argument if the Sub to be called accepts arguments.
What is sub in VBA code?
A sub procedure is a collection of Visual Basic statements that are delimited by the Sub and End Sub statements and that execute commands without producing a result. A calling procedure may pass constants, variables, or expressions as arguments to a sub procedure.
What does sub or function not defined mean in VBA?
A compile error known as “Sub or Function not Defined” appears when VBA is unable to locate a procedure or other reference by name. A typo is the most common cause of this message.
