This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

Writing functions for Excel using VBA Part one of a three-part series of blogs
On-line training in writing functions using VBA, for use either within other VBA programs or within Excel itself. Writing and Using Functions in Excel Visual Basic (this blog) Examples of a Function to Make Coding Easier Writing Functions for Use within Excel VBA This blog is part of our Excel VBA tutorial. Wise Owl's main business is running classroom-based training courses; have a look at our Excel courses or VBA courses, for example.

Writing functions for Excel using VBA

Part one of a three-part series of blogs

On-line training in writing functions using VBA, for use either within other VBA programs or within Excel itself.

Writing and Using Functions in Excel Visual Basic (this blog)
Examples of a Function to Make Coding Easier
Writing Functions for Use within Excel VBA

This blog is part of our Excel VBA tutorial. Wise Owl's main business is running classroom-based training courses; have a look at our Excel courses or VBA courses, for example.

Posted by Andy Brown on 24 February 2012

Writing and Using Functions in Excel Visual Basic

Most programs you write will begin with the word Sub - but not all:

When you choose Insert --> Procedure... from the VBA code editor menu, you can insert a Sub or a Function (the word Property is discussed in a later blog on classes).

There are two reasons that you might create a function:

To help make code easier to write, read and maintain; or
To supplement the list of built-in Excel functions.

This blog will show how to do each of these in turn, but first let's look at the syntax of a function.

How to Write Functions in VBA

The difference between a subroutine and a function in VBA is that a function returns a value. Consider the following very simple function to square a number:

Function Square(AnyNumber As Integer) As Long

'return the square of any integer

Square = AnyNumber * AnyNumber

End Function

There are 3 differences between this and a normal subroutine:

The numbered differences are explained below.

The differences are:

A function begins with Function and ends with End Function (rather than Sub and End Sub).
A function has a data type (this one is of type Long, since we don't know how big an integer we'll have to return).
Within a function, you have to set the name of the function equal to something (here, the square of the number contained in the argument).

VB, C# and many other programmers should note that - irritatingly - there is no RETURN statement to return the value of a function in VBA.

You can call functions from Excel (shown in more detail in the final part of this blog), from another subroutine or from the Immediate window. Examples of each follow!

Calling Functions from Another Subroutine

One way to call a function is from another program, or procedure:


We want to return this ...	... when you type in a number.

The program above (stripped of any error-checking) might read:

Sub ShowSquare()

Dim n As Integer

'get the number user wants to square

n = CInt(InputBox("Type in a number"))

'show this square

MsgBox "The square of " & n & " is " & _

Square(n)

End Sub

As you see, you can use Square just as if it were a built-in function in VBA.

Displaying the Value of a Function in the Immediate Window

You can do this in the usual way:

Here we've called our Square function 3 times, with a different argument each time.

Again, you can treat your function as if it were one of the built-in ones in VBA.

Calling a Function from Excel

One other way to call a function - and perhaps the sexiest - is to type it into Excel:


Your Square function appears ...	... and gives you the answer!

The formula for the cell shown above might look like this:

The function takes a single argument, which must be an integer. Here it is the value of cell C2.

One slight annoyance is that when you type the start of a function - in the case above, =Square( - Excel doesn't suggest possible arguments for it. You can get round this by using the function wizard, as shown in the final part of this blog.

Now that we've seen what a function is and how to run one, let's look at how you might incorporate them into Excel.

Parts of this blog
Writing and Using Functions in Excel Visual Basic (this blog) Examples of a Function to Make Coding Easier Writing Functions for Use within Excel VBA

Some other pages relevant to the above blogs include:

This blog has 0 threads

Add a new post

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

BLOGS BY TOPIC

BLOGS BY AUTHOR