Let’s take some very simple macro and show how it can be converted into a function that returns a value. Here’s the basic macro:
Sub MainMacro()
y = InputBox "Enter a value in centigrade"
y = 32 + (y * 9) / 5
MsgBox "The answer is " & y & Fahrenheit"
End Sub
Splitting this into a main subroutine and a function, we get the following:
Sub MainMacro()
y = InputBox "Enter a value in centigrade"
x = FtoC(y)
MsgBox "The answer is " & x & Fahrenheit"
End Sub
Function FtoC (y)
FtoC = 32 + (y * 9) / 5
End Function
The important part is that the function returns a value by assigning a value to the function name (as though the function name was itself a variable).
As with the subroutine example, the advantage of splitting the code will only come if you have to perform the centigrade to Fahrenheit conversion many times in your code. For example, suppose your code had ten separate lines, in different places that did a conversion of centigrade to Fahrenheit:
x = 32 + (y * 9) / 5
and you now found out that you needed to check that a sensible value was created. In this instance, you’d have to go and modify ten lines of code. If you'd split the code into a function instead, you could adjust the function once. For example:
Function FtoC (y)
If y < -273.15 Then
MsgBox "Colder that absolute zero!"
Exit Function
End If
FtoC = 32 + (y * 9) / 5
End Function