Using EssVSetSheetOption macros to turn retain on/off

Slate

New Member
Joined
Jun 6, 2008
Messages
4
Hi,

I am trying to write an Essbase macro that will either turn the retain options all off or on but am having trouble.

For example, the one to turn the retain formulas options off I have got:

Declare Function EssVSetSheetOption Lib "ESSEXCLN.XLL" (ByVal sheetName As Variant, ByVal item As Variant, ByVal sheetOption As Variant) As Long

then:

sub Turnoff()

X = EssVSetSheetOption(Null, 11, True)
X = EssVSetSheetOption(Null, 22, True)
X = EssVSetSheetOption(Null, 23, False)
X = EssVSetSheetOption(Null, 22, False)
X = EssVSetSheetOption(Null, 21, False)
X = EssVSetSheetOption(Null, 11, False)

End sub

However, when I try to run it is gives three error messages:

1. Suppress Zero or Missing options cannot be modified in Formula Preservation mode
2. Same again
3. The retain on and keep and remove options cannot be modified with the Both Member Name and Alias option set

I am confused because I have code above this that makes sure suppress missings is off first anyway - and I don't try and modify the Member Name and Alias setting either!

Thanks

Liam
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Talesind

Board Regular
Joined
Jun 21, 2007
Messages
50
The problem could be that essbase already has some options selected befoe the code runs. I've had problem like this in a past when trying to use essbase options. Check what options areopen in essbase before running the code.
 
Upvote 0

Slate

New Member
Joined
Jun 6, 2008
Messages
4
Thanks - but I have already tried that and I'm not sure what else to do.

Specifically I am getting the error messages when:

X = EssVSetSheetOption(Null, 11, True) - works ok


X = EssVSetSheetOption(Null, 22, True) - Suppress Zero or Missing options cannot be modified in Formula Preservation mode

X = EssVSetSheetOption(Null, 23, False) - works ok

X = EssVSetSheetOption(Null, 22, False) - Suppress Zero or Missing options cannot be modified in Formula Preservation mode

X = EssVSetSheetOption(Null, 21, False) - The retain on and keep and remove options cannot be modified with the Both Member Name and Alias option set

X = EssVSetSheetOption(Null, 11, False) - works ok


Do you have any other ideas?
 
Upvote 0

Talesind

Board Regular
Joined
Jun 21, 2007
Messages
50
It would appear that you've got 2 conflicting options set. before the line

X = EssVSetSheetOption(Null, 21, False)

try adding

X = EssVSetSheetOption(Null, 24, False)
 
Upvote 0

Slate

New Member
Joined
Jun 6, 2008
Messages
4
Worse still! I now get an additional error message on the essvsetsheetoption (Null, 24, False) code saying:

"Both member name and alias setting cannot be modified in formula preservation code"

So.. I tried putting essvsetsheetoption (Null, 11, False) above this and I still get the same error!?! :confused:

Starting to wonder if this is a bug in Essbase!
 
Upvote 0

Talesind

Board Regular
Joined
Jun 21, 2007
Messages
50
it's not a bug as such, essbase has some options that are mutually exclusive, normally it won't let you choose them, but since you're working in code that bypasses the normal functions.

It might be an idea to manually select all of the options you want from essbase and find where there are conflicts and decide which option you need the most :/

Once that's out of the way just change your code options to suit.

Here's a full option list if you've not already got one

http://www.pcreview.co.uk/forums/thread-2311105.php
 
Upvote 0

Forum statistics

Threads
1,190,678
Messages
5,982,220
Members
439,769
Latest member
trungminh2802

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top