Use Visual Basic to control slicer values and Power Pivot. Excel 2010, 64-bit PowerPivot

[danl1948]

I have been working on a project that requires the generation of Sales Reports which include Year-to-Date results based on a Fiscal Year that begins October 1.

Power Pivot is the best method of extracting the needed results and comparing them with Last-Year-to-Date, Total Sales for Last Year, and this Fiscal Year Sales Forecast, but I wanted to control the report generation with Visual Basic. That meant learning how to control Power Pivot Slicers with VBA. The best I could manage was using the following Case…Else method. Little is yet available on VBA controlling Power Pivot or slicers, so I did the best I could do in the time I had available.

Can anyone help me shorten the code? Is there an array formula that could be used to generate these values? It works, but it will never win a beauty contest.

The slicer in use has the Months in Fiscal Order, and uses the abbreviation for each month. The Else is not needed because nothing but the 12 values would ever appear.

Comment: Decide which Months are included, based on the Fiscal Year beginning October 1.

Select Case MonthNbr

Case 1
ActiveWorkbook.SlicerCaches("Slicer_FiscalMonthShort").VisibleSlicerItemsList _
= Array("[Calendar].[FiscalMonthShort].&[Oct]")
Case 2
ActiveWorkbook.SlicerCaches("Slicer_FiscalMonthShort").VisibleSlicerItemsList _
= Array("[Calendar].[FiscalMonthShort].&[Oct]", "[Calendar].[FiscalMonthShort].&[Nov]")
Case 3
ActiveWorkbook.SlicerCaches("Slicer_FiscalMonthShort").VisibleSlicerItemsList _
= Array("[Calendar].[FiscalMonthShort].&[Oct]", "[Calendar].[FiscalMonthShort].&[Nov]", "[Calendar].[FiscalMonthShort].&[Dec]")

More of the same, growing in length until Month 12…

Case 12
ActiveWorkbook.SlicerCaches("Slicer_FiscalMonthShort").VisibleSlicerItemsList _
=Array("[Calendar].[FiscalMonthShort].&[Oct]", "[Calendar].[FiscalMonthShort].&[Nov]", "[Calendar].[FiscalMonthShort].&[Dec]", "[Calendar].[FiscalMonthShort].&[Jan]", "[Calendar].[FiscalMonthShort].&[Feb]", "[Calendar].[FiscalMonthShort].&[Mar]", "[Calendar].[FiscalMonthShort].&[Apr]", "[Calendar].[FiscalMonthShort].&[May]", "[Calendar].[FiscalMonthShort].&[Jun]", "[Calendar].[FiscalMonthShort].&[Jul]", "[Calendar].[FiscalMonthShort].&[Aug]", "[Calendar].[FiscalMonthShort].&[Sep]")
End Select.

Any help in finding VBA code to control slicers and Power Pivot would be appreciated!

 

[scottsen]

Only thing I can thing of is something not directly related to the VBA... which is that it seems you have to select a bunch of slicer values... is that really what you want? I mean, why not have a [Fiscal YTD] measure, and when I select Jan, it means Oct-Jan ? (instead of selecting Oct, Nov, Dec, Jan directly)

 

[danl1948]

That would work well using a Power Pivot alone, but this report goes to some senior managers who prefer paper. The report works fairly well in Excel, but creating the print-out required some Visual Basic in order to automate the process. I also had to duplicate the layout and some conditional formatting, which was simple enough. I can see me using this Visual Basic to control the slicers in the future, or at least until there is a requirement to have all senior managers work in a paperless environment. I will have a look at your blog shortly... Nice to hear from the "other" coast!