Page **1** of **1**

### Evaluation of DBRW Formula in VBA

Posted: **Tue Feb 13, 2018 3:10 pm**

by **Amorau**

Dear Experts,

I’m currently working on some automation functions for TM1 in Excel/VBA.

For some purpose I would like to pass on the result of a DBRW Formula to a VBA variable.

I cannot simply us the cell value, as the DBRW Formula could be part of another calculation within that cell.

Example:

=DBRW($B$1;$B$2;$B$3;$B$4;$B$5;$B$6;$B$7;$B$8;B$11;$A12) * 2

Would anyone be able to tell me how to evaluate the DBRW?

Any help is highly appreciated!

### Re: Evaluation of DBRW Formula in VBA

Posted: **Tue Feb 13, 2018 3:38 pm**

by **Steve Rowe**

Like most of the TM1 functions you can use Application.Run("DBRW" , Arg1 , Arg2, etc)

### Re: Evaluation of DBRW Formula in VBA

Posted: **Tue Feb 13, 2018 9:49 pm**

by **jrizk**

For simple formulas you can strip out the dbrw based on *, / , +, - after the closing bracket using Instr.

More complex formulas will require a some code or function to work out the positions of the opening and closing brackets of the the DBRW so it can be stripped out using those positions.

x = InStr(1, Range("A10").Formula, "*")

If x > 0 Then

y = Mid(Range("A10").Formula, 1, x - 1)

result= Application.Evaluate(y)

End If

### Re: Evaluation of DBRW Formula in VBA

Posted: **Wed Feb 14, 2018 8:02 am**

by **Amorau**

Steve Rowe wrote: ↑Tue Feb 13, 2018 3:38 pm

Like most of the TM1 functions you can use Application.Run("DBRW" , Arg1 , Arg2, etc)

Steve, thanks for your help with this. That was exactly what I had tried unsuccessfully.

However your post helped me understanding that my mistake was somewhere else:

I had tried:

TM1Result = Application.Run(FType, TM1Parameters)

with "FType" containing the type of formula (e.g. DBRW or DBRA) and

with "TM1Parameters" containing the parameters as string (e.g. "SERVER:CUBE,EUR,Turnover,ACT,Worldwide,....")

I now moved all parameters to an array and it works:

TM1Result = Application.Run(FType, aParameters(0), aParameters(1),....,aParameters(10) )

QUESTION: The number of parameters can change, depending on the number of dimensions of the cube.

Is there a way to evalute the DBRW with a dynamic range of parameters?

### Re: Evaluation of DBRW Formula in VBA

Posted: **Wed Feb 14, 2018 3:16 pm**

by **Wim Gielis**

You have to do the hard work of splitting up the formula in pieces, one argument is 1 piece, and work from there.

Watch out for arguments that are text but contain the separator for arguments, like a comma in the name of an element.

The TM1 Tools addin has code for this, have a look at it, here on the forum.

### Re: Evaluation of DBRW Formula in VBA

Posted: **Wed Feb 14, 2018 3:47 pm**

by **Amorau**

Hi Wim,

I already split the formula into pieces (with the help of code from the addin) and now I have an array (see below) with all arguments.

However the array lengh can be varying, depending on the number of dimension in the queried cube.

I found a solution for my problem today:

With a "select case" I can evaluate the DBRW Formula while considering the needed amount of arguments.

(in "numArgs" I have the length of the array)

Select Case numArgs

Case 2

TM1Result = Application.Run(FType, argsArrayValue(0), argsArrayValue(1), argsArrayValue(2))

Case 3

TM1Result = Application.Run(FType, argsArrayValue(0), argsArrayValue(1), argsArrayValue(2), argsArrayValue(3))

Case 4

TM1Result = Application.Run(FType, argsArrayValue(0), argsArrayValue(1), argsArrayValue(2), argsArrayValue(3), argsArrayValue(4))

Case 5

TM1Result = Application.Run(FType, argsArrayValue(0), argsArrayValue(1), argsArrayValue(2), argsArrayValue(3), argsArrayValue(4), argsArrayValue(5))

etc...

But this is no elegant solution.

I thought that there probably would be a better solution for calling a function with variable amount of arguments....

Is is not??

Many Thanks for your comments!

### Re: Evaluation of DBRW Formula in VBA

Posted: **Thu Feb 15, 2018 2:21 am**

by **jrizk**

Try:

sDBFormula = "DBRW("

For i = 0 To numArgs

sDBFormula = sDBFormula & """" & Application.Evaluate(argsArrayValue(i)) & ""","

Next i

sDBFormula = Mid(sDBFormula, 1, Len(sDBFormula) - 1) & ")"

myValue = Application.Evaluate(sDBFormula)

You may have to include/exclude the quotes for each of the arguments in the above depending in how you split the arguments.

### Re: Evaluation of DBRW Formula in VBA

Posted: **Thu Feb 15, 2018 9:16 am**

by **Amorau**

Thank you, jrizk,

That was helpful!

Using the Evaluate Method helped:

TM1VBAString = FType & "(" & Chr(34) & Join(argsArrayValue, Chr(34) & "," & Chr(34)) & Chr(34) & ")"

TM1Result = Application.Evaluate(TM1VBAString)

Just using the JOIN instead of For...next.