1.Add a button to a spreadsheet called VBScript, typing on this tab the parameters to use in EPM query, according to the next figure.

Creating a button

Adding a button to a spreadsheet can be performed using Excel's development menu, with the Design mode enabled.

2.The second step is to write a procedure to read the parameters from the spreadsheet and execute a query to EPM Server when clicking a button. The procedure is the following:

Sub GetEpmTagValues()
  Dim EpmServer, BVName
  Sheets("VBScript").Select
  EpmServer = Range("B2")
  BVName = Range("B7")
  MsgBox "Function EpmAggregate" & vbNewLine _
    & "Server: " & EpmServer & vbNewLine _
    & "BV-Name: " & BVName
  Range("A10:C16").Select
  Selection.ClearContents
  Selection.FormulaArray = "=EpmAggregate(VBScript!B2,_
    VBScript!B4, VBScript!B5, VBScript!B3,_
    VBScript!B6, , VBScript!B7)"
End Sub

This procedure selects the VBScript spreadsheet, reads values from cells B2 and B7 and links them to variables EpmServer and BVName, which are displayed on a message window before effectively performing the query. Then, an area is selected, cells A10:C16, where a function in Multi-Array Formula format is placed with the query.

3.After linking the command to press the button to the GetEpmTagValues procedure, disable the Design mode and click the button to display the window with a message informing the server and the variable to query, according to the next figure.

Message window

4.When clicking OK on this message window, the code that copies the function in Multi-Array Formula format to the selected cells is executed, displaying the results according to the Layout defined as default.

Procedure result

Notice that the formula was defined as a Multi-Array Formula type, as it is displayed between braces. It is important to notice that the formulas placed on cells using procedures written in VBA must be in this format, that is, using a method of attributing their content to the FormulaArray cell.

Another way of using EPM functions in procedures written in VBA is by loading EPM query objects. This way, users can process data on the script and later place the results on the spreadsheet in the desired format. The next example demonstrates this procedure.

1.Add a button to a spreadsheet, according to the next figure.

Adding a button to a spreadsheet

2.Write a procedure to execute a query to EPM Server when clicking a button. The procedure is the following:

Sub Execute_Dataset01()
  Dim o As Object
  Static queryResultAsObject As Variant
  Dim callerRows As Integer
  Dim callerColumns As Integer
  Set o = CreateObject("AutomationAddIn.EpmFunctions")
  With o
    queryResultAsObject = .EpmQuery("", "Dataset01")
  End With
  callerRows = UBound(queryResultAsObject, 1)
  callerColumns = UBound(queryResultAsObject, 2)
  Dim startRange As Range
  Set startRange = Range("A4")
  Dim endRow As Integer
  endRow = startRange.Row + callerRows
  Dim endColumn As Integer
  endColumn = startRange.Column + callerColumns
  Dim endRange As Range
  Set endRange = Cells(endRow, endColumn)
  Dim targetRange As Range
  Set targetRange = Range(startRange, endRange)
  With targetRange
    .Value = queryResultAsObject
  End With
End Sub

This procedure creates a query object, AutomationAddIn.EpmFunctions, and executes this object's EpmQuery method, leaving empty the connection field, that is, uses the default connection, and informing the name of a previously created Dataset in EPM Studio, Dataset01. Once this method is executed, the results are placed on the spreadsheet starting at cell A4, defined as the initial point in the procedure.

3.After linking the command of pressing a button to the Execute_Dataset01 procedure, disable the Design mode and click the button to perform the query and display the results, according to the next figure.

Procedure result

This other way of executing EPM functions in Excel's VBA scripts gives full autonomy and flexibility for processing data via scripts, as well as for presenting results on a spreadsheet.