I thought my example illustrated nicely that it can be useful. Instead of having to write two almost identical procedures (one as a sub, and the other a function) that only differ in the way how they return the result, you need to write only one. This follows the ideal of code reuse.
Moreover, having only one sub/function instead of two makes it easier to use, since you don't have to remember and deal with two different names for the same functionality. That is, I believe, the main reason why Microsoft allowed functions to be so easily used as subs.
You might ask why I'm not simply leaving out the "If I´mASub Then" clause. Indeed, that's possible; VBA handles this undauntedly, even if s1 is a constant. But I consider that a hack. Each time a function changes its parameters, it opens a can of worms of possible unpleasant surprises. I generally try to catch my programming errors before they happen, which means, I don't change a parameter in a function unless I really mean it. Look at the following code; just changing a constant to a variable (a change that is not uncommon when a macro grows into a wider range of applicability) can lead to the same call giving different results. That spells trouble!
Function ConcatStrings _
(s1 As String, s2 As String, Optional sSeparator As String = " ") _
As String
ConcatStrings = IIf(s1 = "", s2, s1 & sSeparator & s2)
s1 = ConcatStrings
End Function
Sub Test()
Dim sLeft1 As String
sLeft1 = "left"
Const sLeft2 = "left"
Const sRight = "right"
Dim sSum As String
Debug.Print "before", sLeft1, sLeft2
sSum = ConcatStrings(sLeft1, sRight, "+")
sSum = ConcatStrings(sLeft2, sRight, "+")
Debug.Print "after", sLeft1, sLeft2
End Sub