Using OneStream Excel VBA functions

New Contributor III

Do the OneStream Excel VBA functions return a value that can be used to determine success or failure?  For example, I am loading data to a cube using a sheet with a number of XFSetCell() formulas and then using the VBA function RefreshXFFunctionsForActiveWorksheet() in a module to load this data:

Call ExecuteXFFunction("RefreshActiveSheetXF")


Public Sub ExecuteXFFunction(XFFunction As String)

' This executes whatever XF functions are defined on the active worksheet. The active worksheet needs to be set before calling this function

Set xfAddIn = Application.COMAddIns("OneStreamExcelAddIn")

If Not xfAddIn Is Nothing And Not xfAddIn.Object Is Nothing Then
    Select Case XFFunction

       Case "RefreshActiveSheetXF"

                  Call xfAddIn.Object.RefreshXFFunctionsForActiveWorksheet

      Case "RefreshActiveSheetQV"

                Call xfAddIn.Object.RefreshQuickViewsForActiveWorksheet

      End Select

End If

End Sub

What I want to do is to check to see if the xfAddIn.Object.RefreshXFFunctionsForActiveWorksheet has been successful, so want to check a return value. Typically I would do something like

rtn = functionName

and then check the value in rtn but need to use Call with the xfAddIn functions, so is there a way to check the success/failure?


Honored Contributor

Well, if it's a Function it will return something, if it's a Sub it will not... I don't have the add-in at hand at the moment, but intellisense autocomplete in your macro editor should tell you which one it is. (Edit: actually not, in the regular VBA editor; one would have to use the proper Visual Studio or equivalent.)

New Contributor III

Thanks Jack.  I suppose your response highlights the central issue, which is the complete lack of documentation about these functions (I know there is a knowledgebase article, but all that contains is a list of the 10 functions that are available). I suppose I could spend a lot of time second-guessing how these functions work and how best to use them, but it would be much better if there was proper documentation about them. 

Honored Contributor

Hard to disagree 😥 We know that documentation needs to improve in a number of areas, there are internal efforts that hopefully will bear fruit soon. Just a year ago, the Design & Reference guide was a PDF, now it's a searchable site together with all the other guides... We'll get there.