Dealing With Excel Arrays

One of the nice things in Excel is the ability to treat scalar, any size vector, and any size matrix in the same way (from the developer's perspective). This is very powerful, and saves a lot of development time.

It gets interesting when you add COM and C# into the picture… I am working on a demo which includes Excel and C# (more on the demo in a different post), and one of the things we needed to do is pass arrays from the spreadsheet to our C# logic. We are using COM for the interaction. This is where the problem starts. When you fill up an array in Excel and pass it over COM, it is received at the other side as an object, not as an array, and you can not access the array's elements.

The solution for that is to use reflection and to interrogate the object using simple array methods. It took us some time to find how this should be done properly (thanks to PK), and this is it:

First of all, let's assume we have a range of doubles in your spreadsheet. I named the range "double_range":





Next, I have a utility function which populates a VBA array with the values:





My VBA code which passes the array, looks like:



My C# object does not use an attribute setter, but rather have a setter method which invokes a translator utility function:



Translator function is the most important piece of this puzzle:



I have uploaded the code snippets into a text file here.

The VBSpaceAPI will include such helpers in its next release and Will support arrays natively.

-Guy

VB Space API Alfa Release

An Alfa release of the Space VB API is now available at OpenSpaces.org.

There are a number of changes in this build. Main ones are a new installer, a fix for default value in read and take APIs, and a more comprehensive example and documentation pages.

You can read more about it in the project pages, but one thing I want to specifically mention in my experience with the installer program.

It has been very long since the last time I had to compile an installer program. Actually, it was around 1998, where I created a setup for a university project. It was based on InstallShield, which made the entire process long and painful! This time it was very different. I used a utility called Inno Setup, a very lightweight and simple installer. It took me no more than 10 minutes to create the first run of the setup, and another half an hour or so to customise it with my own specific stuff (making sure GigaSpaces is installed, invoking the example at the end of the setup, etc'), which was also straight forward to do thanks to the detailed help files provided.

Now, surely InstallShield could do everything I needed and much more, but it would have been much harder and a longer process... Kudos to Jordan Russell for the well designed and implemented installer utility!

Enjoy!

-Guy

Google's Cloud Is Now Public

Well almost...

Google has announced AppEngine, which is, in essence, a platform for developing web applications using Google's platform, and deploying them into Google's cloud.

Google like Google, released AppEngine in a very controlled way, granting access to the first 10,000 users who registered to the Beta program. Too bad I wasn't quick enough… I am sure that the user-experience and the set of features provided will make developing and deploying web apps into Google an easy and nice experience.

As for the release being a Beta, that's also a nice trick by Google. It really doesn’t mean that AppEngine is not mature yet, it's just a way to lower our expectation a bit I guess. Take GMail for instance, it's still at Beta stage!

A very detailed description of the offering from Brady Forrest, which also discusses some differences between AppEngine and Amazon's AWS

-Guy

VB Space API 0.1 Is Released

I have released the first version of the VB API as a Pre-Alfa under OpenSpace.org. The main purpose of this early release is to gather feedback from prospective users, and build-up a proper roadmap. The backlog is filling up with stuff such as install program, batch operations, notification, and additional space functionality, and I'd appreciate your input and rating as to those or other items.

The release includes a nice little Excel file as an example, where you can interact with the space to write and read Person objects. The Excel file includes comments which explain how the example should be used, whereas the code, being very simple and self-explanatory, does not require extra elaboration in my opinion. Follow the installation and example instructions and give it a try. Comments are welcomed!

Technical background and limitations – The VB API uses GigaSpaces C# API over COM. This means that I have a C# class which wraps GigaSpaces' C# API, and is exposed as a COM class. From this point onwards, the VB application communicates with my C# layer using COM.

There is a number of VB / COM limitations which I had to work around. I am still looking for ways to overcome these issues. One of the things you will note is that the Read API takes two arguments: a template, and a default value (there is an overloaded Read which also takes a blocking timeout as a parameter):

This is because VB over COM does not allow you (or I haven’t yet found the proper way) to return a null value from the C# layer and set it to the Response object, nor did I find a way to cast COM's return value. So when there is no result back for the template match in the space, I could not simply return null. I am looking at generics support for VB / COM, in which case the problem is solved as I can do something like: return default(T);

Another limitation I found is the inability to specify a VB class as serializable, which means that you need to declare your business classes using C#. It's a fairly quick and straight forward task, though I am still googling for a proper solution which will allow users to stay in the VB environment from start to end. If that proves to be impossible I can think of means such as generators to ease this process.

In next posts I will demonstrate how to create a full example from scratch.

-Guy

GigaSpaces VB API

GigaSpaces provides scaling solutions for Excel, a.k.a. "Excel That Scales"

A lot have been said and written about it, and there is plenty of collaterals, examples, and webcasts. That's all good and fine, but what I would like to suggest in this post is a simplified way to access the space from within an Excel spreadsheet and / or any other VB application.

I am targeting Excel or VB developers who require simple means to interact with the space, and prefer not to exhaust their C# skill-set.

As an example, say you want to write a Person object into the space. Using the C# as an access layer to the space, you will need to implement your Person C# class, and space accessibility methods. See the following example for more details (replace HelloMsg with Person…)

What if you could work with the space API from within your Excel code without having to develop an access function (WritePerson() in our case) in C#? I.e. What if you had a reference to VB Space proxy? Think of your code looking something like the following (using the Person example):



Sub WriteToSpace()
Dim aPerson As Person
Set aPerson = New Person

aPerson.FirstName = ActiveCell.Offset(1, 0).Value
aPerson.LastName = ActiveCell.Offset(2, 0).Value
aPerson.Age = ActiveCell.Offset(3, 0).Value

Call GigaSpace.Write(aPerson)
End Sub

And to read a Person object, you'd have to do something like the following (note that I filter the person according to the current user's selection as first name, and then just print the last name and age):

Sub ReadFromSpace()
Dim Template As Person
Set Template = New Person

Template.FirstName = ActiveCell.Value

Dim Response As Person

Set Response = GigaSpace.Read(Template)

ActiveCell.Offset(1, 0).Value = Response.LastName
ActiveCell.Offset(2, 0).Value = Response.Age
End Sub

In this example all you need to do is define your Person object (as a space class using C#), and include it in your COM libraries. The rest is your good old VB code working with the space API…

If you like this approach, stay tuned. I am working on the first version of the API, which will be released fairly soon.

-Guy