Ejemplo 1: excel cómo devolver un resultado de una función VBA
'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)Ejemplo 2: excel VBA devuelve una función de resultado
'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)¡Haz clic para puntuar esta entrada!
(Votos: 0 Promedio: 0)