Wednesday 7 May 2008

GigaSpaces VB API Beta Release

GigaSpaces VB API version 1.0 is now available under

Up and above some enhancements and stability fixes, the main addition in this release is native notification support for VBA applications.

The distribution includes a comprehensive example of notifications, which includes also (as a side effect) interoperability with Java. It is a simulation of FXSpot Pricing System in which prices are updated by an external system (the Java feeder in our case), and at the other end there is a spreadsheet which is listening to the FXSpot updates.

Here are the basics of the example:


@SpaceClass(persist = false, fifo = false, replicate = false)
public class FXSpot {
    private String fxName;
 private double modified;
 private float value;    

 @SpaceId(autoGenerate = false)
 public String getFxName() {
  return fxName;
 public void setFxName(String _fxName) {
  fxName = _fxName;
 @SpaceProperty(nullValue = "0")
 public double getModified() {
  return modified;
 public void setModified(double _modified) {
  modified = _modified;
 @SpaceProperty(nullValue = "0")
 public float getValue() {
  return value;
 public void setValue(float _value) {
  value = _value;

The counterpart PONO (I have omitted the COM methods):

    [ClassInterface(ClassInterfaceType.AutoDual), ComVisible(true)]
    public class FXSpot
        private string _fxName;
        private double _modified;
        private float _value;
   public FXSpot()
            _fxName     = null;
            _modified   = 0;
            _value      = 0;

        [SpaceID(AutoGenerate = false)]
        [SpaceProperty(AliasName = "fxName", NullValue = "")]
        public string FXName
            get { return _fxName; }
            set { _fxName = value; }

        [SpaceProperty(AliasName = "modified", NullValue = 0)]
        public double Modified
            get { return _modified; }
            set { _modified = value; }

        [SpaceProperty(AliasName = "value", NullValue = 0)]
        public float Value
            get { return _value; }
            set { _value = value; }

Java feeder – After looking up the space, and initializing Random helpers and currency names, it looks like:

    FXSpot spot = new FXSpot();

    double modified = (double)System.currentTimeMillis(); 

    // convert to Excel date
    modified = (modified + 7200000) / 86400000 + 25569;


    System.out.println("Writing spot = " + spot); 

    spaceProxy.write(spot, null, Lease.FOREVER);


Up until now, nothing new really… Now begins the interesting bit!

After omitting the initialization parts of the VBA code, what we have is the following:

Registration function:

Function RegisterNotifications(ByVal sRng As String)
    If GigaSpace Is Nothing Then
        Call Init
    End If
    Dim rng As range
    rng = range(sRng)
    Dim Template As FXObjects.FXSpot
    Template = New FXObjects.FXSpot
    Template.fxName = rng.Value
    Dim modifiers As Integer
    modifiers = NotificationModifiers.Update 

    Call GigaSpace.RegisterNotification(Template, "Notify", modifiers, sRng)
End Function

It is very easy to explain the code. If you have ever read the docs or used GigaSpaces’ notifications API, you should feel pretty comfortable here. Same rules apply… If you haven’t, a quick browse here will help understanding the API and terminology better.

You simply invoke GigaSpace.RegisterNotification method with a template to match, the name of your notify function, notify modifiers (take, write, update, all), and last argument which is not part of GigaSpaces notify API is called vbHint. It can hold any object the user wishes to receive back when the notify function is triggered for this specific registration (i.e. template match and operation match). In this example I have iterated through a list of currencies, and I pass the cell address of the specific currency to the registration so that when my Notify method is invoked, I can lookup the relevant cell easily.

My Notify function:

Function Notify(ByVal eventType As Long, ByVal pono As Object, ByVal vbHint As Object)
    Dim sRng As String
    sRng = vbHint
    Dim rng As range
    rng = range(sRng)
    Dim spot As FXObjects.FXSpot
    spot = pono
    rng.Offset(0, 1).Value = spot.Value
    rng.Offset(0, 2).Value = spot.Modified
End Function

Note the Notify signature. When registering to notifications, the notify function must have this signature in terms of the types it takes as arguments. The eventType represents the space operation which triggered the notification, and the pono is the object which matched the registration’s template and triggered the notify.

In this example, I take the values from the notification object, and update the relevant Excel cells (I use the vbHint for that).

The example includes a button to UnRegisterNotifications.

Public Sub UnReg()
    If Not (GigaSpace Is Nothing) Then
        Call GigaSpace.UnRegisterNotifications
    End If
End Sub

The UnRegisterNotifications method removes all registrations for the invoking Excel from the space.

One more thing to note is the termination of the Excel process. As notifications require some daemon threads to run at the background, the Excel needs to invoke GigaSpace.Terminate function which cleans up these threads and frees some resources. Failing to invoke the method could result a hanging Excel process. As a best practice, you should catch the Excel workbook close event and call the terminate method.

Example (at the workbook level):

Private Sub Workbook_BeforeClose(ByVal Cancel As Boolean)
    Call TerminateGigaSpace
End Sub

Which invokes the Terminate method:

Public Function TerminateGigaSpace()
    If Not (GigaSpace Is Nothing) Then
        Call GigaSpace.Terminate
    End If
End Function

The example with inline comments and documentation is packed in the setup. After downloading and completing the installation process, the example Excel can be run from --> Programs --> VB Space API --> Examples --> FXSpot Notify. The example is located under \Examples\FXPrices. Follow the readMe.txt to run and change the example.




Unknown said...


This is a great project! Keep up the work on it:).

I'm looking forward to hearing some feedback from users, so as soon as you do, please tell us all about their experiences with it.

Good luck in our Internal OpenSpaces Developer Challenge!


dekel said...

This is cool stuff! I'm being asked a lot about VBA by our Excel prospects, who wants to preserve their existing investment and do not plan to move to managed code and Excel 2007 in the near future. This will definitely provide me with an interesting topic for discussion.

Unknown said...

Very impressive use of Gigaspaces OpenSpace technology .Net and Excel,this should make delivery much more simple and fast.

Pratik Pandya said...


Great project, especially the event notification stuff. This is really cool and just the 'clincher' we were looking for. This should give the pilot project we are doing(and that you have ben heavily involved with) a huge selling point and a competetive edge!.

just as background: We are currently piloting a Grid/Space based approch for our Front Office energy trading framework/platform and while most people appreciate the benefits, the management has been nervous about the additional investments required in retraining and/or hiring additional suitably skilled people to make this a success with the people who will be using this system the most. We have a massive investment in Excel/VBA based applications and it would have been very difficult to 'sell' a grid/space based solution in our environment without such an API.

This should enable our financial analysts/mathematicians who have Excel and VBA skills to leverage the power of the Space based solutions without having to undergo a steep learning curve to master the Java or C#/.Net APIs(assuming they have the time or inclination to do this in the first place!).

Great work that in my opinon should become a part of the core product!

Pratik Pandya, Technical Consultant
(RWE Systems, Front Office Trading).

Anonymous said...


Great project and fantastic implementation.

It takes the RTD server of Microsoft apart and especially integrated within the Events model of the Spaces makes the 'timer' approaches of Microsoft a thing of the past!

1. It adherents with the COM event model of excel, VB(A) & VB Scripting and indeed such an extension enables to bring data within COM clients in a stable asynchronous fashion (as far as COM is concerned).

2. Its potential within trading environments is enormous as it allows taking advantage of grid technologies without overloading the clients and indeed “inherent” from the threaded model of the grid.

3. It enhances Excel from a read-only to a “proper” GUI that allows updates.

4. Within quantitative environments, it provides RAD development ability and sharing the results back to the grid. This means that algorithms, logic, etc. can also be distributed and implemented at the client not only middleware server. Great idea for changing/multiplying the faces of the middleware.

5. I am sure that can also reduce deal turn-around cycles.

6. Last but not least the COM legacy stuff can be integrated within the grid which means lower development costs!

As Pratik said, I do too believe that it should become part of the core-product as indeed opens new niche markets to gaga-spaces across many industries.

Please keep up the good work and indeed keep us posted!

Panos Karamanis
Senior Quantitative Structurer
RWE Trading & Marketing
Structuring & Valuation