HAMMER Alongside, as a COM Server

Although it has always been possible to call HAMMER from within VBA via the Application.Run method, this is a somewhat clunky way of doing so and it can also be very inefficient, particularly for tight loops. But now, with this release (V 1.2.0 (Beta) ) core functionality is exposed as a COM Server, which means easier and more efficient interfacing between VBA and the .NET multi-threaded enabled world of the datasmith’s HAMMER.

I call this “HAMMER Alongside”, to differentiate it from HAMMER Inside where I use HAMMER internals to craft stand alone XLLs. With this COM Server method, the standard HAMMER add-in (installed or just-in-time registered) is needed alongside either a VBA add-in or a VBA enabled workbook. The extra “moving parts” are easily justified by the extra flexibility that this method allows (particularly to those with a reasonable grasp of VBA, but perhaps lacking any familiarity with the .NET world).

By learning a small amount of IronPython (here’s a good starting place) it’s relatively easy to hook up VBA to any .NET library through HAMMER, without having to invest time and money in learning heavy duty development environments such as Visual Studio. Using “Internal Threads, it’s also possible to take advantage of .NETs multi-threading capability from VBA. (Note: both these options require .NET4).

So with VBA becoming a first-class language with regards to HAMMER, why offer Python as an alternative scripting choice? Well, one reason is to offer a means to access .NET’s power as per the last paragraph. But, the primary reason, is related to the ability of HAMMER transformations to be “detached” from Excel and run via HAMMER’s command line tools. The .NET 4 version of the command line tool supports Python, so it is possible to initially build out a micro-ETL transformation within Excel (using Python to perform the business logic alongside SQL) and then transfer that logic to the command-line tools with a minimum of modification.

This could be useful , for example, when a PowerPivot model moves to the server, its related HAMMER-powered micro-ETL processing could do likewise (most enterprise ETL tools support call-outs to command-line processes).

The COM server can be accessed from VBA only via late-binding like so:

Set comServ = CreateObject(“hammerCOMServerV1”)

The server’s methods are:

  • hammer(…) – works the same way as the UDF version.
  • hammerVersion() – returns HAMMER version, again the same as UDF version.
  • hammerVersionOK(version) – e.g.  isOK= comServ.hammerVersionOK(120) will return TRUE if the current version is >= V1.2.0
  • arrayResize(anArray) enables the creation of “toFit” UDFs.
  • arrayToSheet(anArray) likewise for “toSheet” UDFs.
  • arrayToRange(anArray,pasteToWhereString) enables “toRange” UDFs.
  • hammer_ppRefresh_inline(optional table,optional timeout) – refreshes a PowerPivot model, again the same as its UDF equivalent.

Download the latest version of HAMMER from here …

Advertisements

