Hi and a warm welcome to my Excel blog if this is your first time visiting. A huge welcome back if you are a regular. In today’s Macro Monday post I want to chat about the difference between a VBA Sub procedure and a VBA Function procedure. Yes, I thought it was about time to address the part that both Sub and Functions play in VBA.

When most people talk about Macros in Excel they are talking about Sub procedures. Most of the macros that we record or write in VBA are Sub procedures. Sub is basically a command. By executing a Sub procedure and what happens absolutely depends on the code that you write as part of that Sub procedure.

But, we also have a Function which is also a procedure, but quite different from its brother or sister the Sub. You are probably already aware of the concept of an Excel Function if you have used Excel in any shape or form for a while. For example a VLOOKUP, SUM, INDEX, MIN, MAX. These are all Functions that usually take at least one argument and do some calculations in the background to return a single value. That is a Function.

SUB v FUNCTIONS

⇒A Sub procedure is a group of VBA statements that will perform a single or group actions in Excel.

⇒A Function procedure is a group of VBA statements that perform a calculation and like the definition of an Excel Function will return a single value.

If you want to browse through all of the Macro Monday series of blog posts you can do so at the link below.

How To Excel At Excel – Macro Mondays Blog Posts.

This link is updated every week with new content so make sure to BOOKMARK it for ease.

Sub Procedures

In my Macro Mondays blog post series, I have been sharing mainly Sub Procedures with you. Usually to take repetition out of everyday Excel processes and procedures you carry out to help speed up your day and get your job done more efficiently.

Every Sub procedure starts with the word Sub and ends with the word End Sub statement. Logical isn’t it. Let’s remind ourselves of the process to distinguish it from the Function procedure. In this example, I will use a VBA Sub procedure to rename the active sheet in my Excel workbook ‘output’.

First, we need to create a new module to save this Macro into. Insert a module by opening Visual Basic by hitting ALT+F11, or select the Developer Tab and in the Code Group Select Visual Basic. You can now hit Insert Module. I have chosen to store this in my Personal Macro Workbook so I can use with any workbook I want to.

As soon as you type the line of code Sub NameMySheets (which is the name I have selected for my macro) and hit return Excel automatically inserts the End Sub part of the code for you.

This macro will re-name the active sheet “output”. This is really a super simple few lines of code as my workbooks only contain one worksheet and therefore the active worksheet is the default one. If you had more than one worksheet then you would need to click on the worksheet you want to rename to tell Excel that it is the active worksheet.

Function Procedures.

Let’s now take a look at Function procedures. On the way that every Sub procedure begins with the words Sub, Functions always begin with…yes you guessed it the word Function, and ends with the words End Function. Most calculations can be achieved with what is provided, but it isn’t long before you find yourself wishing that there was a function that did a particular job, and you can’t find anything suitable in the list. You need a UDF. (User Defined Function). Now, these can be easy to the most complex, but the theory is the same, you want Excel to do a calculation to return a single value back. Let’s walk through a fairly simple one to get the idea going. We will calculate the area of a rectangle. This is simple

The Area Of An Rectangle = LENGTH x WIDTH

First, we need to create a new module to save this Macro into. Insert a module by opening Visual Basic by hitting ALT+F11, or select the Developer Tab and in the Code Group Select Visual Basic. You can now hit Insert Module.

Into the empty module type

Function RecArea ()

Excel automatically again inserts for us the End Function code. Nice.

• Insert your cursor into the brackets. This is where we specify our arguments just as we do in most Excel Functions. An argument is some information that Excel requires to carry out the calculation
• We specify Length As Double and Width As Double. This declares the Excel the type of data to expect. This speeds up our code and helps avoid any errors. Double data type allows large numbers and also fractions.
• The next part of our Function procedure is the actual calculation.

Testing The Function

This really is my favourite part here, testing my newly created function. Just begin to type the name of the function as you would with other functions and the arguments will require will appear.

So, that’s it guys, we have covered the difference between a Sub Procedure and a Function Procedure in Excel. A Sub Procedure is a group of VBA statements that will perform a single or group actions in Excel. Alternatively, a Function procedure is a group of VBA statements that perform a calculation and like the definition of an Excel Function will return a single value.

Do you use mainly Sub procedures or have you written some UDF to help your Excel work?. Why not share in the comments below.

What Next? Want More Tips?

So, if you want more tips then sign up for my Monthly Newsletter where I share 3 Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.

 

If you want to see all of the blog posts in the Macro Monday series. Click on the link below

How To Excel At Excel – Macro Mondays Blog Posts.

Do You Need Help With An Excel Problem?.

I am pleased to announce I have teamed up with Excel Rescue, where you can get help with Excel FAST.