11-24-2022
10:46 AM
- last edited
2 weeks ago
by
JackLacava
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?
11-24-2022 11:30 AM - edited 11-24-2022 12:51 PM
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.)
11-24-2022 12:27 PM
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.
11-24-2022 12:49 PM
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.