Skip to main content
I
ianreid
Newcomer
November 24, 2022

Using OneStream Excel VBA functions

  • November 24, 2022
  • 2 replies
  • 0 views

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?

    2 replies

    J
    OneStream Employee
    JackLacavaOneStream Employee
    OneStream Employee
    November 24, 2022

    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.)

    I
    ianreidAuthor
    Newcomer
    November 24, 2022

    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. 

      J
      OneStream Employee
      JackLacavaOneStream Employee
      OneStream Employee
      November 24, 2022

      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.

      Terms & ConditionsAccessibility statement