Code360 powered by Coding Ninjas X Naukri.com. Code360 powered by Coding Ninjas X Naukri.com
Table of contents
1.
Introduction
2.
What is a Function?
3.
What is a Sub?
4.
Passing Arguments By Value and By Reference
5.
Frequently Asked Questions
6.
Key Takeaways
Last Updated: Mar 27, 2024

Functions and Sub in Excel

Author Ankit Mishra
1 upvote
Master Python: Predicting weather forecasts
Speaker
Ashwin Goyal
Product Manager @

Introduction

Data has become one of the essential things for an individual in today's modern world. You must have filled out your information on the application or website.

However, if a person wishes to learn how to handle data in the form of tables, they may do so using Excel. Excel is a powerful tool or software that we have at our ease. It may be used to create tables, computations, sheets, etc.

Functions and Subs are essential tools used to perform multiple tasks. Today we will discuss them in brief.

So, without wasting any more time, let's get to the point of our discussion. First, we'll look at Excel's functions and how they work.

What is a Function?

A function is a predefined formula already implemented in Excel, or you may define your function.

Functions carry out different calculations in a specific order based on the values specified as arguments or parameters.

If you expect a result from the Excel VBA, you can use a function. Place a function into the code (Click Insert, Module) in the Visual Basic Editor. For example, the function with the name prod(Product).

Function Prod(x As Double, y As Double) As Double

Prod = x * y

End Function

Let's understand how this works. This method takes two parameters (of type Double). It returns a value of type Double (the part after As also of type Double). You can use the function name (Prod) in your code to specify which result you wish to return (here x * y).

You can now make a call to this function from any stage in your code by simply using the name or Alias of the function and giving a value for each argument.

Press the command button on your worksheet and add the following line of code:

dim multiply As Double                                //Declaration of variable(using Dim)

multiply = Prod(2, 6) + 11

MsgBox multiply                                          // Display the result(Using MsgBox)

The function returns a value which is the product of 2,6, and after that, 11 is added, giving a value which is 23, so you have to 'catch' this value in your code. You can declare another variable (multiply) for this. Next, you perform the addition of another value to this variable (not necessary). Finally, display the value using a MsgBox.

When you press the command button on the sheet, the following is displayed:

If you want to perform a task that returns a result (such as the sum of a group of numbers), you would typically use a Function procedure.

I hope you understand how we deal with functions; now, coming to the second part of our discussion, Sub. Let's get straight into it.

Get the tech career you deserve, faster!
Connect with our expert counsellors to understand how to hack your way to success
User rating 4.7/5
1:1 doubt support
95% placement record
Akash Pal
Senior Software Engineer
326% Hike After Job Bootcamp
Himanshu Gusain
Programmer Analyst
32 LPA After Job Bootcamp
After Job
Bootcamp

What is a Sub?

A Sub is a chunk of code used to perform a specific task. But, unlike Functions, it does not return any value, where we used to return a value from our code.

A Sub is also known as a subroutine or subprocedure.

Let's have a look at the code part of Sub.

SubProd(x As Double, y As Double)

MsgBox x * y

End Sub

This Sub has two arguments named x and y (of type Double). Notice that, It does not have a return type!

You can make a call to this Sub from any stage of your code by simply using the Sub's name and giving a value for each argument now if we call the Sub, i.e., prod.

Prod 7,3

 

When you press the command button on the sheet, the following is displayed:

Passing Arguments By Value and By Reference

ByVal

This means that only the value (i.e., a copy of the argument) is supplied to the procedure. Any modifications made to the argument within the process are lost when the operation is terminated.

SubAddToColumn(ByVal variable As Integer)
        .
        .
        .
End Sub

ByRef

This signifies that the argument's address is provided to the argument. Any changes to the argument made within the procedure will be remembered after the process is exited.

SubAddToColumn(ByRef variable As Integer)
        .
        .
        .
End Sub

Now we have completed our discussion on Functions and Sub and how they differ from each other. Let's look at some Frequently asked questions on Functions and Sub.

Frequently Asked Questions

  1. What are VBA Functions?
    A function is a collection of operations that can be invoked from anywhere in the program. This allows us to reuse the same program without having to rewrite it every time.VBA includes a variety of built-in functions and also allows users to develop their unique functions using the VB editor.
  2. What is ByRef in VBA?
    This signifies that the argument's address is provided to the procedure. Any changes to the argument made within the procedure will be remembered after the process is exited.
  3. What does VBA stand for?
    VBA is an abbreviation for Visual Basic for Application, a programming language that allows you to automate practically any activity in Excel.
  4. What is a Module?
    A VBA module is a file with the ".bcf" extension that has a code window where you can write a macro. You can enter, delete, import, and export modules in the workbook.

Key Takeaways

In this article, we have extensively discussed VBA functions and subprocedures in excel. Then we discussed the differences between Function and Sub. Later we saw how to write custom functions and use them in the workbook. At last, we got to know about Calling a function or a Sub This will help us reduce the length of the code and give better readability.

We also learned about passing variables ByVal and ByRef between functions or subs.

If you want to learn more, check out our articles on Code studio. Do upvote our blog to help other ninjas grow.

“Happy Coding!”

 

Previous article
Array in VBA
Next article
Application Object
Live masterclass