Hello Excellers and welcome back to another blog post in this #macromonday series.  Today lets look at executing a function procedure in Excel.  But first, if you need a reminder of the difference between a VBA Sub procedure and a VBA Function procedure, let’s do a small recap.

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 read 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 so you can check back weekly.

Sub Procedures

In my Macro Mondays blog post series, I have been sharing mainly Sub Procedures.  Usually, these macros take the 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 convert all of the user selected cells to Upper Case.

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.

 

Further Notes On Your Personal Macro Workbook

To read more about your Personal Macro Workbook you can read my corresponding blog posts below.

Macro Mondays -Creating and Updating Your Personal Macro Workbook

Macro Mondays – Create A Shortcut To Your Personal Excel Macro Workbook

Why Is My Personal Macro Workbook Not Loading Automatically?

 

As soon as you type the line of code Sub ConvertToUpper (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 convert all of the cells that a user has selected and automatically converts the text to Upper Case.  This is a Sub Procedure.  Let’s move onto Function Procedures.

 

Function Procedures.

In 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 by default within Excel.  After you have used the software for a time 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 on 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.

The full code can be seen below.  It is a simple example.

 

So, we now know the difference between a Procedure and a Function.  How on earth do we call or use the Function we have created?.  There are a number of ways to call the function which I will take you through one by one below.

1. Call The Function From A Sub Procedure

As you cannot call the Excel Function directly, you must, therefore, call it from another procedure.  If we take our example of the RecArea function already used, we can create a procedure which is defined in the same module as the custom created RecArea function.  There are two stages.   First display an Input Box (well two of them, one after the other), to collect the two values of Length and Width.  Secondly, we then use the MsgBox function to display to the user the result after it is processed by the RecArea Function.  So, let’s get to writing some VBA Code.

Preparing To Write The Macro

Our VB editor should be already open, from writing the Function RecArea.  If it does not then follow the steps above to create the function.  We now need to write the sub Procedure in the same module as the Function.  Type Sub then the name of your macro.  In this example, I have called it simply Sub AreaCalc.  Notice that Excel will automatically enter the end text End Sub to end the Sub Routine.  We simply need to enter the rest of the code between these two lines.

[stextbox id=’info’]

Sub AreaCalc()

End Sub

[/stextbox]

Declaring Variables

The stage in to declare any variables.  This ensures excel allocates memory to these values.  In this macro, we need to declare 3 variables,  Length, Width and RecArea values.  I am declaring the value type as Double.  This means it is one of the non-integral numeric data types. This means that, just as a decimal, it can be used to hold both integers and fractions.

[stextbox id=’info’]

Dim Length As Double, Width As Double

Dim RecArea As Double

[/stextbox]

Collecting The Width And Length Off The User

We now use the InputBox function to collect the variables of Width and Length to use in our function.  Once you begin to type InputBox, Excel automatically will display the full list of available optional arguments to use.  In this example, I have kept it straightforward and just used the first option of Prompt.  This is the text surrounded by quotation marks and indicates to the user what value to enter into the box.

[stextbox id=’info’]

Width = InputBox(“Enter The Width”)
Length = InputBox(“Enter The Length”)

RecArea = Width * Length

[/stextbox]

 

Displaying The Result Of The Function

This next line of code is simple.  We instruct Excel to display the result of the RecArea function in a message box.

[stextbox id=’info’]

MsgBox (RecArea)

[/stextbox]

Ending The Routine

The End Sub code ends the routine once the calculation has completed has been displayed in the message box.

[stextbox id=’info’]

End Sub

[/stextbox]

2. Call The Excel Function From A Worksheet Formula

We can also call the VBA Function procedure from a worksheet formula. Activate a worksheet in the same workbook that holds the RecArea function definition. Then enter the following formula in any cell

=RecArea(5,8)

You can see that once you begin typing the =Rec into any cell the Function syntax appears just like any other Excel Function.

Your answer to this example will be 40.  You can use this function any number of times in the worksheet. Like Excel’s built-in functions, your custom functions appear in the Insert Function dialog box.

 

3. Call The Excel Function From The Visual Basic Immediate Window

The final way to call a Function procedure is to use the Immediate Window in the VB Editor.  This is usually only used for testing.  It pretty much works the same way as calling the Function from a worksheet formula.  You will, of course, need the Immediate Window to be visible.  If it is not then it can be activated by selecting the View Menu | Immediate Window

So how do you use the Immediate Window?.  All you need to do is Type ? then the Function.

As can be seen in the screencast below where I can call the RecArea function.

So, there are a number of ways to call Excel Functions, either from a Sub Procedure, from the worksheet or by using the Immediate Window.  Try all three methods to test your VBA code.

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

Macro Mondays Blog Posts.

 

Do You Need Help With An Excel Problem?.

Finally, I am pleased to announce I have teamed up with Excel Rescue, where you can get help FAST. All you need to do is choose the Excel task that most closely describes what you need to be done. Above all, there is a money back guarantee and similarly Security and Non-Disclosure Agreements. Try It!. Need Help With An Excel VBA Macro?. Of course, you don’t need to ask how to list all files in a directory as it is right here for free.