2 Replies Latest reply: Jun 9, 2014 8:00 AM by Celvin Kattookaran RSS

    Help with VBA Error with Essbase Function

    5cc6afef-005d-4ebb-a898-1b67f6245153

      Hello-

       

      I'm trying to create a VBA script to be used in a macro that will look at cell "A2" and drill-down/ zoom the Membe selection in A2  to the lowest level. I've used the Essbase VBA help guide to get this far (see below). The error I keep receiving is Syntax Error. I know a little about VBA and have modified scripts in the past, but next dealt with Essbase Functions inside a script. Please help me understand where I have gone wrong.

       

      Assumptions: I am connected to my Oracle database and am properly logged

                            This is an ad-hoc

      ---------------


      Sub ZoomData()

       

      ByVal vtSheetName As Variant
      ByVal vtSelection As Variant
      ByVal vtLevel As Variant
      ByVal vtAcross As Variant

       

      Declare Function HypZoomIn Lib "HsAddin" (ByVal Sheet1 As Variant, ByVal vtSelection
      As Variant, ByVal vtLevel As Variant, ByVal vtAcross As Variant) As Long

       

      HypZoomIn(vtSheetName, vtSelection, vtLevel, vtAcross)

       

      X = HypZoomIn(Empty, Range("A2"), 2, False)

       

      If X = 0 Then

      MsgBox ("Zoom successful.")

      Else
      MsgBox ("Zoom failed.")
      End If

       

      End Sub

       

       

      -------------------------

       

      instructions from the help guide

       

      HypZoomIn

       

      Data source types:

       

       

      Essbase, Planning (ad hoc only), Financial Management (ad hoc only),

       

       

      Description

       

      HypZoomIn() retrieves and expands data from Smart View based on the selected members.

       

      Syntax

       

      HypZoomIn(vtSheetName, vtSelection, vtLevel, vtAcross)

       

      ByVal vtSheetName As Variant

       

      ByVal vtSelection As Variant

       

      ByVal vtLevel As Variant

       

      ByVal vtAcross As Variant (not used)

       

      Parameters

       

      vtSheetName:

       

       

      For future use. Currently the active sheet is used.

       

       

      vtSelection:

       

       

      Range object which refers to the members that will be zoomed. If selection is Null

       

       

      vtLevel:

       

       

      Number indicating the granularity of the zoom. The following list describes the valid

       

       

      vtAcross:

       

       

      Not used.

       

       

      Return Value

       

      Returns 0 if successful; otherwise, returns the appropriate error code.

       

      Example

       

      Declare Function HypZoomIn Lib "HsAddin" (ByVal sheetName As Variant, ByVal vtSelection

       

      As Variant, ByVal vtLevel As Variant, ByVal vtAcross As Variant) As Long

       

      Sub ZoomData()

       

      X=HypZoomIn(Empty, RANGE("B3"), 1, FALSE)

       

      If X = 0 Then

       

      MsgBox("Zoom successful.")

       

      About VBA Functions in Smart View

       

       

      191

       

       

      Else

       

      MsgBox("Zoom failed.")

       

      End If

       

      End Sub