Using EssCalc in Excel

There was a question on the Network54 board about our EssCalc component and how to use it in Excel. I didn't know anyone was still using the code, which was last touched in the VB5 days, but I thought the answer may make a good blog post.

EssCalc is a free utility that I wrote many years ago to show how to create and execute custom tokenized calculations from VB. It is, however, just as usable from Excel. To use it from Excel, follow these steps:

1. Download the code from the downloads area of our website at http://www.appliedolap.com/.
2. Extract the code to a directory.
3. In the VBA editor, import the file CEssCalc.cls into your project.
4. Confirm the API declarations in CEssCalc match those for your version of Essbase.
5. Import the file essxlvba.txt to get the Excel VBA declarations.
6. Instead of using the code in modMain to get a context handle (hCtx), use the Excel functionEssVGetHctxFromSheet.
7. Use the class to create/run a calc.

Here is the sample code that works on my system (including a calc script with a valid syntax):

Sub RunCalc()
Dim oCalc As New CEssCalc ''' calc object
Dim hCtx As Long

On Error GoTo ErrorHandler

''' get the hctx
hCtx = EssVGetHctxFromSheet(GetSheetname())

''' the context handle is required
oCalc.hCtx = hCtx

''' this is how you get a file off the server
'oCalc.CalcFile = "Test"

'oCalc.CalcFileLocal = "c:\temp\test.csc"

''' This is how you create a script in code
With oCalc
.AddLine "FIX(""T.MARKET"", ""T.PRODUCT"",""T.SCENARIO"") ", True
.AddLine " CALC DIM(""Measures""); "
.AddLine "ENDFIX;"
End With

''' this is how you show the calc string
MsgBox oCalc.CalcString

''' set the process state check to 2 seconds
oCalc.Interval = 2000

''' this how you replace one token
oCalc.ReplaceToken "T.MARKET", "New York"

''' replace a bunch of tokens (if they exist)
Dim cTokens As Collection
Dim cReplacements As Collection

Set cTokens = New Collection
Set cReplacements = New Collection

cTokens.Add "T.PRODUCT"
cTokens.Add "T.SCENARIO"
cReplacements.Add "Cola", "T.PRODUCT"
cReplacements.Add "Budget", "T.SCENARIO"

oCalc.ReplaceTokens cTokens, cReplacements

''' see the calc string again with the rest of the tokens replaced
MsgBox oCalc.CalcString

Exit Sub

ErrorHandler:
MsgBox Err.Description
End Sub


''' get the sheetname in Essbase required format
Function GetSheetname(Optional oSheet As Worksheet)
If oSheet Is Nothing Then
Set oSheet = ActiveSheet
End If

GetSheetname = "[" & oSheet.Parent.Name & "]" & oSheet.Name
End Function

What I realized after looking at this code is how old it actually is.. I wrote this code over 10 years ago as I recognize portions of it from our ActiveOLAP for Essbase 1.0 product which shipped in 1999.

By the way, to do this same thing in our Dodeca product takes zero lines of code. We have a feature called Workbook Script which would allow you to attach one or more tokenized calc scripts to an event. For example, you could attach the calc scripts to the 'WorkbookAfterSend' event which would cause Dodeca to automatically replace the tokens in the script and run the calc whenever a user presses the Send button (and after all Send Ranges in the workbook have been successfully sent to the Essbase server). In other words, Dodeca makes running a custom Essbase calculation much easier.