EssVSetSheetOption

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

ItemOptionExpected 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: