Skip to content
April 17, 2012 / Erik Svensen

Rename Slicer names for easier use in Excel Cube functions

 

OR The easier way – Use the Name manager in the Formulas Tab – Thanks for the tip Milang

 

When working with PowerPivot the feature of inserting slicers to filter your pivot tables is quite handy.

But sometimes pivottables can’t perform the layout you want and you use the Excel CUBE functions instead, and in those formulas you can refer to the slicers by their name.

In this case Slicer_Month.

But sometimes you insert the same dimension as a slicer several times and modify which pivot tables they should be linked to and then you end up with slicers having names

    

In order to make it easier to construct your functions it would be more appropriate to call the second Slicer_LastYear.

There is no way to modify this via the userinterface but it can be modified via VBA and the Excel Object model.

But this simple code in a userform do the trick

Option Explicit

Dim objSlicer As SlicerCache

Private Sub cmdCancel_Click()

Unload Me

End Sub

Private Sub cmdOK_Click()

objSlicer.Name = Me.txtSlicerName.Text

Unload Me

End Sub

Private Sub UserForm_Initialize()

On Error Resume Next

Set objSlicer = ThisWorkbook.ActiveSlicer.SlicerCache

On Error GoTo 0

If Not objSlicer Is Nothing Then

Me.txtSlicerName.Text = objSlicer.Name

Else

MsgBox “No slicer selected”, vbExclamation

Me.txtSlicerName.Enabled = False

Exit Sub

End If

End Sub

So select a Slicer and run the form

And you can rename the slicer name to use in functions.

And now the slicer can be referenced via the name you give it.

If you want a copy of the model you can download it here – https://skydrive.live.com/redir.aspx?cid=093b838bfb55945e&resid=93B838BFB55945E!3317&parid=93B838BFB55945E!305

Advertisements

7 Comments

Leave a Comment
  1. Anoop Hariharan (@anoophariharan) / Jun 2 2013 1:08 pm

    Was struggling with confusing default slicer names esp when writing OLAP cube formulae. Ended up making a Excel Addin called Slicer Control with C#. It renames the slicer in a second and tight on memory usage. The only way available now to rename slicer names in excel. http://sourceforge.net/projects/excelslicercont/

  2. milang / Sep 11 2013 7:56 pm

    You can use name manager to rename slicers!

    • Erik Svensen / Sep 11 2013 8:07 pm

      You are so right … Great tip – thanks for sharing… Sometimes we cross the river for Water 🙂

  3. Sajid Mansoor / Aug 10 2015 12:03 pm

    simply use name manager

    • Erik Svensen / Aug 10 2015 3:10 pm

      Absolutely – As mentioned in the first line of the acticle 🙂

  4. Bertrand d'Arbonneau / Feb 10 2016 10:34 am

    FYI, I included your code in an add-in which you may find useful for its other features.
    http://www.sqlbi.com/tools/power-pivot-utilities/

    • Erik Svensen / Feb 11 2016 3:25 pm

      Great that you find it useful for your great add-in

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: