This page has 0 threads | Add post

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

Writing functions for Excel using VBA Part two 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 Examples of a Function to Make Coding Easier (this blog) 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 two 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
Examples of a Function to Make Coding Easier (this blog)
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

Examples of a Function to Make Coding Easier

Whenever you naturally would want to ask a question in code, you should use a function. To see what I mean by that rather vague proposition, let's look at a worked example.

Our Example - Adding a Worksheet

Suppose you want to write a subroutine to allow a user to input a new worksheet:

After adding a new worksheet called test, it will appear in the list of worksheet tabs.

Here is some code to do this:

Sub AddWorksheet()

'the name of the new worksheet

Dim SheetName As String

'ask user to type in name

SheetName = InputBox("Type in new name", "Worksheet name")

'add a new worksheet, which becomes the active sheet

Worksheets.Add

'rename this sheet to the new name typed in

ActiveSheet.Name = SheetName

End Sub

When you run the AddWorksheet routine above, you will see an input box and be able to type in the name of a worksheet, which Excel will then create:

A user can type in the name of a worksheet in this dialog box and press Enter to create it.

The problem is: what happens if this worksheet already exists?

Pseudo-Code for our Example

In pseudo-code (half VBA, half English), here's what we're trying to do:

Display an input box
Store new worksheet name

If a worksheet with this name already exists
Display error
Otherwise
Create new sheet and give it the right name

What this shows is that we could really do with a function to determine if a worksheet with a given name already exists. We'll give it a name, and define the inputs and output.

What	Notes
Function name	DoesSheetExist
Inputs	SheetName - String
Output	Whether the sheet exists or not - Boolean

Given the above, it should be fairly easy to write our function.

The Final Code

Our function could now loop over the collection of worksheets trying to find one with a given name:

Function DoesSheetExist(SheetName As String) As Boolean

'ARGUMENTS

'=========

'SheetName The sheet name we're testing for

'initialise the function (assume it's false)

DoesSheetExist = False

Dim w As Worksheet

For Each w In Worksheets

'for each worksheet, see if the name matches

If UCase(w.Name) = UCase(SheetName) Then

'it does!

DoesSheetExist = True

'no need to test any more

Exit Function

End If

Next w

'if we get here, we didn't find the sheet

'(but function initialised to false, so no need

'to do anything more)

End Function

You could then call this function as follows:

Sub AddWorksheet()

'the name of the new worksheet

Dim SheetName As String

'ask user to type in name

SheetName = InputBox("Type in new name", "Worksheet name")

'add a new worksheet, which becomes the active sheet

'(but first check whether exists or not)

If DoesSheetExist(SheetName) Then

MsgBox "Worksheet with this name already exists"

Exit Sub

End If

Worksheets.Add

'rename this sheet to the new name typed in

ActiveSheet.Name = SheetName

End Sub

Voila! Note that you could have done this without the function - that's always true - but we're aiming for neat, easy-to-read code.

Parts of this blog
Writing and Using Functions in Excel Visual Basic Examples of a Function to Make Coding Easier (this blog) 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

Google Sheets

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