9 responses to “HAMMER Alongside, as a COM Server

  1. infinitybraid

    Tom,
    I love the new hammertorange feature but unforunately I’ve had a number of crashes since installing this release. Most notably some of my larger models no longer have the ability to save without a crash. I can recalc and execute hammer calls fine but if i try to save without first disabling the add in it crashes without fail. The behavior came after the upgrade to hammer even not directly testing new features.Any idea what could be causing the failure. Running on 32bit 2010 win7 64 bit.

    Regards,
    Sean

  2. Sean,

    Are these models, using the new features and if so, which ones? Which version of the add-in (check with the HAMMER_Version() function). What error messages and what sort of crash?

    Tom

  3. Sean,

    That should have been HAMMERVersion().

    I’ve found something which might be the cause, so could you try this version …

    http://bit.ly/seantest1

  4. As for your quick fix, I tried it on one of my problem models and it took a bit longer to reload but appeared to recalc and save without issue. I will try to test more extensively over the next few days

    Since I hadn’t noticed a problem until after leaving a workbook open over night and then attempting a save in the early AM. I’m not too sure where exactly things went South. I had primarily been testing the hammerToRange() function to output results into the top left hand cell of an excel table. I had actually went back to V 1.1.1 (Beta)NET4.0 Excel 2007 & 2010 32bit – Python included; but was experiencing some difficulties there as well. The last version I can recall without significant regression was V1.

    I noted your warning about possible endless loops but outside of ensuring I don’t have a hammer call that inadvertently triggers a circular “hammer storm”
    I’m not positive on what else I should try to avoid.

    -Sean

    • Since V1.0.0 the major changes have been the introduction of COM aware commands, such as XLRANGE and the toRange functionality, these required me to change the type of my function calls (from a type 1 (no interaction with sheets other than via primitive types, limited but very fast, original old fashioned C UDF) to Type 2 UDFs (can access a lot more information about the workbook, effectively like VBA macros)). I had forgotten to make this change for some functions (hence the quick fix). Not changing the type usually doesn’t cause immediate errors but can result in the type of failure you witnessed (it’s to do with COM proxy references getting lost I think).

      I think it may have been you that I had the conversation with on Twitter re my reluctance to link HAMMER more deeply into the Excel Object Model (mainly to keep it Excel friendly but not Excel dependent, so that command-line HAMMER could be used to off-load ETL-type work if necessary). But I couldn’t resist the ease-of-use of toRange etc. 🙂

      Such COM interactions are much slower than the core array-formula interface and I’ll probably make a breaking change to require activation prior to enabling the COM based functionality (will definitely do it for the latest COM Server stuff).

      Keep an eye on it and let me know if have any more problems.

      By the way, when you say “it took a bit longer to reload” what do mean, the initial registration of the add-in, or individual calls to HAMMER? How much longer?

      Tom

  5. Tom,

    Yes that was me you spoke with via Twitter. I agree that it is nice to be able to leverage Hammer via cmd line calls but at the same time it is even more enticing (at least from this side of the fence) to get most of this functionality within excel. So personally I don’t mind concessions to the cmd line side for the sake of excel simplicity. Using it as a trigger for a more robust ETL process is enticing

    Speed wise I don’t think I have any slow downs now that I’ve run a few tests. After performing a full recalc and saving the initial troubled model it seems to be humming fine.

    Two observances about the hammerToRange function:
    If I use hammerToRange to output to the upper left cell of an existing excel table (2007+) I would expect that the result should replace a table’s content headers and all. then the result is as expected however even though my existing table remains in tact (same name, etc. with new results) all formulas with references to that table are now severed and replaced with the dreaded #REF! error. My thought was that perhaps this was because you were tearing down and rebuilding the table behind the scenes so in the process the reference was severed. My workaround then was to try to write the entire set of data with no headers directly to the table body, but this also caused a reference error. Can you enlighten me as to why my approach to using hammer to update an excel table in this manner is flawed? Or indeed if it is at all possible?

    The other side effect I noted w/ hammer to range is if you don’t specify a sheet name in your string reference it will work against you (ie: using C11 instead of Sheet1!C11). If you have Automatic calculations turned off the results of a hammerToRange function will actually output to the currently active worksheet when you recalculate regardless of where the function itself exists. I’m not positive if this is by design or not, but it certainly surprised me the first few times it overwrote my data. 😉

    My other ob

    • Sean,

      re: 1st Observation

      If a table falls within the output area of a toRange the function simply remembers the name of a table, clears the entire table (headers and all), pastes the data as a normal range, and then reconstructs a new table with the same name (hence the #REFs etc). The sort of functionality that say a data source linked table offers (maintaining formulas and references etc.) is not exposed via an API, so that’s the best I can do (well I could search and remember formulas and re-add with the new table, but I’d still end up with #REFs). The pre and post macros can be used to get around some of the problems. (The main use case for rebuilding a table with the same name was to make re-loading PowerPivot linked tables easier).

      re: 2nd Observation.

      Yes that is to be expected, the address is not a pointer reference as would be the case with a normal range reference, its a string that’ll be raw evaluated by a Range Object like so Range(“A2”), so the address operates in the current context (not the functions initial context). You should always fully describe the string reference, including sheet name and, if multiple workbooks likely, the workbook name. Not really by design, just a consequence.

      Tom

  6. <>

    Just a thought Tom, could you not convert the table to a range, paste the data, and then format as a table again?

    • Hi Bob,

      I could but then I’d end-up with the problem of a new query returning fewer rows (or columns) than the one it replaces. So even if I convert to a range, I still need to issue a clear data (which affects the “CurrentRegion”, hence the need to surround all such tables – Excel or range – with a whitespace buffer) so I’ll still trigger #REFs

      Tom