VBA Function - VBA String Function

A VBA function is a kind of procedure. Here, we concentrate on the VBA string function, a function that returns a string.

A VBA function is a kind of VBA procedure, the only other kind being a VBA subroutine. What distinguishes a VBA function from a subroutine is its ability to return data. In our case we concentrate on a VBA function’s ability to return a VBA string: 

Public Function Copyright(aName)
    Copyright = Chr(169) & aName
End Function 

The VBA function Copyright here takes an untyped argument and prepends a copyright symbol to it. Note the distinguishing feature of a VBA function is an assignment involving the function’s name on the left hand side of an expression. 

The default for a VBA function is to pass variables by reference, meaning the value of the original variable can be changed inside the called procedure. You can also pass variables by value (the original variable remains unaltered). The following header shows both cases: 

Sub RefAndVal(X ByVal, Y As MailItem) As String 

In this example X is untyped, and Y is typed as a MailItem. The return type is also set (As String). Typing of arguments and returns from procedures is optional. Typing provides more information to the compiler, developer and reader and may assist in solving errors at the compilation rather than run-time state. Then again, it places a limit on what type of object the argument to a VBA function can take, thereby reducing the flexibility of the function. It is also more to read and write and may make simple procedures unwieldy and more difficult to understand. 

If you are a Java programmer then you will be used to typing your arguments. If you are a Smalltalk programmer you will be happy with typeless arguments. If, like me, you have programmed in both these languages, then you should be happy to experiment with both techniques when writing a VBA function. A good way to proceed might be to program without defining types to begin with. This will let your coding flow quickly. Then, later, you can add typing to help with bug tracing and to make the code clearer (if this is necessary). 

Because typing is not enforced, you will probably find that even a strict typing enthusiast is liable to miss out the type now and again. So in summary, do not worry if someone pulls you up for not being strict with typing. Just tell them that there are very good languages out there that do not use typing at all (e.g. Smalltalk).