Introduction to VBA Functions

Posted on by

Today I introduce VBA functions using the emailing from Excel macro as my example. We will look at how functions are different to a sub, how to declare a function and how to use arguments, especially optional arguments. I conclude by outlining the steps in the emailing from Excel macro.

How is a Sub different to a Function?

There are two types of procedures in VBA, Excel’s macro language.  They are subs and functions. The big difference between a sub and a function is that a function returns a number or string (or an array) whereas a sub doesn’t. A function procedure acts in a similar way to an Excel worksheet function or a VBA built-in function.
Some other differences are:

  • Subs, if they are not marked as Private and don’t have any arguments, display on the list of macros in Excel when you press Alt + F8. Functions don’t display on this list.
  • Functions, if they are not marked as Private, display in the Insert Function dialog box in Excel. By default, they are in the User Defined category. Functions suitable for a worksheet function must be passive in the sense that they cannot manipulate ranges or things on the worksheet.

Defining a VBA Function

As a VBA function returns a value, there are two important differences to the structure of a function procedure compared to a sub:

  • The type of data that is returned back to the user can be declared on the first line of the function after the arguments. It is optional.  In our example it is a Boolean type (i.e. True or False) – see below.
  • A value must always be assigned to the name of the function before the running of the procedure has been completed. This is the value that gets returned by the function.

Having said this, many things are in common between a function and a sub, e.g. declaring the scope of the procedure and declaring arguments.

Basic Structure of a VBA Function

Here is the basic structure of a VBA function:

[Private | Public] [Static] Function FunctionName([argumentlist])  [As type]
   ...[Various statements]
   FunctionName = expression
 End Function

If something is enclosed in square brackets then that means that it is optional.

Now let’s look at the SendShortEmail function from that earlier post to see how the function is defined:

Public Function SendShortEmail(EmailAddress As String, EmailSubject As String, ShortMessage As String, Optional LotusNotes As Boolean = True, Optional Outlook As Boolean = False, Optional SendKeysCode As Variant, Optional DelaySeconds As Integer = 2) As Boolean

[Several statements]
SendShortEmail = ...
[Other statements]
End Function

Scope

There are three possible words at the start of this procedure, Public, Private or Static.  Static can be used with either Public or Private or on its own.  As all three keywords are optional, you could also leave them out completely. Here are the meanings of these three keywords – the first two define the scope of the procedure:

  • Private: The procedure is only accessible to other procedures in the same module.
  • Public: The procedure is accessible to all other procedures in the same workbook.
  • Static: The procedure’s variables are preserved when the procedure ends.

I chose Public as I put this macro on its own module sheet and called the function from another module sheet within the same workbook. If you put this function onto the same module sheet as the rest of your macro then Private would be the best option.

Arguments

The arguments of the function are enclosed in parentheses after the function name. Each argument in the above function also has the phrase “As ArgumentType” after the argument name which specifies the data type of the argument. Specifying the data type is optional but I prefer to do this as it helps to produce better code.

This function has seven arguments, three compulsory or required arguments and four optional arguments.  The optional arguments have to follow the compulsory ones.

One important thing to consider with optional arguments is how to handle the situation if no value is specified in the procedure call. There are basically two ways of handling this; one specifies a default value when defining the function and the other handles it within the procedure. There out of the four optional arguments specify a default value in the argument list while declaring the function, e.g.

Optional LotusNotes As Boolean = True

The SendKeysCode argument uses the other approach – handling it within the procedure. If you want to check if an optional argument is missing from the procedure call then use the IsMissing function. However, in this case, the optional argument always needs to be defined as a Variant data type. In our function there is an If … Then statement block which uses the IsMissing function and works out a suitable value of the argument when it is missing from the function call:

If IsMissing(SendKeysCode) Then
    If Outlook Then
        SendKeysCode = "^~" ' Ctrl + Enter
    ElseIf LotusNotes Then
        SendKeysCode = "%1" ' Alt + 1
    Else
        SendKeysCode = "%s" ' Alt + s
    End If
End If

An Example of a VBA Function

Now let’s look at the overall flow of the emailing from Excel macro as an example of a VBA function.

As mentioned in the original post, at the top of the module sheet there is a declaration of a Windows Application Programming Interface (API) function called ShellExecute. This code needs to go at the top of the VBA code sheet. The function is used to do the emailing. Most of the macros I write don’t use API functions and hence they don’t need this piece of code to declare that API function.

Here is the basic flow of the SendShortEmail function:

  • Declare (i.e. define) the function, including its arguments.
  • Declare any variables that are used just within the function itself – Dim statements.
  • Turn on error trapping in case something goes wrong – On Error statement.
  • Work out a value for the SendKeys argument if it is not specified in the function call.
  • Do some checks on the DelaySeconds argument before creating an appropriately formatted value for the TimeDelay variable.
  • Perform a check on the EmailAddress argument.
  • Form the email URL and assign it to the EmailURL argument.
  • Using the API function, ShellExecute, create the email and assign the returned function value to the CheckSend variable.
  • If the creation of the email didn’t produce an error code then wait for a certain length of time (TimeDelay variable) before simulating a particular pressing of the keys (SendKeysCode argument) and assigning the return value of the function to True.
  • Exit the function.
  • If there were any errors while running this function (as opposed to errors while creating or sending the email) then this last piece of code gets executed which assigns the return value of the function to False.

I hope that gives you not only a better understanding of that particular macro but of VBA functions in general.