Formatando Números no Excel VBA
Os números vêm em todos os tipos de formatos nas planilhas do Excel. Você já deve estar familiarizado com a janela pop-up do Excel para fazer uso de diferentes formatos numéricos:
A formatação de números torna os números mais fáceis de ler e entender. O padrão do Excel para números inseridos nas células é o formato "Geral", o que significa que o número é exibido exatamente como você o digitou.
Por exemplo, se você inserir um número redondo, por exemplo, 4238, será exibido como 4238 sem vírgula decimal ou separadores de milhares. Um número decimal como 9325,89 será exibido com o ponto decimal e os decimais. Isso significa que ele não se alinhará na coluna com os números redondos e ficará extremamente confuso.
Além disso, sem mostrar os separadores de milhares, é difícil ver o quão grande um número realmente é sem contar os dígitos individuais. É em milhões ou dezenas de milhões?
Do ponto de vista de um usuário olhando para uma coluna de números, isso torna muito difícil de ler e comparar.
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 em uma planilha, mas também a coisas como caixas de mensagem, controles de formulário de usuário, 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 em 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 de formatação em VBA
Se você estiver mostrando uma caixa de mensagem, a função Formatar pode ser usada diretamente:
| 1 | Formato MsgBox (1234567.89, "#, ## 0.00") |
Isso exibirá um grande número usando vírgulas para separar os milhares e mostrar 2 casas decimais. O resultado será 1.234.567,89. Os zeros no lugar do hash garantem que os decimais sejam mostrados como 00 em números inteiros e que haja um zero à esquerda para um número menor que 1
O símbolo hashtag (#) representa um marcador de posição de dígito que exibe um dígito se estiver disponível naquela posição, ou então nada.
Você também pode usar a função de formato para endereçar uma célula individual ou um intervalo de células para alterar o formato:
| 1 | Folhas ("Folha1"). Intervalo ("A1: A10"). NumberFormat = "#, ## 0,00" |
Este código irá definir o intervalo de células (A1 a A10) para um formato personalizado que separa os milhares com vírgulas e mostra 2 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:
| 1 | Application.StatusBar = Formato (1234567.89, "#, ## 0,00") |
Você limpa isso na barra de status usando:
| 1 | Application.StatusBar = "" |
Criação de uma string de formatação
Este exemplo adicionará o texto ‘Total de vendas’ após cada número, bem como incluirá um separador de milhares
| 1 | Folhas ("Folha1"). Intervalo ("A1: A6"). NumberFormat = "#, ## 0,00" "Vendas totais" "" |
Esta é a aparência de seus números:
Observe que a célula A6 tem uma fórmula ‘SUM’, que incluirá o texto ‘Total Sales’ sem exigir formatação. Se a formatação for aplicada, como no código acima, ela não colocará uma instância extra de ‘Vendas totais’ na célula A6
Embora as células agora exibam caracteres alfanuméricos, os números ainda estão presentes na forma numérica. A fórmula ‘SUM’ ainda funciona porque está usando o valor numérico em segundo plano, não como o número é formatado.
A vírgula na string de formato fornece o separador de milhares. Observe que você só precisa colocar isso na string uma vez. Se o número chegar a milhões ou bilhões, ainda vai separar os dígitos em grupos de 3
O zero na string de formato (0) é um marcador de posição de dígito. Ele exibe um dígito se estiver lá, ou um zero. Seu posicionamento é muito importante para garantir uniformidade com a formatação
Na string de formato, os caracteres hash (#) não exibirão nada se não houver nenhum dígito. No entanto, se houver um número como 0,8 (todos decimais), queremos que ele seja mostrado como 0,80 para que se alinhe com os outros números.
Usando um único zero à esquerda da vírgula decimal e dois zeros à direita da vírgula decimal na string de formato, isso dará o resultado necessário (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.
Usando uma string de formato para alinhamento
Podemos querer ver todos os números decimais em um intervalo alinhados em suas casas decimais, de modo que todas as casas decimais estejam diretamente abaixo umas das outras, não importa quantas casas decimais existam em cada número.
Você pode usar um ponto de interrogação (?) Em sua string de formato para fazer isso. O ‘?’ Indica que um número é mostrado se estiver disponível, ou um espaço
| 1 | Sheets ("Sheet1"). Range ("A1: A6"). NumberFormat = "#, ## 0.00 ??" |
Isso exibirá seus números da seguinte maneira:
Todos os pontos decimais agora se alinham um abaixo do outro. A célula A5 tem três casas decimais e isso tiraria o alinhamento normalmente, mas usar o caractere ‘?’ Alinha tudo perfeitamente.
Usando caracteres literais na string de formato
Você pode adicionar qualquer caractere literal à string de formato precedendo-o com uma barra invertida (\).
Suponha que você queira mostrar um indicador 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 se refere ao seu local e o altera para aquele apropriado para o local definido no Painel de Controle do Windows. Isso pode ter implicações se o seu aplicativo Excel estiver sendo distribuído em outros países e você quiser garantir que, seja qual for o local, o indicador de moeda será sempre o mesmo.
Você também pode querer indicar que os números estão em milhões no exemplo a seguir:
| 1 | Folhas ("Folha1"). Intervalo ("A1: A6"). NumberFormat = "\ $ #, ## 0.00 \ m" |
Isso produzirá os seguintes resultados em sua planilha:
Ao usar uma barra invertida para exibir caracteres literais, você não precisa usar uma barra invertida para cada caractere individual em uma string. Você pode usar:
| 1 | Folhas ("Folha1"). Intervalo ("A1: A6"). NumberFormat = "\ $ #, ## 0.00 \ mill" |
Isso exibirá ‘moinho’ após cada número dentro do intervalo formatado.
Você pode usar a maioria dos caracteres como literais, mas não caracteres reservados, como 0, # ,?
Uso de vírgulas em uma string de formato
Já vimos que as vírgulas podem ser usadas para criar separadores de milhares para números grandes, mas também podem ser usadas de outra maneira.
Ao usá-los no final da parte numérica da string de formato, eles agem como escalonadores de milhares. Em outras palavras, eles dividirão cada número por 1.000 sempre que houver uma vírgula.
Nos dados de exemplo, estamos mostrando com um indicador de que está em milhões. Ao inserir uma vírgula na string de formato, podemos mostrar esses números divididos por 1.000.
| 1 | Folhas ("Folha1"). Intervalo ("A1: A6"). NumberFormat = "\ $ #, ## 0.00, \ m" |
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 formato, os números serão divididos por um milhão
| 1 | Sheets ("Sheet1"). Range ("A1: A6"). NumberFormat = "\ $ #, ## 0.00 ,, \ m" |
Este será o resultado usando apenas uma vírgula (divida 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 fazer isso no código VBA, o que significa que você pode manipular a string de formato programaticamente para fazer alterações.
Você pode usar até quatro seções em sua string de formato. Cada seção é delimitada por um ponto e vírgula (;). As quatro seções correspondem a positivo, negativo, zero e texto
| 1 | Intervalo ("A1: A7"). NumberFormat = "#, ## 0,00; [Vermelho] - #, ## 0,00; [Verde] #, ## 0,00; [Azul]” |
Neste exemplo, usamos o mesmo hash, vírgula e zero caracteres para fornecer milhares de separadores e duas casas 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 apresenta uma cor (Vermelho) que é mantida dentro de um par de colchetes. O formato é o mesmo que para números positivos, exceto que um sinal de menos (-) foi adicionado na frente.
A terceira seção para números zero usa uma cor (verde) entre colchetes com a sequência numérica igual aos números positivos.
A seção final é para valores de texto, e tudo o que isso precisa é uma cor (azul) novamente entre colchetes
Este é o resultado da aplicação desta string de formato:
Você pode ir mais longe com as condições dentro da string de formato. Suponha que você queira mostrar todos os números positivos acima de 10.000 como verdes e todos os outros números como vermelhos, você pode usar esta string de formato:
| 1 | Intervalo ("A1: A7"). NumberFormat = "[> = 10000] [Verde] #, ## 0,00; [<10000] [Vermelho] #, ## 0,00" |
Esta string de formato inclui condições para> = 10.000 definidas entre colchetes para que o verde só seja usado quando o número for maior ou igual a 10.000
Este é o resultado:
Usando frações na formatação de strings
As frações não são frequentemente usadas em planilhas, já que normalmente equivalem a decimais com os quais todos estão familiarizados.
No entanto, às vezes eles servem a um propósito. Este exemplo exibirá dólares e centavos:
| 1 | Range ("A1: A7"). NumberFormat = "#, ## 0" "dólares e" "00/100" "centavos" "" |
Este é o resultado que será produzido:
Lembre-se de que apesar dos 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 e você pode usar formatos nelas da mesma forma que os números. Se você formatar uma data como um número numérico, verá um grande número à esquerda da vírgula decimal e várias casas decimais. O número à esquerda da vírgula decimal mostra o número de dias começando em 01 de janeiro de 1900, e as casas decimais mostram o tempo com base em 24 horas
| 1 | Formato MsgBox (Now (), "dd-mmm-aaaa") |
Isso formatará a data atual para mostrar '08 -Jul-2020 '. Usar ‘mmm’ para o mês exibe os primeiros três 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 formato:
| 1 | Formato MsgBox (Now (), "dd-mmm-aaaa 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 oposição a um relógio de 24 horas.
Você pode incorporar caracteres de texto em sua string de formato:
| 1 | Formato MsgBox (Now (), "dd-mmm-aaaa hh: mm AM / PM" "hoje" "") |
Isso exibirá '08 -Jul-2020 01:25 de hoje'
Você também pode usar caracteres literais usando uma barra invertida na frente, da mesma forma que para strings 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úmero, embora alguns deles vão além do que está normalmente disponível na janela pop-up. Além disso, você não tem flexibilidade sobre o número de casas decimais ou se separadores de milhares são usados.
Número Geral
Este formato exibirá o número exatamente como ele é
| 1 | Formato MsgBox (1234567.89, "Número Geral") |
O resultado será 1234567,89
Moeda
| 1 | Formato MsgBox (1234567.894, "Moeda") |
Este formato adicionará um símbolo de moeda na frente do número, por exemplo, $, £ dependendo da sua localidade, mas também formatará o número com 2 casas decimais e separará os milhares com vírgulas.
O resultado será $ 1.234.567,89
Fixo
| 1 | Formato MsgBox (1234567.894, "Fixo") |
Este formato exibe pelo menos um dígito à esquerda, mas apenas dois dígitos à direita da vírgula decimal.
O resultado será 1234567,89
Padrão
| 1 | Formato MsgBox (1234567.894, "Padrão") |
Isso exibe o número com os mil separadores, mas apenas com duas casas decimais.
O resultado será 1.234.567,89
Por cento
| 1 | Formato MsgBox (1234567.894, "Porcentagem") |
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
| 1 | Formato MsgBox (1234567.894, "Científico") |
Isso converte o número para o formato exponencial
O resultado será 1.23E + 06
Sim não
| 1 | Formato MsgBox (1234567.894, "Sim / Não") |
Isso exibe 'Não' se o número for zero, caso contrário, exibe 'Sim'
O resultado será 'Sim'
Verdadeiro falso
| 1 | Formato MsgBox (1234567.894, "Verdadeiro / Falso") |
Isso exibe 'Falso' se o número for zero, caso contrário, exibe 'Verdadeiro'
O resultado será ‘Verdadeiro’
Ligado desligado
| 1 | Formato MsgBox (1234567.894, "On / Off") |
Isso exibe 'Desligado' se o número for zero, caso contrário, exibe 'Ligado'
O resultado será ‘On’
Data Geral
| 1 | Formato MsgBox (Now (), "Data Geral") |
Isso exibirá a data como data e hora usando a notação AM / PM. A forma como a data é exibida depende de suas configurações no Painel de Controle do Windows (Relógio e Região | Região). Pode ser exibido como ‘mm / dd / aaaa’ ou ‘dd / mm / aaaa’
O resultado será ‘7/7/2020 3:48:25 PM’
Data Longa
| 1 | Formato MsgBox (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 não inclui o tempo.
O resultado será ‘terça-feira, 7 de julho de 2022’
Data Média
| 1 | Formato MsgBox (Now (), "Medium Date") |
Isso exibe uma data conforme definida nas configurações de data abreviada conforme definido por local no Painel de Controle do Windows.
O resultado será '07 -Jul-20 '
Encontro curto
| 1 | Formato MsgBox (Now (), "Short Date") |
Exibe uma data abreviada conforme definido no Painel de controle do Windows (Relógio e região | Região). A forma como a data é exibida depende da sua localidade. Pode ser exibido como ‘mm / dd / aaaa’ ou ‘dd / mm / aaaa’
O resultado será ‘7/7/2020’
Muito tempo
| 1 | Formato MsgBox (Now (), "Long Time") |
Exibe um longo tempo conforme definido no Painel de Controle do Windows (Relógio e Região | Região).
O resultado será "4:11:39 PM"
Tempo Médio
| 1 | Formato MsgBox (Now (), "Medium Time") |
Exibe um tempo médio conforme definido por sua localidade no Painel de Controle do Windows. Isso geralmente é definido como formato de 12 horas usando horas, minutos e segundos e o formato AM / PM.
O resultado será '04:15'
Tempo curto
| 1 | Formato MsgBox (Now (), "Short Time") |
Exibe um tempo médio conforme definido no Painel de Controle do Windows (Relógio e Região | Região). Isso geralmente é definido como formato de 24 horas com horas e minutos
O resultado será '16: 18 '
Perigos de usar formatos predefinidos do Excel em datas e horários
O uso de formatos predefinidos para datas e horas no Excel VBA é muito dependente das configurações no Painel de Controle do Windows e também de como o local está definido
Os usuários podem alterar facilmente essas configurações, e isso terá um efeito em como suas datas e horas são exibidas no Excel
Por exemplo, se você desenvolver um aplicativo Excel que usa formatos predefinidos em seu código VBA, eles podem mudar completamente se um usuário estiver em um país diferente ou usando uma localidade diferente da sua. Você pode descobrir que as larguras das colunas não se ajustam à definição de data ou, em um formulário de usuário, o controle Active X, como um controle de caixa de combinação (suspenso), é muito estreito para que as datas e horas sejam exibidas corretamente.
Você precisa considerar onde o público está geograficamente ao desenvolver seu aplicativo Excel
Formatos definidos pelo usuário para números
Existem vários parâmetros diferentes que você pode usar ao definir sua string de formato:
| Personagem | Descrição |
| String Nula | Sem formatação |
| 0 | Marcador de posição de dígito. Exibe um dígito ou zero. Se houver um dígito para essa posição, ele exibe o dígito, caso contrário, exibe 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 mostrado pelos zeros. Se houver mais dígitos antes da vírgula decimal do que zeros, eles serão exibidos normalmente. |
| # | Marcador de posição de dígito. Isso exibe um dígito ou nada. Ele funciona da mesma forma que o marcador de posição zero acima, exceto que os zeros à esquerda e à direita não são exibidos. Por exemplo, 0,75 seria exibido usando zero marcadores de posição, mas seria 0,75 usando # marcadores de posição. |
| . Ponto decimal. | Apenas um permitido por string de formato. Este personagem depende das configurações no Painel de Controle do Windows. |
| % | Marcador de posição de porcentagem. Multiplica o número por 100 e coloca o caractere% onde ele aparece na string de formato |
| , (vírgula) | Separador de milhar. Isso é usado se 0 ou # marcadores de posição forem usados e a string de formato contiver uma vírgula. Uma vírgula à esquerda da vírgula decimal indica o arredondamento para o milhar mais próximo. Por exemplo. ## 0, Duas vírgulas adjacentes à esquerda do separador de mil indicam o arredondamento para o milhão mais próximo. Por exemplo. ## 0 ,, |
| E- E + | Formato científico. Isso exibe o número exponencialmente. |
| : (dois pontos) | Separador de hora - usado ao formatar 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 do listado aqui, coloque uma barra invertida (\) antes dele |
Formatos definidos pelo usuário para datas e horários
Todos esses caracteres podem ser usados em sua string de formatação ao formatar datas e horas:
| Personagem | Significado |
| c | Exibe a data como ddddd e a hora como ttttt |
| d | Mostra o dia como um número sem zero à esquerda |
| dd | Mostra o dia como um número com zero à esquerda |
| ddd | Exibe o dia como uma abreviatura (dom - sáb) |
| dddd | Exibir o nome completo do dia (domingo - sábado) |
| ddddd | Exibe um número de série de data como uma data completa de acordo com a data abreviada 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 Long Date nas configurações internacionais do Painel de controle do Windows. |
| C | Exibe o dia da semana como um número (1 = domingo) |
| ww | Exibe a semana do ano como um número (1-53) |
| m | Mostra o mês como um número sem zero à esquerda |
| milímetros | Mostra o mês como um número com zeros à esquerda |
| mmm | Exibe o mês como uma abreviatura (janeiro a dezembro) |
| mmmm | Exibe o nome completo do mês (janeiro - 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 |
| aaaa | Mostra o ano como um número de quatro dígitos |
| h | Mostra a hora como um número sem zero à esquerda |
| hh | Mostra a hora como um número com zero à esquerda |
| n | Mostra o minuto como um número sem zero à esquerda |
| nn | Mostra o minuto como um número com zero à esquerda |
| s | Mostra o segundo como um número sem zero à esquerda |
| WL | Mostra o segundo como um número com zero à esquerda |
| ttttt | Exibe um número de série de hora como uma hora completa. |
| MANHÃ TARDE | Use um relógio de 12 horas e exiba AM ou PM para indicar antes ou depois do meio-dia. |
| manhã tarde | 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 |
