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.

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

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

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:

New worksheet in tabs

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:

New worksheet name input box

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.

 

This blog has 0 threads Add post