Excel VBA – Formatação de Números

Formatação de Números no Excel VBA

Os números são apresentados em todos os tipos de formatos nas planilhas do Excel. Talvez você já esteja familiarizado com a janela pop-up do Excel para usar diferentes formatos numéricos:

A formatação dos números facilita a leitura e a compreensão dos mesmos. O padrão do Excel para números inseridos em células é o formato “Geral”, o que significa que o número é exibido exatamente como você o digitou.

Por exemplo, se você digitar um número redondo, como 4238, ele será exibido como 4238, sem ponto decimal ou separador de milhares. Um número decimal, como 9325,89, será exibido com o ponto decimal e as casas decimais. Isso significa que ele não ficará alinhado na coluna com os números redondos e terá uma aparência extremamente confusa.

Além disso, sem mostrar os separadores de milhares, é difícil ver o tamanho real de um número sem contar os dígitos individuais. Ele está em milhões ou dezenas de milhões?

Do ponto de vista de um usuário que olha para uma coluna de números, isso dificulta bastante a leitura e a comparação.

No VBA, você tem acesso exatamente à mesma gama de formatos que tem no front-end do Excel. Isso se aplica não apenas a um valor inserido em uma célula de uma planilha, mas também a itens como caixas de mensagem, controles UserForm, tabelas e gráficos e a barra de status do Excel no canto inferior esquerdo da planilha.

A função Format é uma função extremamente útil no VBA em termos de apresentação, mas também é muito complexa em termos da flexibilidade oferecida na forma como os números são exibidos.

Como Usar a Função Format no VBA

Se estiver exibindo uma caixa de mensagem, a função Format poderá ser usada diretamente:

MsgBox Format(1234567.89, "#,##0.00")

Isso exibirá um número grande usando pontos para separar os milhares e para mostrar duas casas decimais. O resultado será 1.234.567,89. Os zeros no lugar do hash garantem que as casas decimais sejam mostradas como 00 em números inteiros e que haja um zero inicial para um número menor que 1

