return a value from excel function vba code example

Example 1: excel how to return a result from a VBA function

'In VBA, you assign the return value to the name of the function.
'This is different from many languages that use the 'return' keyword:

Function Yesterday() As Date
  Yesterday = Date() - 1
End Function

'VBA functions can be called from other VBA code:
d = Yesterday

'But VBA functions can also be called directly from an Excel 
'worksheet if the function is placed in a VBA standard code module.
'In this context, a VBA function is referred to as an Excel 
'User Defined Function (UDF) for the workbook.
'For example, enter the following formula in cell A1:
=Yesterday()

'If the function returns an object instead of an intrinsic 
'data type then the Set keyword is required for the assignment:
Function NewDictionary()
    Set NewDictionary = CreateObject("Scripting.Dictionary")
End Function
'And you must use Set when calling the function as well:
Set MyDic = NewDictionary 
'VBA functions that return objects cannot be used as 
'UDFs in a worksheet, with one exception. If the function 
'returns a Range object, it can be used as a UDF.    
    
'------------------------------------------------------------------

'Notes: In many programming languages, a function terminates 
'      	execution when the 'return' keyword is encountered. 
'      	VBA is different. Execution continues beyond the value
'		assignment to the function name... until the last line
'      	of the function is reached... or until an 
'		'Exit Function' statement is encountered.
    
'		The function Name can be used as a variable
'		inside the function as in this recursive function:
    
        Function Factorial(n)
            Select Case n
                Case Is < 2: Factorial = 1
                Case Else:   Factorial = n * Factorial(n - 1)
            End Select
        End Function
        
'		However, it is not always a great idea to use the function
'		name as a variable. It can be confusing in longer functions
'		and it has limitations. If the function is designed to return
'		a dynamic array and the function code attempts to ReDim the
'		function name, an compile-time error is raised.
    
'		As a side note, VBA can access hundreds of Excel's built-in 
'		worksheet functions. Here are three ways to access
'		the native FACT() worksheet function from VBA:
    
        MsgBox [FACT(7)]
        MsgBox Evaluate("FACT(" & 7 & ")")
        MsgBox WorksheetFunction.Fact(7)

Example 2: excel VBA return a result function

'In VBA, you assign the return value to the name of the function.
'This is different from many languages that use the 'return' keyword:

Function Yesterday() As Date
  Yesterday = Date() - 1
End Function

'VBA functions can be called from other VBA code:
d = Yesterday

'But VBA functions can also be called directly from an Excel 
'worksheet if the function is placed in a VBA standard code module.
'In this context, a VBA function is referred to as an Excel 
'User Defined Function (UDF) for the workbook.
'For example, enter the following formula in cell A1:
=Yesterday()

'If the function returns an object instead of an intrinsic 
'data type then the Set keyword is required for the assignment:
Function NewDictionary()
    Set NewDictionary = CreateObject("Scripting.Dictionary")
End Function
'And you must use Set when calling the function as well:
Set MyDic = NewDictionary 
'VBA functions that return objects cannot be used as 
'UDFs in a worksheet, with one exception. If the function 
'returns a Range object, it can be used as a UDF.    
    
'------------------------------------------------------------------

'Notes: In many programming languages, a function terminates 
'      	execution when the 'return' keyword is encountered. 
'      	VBA is different. Execution continues beyond the value
'		assignment to the function name... until the last line
'      	of the function is reached... or until an 
'		'Exit Function' statement is encountered.
    
'		The function Name can be used as a variable
'		inside the function as in this recursive function:
    
        Function Factorial(n)
            Select Case n
                Case Is < 2: Factorial = 1
                Case Else:   Factorial = n * Factorial(n - 1)
            End Select
        End Function
        
'		However, it is not always a great idea to use the function
'		name as a variable. It can be confusing in longer functions
'		and it has limitations. If the function is designed to return
'		a dynamic array and the function code attempts to ReDim the
'		function name, an compile-time error is raised.
    
'		As a side note, VBA can access hundreds of Excel's built-in 
'		worksheet functions. Here are three ways to access
'		the native FACT() worksheet function from VBA:
    
        MsgBox [FACT(7)]
        MsgBox Evaluate("FACT(" & 7 & ")")
        MsgBox WorksheetFunction.Fact(7)