Description
Sets individual spreadsheet options.
Note: | This function is not plural because you can set only one option. |
Syntax
EssVSetSheetOption(sheetName, item, sheetOption)
ByVal sheetName As Variant
ByVal item As Variant
ByVal sheetOption As Variant
Parameters
sheetName
Text name of worksheet to operate on. SheetName is of the form "[Book.xls]Sheet". If sheetName is Null or Empty, the active worksheet is used.
item
Number indicating which option is to be set. item cannot be Null or Empty. Table 16 indicates which options are set for which number and the expected data type.
Note: | Items 21 and 31 are not used. |
Table 16. Item Number Options and Expected Data Types
Item | Option | Expected Data Type and Value of sheetOption |
---|
1 | Specify drill level setting 1 Next level 2 All levels 3 Bottom level 4 Sibling level 5 Same level 6 Same generation 7 Calc level | Number |
2 | Enable Include-selection setting | Boolean |
3 | Enable Within Selection Group setting | Boolean |
4 | Enable Remove Unselected Group setting | Boolean |
5 | Specify Indent setting 1 No indentation 2 Indent sub items 3 Indent totals | Number |
6 | Enable suppress missing setting | Boolean |
7 | Enable suppress zeros setting | Boolean |
8 | Enable suppress underscores setting | Boolean |
9 | Specify alias for missing text | Text |
10 | Enable Update mode setting (block storage databases only) | Boolean |
11 | Enable Retain on Retrieval formula preservation setting | Boolean |
12 | Enable adjust columns setting | Boolean |
13 | Enable alias names setting | Boolean |
14 | Specify alias names table setting | Text |
15 | Enable template retrieve mode | Boolean |
16 | Enable free form/Version-2.x mode | Boolean |
17 | Enable auto sort rows setting | Boolean |
18 | Enable use styles | Boolean |
19 | Specify No Access label | Text |
21 | Enable Retain on Keep Only and Remove Only formula preservation setting | Boolean |
22 | Enable Retain on Zooms formula preservation setting | Boolean |
23 | Enable Formula Fill setting | Boolean |
24 | Enable Member Name and Alias setting | Boolean |
25 | Enable Repeat Member Labels setting | Boolean |
26 | Enable Sheet Option for Query Designer setting | Boolean |
27 | Enable Latest Time Period | Boolean |
28 | Specify Latest Time Period | Text |
29 | Enable Hybrid Analysis | Boolean |
30 | Enables metadata sampling when performing a Zoom In operation | Boolean |
32 | Indicates the percentage of the members to retrieve when metadata sampling is enabled | Number |
33 | Enables display of the qualified name of the duplicate (non-unique) member in the Excel comment box | Boolean |
34 | Enables display of the qualified name of the duplicate member in the Excel spreadsheet | Boolean |
sheetOption
A Boolean value denoting the new value of item. sheetOption cannot be Null or Empty.
Return Value
Returns 0 if successful. A negative number indicates a local failure (see VBA Return Values). A return value greater than zero indicates a failure on the server.
Example
Declare Function EssVSetSheetOption Lib "ESSEXCLN.XLL" (ByVal sheetName As Variant, ByVal item As Variant, ByVal sheetOption As Variant) As Long
Sub SetSheet()
X=EssVSetSheetOption(Null, 6, FALSE)
If X=0 Then
MsgBox("#Missing values will appear. ")
Else
MsgBox("Error. #Missing option not set.")
End If
End Sub
Notes:
You can use the Level Constants instead of 1-7 to set the drill level (see VBA Level Constants).
See Essbase Options Dialog Box.
To use items 21 and 22, you must enable item 11.
To use item 23, you must enable item 22.
If you enable items 11, 21, or 22, items 6 and 7 are not available.
If you enable item 22, item 4 is not available.