Objetivo: Como criar um gráfico Excel com eixo Y com altura dinâmica VBA.
Este artigo tem como objetivo demonstrar como alterar os valores máximo e mínimo de um gráfico Excel de modo que o gráfico seja sempre apresentado da melhor forma.
No exemplo criado temos uma planilha simples com as vendas realizadas por dia por cada vendedor durante o ano de 2014.
Note que para as datas foram inclusos também os campos Dia, Mês e Ano, que utilizam as respectivas funções com os mesmos nome e utilizadas somente passando a data, o seu retorno são os campos homônimos aos seus nomes, e a sintaxe é a seguinte:
Para um valor de 02/01/2014 na célula A14:
- DIA(A14) -> 2
- MÊS(A14) -> 1
- ANO(A14) -> 2014
Estes campos são importantes para a criação da tabela dinâmica.
Na planilha Gráfico, foi criada uma tabela dinâmica com as seguintes configurações:
Caso não esteja familiarizado com o uso de tabelas dinâmicas sugiro que veja o artigo http://guiadoexcel.com.br/tabelas-dinamicas, pois o uso desta ferramenta abre várias possibilidades no Excel.
A tabela dinâmica criada fica no seguinte formato:
Clique sobre a mesma e na aba Inserir e selecione a opção gráfico de colunas 2D, o Excel irá criar um gráfico dinâmico com os dados criados.
Após a criação do gráfico insira as segmentações de dados clicando sobre Dia, depois Mês, Ano e Vendedor, veja neste artigo como criar segmentações de dados: http://guiadoexcel.com.br/segmentacao-de-dados-com-tabela-dinamica-excel-bi-excel-dashboards-excel.
As segmentações foram então ajustadas em torno do gráfico dinâmico, ficando da seguinte forma:
Clicando então na guia Desenvolvedor e no botão Visual Basic foi acessada a ferramenta de desenvolvimento VBA do Excel, selecionado o objeto Plan2 (Gráfico) e incluso o seguinte código fonte:
'Método disparado ativação da planilha, quando ativa realiza a atualização do gráfico
Private Sub Worksheet_Activate()
lsAtualizarGrafico
End Sub
'Função que calcula a altura do eixo Y
Private Sub lsAtualizarGrafico()
'Seleciona o gráfico
ActiveSheet.ChartObjects("Gráfico 1").Activate
'Seleciona o eixo Y do gráfico
ActiveChart.Axes(xlValue).Select
'O valor mínimo da escala do eixo Y recebe o valor mínimo da tabela dinâmica - 5%
ActiveChart.Axes(xlValue).MinimumScale = WorksheetFunction.Small(Sheets("Gráfico").Range("b35:z50"), 1) * 0.95
'O valor máximo da escala do eixo Y recebe o valor máximo da tabela dinâmica + 2%
ActiveChart.Axes(xlValue).MaximumScale = WorksheetFunction.Large(Sheets("Gráfico").Range("b35:z50"), 1) * 1.02
End Sub
'Método chamado em alterações realizadas na planilha
Private Sub Worksheet_Change(ByVal Target As Range)
lsAtualizarGrafico
End Sub
O código fonte incluso está devidamente comentado informando o que cada linha está realizando, mas efetivamente para desenvolver é necessário conhecimento em VBA.
Baixe o arquivo e veja o resultado final do projeto.
Abraço
Marcos Rieper
