✔ Programação GLOBAL®: Excel Slicer VBA - Inserindo ou Modificando Segmentações de Dados

Excel Slicer VBA - Inserindo ou Modificando Segmentações de Dados

Consultancy in Excel, Access, VBA & PowerPoint projects

DESENVOLVO APLICAÇÕES

Perguntas, Questões e Esclarecimentos

Por mais incrível que isso possa parecer, diversas pessoas não conseguem utilizar as Tabelas Dinâmicas por ter dificuldades de compreender como funcionam ou por não terem a menor noção de como manipular suas parametrizações.

Aqui está um código VBA que pode nos ajudar a automatizar ainda mais os nossos relatórios, trabalhando com Segmentações de Dados no MS Excel.

Os Slicers (Segmentação de Dados), como você sabe, são a mais recente introdução do MS Excel, e o melhor de tudo, podem ser controlados via VBA.

Criando um Slicer com o VBA

Cada objeto Slicer representa uma segmentação de dados em uma pasta de trabalho. Segmentações de dados são usadas para filtrar dados.

Set My_Slicer_Caches = ActiveWorkbook.SlicerCaches
Set My_Slicers = Add(Source, SourceField, [Name])
Set My_Slicer = Add(SlicerDestination, [Level],[Name],[Caption],[Top],[Left],[Width],[Height])

Use a propriedade Slicers (Traduzido automaticamente) para retornar a coleção de Segmentações de dados. O exemplo de código a seguir exibe o número de segmentação de dados no cache de segmentação de dados.

MsgBox ActiveWorkbook.SlicerCaches(1).Slicers.Count

Onde,

My_Slicer_Caches é um objeto do tipo SlicerCaches

My_Slicers é um objeto do tipo Slicers

My_Slicer é um objeto do tipo Slicer

A fonte é normalmente o nome da tabela dinâmica a partir da qual o Slicer foi criado.

SourceField é o nome do campo do cabeçalho para o qual o Slicer da será criado. Name, Caption, Top, Left, Width e Height são usados para especificar as outras partes da hierarquia.

Imagine uma tabela dinâmica que mostra as vendas de uma série de categorias de produtos em várias regiões. Primeiro, vamos construir um campo Slicer "Região" e, em seguida, gradualmente realizar outras operações usando VBA. Podemos estender o mesmo código para qualquer outro campo da tabela dinâmica - seja visível ou não.

Mas uma palavra de cautela, se você já criou um Slicer a partir de um determinado campo de uma tabela dinâmica específica, pode ter dificuldades para executar outro trecho de código que crie um outro Slicer para o mesmo campo sem apagar o primeiro. Nesses casos, podemos ter o temido Run-time error '1004′: Application-defined or object-defined error.

Toda vez que executar outro trecho de código, pode querer voltar para a planilha Excel e excluir manualmente o Slicer. Se o erro persistir, vá para o botão Slicer, crie explicitamente o Slicer para esse campo e, em seguida, volte, clique direito no Slicer e remova-o. A opção mais fácil é a de excluir o Slicer no final de cada trecho de código VBA, inserindo algo como slicer.delete

Então, vejamos como podemos criar um Slicer simples, utilizando VBA. Neste caso, queremos criar um Slicer no campo "Região". Então, agarre-se a sua tabela dinâmica, e diga ao aplicativo que queremos criar um Slicer para o campo "Região" e, em seguida, especifique as coordenadas e o modo como queremos que seja apresentado.

Como criar um Slicer

Sub CriarSlicer()

Dim i As SlicerCaches

Dim j As Slicers

Dim k As Slicer

Set i = ActiveWorkbook.SlicerCaches

Set j = i.Add(ActiveSheet.PivotTables(1), "Region", "My_Region").Slicers

Set k = j.Add(ActiveSheet, , "My_Region", "Region", 0, 0, 200, 200)

Msgbox "Slicer Criado!"

End Sub

Onde

Region é o campo nome que queremos construir para oSlicer.

My_Region é o nome que foi atribuído ao objeto Slicers.

Ligue ou desligue os campo num Slicer usando VBA

Depois de ter criado o Slicer, a próxima coisa que provavelmente gostaria de fazer é poder ligar e desligar vários itens dentro do Slicer. No nosso caso, o Slicer para o campo "Region" tem quatro itens exclusivos - leste, oeste, norte e sul.

Vamos ver como podemos ligar (ou desligar) o item "West" dentro do pivot field usando VBA.

Sub TurnOffOnSlicerField()

Dim i As SlicerCaches

Dim j As Slicers

Dim k As Slicer

Set i = ActiveWorkbook.SlicerCaches

Set j = i.Add(ActiveSheet.PivotTables(1), "Region", "Region").Slicers

Set k = j.Add(ActiveSheet, , "Region", "Region", 0, 0, 200, 200)

