Difference between Subs and Functions: what does "return a value" mean?

Question

I am an VBA (and programming in general )noob. I am trying to understand the fundamental difference between a subroutine and a function in VBA. I read various websites / books, but none of them make it crystal clear what these concepts are and how they differ.

In particular, I see this explanation given everywhere: "a sub performs a task but does not return a value" but " a function returns the value of the tasks to be performed".

Well... So suppose I write a function that takes a (numerical) value from a cell in Excel and returns double that value (in another cell). I can also write a sub that does the same: read a cell's value and then print double that value. So why does it say that sub does not return a value when it clearly does?!

Please help me understand this.

Answer 1

Sub vs Function

• Note that there are inaccuracies and missing information in the following attempt to describe the difference between a sub and a function.

Sub

• The following is a simple procedure that returns twice the value of A1 in B1.

Sub sDoubleSimple()
    Range("B1").Value = 2 * Range("A1").Value
End Sub

• To use it for cells of your choice, you could add two arguments and rewrite the procedure:

Sub sDouble(ByVal SourceCell As Range, ByVal DestinationCell As Range)
    DestinationCell.Value = 2 * SourceCell.Value
End Sub

• To utilize it, you could call it in the following ways:

Sub sExamples()
    
    sDouble Range("A1"), Range("B1")
    
    sDouble Range("A2"), Range("B2")

    Dim sCell As Range
    For Each sCell In Range("A3:A10").Cells
        sDouble2 Cells(sCell.Row, "B"), sCell
    Next sCell

End Sub

• Basically, it returns twice the values of column A in column B.
• Note that returns I have used in the previous rather inaccurate descriptions is not the returns related to functions.

Function

• To 'rewrite the last sub as a function' you could do...

Function fDouble(ByVal SourceCell As Range) As Double
    fDouble = 2 * SourceCell.Value
End Function

... and utilize it like this:

Sub fExamples()
    
    Range("B1").Value = fDouble(Range("A1"))
    
    Range("B2").Value = fDouble(Range("A2"))
    
    Dim sCell As Range
    For Each sCell In Range("A3:A10").Cells
        Cells(sCell.Row, "B").Value = fDouble(sCell)
    Next sCell
    
End Sub

• At first glance, except for the syntax, it seems there is no difference between the 'sub and function ways'. And they practically do the same with a huge difference, e.g. looking at the line...

  Range("B1").Value = fDouble(Range("A1"))
  

  we can see that fDouble(Range("A1")) will be written to Range("B1") so we can conclude that the former must be a number which is the return value of the function i.e. we can use it in various ways, to return it in a message box...

Sub fExamplesMsg()
    
    MsgBox fDouble(Range("A1"))
    
    MsgBox fDouble(Range("A2"))
    
    Dim sCell As Range
    For Each sCell In Range("A3:A10").Cells
        MsgBox fDouble(sCell)
    Next sCell

End Sub

... or in the Immediate window (Ctrl+G)...

Sub fExamplesPrint()
    
    Debug.Print fDouble(Range("A1"))
    
    Debug.Print fDouble(Range("A2"))
    
    Dim sCell As Range
    For Each sCell In Range("A3:A10").Cells
        Debug.Print fDouble(sCell)
    Next sCell

End Sub

• Most importantly, we can write the return value to a variable and e.g. use it in an If statement to further choose what will be returned in the cells.

Sub fExamplesConditional()
    
    Dim cValue As Double
    
    cValue = fDouble(Range("A1"))
    If cValue > 50 Then
        Range("B1").Value = cValue
    End If
    
    cValue = fDouble(Range("A2"))
    If cValue > 50 Then
        Range("B2").Value = cValue
    End If
    
    Dim sCell As Range
    For Each sCell In Range("A3:A10").Cells
        cValue = fDouble(sCell.Value)
        If cValue > 50 Then
            Cells(sCell.Row, "B").Value = cValue
        End If
    Next sCell
    
End Sub

• Basically, it will return twice the value of column A in column B if the result is greater than 50.

Conclusion

• You could say a sub does while a function returns. A function will return a value in the calling procedure where you can further manipulate it. Think about all of the Excel functions, they all return a result. They don't write the result to another cell but to the cell, they are written in e.g. in cell B1 you could use =fDouble(A1).

Answer 2

"Return" is not the same as "print". Printing is a procedure of showing a result to the user; in general, the program can not make use of a printed value. Returning a value, on the other hand, is done for the purpose of further processing; the user does not directly get access to it.

To compare to real world: a subroutine is like a command to do a certain action; a function is like a question, giving you an answer that you can further ask about.

Imagine you are a foreman in a construction company, but you hate leaving your office. "Install a window on the south wall" is a command — a subroutine. A worker goes out and does it. Or "Take a photograph of the building, and show it to the client". The foreman does not get any information from that (apart from the confirmation that the worker has done what you asked).

On the other hand, "How tall is the tree next to the building?", "Is there any cement left?", "Ask the client about the photograph and tell me what his comments were" all give the foreman an answer, and the foreman can make further decisions based on it. This is what functions are.

If you have a function AreaOfRectangle that can calculate width muliplied by height, you can also have a function AreaOfRectangularBox that asks AreaOfRectangle for the area of the front, left and top face of the box, doubles each of those (since back, right and bottom faces are the same) and adds them together, returning the area of the rectangular box. The user still doesn't know what that number is, but the program does.

If you have a subroutine AreaOfRectangle, and it shows what width times height is on the screen, you cannot use this knowledge in the rest of your program, because AreaOfRectangle gives no information back to the program (except for the fact that it is done with whatever it was doing).

---

In some programming languages, subroutines have the ability to modify their parameters, so it is not so cut-and-dried, but still the distinction between "returns" (to the calling code) and "prints" (to the user) stays relevant. Also, in some languages, subroutines do not necessarily even have to report when they are finished (these are called "asynchronous"); but this too is a detail for much later.

EDIT:

returns double that value (in another cell)

Something like Range("A2").Value = Range("A1").Value * 2 is not "returning". This is commanding the program to store a value in a cell — conceptually close to a subroutine (and in some languages, it might be a subroutine — something like SetAt(X, Y, Val) — though VBA uses assignment to a .Value property). A program could later inquire about the value in that cell — conceptually close to a function (and in some languages, it might actually be a function — something like Val = GetAt(X, Y) — though VBA uses a simple access to a .Value property).

If you are using the return value of a function inside a formula in a cell, something like =DoubleThis(5), that is actually proper returning. But crucially, this is a formula, so basically it is still in the realm of code, not of the user. You could also write =DoubleThis(5)+7, and the return value of 10 would be used to be added with 7, for the total result of 17. (Then Excel takes over and displays the cell value; but this is not your code any more.)

Sometimes people use this word somewhat casually; but if you are learning programming, you have to strictly distinguish the two scenarios.