Topics

  1. What is function binding?
  2. Types of binding
    1. Local vs Remote bindings
    2. Static vs Virtual bindings
    3. Object vs Handle
    4. Direct vs Indirect
    5. Sync vs Async
  3. Supported bindings
    1. Restrictions
  4. Performance considerations
  5. Binding mechanisms
    1. Binding scripts
    2. Binding attributes
    3. Auto-binding

What is function binding?

Cubicle Tools allows users to define new Excel worksheet functions that call .NET methods. This process is called Function Binding.

Excel-DNA already allows users to define new worksheet function that call static .NET methods. Cubicle Tools expands on this functionality to allow parameter resolution through the Object Handler, calls to non-static methods, calls to remote servers and asynchronous method calls.

There are five factors that determine the behaviour of a binding. These are described below.

Types of binding

Local vs Remote bindings

A binding can either be classified as Local or Remote. Local bindings are for method calls in the same AppDomain as the Cubicle Tools add-in. Remote bindings create functions that call methods on a remote server of into a different AppDomain.

Static vs Virtual bindings

As the category name suggest, Static bindings are against class methods and Virtual bindings are against instance methods.

Object vs Handle

This category is only applicable to Virtual bindings. When a binding against an instance method is defined as Handle the worksheet function will not guarantee a call to the same class instance. Instead the corresponding handle reference will be resolved first, to find the target of the invocation.

The assumption is that the handle will always contain a valid target for the function call. If the handle is removed from the repository or replaced with an object of a different Type, the method call will fail.

Handle bindings can cause instability if users are not careful but it can be useful if a users wants to manipulate the behaviour of a function via the object repository.

Direct vs Indirect

Indirect bindings will create a function that resolves function parameters via the Object Handler and allows the caller to store the result back to the repository. An Indirect binding will add an optional __key parameter to the end of the method signature. If the user provides a value to the key parameter the result of the function call will be stored in the Object Handler and a handle will be generated from the key. See Handler Generation for more information.

Suppose an add function was bound as Indirect to a .NET method that sums up two parameters with the following signature:
double add(double a, double b)

Then the newly bound Excel Function will have the following signature:
double add(object a, object b, optional string __key)

Suppose there are two System.Double instances in the Object Handler with handles _:0000A and _:0000B with the values 2 and 3 respectively. Then the function call
=add(2,3)

will be equivalent to
=add("_:0000A", "_:0000B").


Furthermore, the function call
=add(2,3,"!five")

will store the result in the Object Handler and return the handle, !:five, to Excel.

Sync vs Async

Sync bindings are for standard function calls and Async bindings will perform an asynchrous call. Async bindings would typically be used for long-running tasks.

Supported bindings

Inherently, Excel-DNA only supports Local+Static+Object+Direct+Sync aka LSODS bindings. All other supported combinations are new.

Perf L/R S/V O/H D/I S/A Description
A Local Static Object Direct Sync LSODS, the default Excel-DNA binding (fastest invocation). No parameter resolution.
B Local Virtual Object Direct Sync A binding to an instance method without parameter resolution
B Local Virtual Handle Direct Sync A binding to a handle instead of the class itself without parameter resolution.
C Local Static Object Indirect Sync A binding to a class method that resolves parameters vis the repository.
C Local Virtual Object Indirect Sync A binding to an instance method, that resolves parameters via the repository.
C Local Virtual Handle Indirect Sync A binding to a handle instead of the class itself with parameter resolution.
D Remote Static Object Indirect Sync A binding to a class method on a remote server
D Remote Virtual Object Indirect Sync A binding to an instance method on a remote server
X Local Static Object Indirect Async An asyncronous static method call with parameter resolution.
X Remote Static Object Indirect Async Not supported yet.


Restrictions

ExcelDNA does not currently have a mechanism to add custom function descriptions or parameter help to runtime bound functions. The only way to add this detail on Local+Static+Object+Direct+Sync bindings by adding the ExcelFunctionAttribute to the source.

This also means that, in the case of overloaded methods, the only way to bind both is to bind them as Indirect and provide them with different function names in the bind script.

Performance considerations

The above binding types have been broadly categorised by performance, where category A is the most and D is the least performant.

Binding mechanisms

There are two mechanisms for defining custom function bindings
  • In code via custom attributes
  • Using an XML script that maps .NET methods to worksheet functions

Binding attributes

Custom attributes can be defined in code that will define the binding characteristics of the worksheet function. These are only valid for Local bindings and will be utilised when the assembly is loaded into the AppDomain of the Cubicle Tools Add-In.

Three custom attributes are available:
  • ExcelCommandAttribute - Part of Excel-DNA
  • ExcelFunctionAttribute - Part of Excel-DNA
  • BindingAttribute - An extension of ExcelFunctionAttribute with additional parameters
    • BindToHandle (default = false) - True for Handle bindings, Object otherwise. For future use.
    • ResolveParameters (default = false) - True for Indirect bindings, Direct otherwise.
    • IsAsync (default = false) - True for Async bindings, Sync otherwise.

Binding scripts

XML scripts can be loaded at any point via the exoRunScript worksheet function for binding additional functions in Excel.

XML scripts are the most flexible and currently the only way to bind worksheet functions to remote servers.

Bind scripts contain a series of <FunctionDescription> and <CommandDescription> elements. The table below shows all the attributes supported by the bind script schema. However, some of them cannot be utilised in the current (v0.3) version of ExcelDNA. The table shows how the bind-script compares with Excel attributes in terms of binding features.

bindingfeatures.png

Auto binding

The Cubicle Add-In will automatically execute all bind scripts in the \autobind directory in alphabetical order.

Last edited Apr 29, 2013 at 9:58 PM by Tjaart, version 7

Comments

No comments yet.