Function vs Sub: What Actually Differs

One does something, the other answers something

You already know the Sub: a block of instructions you run from the macro list or a button. A Function differs in one important way — it hands a value back to whoever called it. A Sub does something; a Function answers something, and because it answers, a cell can ask it.

There is no Return keyword

A Function closes with End Function and declares the type of its answer after the closing bracket. You send that answer back by assigning to the function's own name: the line below is not creating a variable called Perimeter, it is setting the result. Assign nothing and you get an empty value — zero for a number, an empty string for a String.

Function Perimeter(width As Double, height As Double) As Double
    Perimeter = 2 * (width + height)
End Function

Side by side

• A Sub appears in the Alt+F8 list; a Function never does.
• A Function can be typed into a cell as =Perimeter(A1, B1); a Sub cannot.
• A Sub may change cells, formatting and files; a Function called from a cell may not.
• Both take arguments and can be called from other VBA code.

A function you can use in a cell today

TextBetween pulls whatever sits between two markers out of a string — an order number out of an e-mail subject, a code out of a file name. Paste the whole block into a standard module (Alt+F11, Insert → Module) and run DemoTextBetween with F5.

Option Explicit

Function TextBetween(ByVal source As String, ByVal opener As String, _
                     ByVal closer As String) As String
    Dim startPos As Long
    Dim endPos As Long

    startPos = InStr(1, source, opener, vbTextCompare)
    If startPos = 0 Then Exit Function

    startPos = startPos + Len(opener)
    endPos = InStr(startPos, source, closer, vbTextCompare)
    If endPos = 0 Then Exit Function

    TextBetween = Mid(source, startPos, endPos - startPos)
End Function

Sub DemoTextBetween()
    Dim ws As Worksheet
    Dim samples As Variant
    Dim r As Long

    samples = Array("Order [SO-4471] shipped on Monday", _
                    "RE: order [SO-4472] delivery question", _
                    "Invoice for [SO-4473] attached", _
                    "No reference anywhere in this one")

    On Error Resume Next
    Set ws = ThisWorkbook.Worksheets("UDF Demo")
    On Error GoTo 0

    If ws Is Nothing Then
        Set ws = ThisWorkbook.Worksheets.Add( _
            After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))
        ws.Name = "UDF Demo"
    End If

    ws.Cells.Clear
    ws.Range("A1:C1").Value = Array("Subject", "Called from VBA", "Called from a cell")
    ws.Range("A1:C1").Font.Bold = True

    For r = 0 To UBound(samples)
        ws.Cells(r + 2, 1).Value = samples(r)
        ws.Cells(r + 2, 2).Value = TextBetween(CStr(samples(r)), "[", "]")
        ws.Cells(r + 2, 3).Formula = "=TextBetween(A" & (r + 2) & ",""["",""]"")"
    Next r

    ws.Columns("A:C").AutoFit
    ws.Activate
End Sub

Line by line

• Function TextBetween(...) As String — three arguments in, one String out. ByVal means the function works on copies, so it cannot alter the caller's variables.
• InStr(1, source, opener, vbTextCompare) finds the opening marker and returns 0 when it is not there. vbTextCompare makes the search ignore upper and lower case.
• If startPos = 0 Then Exit Function — no marker, no answer. The name was never assigned, so an empty string comes out: the missing-Return rule doing something useful instead of crashing.
• startPos = startPos + Len(opener) steps past the marker itself. Leave it out and the marker turns up in your result.
• The second InStr starts at startPos, so a closing bracket appearing earlier is correctly ignored.
• TextBetween = Mid(source, startPos, endPos - startPos) — assigning to the function's own name is how the value leaves.
• In the Sub, column B calls the function from VBA and column C writes a formula calling the same function from the sheet. One piece of code, two worlds.
• In "=TextBetween(A" & (r + 2) & ",""["",""]"")" the doubled quotes are how one quote character gets into a VBA string.
• The last sample has no markers, so both columns stay empty — the Exit Function branch, visible on the sheet.

The trap: it works in VBA and the cell says #NAME?

Two causes, and each of them costs an afternoon the first time.

• Wrong module. A worksheet function has to live in a standard module — Insert → Module. Typed into a sheet's own module or into ThisWorkbook it stays private to that object: VBA still reaches it as Sheet1.TextBetween(...), a cell answers #NAME?. In the Project Explorer your code must sit under Modules, not under Microsoft Excel Objects.
• A name Excel reads as an address. Call your function TAX1 and =TAX1(A2) fails, because TAX1 is a legal cell reference — columns run to XFD. A name colliding with a built-in function is just as bad. Avoid a trailing digit, and try a new UDF in a cell before building on it.

How this course goes on

Lesson 2 gives your function a description and a category so it appears properly in Excel's function list. Lesson 3 covers argument types and returning a real error such as #DIV/0! instead of a misleading zero; lessons 4 and 5 add optional arguments and functions returning a whole block of cells. The last three save your afternoon: when Excel recalculates a UDF, why a slow one freezes a workbook, and what a UDF is not permitted to do.