Views

Histats

Vitrine

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


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


Deixe os seus comentários! Envie este artigo, divulgue este link na sua rede social...





Tags: VBA, Excel, 
Create, Change, Modify, Pivot Table, Slicer,

LinkWithinBrazilVBAExcelSpecialist

Related Posts Plugin for WordPress, Blogger...