VBA: Inconsistent Evaluate() behaviour

[Dudiello]

Good morning,

I am trying to to use the VBA function Evaluate as per below

Function eval(formula As String) As Variant
eval = Application.Evaluate(formula)
End Function

If A1 I put '=3+2' it works perfectly.

If in A1 i put '=GETPIVOTDATA("[Measures].[Total Rev Disc USD]";$I$1) - where $I$1 is my PowerPivot - I get #Value!

Please note that if instead I use the Named Formulas =EVALUATE($A$1) it works perfectly.


How can I make Evaluate to work in VBA to use GetPivotData?

I even tried with a non-Power Pivot and results are the same.

Thank you!

Fabio

 

[RoryA]

Try replacing the semi colon with a comma:
'=GETPIVOTDATA("[Measures].[Total Rev Disc USD]",$I$1)

 

[Dudiello]

It worked!

Thanks for incredible fast reply and for solving the problem!

Highly appreciated

With my best regards,

Fabio

 

[RoryA]

Glad to help - welcome to the forum too!

 

[Dudiello]

Glad to help - welcome to the forum too!

Thanks!

I am quite stalled now....

I realize Evaluate only handle < 255 characters, so a complex GETPIVOTDATA or CUBEVALUE cannot be created.

Additionally, as I am using PowerPivot, I realize that I cannot use GETPIVOTDATA in VBA with PowerPivot...and there is no support for CUBEVALUE support in VBA.

So basically, locked out on creating dynamic strings to filter CUBE/OLAP...

Any hint?

 

[RoryA]

Not really sure what you're doing specifically, but you could just use VBA to populate a cell with whatever formula you need.

 

[Dudiello]

Not really sure what you're doing specifically, but you could just use VBA to populate a cell with whatever formula you need.

Basically, i need to build around 100 of queries to a CUBE or to GETPIVOTDATA with 7 or 8 iinput variables (e.g. country, product etc). Building custom formulas in EXCEL as Text and than evaluating was what i intended to do.

Building a function in VBA to query the CUBE/PIVOT would have been good but, unfortunately, it cannot be done with PowerPivot nor Cubes.

I will investigate what you say around inserting a formula in a CELL from VBA but it may make it less intuitive to manage then....

Thank you, as usual.

Fabio