TM1 Recalc Current Cell

[Steve Vincent]

I know Excel can do a normal recalc on a single cell

Code: Select all

Range(rangename).Calculate

But is there a similar method to recalc a single cell that includes the TM1 part of recalc?

My reasoning for such ability is long and complicated to explain, but in a nutshell i'm trying to recreate the DBRW ability to send an entered value but keep the formula after its done. I have to wrap my DBRW in an IF statement for this sheet hence why the usual functionality doesn't work, but the sheet has loads of these formulae so a full sheet recalc is just not going to be useable.

TIA

[TJMurphy]

I guess coding an Edit / Enter key on the cell doesn't trigger the TM1 calc then? Wouldn't hit all preceding cells though I guess (or you'd already have tried that!).

Tony

[Steve Vincent]

Not entirely sure what you mean...

I'm almost there, but the recalc of a single cell or just evaluating a DBS in VBA is still tripping me up. If i could use the DBS formula directly in VBA, i have the rest of the code that disassembles the cells formula, extracts the "co-ordinates" of the dimensions and then creates a DBS from it. If i have to put that formula in to a cell or can evaluate it directly in VBA i don't mind, but as yet i've not found a way to do either.

[Steve Rowe]

Can't you use

answer=Run("DBS", blah)?

[Steve Vincent]

Kinda

I have the full set of cell refs for the formula in a variable, but the DBS in VBA insists on Range($A$1) rather than just the $A$1 so i just need to write some code to insert the extra garbage it needs and we should be away. Trying to make this generic so i can use it on any file (and share it) but needing to search and replace indefinately thru the string might mean i can the idea and just write it so it works on this particular file. We'll see how this afternoon goes

[Steve Vincent]

<sigh>

Still fighting it. Taken ages to get the code to return values rather than *KEY_ERR but i got that sorted in the end. Seems it's picky about the way data is entered in to the brackets - if you have 9 dims then you must have 9 variables to hold each part, having one variable to hold the equlivalent text for the whole sequence will not work. Shame, but unless someone else can find a way its all i can do at present.

So, ignoring the way i get the array populated, i now have the following;

Code: Select all

sFormulaEval = Run("DBS", sValue, sDimension(1), sDimension(2), sDimension(3), _
                            sDimension(4), sDimension(5), sDimension(6), _
                            sDimension(7), sDimension(8), sDimension(9))

Problem is, its not actually sending anything. Using DBRW works fine, but DBS isn't sending any values. I'm guessing it might be a syntax thing, but documentation is somewhat thin on the ground in this area and i don't really know where to go now

[Kerry372]

Can you select the cell and then use sendkeys.

SendKeys "{F2}"
SendKeys "{ENTER}"

[paulsimon]

Steve

In your code

Code: Select all

sFormulaEval = Run("DBS", sValue, sDimension(1), sDimension(2), sDimension(3), _
                            sDimension(4), sDimension(5), sDimension(6), _
                            sDimension(7), sDimension(8), sDimension(9))

You are missing the parameter for the cube name. Remember that this needs to be server:cube.

Then I think it should work. I have done things like that in the past.

Regards

Paul Simon

[paulsimon]

Steve

By the way, to get over the number of dimensions problem, I would enclose the Run in a SUB and then use an IF to check on the UBound (size) of the array and then call a Run statement with the appropriate slots from the array. Arrays in VB can be dynamic, ie variable in size.

I also usually use option base 1 at the top of the module so that arrays start at 1 (easy for humans) rather than 0 (easy for computers)

Regards

Paul Simon

[Alan Kirk]

I also usually use option base 1 at the top of the module so that arrays start at 1 (easy for humans) rather than 0 (easy for computers)

As long as you don't mind having to rewrite significant chunks of your code when you have to migrate it to Visual Fred... sorry, VB.Net, which doesn't support Option Base. It's something I'm holding out against as long as possible, but I know that some day I won't have any choice so I'm avoiding, as far as possible, any constructs which aren't compatible. (Non-zero array bounds, use of variants if I can avoid it (sometimes you can't, but still...), use of Empty, etc...)

Error 9's annoy me more than any other type except Error 13's, for some reason.