Formatação Condicional em Gráficos com VBA

Neste artigo vou mostrar como podemos aplicar “formatação condicional” às colunas de um gráfico, alterando a sua cor conforme os valores da base de dados. Este princípio é bastante interessante, pois permite adicionar mais detalhe ao objeto gráfico quando pretendemos que o mesmo seja dinâmico. Contudo como se trata de um objeto e não uma célula da folha de cálculo, não pode ser simplesmente formatado com formatação condicional, já que a ferramenta se aplica a células e não objetos.

Neste sentido, como estamos a falar de uma propriedade de um objeto, neste caso o elemento “Série” do objeto gráfico, temos de recorrer a VBA para aplicar esta lógica de uma forma dinâmica.

No exemplo vou demonstrar o código que pode ser aplicado a um programa (neste caso executado por um botão na folha de cálculo ou no friso), mas também pode ser aplicado a um evento, de folha, para ser completamente automatizado.

Criar o gráfico e definir os elementos necessários

O gráfico será um gráfico tradicional de colunas, representando apenas uma serie (vendas) categorizada por meses.

Começamos então por selecionar os dados e acedemos ao menu Inserir [Insert] e no grupo dos gráficos escolhemos o gráfico de Colunas.

De seguida escolhemos os elementos necessários para o gráfico, dos quais vou definir as Etiquetas de Dados [Data Labels]. Retiro o eixo dos valores e as linhas de grelha.

Formato também a serie…

para diminuir a largura do intervalo…

E o gráfico tem todos os elementos necessários.

Definir a macro que formata as colunas

Vamos então aceder ao Visual Basic Editor [VBE] através do menu Programador [Developer] e ao botão Visual Basic Editor.

No Visual Basic Editor começamos por criar um módulo que vai conter a nossa rotina.

Definir as variáveis para o gráfico

Começamos por definr as variáveis necessárias para o gráfico, que vão representar cada um dos objetos do gráfico que vamos manipular:

• Variável. Gráfico: Representa e armazena o objeto (gráfico), neste caso o gráfico adicionado na folha.
• Variável. Serie: Representa o objeto Serie do gráfico, que corresponde as colunas do gráfico.
• Variável. Ponto: Representa o ponto da serie em específico quando selecionado para alterar a sua cor.

Sub FormatarSeries()

‘ Atribuir as variáveis

    Dim grafico As ChartObject

    Dim serie As Series

    Dim ponto As Point

    Dim dados as Range ‘ opcional para representar os valores do gráfico

Atribuir as variáveis aos objetos específicos

De seguida atribuímos as variáveis aos objetos. Especificamente a variável do gráfico, que irá então armazenar a informação do objeto (Gráfico) criado na folha.

Sub FormatarSeries()

‘ Atribuir as variáveis

    Dim grafico As ChartObject

    Dim serie As Series

    Dim ponto As Point

    Dim dados as Range ‘ opcional para representar os valores do gráfico

‘ Definir as varieis aos objetos

    Set dados = Range(“C4”, Range(“C4”).End(xlDown))

    Set grafico = Sheet1.ChartObjects(1)

Definir um ciclo em VBA

O próximo conjunto de instruções define um ciclo que percorre todas as series do gráfico, e um segundo ciclo que percorre todos os pontos da serie, analisando o seu valor para definir o formato a aplica (neste caso uma cor à coluna).

For Each serie In grafico.Chart.SeriesCollection

    For Each ponto In serie.Points

        … expressões a executar colocadas aqui

    Next ponto

Next serie

Definir a condição a aplicar em cada ponto da serie

Neste caso vamos aplicar um teste lógico com a expressão IF para avaliar o valor que cada ponto da serie tem. Este valor é obtido através da Etiqueta de Dados [Data Label] e as condições serão as seguintes:

1. Se o valor for superior ou igual a 20 000 €:
   1. Coluna formatada com a cor Verde
   1. Etiqueta de dados formatada com a Fonte a Verde
   1. Aplicação da Orientação da Etiqueta na vertical a 90º
2. Se o valor for inferior ou igual a 10 000 €:
   1. Coluna formatada com o Vermelho
   1. Etiqueta de dados formatada com a Fonte a Vermelho
   1. Aplicação da Orientação da Etiqueta na vertical a 90º
