This project has moved. For the latest updates, please go here.

Modify a measure's formula using a macro

May 18, 2011 at 4:07 PM

Hi everyone,

I'd like to know if it is possible to update/change a measure's formula created using the OLAP pivottable extensions using a macro.

I've created the following calculation:

calculation name: [Measures].[CO X AVG Filtered]

formula:

IIf(
 [Measures].[CO Y % AVG] > 40
 ,[Measures].[CO X AVG] /100
 ,null
)

i would like now to create a macro that reads a specific cell (containing a number between 0 and 100, inputted by a user) and update the calculation formula with that number (in the example above, replace the 40 after the > sign with the number inputted by the user).

Hope someone can help.

BR,

Gabriel

Coordinator
May 18, 2011 at 9:15 PM

This blog post has some good info on VBA and PivotTables:
http://blogs.office.com/b/microsoft-excel/archive/2008/02/05/common-questions-around-excel-2007-olap-pivottables.aspx

That post was mentioned in my Additional Resources page, so there are some other good ones there:
http://olappivottableextend.codeplex.com/wikipage?title=Acknowledgments%20and%20Additional%20Information&referringTitle=Home

Hope that helps.

Marked as answer by furmangg on 6/10/2014 at 3:49 AM