O símbolo de hashtag (#) representa um espaço reservado para dígitos que exibe um dígito se ele estiver disponível naquela posição, ou então nada.

Você também pode usar a função de formatação para endereçar uma célula individual ou um intervalo de células para alterar o formato:

Sheets("Planilha1").Range("A1:A10").NumberFormat = "#,##0.00"

Esse código definirá o intervalo de células (A1 a A10) para um formato personalizado que separa os milhares com pontos e mostra duas casas decimais.

Se você verificar o formato das células no front end do Excel, verá que um novo formato personalizado foi criado.

Você também pode formatar números na Barra de Status do Excel, no canto inferior esquerdo da janela do Excel:

Application.StatusBar = Format(1234567.89, "#,##0.00")

Para limpar isso da barra de status, use:

Application.StatusBar = ""

Criação de uma String de Formatação

Este exemplo adicionará o texto “Total de Vendas” após cada número, além de incluir um separador de milhares

Sheets("Planilha1").Range("A1:A6").NumberFormat = "#,##0.00"" Total de Vendas"""

É assim que seus números serão exibidos:

Observe que a célula A6 tem uma fórmula “SOMA”, que incluirá o texto “Total de Vendas” sem exigir formatação. Se a formatação for aplicada, como no código acima, ela não colocará uma instância extra de “Total de Vendas” na célula A6.

Embora as células agora exibam caracteres alfa numéricos, os números ainda estão presentes na forma numérica. A fórmula “SOMA” ainda funciona porque está usando o valor numérico em segundo plano, e não como o número está formatado.

O ponto na string de formato fornece o separador de milhares. Observe que você só precisa colocar isso na cadeia uma vez. Se o número for de milhões ou bilhões, ele ainda separará os dígitos em grupos de 3.

O zero na cadeia de caracteres de formato (0) é um espaço reservado para dígitos. Ele exibe um dígito se estiver lá, ou um zero. Seu posicionamento é muito importante para garantir a uniformidade da formatação

Na cadeia de caracteres de formato, os caracteres de hash (#) não exibirão nada se não houver nenhum dígito. Entretanto, se houver um número como 0,8 (todos os decimais), queremos que ele seja exibido como 0,80 para que fique alinhado com os outros números.

Usando um único zero à esquerda da vírgula decimal e dois zeros à direita da vírgula decimal na cadeia de caracteres de formatação, obteremos o resultado desejado (0,80).

Se houvesse apenas um zero à direita da vírgula decimal, o resultado seria “0,8” e tudo seria exibido com uma casa decimal.

Uso de uma String de Formato para Alinhamento

Talvez queiramos ver todos os números decimais em um intervalo alinhados em suar vírgulas decimais, de modo que todas as vírgulas decimais estejam diretamente abaixo umas das outras, independentemente do número de casas decimais que houver em cada número.

Para isso, você pode usar um ponto de interrogação (?) em sua cadeia de formatação. O ‘?’ indica que um número é mostrado se estiver disponível, ou um espaço

Sheets("Planilha1").Range("A1:A6").NumberFormat = "#,##0.00??"

Isso exibirá seus números da seguinte forma:

Todos os pontos decimais agora estão alinhados um embaixo do outro. A célula A5 tem três casas decimais, o que normalmente prejudicaria o alinhamento, mas o uso do caractere ‘?’ alinha tudo perfeitamente.

Uso de Caracteres Literais na String de Formatação

Você pode adicionar qualquer caractere literal à sua cadeia de formatação precedendo-o com uma barra invertida (\).

Suponha que você queira mostrar um código de moeda específico para seus números que não seja baseado em sua localidade. O problema é que, se você usar um indicador de moeda, o Excel automaticamente fará referência à sua localidade e a alterará para a localidade apropriada definida no Painel de Controle do Windows. Isso pode ter implicações se o seu aplicativo do Excel estiver sendo distribuído em outros países e você quiser garantir que, seja qual for a localidade, o código de moeda seja sempre o mesmo.

Talvez você também queira indicar que os números estão em milhões no exemplo a seguir:

Sheets("Planilha1").Range("A1:A6").NumberFormat = "\$#,##0.00 \m"

Isso produzirá os seguintes resultados em sua planilha:

Ao usar uma barra invertida para exibir caracteres literais, não é necessário usar uma barra invertida para cada caractere individual em uma cadeia de caracteres. Você pode usar:

Sheets("Planilha1").Range("A1:A6").NumberFormat = "\$#,##0.00 \mil"

Isso exibirá ‘mil’ após cada número dentro do intervalo formatado.

Você pode usar a maioria dos caracteres como literais, mas não os caracteres reservados, como 0, #,?

Uso de Vírgulas em uma String de Formatação

Já vimos que as vírgulas podem ser usadas para separadores de decimais, mas elas também podem ser usadas de outra forma.

Ao usá-las no final da parte numérica da cadeia de caracteres de formato, elas atuam como escalonadores de milhares. Em outras palavras, elas dividirão cada número por 1.000 sempre que houver uma vírgula.

Nos dados de exemplo, estamos mostrando-os com um indicador de que estão em milhões. Ao inserir uma vírgula na string de formato, podemos mostrar esses números divididos por 1.000.

Sheets("Planilha1").Range("A1:A6").NumberFormat = "\$#,##0.00,\mil"

Isso mostrará os números divididos por 1.000, embora o número original ainda esteja em segundo plano na célula.

Se você colocar duas vírgulas na string de formatação, os números serão divididos por um milhão

Sheets("Planilha1").Range("A1:A6").NumberFormat = "\$#,##0.00,,\mil"

Esse será o resultado usando apenas uma vírgula (dividir por 1.000):

 

Criação de Formatação Condicional na String de Formatação

Você pode configurar a formatação condicional no front-end do Excel, mas também pode fazê-lo em seu código VBA, o que significa que pode manipular a cadeia de formatação programaticamente para fazer alterações.

É possível usar até quatro seções na cadeia de formatação. Cada seção é delimitada por um ponto e vírgula (;). As quatro seções correspondem a positivo, negativo, zero e texto

Range("A1:A7").NumberFormat = "#,##0.00;[Red]-#,##0.00;[Green] #,##0.00;[Blue]"

Neste exemplo, usamos os mesmos caracteres hash, vírgula e zero para fornecer separadores de milhar e dois pontos decimais, mas agora temos seções diferentes para cada tipo de valor.

A primeira seção é para números positivos e não é diferente do que já vimos anteriormente em termos de formato.

A segunda seção para números negativos introduz uma cor (vermelho) que é mantida dentro de um par de colchetes. O formato é o mesmo dos números positivos, exceto pelo fato de um sinal de menos (-) ter sido adicionado na frente.

A terceira seção para números zero usa uma cor (verde) dentro de colchetes com a cadeia numérica igual à dos números positivos.

A seção final é para valores de texto, e tudo o que ela precisa é de uma cor (azul) novamente entre colchetes

Esse é o resultado da aplicação dessa string de formato:

Você pode ir além com condições dentro da string de formato. Suponha que você queira mostrar todos os números positivos acima de 10.000 como verde e todos os outros números como vermelho, você poderia usar essa cadeia de caracteres de formatação:

Range("A1:A7").NumberFormat = "[>=10000][Green]#,##0.00;[<10000][Red]#,##0.00"

Essa string de formato inclui condições para >=10000 definidas entre colchetes, de modo que a cor verde só será usada quando o número for maior ou igual a 10000

Este é o resultado:

 

Uso de Frações em Strings de Formatação

As frações não são usadas com frequência em planilhas, pois normalmente equivalem a decimais, com os quais todos estão familiarizados.

Entretanto, às vezes elas servem a um propósito. Este exemplo exibirá dólares e centavos:

Range("A1:A7").NumberFormat = "#,##0 "" dólares and "" 00/100 "" centavos """

Esse é o resultado que será produzido:

Lembre-se de que, apesar de os números serem exibidos como texto, eles ainda estão lá em segundo plano como números e todas as fórmulas do Excel ainda podem ser usadas neles.

Formatos de Data e Hora

As datas são, na verdade, números nos quais pode usar formatos da mesma forma que nos números. Se você formatar uma data como um número, verá um número grande à esquerda da vírgula decimal e um número de casas decimais. O número à esquerda da vírgula mostra o número de dias a partir de 01-Jan-1900 e as casas decimais mostram a hora com base em 24 horas

MsgBox Format(Now(), "dd-mmm-yyyy")

Isso formatará a data atual para mostrar “08-jul-2020”. O uso de “mmm” para o mês exibe os três primeiros caracteres do nome do mês. Se você quiser o nome completo do mês, use ‘mmmm’

Você pode incluir horas em sua string de formatação:

MsgBox Format(Now(), "dd-mmm-yyyy hh:mm AM/PM")

Isso exibirá ’08-jul-2020 01:25 PM’

“hh:mm” representa horas e minutos e AM/PM usa um relógio de 12 horas em vez de um relógio de 24 horas.

Você pode incorporar caracteres de texto em sua string de formato:

MsgBox Format(Now(), "dd-mmm-yyyy hh:mm AM/PM"" hoje""")

Isso exibirá ’08-jul-2020 01:25 PM hoje’

Você também pode usar caracteres literais usando uma barra invertida na frente, da mesma forma que para as cadeias de caracteres de formato numérico.

Formatos Predefinidos

O Excel tem vários formatos internos para números e datas que você pode usar em seu código. Eles refletem principalmente o que está disponível no front-end de formatação de números, embora alguns deles vão além do que normalmente está disponível na janela pop-up. Além disso, você não tem flexibilidade em relação ao número de casas decimais ou ao uso de separadores de milhares.

Número Geral

Esse formato exibirá o número exatamente como ele é

MsgBox Format(1234567.89, "General Number")

O resultado será 1234567,89

Moeda

MsgBox Format(1234567.894, "Currency")

Esse formato adicionará um símbolo de moeda na frente do número, por exemplo, R$, $, £, dependendo da sua localidade, mas também formatará o número com duas casas decimais e separará os milhares com vírgulas.

O resultado será R$ 1.234.567,89

Fixo

MsgBox Format(1234567.894, "Fixed")

Esse formato exibe pelo menos um dígito à esquerda, mas somente dois dígitos à direita da vírgula decimal.

O resultado será 1234567,89

Padrão

MsgBox Format(1234567.894, "Standard")

Exibe o número com os separadores de milhar, mas apenas com duas casas decimais.

O resultado será 1.234.567,89

Porcentagem

MsgBox Format(1234567.894, "Percent")

O número é multiplicado por 100 e um símbolo de porcentagem (%) é adicionado ao final do número. O formato é exibido com 2 casas decimais.

O resultado será 123456789,40%

Científico

MsgBox Format(1234567.894, "Scientific")

Isso converte o número para o formato exponencial

O resultado será 1,23E+06

Sim/Não

MsgBox Format(1234567.894, "Yes/No")

Exibe “Não” se o número for zero; caso contrário, exibe “Sim”

O resultado será “Sim”

Verdadeiro/Falso

MsgBox Format(1234567.894, "True/False")

Exibe “Falso” se o número for zero; caso contrário, exibe “Verdadeiro”

O resultado será “Verdadeiro”

Ligado/Desligado

MsgBox Format(1234567.894, "On/Off")

Exibe “Desativado” se o número for zero; caso contrário, exibe “Ativado”

O resultado será “Ativado”

Data Geral

MsgBox Format(Now(), "General Date")

Isso exibirá a data como data e hora usando a notação AM/PM. A forma como a data é exibida depende das suas configurações no Painel de Controle do Windows (Relógio e Região | Região). Ela pode ser exibida como “mm/dd/yyyy” ou “dd/mm/yyyy

O resultado será “7/7/2020 3:48:25”

Data Longa

MsgBox Format(Now(), "Long Date")

Isso exibirá uma data longa, conforme definido no Painel de Controle do Windows (Relógio e Região | Região). Observe que ela não inclui a hora.

O resultado será “terça-feira, 7 de julho de 2020”

Data Média

MsgBox Format(Now(), "Medium Date")

Exibe uma data definida nas configurações de data curta, conforme definido pela localidade no Painel de Controle do Windows.

O resultado será “07/jul/20

Data Curta

MsgBox Format(Now(), "Short Date")

Exibe uma data curta, conforme definido no Painel de Controle do Windows (Relógio e Região | Região). A forma como a data é exibida depende de sua localidade. Ela pode ser exibida como “mm/dd/aaaa” ou “dd/mm/aaaa

O resultado será “7/7/2020”

Tempo Longo

MsgBox Format(Now(), "Long Time")

Exibe uma hora longa conforme definido no Painel de Controle do Windows (Relógio e Região | Região).

O resultado será ‘4:11:39’

Hora Média

MsgBox Format(Now(), "Medium Time")

Exibe uma hora média conforme definido pela sua localidade no Painel de Controle do Windows. Normalmente, ela é definida no formato de 12 horas, usando horas, minutos e segundos e o formato AM/PM.

O resultado será “04:15 PM”

Hora Curta

MsgBox Format(Now(), "Short Time")

Exibe uma hora média conforme definido no Painel de Controle do Windows (Relógio e Região | Região). Normalmente, é definido como formato de 24 horas com horas e minutos

O resultado será “16:18”

Perigos de Usar os Formatos Predefinidos do Excel em Datas e Horas

O uso de formatos predefinidos para datas e horas no Excel VBA depende muito das configurações no Painel de Controle do Windows e também da localidade definida

Os usuários podem alterar facilmente essas configurações, o que afetará a forma como as datas e horas são exibidas no Excel

Por exemplo, se você desenvolver um aplicativo do Excel que use formatos predefinidos no código VBA, eles poderão mudar completamente se um usuário estiver em um país diferente ou usar uma localidade diferente da sua. É possível que as larguras das colunas não se ajustem à definição de data ou que, em um formulário de usuário, o controle Active X, como um controle de caixa de combinação (drop down), seja muito estreito para que as datas e horas sejam exibidas corretamente.

É preciso considerar a localização geográfica do público-alvo ao desenvolver o aplicativo do Excel

Formatos Definidos pelo Usuário para Números

Há vários parâmetros diferentes que você pode usar ao definir sua cadeia de formato:

Caractere	Descrição
Cadeia de caracteres nula	Sem formatação
0	Espaço reservado para dígito. Exibe um dígito ou um zero. Se houver um dígito para essa posição, ele exibirá o dígito, caso contrário, exibirá 0. Se houver menos dígitos do que zeros, você obterá zeros à esquerda ou à direita. Se houver mais dígitos após a vírgula decimal do que zeros, o número será arredondado para o número de casas decimais indicado pelos zeros. Se houver mais dígitos antes da vírgula decimal do que zeros, eles serão exibidos normalmente.
#	Espaço reservado para dígitos. Exibe um dígito ou nada. Funciona da mesma forma que o espaço reservado para zero acima, exceto pelo fato de que os zeros à esquerda e à direita não são exibidos. Por exemplo, 0,75 seria exibido usando espaços reservados para zero, mas seria ,75 usando # espaços reservados.
. Ponto decimal.	Somente um é permitido por string de formato. Esse caractere depende das configurações no Painel de Controle do Windows.
%	Espaço reservado para porcentagem. Multiplica o número por 100 e coloca o caractere % onde ele aparece na cadeia de formato
, (vírgula)	Separador de milhar. É usado se forem usados marcadores de posição 0 ou # e a cadeia de caracteres de formato contiver uma vírgula. Uma vírgula à esquerda do ponto decimal indica o arredondamento para o milhar mais próximo. Por exemplo, ##0, Duas vírgulas adjacentes à esquerda do separador de milhar indicam o arredondamento para o milhão mais próximo. Por exemplo, ##0,,
E- E+	Formato científico. Exibe o número exponencialmente.
: (dois pontos)	Separador de tempo – usado na formatação de uma hora para dividir horas, minutos e segundos.
/	Separador de data – é usado ao especificar um formato para uma data
– + £ $ ( )	Exibe um caractere literal. Para exibir um caractere diferente dos listados aqui, preceda-o com uma barra invertida (\)

Formatos Definidos pelo Usuário para Datas e Horas

Todos esses caracteres podem ser usados em sua cadeia de caracteres de formato ao formatar datas e horas:

Caractere	Significado
c	Exibe a data como ddddd e a hora como ttttt
d	Exibe o dia como um número sem zero à esquerda
dd	Exibe o dia como um número com zero à esquerda
ddd	Exibir o dia como uma abreviação (Sun – Sat)
dddd	Exibir o nome completo do dia (domingo – sábado)
ddddd	Exibir um número de série de data como uma data completa de acordo com Short Date nas configurações internacionais do Painel de Controle do Windows
dddddd	Exibe um número de série de data como uma data completa de acordo com a Data Longa nas configurações internacionais do Painel de Controle do Windows.
w	Exibe o dia da semana como um número (1 = domingo)
ww	Exibe a semana do ano como um número (1-53)
m	Exibe o mês como um número sem zero à esquerda
mm	Exibe o mês como um número com zeros à esquerda
mmm	Exibe o mês como uma abreviação (Jan-Dez)
mmmm	Exibe o nome completo do mês (janeiro a dezembro)
q	Exibe o trimestre do ano como um número (1-4)
y	Exibe o dia do ano como um número (1-366)
yy	Exibe o ano como um número de dois dígitos
yyyy	Exibe o ano como um número de quatro dígitos
h	Exibe a hora como um número sem zero à esquerda
hh	Exibe a hora como um número com zero à esquerda
n	Exibe o minuto como um número sem zero à esquerda
nn	Exibe o minuto como um número com zero à esquerda
s	Exibe o segundo como um número sem zero à esquerda
ss	Exibe o segundo como um número com zero à esquerda
ttttt	Exibe um número de série de hora como uma hora completa.
AM/PM	Use um relógio de 12 horas e exiba AM ou PM para indicar antes ou depois do meio-dia.
am/pm	Use um relógio de 12 horas e use am ou pm para indicar antes ou depois do meio-dia
A/P	Use um relógio de 12 horas e use A ou P para indicar antes ou depois do meio-dia
a/p	Use um relógio de 12 horas e use a ou p para indicar antes ou depois do meio-dia