3. Caso não seja nenhuma das condições anteriores…
   1. Coluna formatada com o Azul
   1. Etiqueta de dados formatada com a Fonte a Azul
   1. Aplicação da Orientação da Etiqueta na vertical a 90º

Assim o código colocado dentro do ciclo de cada ponto é o seguinte:

If ponto.DataLabel.Text >= 20000 Then

    ponto.Format.Fill.ForeColor.RGB = RGB(0, 180, 0)    ‘Verde

    ponto.DataLabel.Font.Color = RGB(0, 180, 0)

    ponto.DataLabel.Orientation = 90

ElseIf ponto.DataLabel.Text <= 10000 Then

    ponto.Format.Fill.ForeColor.RGB = RGB(180, 0, 0)    ‘Vermelho

    ponto.DataLabel.Font.Color = RGB(180, 0, 0)

    ponto.DataLabel.Orientation = 90

Else

    ponto.Format.Fill.ForeColor.RGB = RGB(0, 0, 180)    ‘Azul

    ponto.DataLabel.Font.Color = RGB(0, 0, 180)

    ponto.DataLabel.Orientation = 90

End If

A versão completa do código:

Sub FormatarSeries()

    Dim grafico As ChartObject

    Dim serie As Series

    Dim ponto As Point

    Set grafico = Sheet1.ChartObjects(1)

    For Each serie In grafico.Chart.SeriesCollection

        For Each ponto In serie.Points

            If ponto.DataLabel.Text >= 20000 Then

                ponto.Format.Fill.ForeColor.RGB = RGB(0, 180, 0)    ‘Verde

                ponto.DataLabel.Font.Color = RGB(0, 180, 0)

                ponto.DataLabel.Orientation = 90

            ElseIf ponto.DataLabel.Text <= 10000 Then

                ponto.Format.Fill.ForeColor.RGB = RGB(180, 0, 0)    ‘Vermelho

                ponto.DataLabel.Font.Color = RGB(180, 0, 0)

                ponto.DataLabel.Orientation = 90

            Else

                ponto.Format.Fill.ForeColor.RGB = RGB(0, 0, 180)    ‘Azul

                ponto.DataLabel.Font.Color = RGB(0, 0, 180)

                ponto.DataLabel.Orientation = 90

            End If

        Next ponto

    Next serie

End Sub

Executar o código

O código pode ser executado com um botão que quando pressionado “atualiza” o gráfico para representar os dados.

Assim que criado o botão surge a caixa de diálogo para associar o botão à macro.

Para executar a Macro basta pressionar o botão…

Contudo neste caso a atualização do gráfico está dependente da “execução” da Macro. O próximo passo consiste em passar o código para um evento da aplicação, mais especificamente um evento da folha – Worksheet Change Event, que é acionado sempre que é alterada alguma célula na folha.

Colocando o código no procedimento o mesmo vai ser executado sempre que atualizado o valor em alguma célula da folha de cálculo. O código completo é apresentado a seguir:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim grafico As ChartObject

    Dim serie As Series

    Dim ponto As Point

    Set grafico = Sheet1.ChartObjects(1)   

    For Each serie In grafico.Chart.SeriesCollection   

        For Each ponto In serie.Points         

            If ponto.DataLabel.Text >= 20000 Then

                ponto.Format.Fill.ForeColor.RGB = RGB(0, 180, 0)    ‘Verde

                ponto.DataLabel.Font.Color = RGB(0, 180, 0)

                ponto.DataLabel.Orientation = 90

            ElseIf ponto.DataLabel.Text <= 10000 Then

                ponto.Format.Fill.ForeColor.RGB = RGB(180, 0, 0)    ‘Vermelho

                ponto.DataLabel.Font.Color = RGB(180, 0, 0)

                ponto.DataLabel.Orientation = 90

            Else

                ponto.Format.Fill.ForeColor.RGB = RGB(0, 0, 180)    ‘Azul

                ponto.DataLabel.Font.Color = RGB(0, 0, 180)

                ponto.DataLabel.Orientation = 90

            End If

        Next ponto

    Next serie

End Sub