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!
Evaluation of DBRW Formula in VBA
- Steve Rowe
- Site Admin
- Posts: 2423
- Joined: Wed May 14, 2008 4:25 pm
- OLAP Product: TM1
- Version: TM1 v6,v7,v8,v9,v10,v11+PAW
- Excel Version: Nearly all of them
Re: Evaluation of DBRW Formula in VBA
Like most of the TM1 functions you can use Application.Run("DBRW" , Arg1 , Arg2, etc)
Technical Director
www.infocat.co.uk
www.infocat.co.uk
-
- Posts: 48
- Joined: Thu Nov 19, 2009 10:38 pm
- OLAP Product: Tm1
- Version: 10.2.2
- Excel Version: 2010
Re: Evaluation of DBRW Formula in VBA
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
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
J.Rizk
Tm1 for everyone
Tm1 for everyone
-
- Posts: 4
- Joined: Tue Feb 13, 2018 2:40 pm
- OLAP Product: TM1, SSAS, (Cubeware)
- Version: 10.2.2
- Excel Version: 2010
Re: Evaluation of DBRW Formula in VBA
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?
-
- MVP
- Posts: 3128
- Joined: Mon Dec 29, 2008 6:26 pm
- OLAP Product: TM1, Jedox
- Version: PAL 2.0.9.18
- Excel Version: Microsoft 365
- Location: Brussels, Belgium
- Contact:
Re: Evaluation of DBRW Formula in VBA
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.
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.
Best regards,
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
-
- Posts: 4
- Joined: Tue Feb 13, 2018 2:40 pm
- OLAP Product: TM1, SSAS, (Cubeware)
- Version: 10.2.2
- Excel Version: 2010
Re: Evaluation of DBRW Formula in VBA
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!
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!
-
- Posts: 48
- Joined: Thu Nov 19, 2009 10:38 pm
- OLAP Product: Tm1
- Version: 10.2.2
- Excel Version: 2010
Re: Evaluation of DBRW Formula in VBA
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.
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.
J.Rizk
Tm1 for everyone
Tm1 for everyone
-
- Posts: 4
- Joined: Tue Feb 13, 2018 2:40 pm
- OLAP Product: TM1, SSAS, (Cubeware)
- Version: 10.2.2
- Excel Version: 2010
Re: Evaluation of DBRW Formula in VBA
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.
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.