Sign in Get Started

xl-vba how to return result from a function

Dfm 
'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)
View another examples Add Own solution
Log in, to leave a comment

4
4
Jedians 100 points 

                                    '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(&quot;Scripting.Dictionary&quot;)
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 &lt; 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(&quot;FACT(&quot; &amp; 7 &amp; &quot;)&quot;)
        MsgBox WorksheetFunction.Fact(7)

4 (4 Votes)
0
Are there any code examples left?
Find Add Code snippet
New code examples in category VBA
SHOW MORE
Create a Free Account
Unlock the power of data and AI by diving into Python, ChatGPT, SQL, Power BI, and beyond.
Sign up
Develop soft skills on BrainApps
Complete the IQ Test
Relative searches
return vba excel function return in function vba excel vba call function and return value return from vba function functions in vba return value vba excel function return excel vba functions return access vba function return value excel vba create function return value return function in excel vba excel vba method return value vba excel functions return value vba: return value from sub vba: use excel formular put the result in variable vba returning value excel macros - return a value return vba function excel vba function return value vba get value from function how to return a value in vba return value in vba vba functions excel vba function excel-vba how to return result from a function xl-vba how to return result from a function xl vba how to return result from a function excel vba how to return result from a function excel vba how to return a result from a function excel-vba how to return a result from a function excelvba how to return a result from a function vba how to return a result from a function xl-vba return a result from a function xl vba return a result from a function excelvba return a result from a function excel-vba return a result from a function excel vba return a result from a function excel return a result from a VBA function How to return a result from a VBA function How to return a result from a VBA function3 vba sub return string function in VBA return a value from excel function vba functions in vba ms excel vba return value from function function vba return return value from function vba vba return value form function return a value in basic how to develop a vba function the recieve value return value vba funtion return hpw to return a function in vba return command vba Get Value from Method VB ms excel vba return equivalent vba code to write function with return value return from function vba function returning type in vba return value vba vba function return value example how to use function return in vba vba return a value from a function vba return value from sub excel vba return value from function return in vba function return function vba vba use function return returning value from sub vb return value in function vba function VBA vba return variable function vba return value return a value in vba vba function return value vba return value how to return value from function in vba vba return value from function excel vba return how to return a value from a function in vba excel what does .value return excel macro vba return function value vba module return value function that returns a value in vba excel vba returning value from function return in vba excel vba function return a result from sub in vba return vba output string from sub in vba vba return sub that returns a value to a variable in vba how to return a value from a subroutine in vba function return vba vba return function vba function return excel vba return value excel VBA return a result function excel VBA return function result excel VBA how to return a result from a function excel how to return a result from a VBA function
Made with love
This website uses cookies to make IQCode work for you. By using this site, you agree to our cookie policy

Welcome Back!

Sign up to unlock all of IQCode features:
  • Test your skills and track progress
  • Engage in comprehensive interactive courses
  • Commit to daily skill-enhancing challenges
  • Solve practical, real-world issues
  • Share your insights and learnings
Create an account
Sign in
Recover lost password
Or log in with
Not registered?
Sign up takes less than a minute

Create a Free Account

Sign up to unlock all of IQCode features:
  • Test your skills and track progress
  • Engage in comprehensive interactive courses
  • Commit to daily skill-enhancing challenges
  • Solve practical, real-world issues
  • Share your insights and learnings
Create an account
Sign up
Or sign up with
By signing up, you agree to the Terms and Conditions and Privacy Policy. You also agree to receive product-related marketing emails from IQCode, which you can unsubscribe from at any time.
Creating a new code example
Code snippet title
Source