Let i("Region").SlicerItems("West").Selected = False

'You can also use

Let k.SlicerCache.SlicerItems("West").Selected = False

'Or

Let k.SlicerCache.SlicerItems(1).Selected = False

Msgbox "Turned off WEST"

Let i("Region").SlicerItems("West").Selected = True

'Or

Let k.SlicerCache.SlicerItems("West").Selected = True

Msgbox "Turned on WEST"

End Sub

Nomeando Slicer e alterando o Caption usando VBA
Podemos modificar o nome do Slicer e a sua legenda. Não faz muito sentido colocarmos legendas descritivas nos Slicers quando temos mais de Slicer baseado no mesmo campo em diversas tabelas dinâmicas.

Sub ChangeNameCaptionSlicer()

Dim i As SlicerCaches

Dim j As Slicers

Dim k As Slicer

Set i = ActiveWorkbook.SlicerCaches

Set j = i.Add(ActiveSheet.PivotTables(1), "Region", "Region").Slicers

Set k = j.Add(ActiveSheet, , "Region", "Region", 0, 0, 200, 200)

Let k.Name = "Slicer_Name" 'Ou use j(1).Name = "Slicer_Name"

Let k.Caption = "My Caption" 'Ou use j(1).Caption = "My Caption"

Msgbox "Changed slicer name and caption"

End Sub

Deletando um Slicer usando VBA

Então o que fazer depois que o cortador tiver feito o seu trabalho? Dispense-o. Como? Simples, Slicer.delete

Sub delete_slicer()

Dim i As SlicerCaches

Dim j As Slicers

Dim k As Slicer

Set i = ActiveWorkbook.SlicerCaches

Set j = i.Add(ActiveSheet.PivotTables(1), "Region", "My_Region").Slicers

Set k = j.Add(ActiveSheet, , "My_Region", "Region", 0, 0, 200, 200)

Msgbox "Slicer Criiado"

k.Delete

'OR

'j("My_Region").Delete

Msgbox "Slicer Deletado"

End Sub

Alterando um Slicer - Posição, Altura, Largura e Estilo - usando VBA

Coisas como a altura, largura, e posição podem ser alterados - seja no momento da criação do Slicer ou em tempo de execução. Também é possível ajustar a altura e largura dos elementos do Slicer. No exemplo abaixo, primeiro crio um Slicer, em seguida, altero seu top e a coordenada esquerda e, finalmente, ajusto a altura da linha do Slicer e a largura da coluna.

Sub change_slicer_look_feel()

Dim i As SlicerCaches

Dim j As Slicers

Dim k As Slicer

Set i = ActiveWorkbook.SlicerCaches

Set j = i.Add(ActiveSheet.PivotTables(1), "Region", "My_Region").Slicers

Set k = j.Add(ActiveSheet, , "My_Region", "Region", 0, 0, 200, 200) ' Specify the dimensions here

MsgBox "Created Slicer"

Let k.Top = 200

Let k.Left = 200

'OR

'Let j("My_Region").Top = 200

'Let j("My_Region").Left = 200

MsgBox "Moved Slicer"

k.Shape.ScaleWidth 0.4, msoFalse, msoScaleFromTopLeft

k.Shape.ScaleHeight 0.6, msoFalse, msoScaleFromTopLeft

MsgBox "Changed Slicers Row Height and Width"

Let k.RowHeight = 8.4

Let k.ColumnWidth = 358.4

'OR use the 'j("My_Region") syntax as shown above

MsgBox "Changed Slicer Field Row Height and Width"

Let k.Style = "SlicerStyleLight3"

MsgBox "Changed Slicer Color"

End Sub

Podemos classificar, configurar o filtro do cabeçalho e alterar outras configurações do Slicer usando o VBA. Aqui está apenas um pequeno trecho de código.

Criando as configurações de um Slicer com VBA

Sub change_slicer_settings()

Dim i As SlicerCaches

Dim j As Slicers

Dim k As Slicer

Set i = ActiveWorkbook.SlicerCaches

Set j = i.Add(ActiveSheet.PivotTables(1), "Region", "Region").Slicers

Set k = j.Add(ActiveSheet, , "Region", "Region", 0, 0, 200, 200)

Let k.Caption = "Amazing Slicer"

Let k.DisplayHeader = True

Let k.SlicerCache.CrossFilterType = xlSlicerNoCrossFilter ' OR xlSlicerCrossFilterShowItemsWithNoData / xlSlicerCrossFilterShowItemsWithDataAtTop

Let k.SlicerCache.SortItems = xlSlicerSortDescending 'OR xlSlicerSortAscending

Let k.SlicerCache.SortUsingCustomLists = True

Let k.SlicerCache.ShowAllItems = False

End Sub