FORMULA ONE 6.1 中自定义函数的实现 formula one 要实现自定义函数，首先要有一个DLL文件，在这个DLL文件中把所要实现的函数写在里面。然后在formula one 控件中调用 ADDIN 函数，把所加入的DLL文件引入注册一下即可使用在DLL文件定义的函数。下面以VB 为例说明一下怎么写这个DLL文件。 首先在VB中选择新建工程，在新建工程对话框中选择 ActiveX DLL 点击确定后进入代码编辑框后，首先把类模块中的类的名字改为 F1Functions (注：必须是这个名字)然后把类的属性Instancing 设为 5或是 MultiUse。在这个类中所有参数定义必须用BYVAL All arguments must also be passed ByVal. Note “ByVal” means “by value.” When a parameter is passed by value, anyone referencing that parameter is prevented from modifying that variable’s value. 接下来在这个类中实现自定义的函数。 下一步置工程的属性。在工程属性对话框中的启动对象设为NONE 线程模块中设为’单线程’，选中’升级Activex 控件’，其它的属性就用默认的即可。接下就可以生成DLL文件。下附Formulaone6.1的帮助说明 Formula One ActiveX Add-Ins in Visual Basic General Design Principles Formula One implements Add-Ins as ActiveX DLLs. To function as a Formula One Add-In, an ActiveX DLL must, at a minimum, implement a creatable class named F1Functions. When the DLL is registered with Formula One either through the API or through the Add-In Manager dialog, Formula One attempts to locate and create this class. Formula One only recognizes the DLL as a valid add-in if the DLL implements a creatable F1Functions class. Note In Visual Basic, the standard way to make a class creatable in an ActiveX DLL is to set the class’s Instancing property to a value of 5. Visual Basic describes such a class as MultiUse. Formula One treats any member of the F1Functions class that follows the guidelines set forth in this chapter as a worksheet function and ignores any functions not meeting the guidelines. All arguments must also be passed ByVal. Note “ByVal” means “by value.” When a parameter is passed by value, anyone referencing that parameter is prevented from modifying that variable’s value. 184 Formula One ActiveX User’s Guide As Formula One’s add-in capabilities increase, future versions may look for new classes in addition to F1Functions. To maximize compatibility with future versions of Formula One, add-in developers should refrain from using the prefix F1 on internal class names in their add-in DLLs?especially on classes that are creatable. Formula One presents the ActiveX DLL’s project description to end-users via the Add-In Manager dialog; therefore, the project description should be an informative phrase (typically no longer than a short sentence) describing the add-in DLL. The Project Description field can be found on Visual Basic’s Project Properties dialog under the General page. Thread Safety Although Formula One is single-threaded, future versions of Formula One may support multithreading. If so, Formula One’s calc engine will create a separate instance of F1Functions for each thread. This means that if your add-in functions use only automatic variables or data defined within the F1Functions class, you will not need to be concerned about thread safety. However, if you must refer to data outside of F1Functions (for example, if all threads must share common data), you are responsible for ensuring that your code is thread-safe. Add-In Function Requirements All arguments to an add-in function must be passed ByVal or the function will not be recognized by Formula One. To use the F1AddInArray and F1AddInArrayEx types, the Formula One control must be added to the project. Formula One recognizes functions that use the following data types (all arguments must be ByVal): Type Allowed as Argument Allowed as Return Value Boolean Yes Yes Double Yes Yes F1AddInArray Yes No F1AddInArrayEx Yes No String Yes Yes Variant* Yes Yes *Use Vari ant when a single function can accept or return values of different types. A Vari ant may also be of type vbEmpty. A Vari ant argument cannot accept a reference to more than one cell. If you need to accept such references, you may use F1AddInArray or F1AddInArrayEx instead. F1AddInArray Use the type F1AddInArray when using an area reference as an argument. You must add the Formula One control to your project to use this type. F1AddInArray will only accept a “simple” reference?a reference to a single 2d area. If you specify a 3d area or union reference, the formula evaluator returns #VALUE! without calling the add-in function. Use F1AddInArrayEx for support of 3d area or union references. The members of F1AddInArray are: Function Rows()As Long Function Cols()As Long Function GetArrayType()As Long Function GetValue(ByVal Row As Long,ByVal Col As Long) Function IterStart()As Boolean Function IterNext()As Boolean Function IterGetValue()As Variant Function IterGetValueEx(Row As Long,Col As Long)As Variant F1AddInArray.GetArrayType always returns F1AddIn2dArea. You may use IterStart and IterNext in a loop to iterate through the non-empty elements of the array. This can be much faster than examining each element individually when a sparsely populated array is expected. Example Code Dim Found As Boolean Found =TheArray.IterStart() While Found ’ Use IterGetValue or IterGetValueEx to ’ retrieve the value of the current element.. Found =TheArray.IterNext() Wend F1AddInArrayEx You may also use the type F1AddInArrayEx when using an area reference as an argument. Like F1AddInArray, F1AddInArrayEx accepts “simple” 2d area references. However, F1AddInArrayEx also accepts 3d area and union references. To only allow 2d area references, use F1AddInArray. You must add the Formula One control to your project to use this type. The members of F1AddInArrayEx are: Function Areas()As Long ’ Number of Areas in Array Function Rows(ByVal Area As Long)As Long ’Nbr Rows in Area Function Cols(ByVal Area As Long)As Long ’Nbr Cols in Area Function GetArrayType()As Long Function GetValue(ByVal Area As Long,ByVal Row As Long,_ ByVal Col As Long) Function IterStart()As Boolean Function IterNext()As Boolean Function IterGetValue()As Variant Function IterGetValueEx(Area As Long,Row As Long,Col As Long)_ As Variant F1AddInArrayEx.GetArrayType returns F1AddIn2dArea, F1AddIn3dArea or F1AddInRegion. Note You may use IterStart and IterNext in a loop to iterate through the non-empty elements of the array. This can be much faster than examining each element individually when a sparsely populated array is expected. Visual Basic Example Add-Ins Example 1 ’ ==ADDTHESE(1,2) ’ All arguments must be ByVal Function AddThese(ByVal X As Double,ByVal Y As Double)_ As Double AddThese =X +Y End Function Example 2 ’ ==CONCATENATETHESE("ABC","XYZ") ’ All arguments must be ByVal Function ConcatenateThese(ByVal X As String,_ ByVal Y As String)As String ConcatenateThese =X +Y End Function Example 3 ’ ==SUMOFRANGE(A1:C5) ’ All arguments must be ByVal Function SumOfRange(ByVal Range As F1AddInArrayEx)_ As Double On Error GoTo ErrorHandler Dim Sum As Double Sum =0 Dim GotOne As Boolean GotOne =Range.IterStart While GotOne Sum =Sum +CDbl(Range.IterGetValue) GotOne =Range.IterNext Wend SumOfRange =Sum Exit Function ErrorHandler: Err.Raise F1AddInValueError End Function Example 4 ’ ==MAKEERROR(TRUNC(RAND()*7)+1) ’ All arguments must be ByVal Function MakeError(ByVal WhichOne As Double)_ As Double Select Case WhichOne Case 1 Err.Raise F1AddInNullError ’ ##NULL! Case 2 Err.Raise F1AddInDivZeroError ’ ##DIV/0! Case 4 Err.Raise F1AddInRefError ’ ##REF! Case 5 Err.Raise F1AddInNameError ’ ##NAME? Case 6 Err.Raise F1AddInNumError ’ ##NUM! Case 7 Err.Raise F1AddInNaError ’ ##N/A Case Else Err.Raise F1AddInValueError ’ ##VALUE! End Select End Function
formula one 要实现自定义函数，首先要有一个DLL文件，在这个DLL文件中把所要实现的函数写在里面。然后在formula one 控件中调用 ADDIN 函数，把所加入的DLL文件引入注册一下即可使用在DLL文件定义的函数。下面以VB 为例说明一下怎么写这个DLL文件。
首先在VB中选择新建工程，在新建工程对话框中选择 ActiveX DLL 点击确定后进入代码编辑框后，首先把类模块中的类的名字改为 F1Functions (注：必须是这个名字)然后把类的属性Instancing 设为 5或是 MultiUse。在这个类中所有参数定义必须用BYVAL
All arguments must also be passed ByVal.
Note “ByVal” means “by value.” When a parameter is passed by value, anyone
referencing that parameter is prevented from modifying that variable’s value.
接下来在这个类中实现自定义的函数。
下一步置工程的属性。在工程属性对话框中的启动对象设为NONE 线程模块中设为’单线程’，选中’升级Activex 控件’，其它的属性就用默认的即可。接下就可以生成DLL文件。下附Formulaone6.1的帮助说明
Formula One ActiveX Add-Ins in Visual Basic
General Design Principles
Formula One implements Add-Ins as ActiveX DLLs.
To function as a Formula One Add-In, an ActiveX DLL must, at a minimum,
implement a creatable class named F1Functions. When the DLL is registered with
Formula One either through the API or through the Add-In Manager dialog,
Formula One attempts to locate and create this class. Formula One only recognizes
the DLL as a valid add-in if the DLL implements a creatable F1Functions class.
Note In Visual Basic, the standard way to make a class creatable in an ActiveX
DLL is to set the class’s Instancing property to a value of 5. Visual Basic
describes such a class as MultiUse.
Formula One treats any member of the F1Functions class that follows the
guidelines set forth in this chapter as a worksheet function and ignores any
functions not meeting the guidelines.
All arguments must also be passed ByVal.
Note “ByVal” means “by value.” When a parameter is passed by value, anyone
referencing that parameter is prevented from modifying that variable’s value. 184 Formula One ActiveX User’s Guide
As Formula One’s add-in capabilities increase, future versions may look for new
classes in addition to F1Functions. To maximize compatibility with future
versions of Formula One, add-in developers should refrain from using the prefix
F1 on internal class names in their add-in DLLs?especially on classes that are
creatable.
Formula One presents the ActiveX DLL’s project description to end-users via the
Add-In Manager dialog; therefore, the project description should be an
informative phrase (typically no longer than a short sentence) describing the add-in
DLL. The Project Description field can be found on Visual Basic’s Project
Properties dialog under the General page.
Thread Safety
Although Formula One is single-threaded, future versions of Formula One may
support multithreading. If so, Formula One’s calc engine will create a separate
instance of F1Functions for each thread. This means that if your add-in functions
use only automatic variables or data defined within the F1Functions class, you
will not need to be concerned about thread safety. However, if you must refer to
data outside of F1Functions (for example, if all threads must share common data),
you are responsible for ensuring that your code is thread-safe.
Add-In Function Requirements
All arguments to an add-in function must be passed ByVal or the function will not
be recognized by Formula One. To use the F1AddInArray and
F1AddInArrayEx types, the Formula One control must be added to the project.
Formula One recognizes functions that use the following data types (all arguments
must be ByVal):
Type
Allowed as
Argument
Allowed as
Return Value
Boolean Yes Yes
Double Yes Yes
F1AddInArray Yes No
F1AddInArrayEx Yes No
String Yes Yes
Variant* Yes Yes
*Use Vari ant when a single function can accept or return values of different types.
A Vari ant may also be of type vbEmpty. A Vari ant argument cannot accept a
reference to more than one cell. If you need to accept such references, you may
use F1AddInArray or F1AddInArrayEx instead.
F1AddInArray
Use the type F1AddInArray when using an area reference as an argument. You
must add the Formula One control to your project to use this type. F1AddInArray
will only accept a “simple” reference?a reference to a single 2d area. If you
specify a 3d area or union reference, the formula evaluator returns #VALUE!
without calling the add-in function. Use F1AddInArrayEx for support of 3d area
or union references.
The members of F1AddInArray are:
Function Rows()As Long
Function Cols()As Long
Function GetArrayType()As Long
Function GetValue(ByVal Row As Long,ByVal Col As Long)
Function IterStart()As Boolean
Function IterNext()As Boolean
Function IterGetValue()As Variant
Function IterGetValueEx(Row As Long,Col As Long)As Variant
F1AddInArray.GetArrayType always returns F1AddIn2dArea.
You may use IterStart and IterNext in a loop to iterate through the non-empty
elements of the array. This can be much faster than examining each element
individually when a sparsely populated array is expected.
Example Code
Dim Found As Boolean
Found =TheArray.IterStart()
While Found
’ Use IterGetValue or IterGetValueEx to
’ retrieve the value of the current element..
Found =TheArray.IterNext()
Wend
F1AddInArrayEx
You may also use the type F1AddInArrayEx when using an area reference as an
argument. Like F1AddInArray, F1AddInArrayEx accepts “simple” 2d area
references. However, F1AddInArrayEx also accepts 3d area and union references.
To only allow 2d area references, use F1AddInArray.
You must add the Formula One control to your project to use this type.
The members of F1AddInArrayEx are:
Function Areas()As Long ’ Number of Areas in Array
Function Rows(ByVal Area As Long)As Long ’Nbr Rows in Area
Function Cols(ByVal Area As Long)As Long ’Nbr Cols in Area
Function GetArrayType()As Long
Function GetValue(ByVal Area As Long,ByVal Row As Long,_
ByVal Col As Long)
Function IterStart()As Boolean
Function IterNext()As Boolean
Function IterGetValue()As Variant
Function IterGetValueEx(Area As Long,Row As Long,Col As Long)_
As Variant
F1AddInArrayEx.GetArrayType returns F1AddIn2dArea, F1AddIn3dArea or
F1AddInRegion.
Note You may use IterStart and IterNext in a loop to iterate through the non-empty
elements of the array. This can be much faster than examining each element
individually when a sparsely populated array is expected.
Visual Basic Example Add-Ins
Example 1
’ ==ADDTHESE(1,2)
’ All arguments must be ByVal
Function AddThese(ByVal X As Double,ByVal Y As Double)_
As Double
AddThese =X +Y
End Function
Example 2
’ ==CONCATENATETHESE("ABC","XYZ")
’ All arguments must be ByVal
Function ConcatenateThese(ByVal X As String,_
ByVal Y As String)As String
ConcatenateThese =X +Y
End Function
Example 3
’ ==SUMOFRANGE(A1:C5)
’ All arguments must be ByVal
Function SumOfRange(ByVal Range As F1AddInArrayEx)_
As Double
On Error GoTo ErrorHandler
Dim Sum As Double
Sum =0
Dim GotOne As Boolean
GotOne =Range.IterStart
While GotOne
Sum =Sum +CDbl(Range.IterGetValue)
GotOne =Range.IterNext
Wend
SumOfRange =Sum
Exit Function
ErrorHandler:
Err.Raise F1AddInValueError
End Function
Example 4
’ ==MAKEERROR(TRUNC(RAND()*7)+1)
’ All arguments must be ByVal
Function MakeError(ByVal WhichOne As Double)_
As Double
Select Case WhichOne
Case 1
Err.Raise F1AddInNullError ’ ##NULL!
Case 2
Err.Raise F1AddInDivZeroError ’ ##DIV/0!
Case 4
Err.Raise F1AddInRefError ’ ##REF!
Case 5
Err.Raise F1AddInNameError ’ ##NAME?
Case 6
Err.Raise F1AddInNumError ’ ##NUM!
Case 7
Err.Raise F1AddInNaError ’ ##N/A
Case Else
Err.Raise F1AddInValueError ’ ##VALUE!
End Select
End Function