Formateo de números en Excel VBA

Formateo de números en Excel VBA

Los números tienen todo tipo de formatos en las hojas de cálculo de Excel. Puede que ya estés familiarizado con la ventana emergente de Excel para hacer uso de diferentes formatos numéricos:

El formato de los números facilita su lectura y comprensión. El formato predeterminado de Excel para los números introducidos en las celdas es el «General», lo que significa que el número se muestra exactamente como lo has escrito.

Por ejemplo, si introduces un número redondo, por ejemplo 4238, se mostrará como 4238 sin punto decimal ni separadores de miles. Un número decimal como 9325,89 se mostrará con el punto decimal y los decimales. Esto significa que no se alineará en la columna con los números redondos, y se verá extremadamente desordenado.

Además, sin mostrar los separadores de miles, es difícil ver el tamaño real de un número sin contar los dígitos individuales. ¿Está en millones o en decenas de millones?

Desde el punto de vista de un usuario que mira hacia abajo en una columna de números, esto hace que sea bastante difícil de leer y comparar.

 

En VBA tienes acceso a exactamente la misma gama de formatos que tienes en el front-end de Excel. Esto se aplica no sólo a un valor introducido en una celda de una hoja de cálculo, sino también a cosas como cuadros de mensajes, controles de UserForm, tablas y gráficos, y la barra de estado de Excel en la esquina inferior izquierda de la hoja de cálculo.

La función Format es una función extremadamente útil en VBA en términos de presentación, pero también es muy compleja en términos de la flexibilidad ofrecida en cómo se muestran los números.

Cómo utilizar la función Format en VBA

Si usted está mostrando un cuadro de mensaje, entonces la función Format puede ser utilizada directamente:

MsgBox Format(1234567.89, "#.##0,00")

Esto mostrará un número grande usando puntos para separar los miles y comas para mostrar 2 decimales. El resultado será 1.234.567,89. Los ceros en lugar de la coma aseguran que los decimales se muestren como 00 en los números enteros, y que haya un cero inicial para un número que sea menor que 1

El símbolo de la almohadilla (#) representa un marcador de posición de dígitos que muestra un dígito si está disponible en esa posición, o bien nada.

También puede utilizar la función de formato para dirigirse a una celda individual, o a un rango de celdas para cambiar el formato:

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

Este código establecerá el rango de celdas (A1 a A10) con un formato personalizado que separa los miles con puntos y muestra 2 decimales. Si compruebas el formato de las celdas en la pantalla principal de Excel, verás que se ha creado un nuevo formato personalizado. También puede formatear los números en la barra de estado de Excel, en la esquina inferior izquierda de la ventana de Excel:

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

 

Esto se borra de la barra de estado utilizando:

Application.StatusBar = ""

Creación de una cadena formato.

Este ejemplo añadirá el texto ‘Total de ventas’ después de cada número, además de incluir un separador de miles

Sheets("Hoja4").Range("A1:A8").NumberFormat = "#,##0.00"" Total Ventas"""

Este es el aspecto que tendrán sus números:

Observe que la celda A6 tiene una fórmula ‘SUM’, y ésta incluirá el texto «Total de ventas» sin necesidad de darle formato. Si se aplica el formato, como en el código anterior, no pondrá una instancia extra de ‘Total de ventas’ en la celda A6.

Aunque las celdas ahora muestran caracteres alfanuméricos, los números siguen estando presentes en forma numérica. La fórmula ‘SUMA’ sigue funcionando porque está utilizando el valor numérico en el fondo, no cómo está formateado el número.

La coma en la cadena de formato proporciona el separador de miles. Tenga en cuenta que sólo tiene que ponerlo en la cadena una vez. Si el número es de millones o billones, seguirá separando los dígitos en grupos de 3.

El cero en la cadena de formato (0) es un marcador de dígitos. Muestra un dígito si está ahí, o un cero. Su colocación es muy importante para asegurar la uniformidad del formato

En la cadena de formato, los caracteres hash (#) no mostrarán nada si no hay ningún dígito. Sin embargo, si hay un número como .8 (todos los decimales), queremos que se muestre como 0.80 para que se alinee con los otros números.

Utilizando un solo cero a la izquierda del punto decimal y dos ceros a la derecha del punto decimal en la cadena de formato, se obtendrá el resultado deseado (0,80).

Si sólo hubiera un cero a la derecha del punto decimal, el resultado sería «0,8» y todo se mostraría con un decimal.

Cómo utilizar una cadena de formato para la alineación

Es posible que queramos ver todos los números decimales de un rango alineados en sus puntos decimales, de manera que todos los puntos decimales estén directamente debajo de cada uno, por muchos lugares de decimales que haya en cada número. Para ello, puede utilizar un signo de interrogación (?) dentro de su cadena de formato. El ‘?’ indica que se muestra un número si está disponible, o un espacio

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

Esto mostrará los números de la siguiente manera:

 

Todos los puntos decimales ahora se alinean uno debajo del otro. La celda A5 tiene tres posiciones decimales y esto desviaría la alineación normalmente, pero el uso del carácter ‘?’ alinea todo perfectamente.

Uso de caracteres literales para cadena de formato.

Puedes añadir cualquier carácter literal a tu cadena de formato precediéndolo de una barra invertida (\).

Suponga que quiere mostrar un indicador de moneda particular para sus números que no se basa en su localidad. El problema es que si utilizas un indicador de moneda, Excel se refiere automáticamente a tu local y lo cambia por el apropiado para la configuración regional que está configurada en el Panel de Control de Windows. Esto podría tener implicaciones si tu aplicación de Excel se distribuye en otros países y quieres asegurarte de que, sea cual sea la configuración regional, el indicador de moneda sea siempre el mismo.

También puede querer indicar que los números están en millones en el siguiente ejemplo:

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

Esto producirá los siguientes resultados en su hoja de cálculo:

 

Al utilizar una barra invertida para mostrar caracteres literales, no es necesario utilizar una barra invertida para cada carácter individual dentro de una cadena. Puede utilizar:

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

Esto mostrará ‘mill’ después de cada número dentro del rango formateado. Puede utilizar la mayoría de los caracteres como literales, pero no los caracteres reservados como 0, #,?

Uso de comas en una cadena de formato

Ya hemos visto que las comas pueden utilizarse para crear separadores de miles para números grandes, pero también pueden utilizarse de otra manera. Al utilizarlas al final de la parte numérica de la cadena de formato, actúan como escaladores de miles. En otras palabras, dividirán cada número por 1.000 cada vez que haya una coma. En los datos del ejemplo, los mostramos con un indicador de que están en millones. Insertando una coma en la cadena de formato, podemos mostrar esos números divididos por 1.000.

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

Esto mostrará los números divididos por 1.000 aunque el número original seguirá estando en el fondo de la celda. Si pone dos comas en la cadena de formato, entonces los números se dividirán por un millón

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

Este será el resultado utilizando sólo una coma (dividir por 1.000):

Creación de formato condicional dentro de cadena de fomato.

Puedes configurar el formato condicional en la parte frontal de Excel, pero también puedes hacerlo dentro de tu código VBA, lo que significa que puedes manipular el formato de cadena programáticamente para hacer cambios. Puede utilizar hasta cuatro secciones dentro de su cadena de formato. Cada sección está delimitada por un punto y coma (;). Las cuatro secciones corresponden a positivo, negativo, cero y texto

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

En este ejemplo, utilizamos los mismos caracteres de almohadilla, coma y cero para proporcionar separadores de miles y dos puntos decimales, pero ahora tenemos secciones diferentes para cada tipo de valor.

La primera sección es para los números positivos y no difiere de lo que ya hemos visto anteriormente en cuanto al formato.

La segunda sección para los números negativos introduce un color (Rojo) que se mantiene dentro de un par de corchetes. El formato es el mismo que para los números positivos, salvo que se ha añadido un signo menos (-) delante.

La tercera sección para los números cero utiliza un color (Verde) dentro de corchetes con la cadena numérica igual que para los números positivos.

La última sección es para los valores de texto, y todo lo que necesita es un color (azul) de nuevo entre corchetes.

Este es el resultado de aplicar esta cadena de formato:

Puede ir más allá con las condiciones dentro de la cadena de formato. Supongamos que quiere mostrar todos los números positivos por encima de 10.000 como verdes, y todos los demás números como rojos, podría utilizar esta cadena de formato:

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

Esta cadena de formato incluye las condiciones para >=10000 entre corchetes, de modo que el verde sólo se utilizará cuando el número sea mayor o igual a 10000

Este es el resultado:

 

Uso de fracciones en cadena de formato.

Las fracciones no se utilizan a menudo en las hojas de cálculo, ya que normalmente equivalen a decimales con los que todo el mundo está familiarizado. Sin embargo, a veces sirven para algo. Este ejemplo mostrará dólares y centavos:

Sheets("Hoja4").Range("A1:A7").NumberFormat = "#,##0 "" dollars and "" 00/100 "" cents """

Este es el resultado que se producirá:

Recuerde que a pesar de que los números se muestren como texto, siguen estando ahí en el fondo como números y se pueden seguir utilizando todas las fórmulas de Excel sobre ellos.

Formatos de fecha y hora

Las fechas son en realidad números y puedes usar formatos en ellas de la misma manera que para los números. Si formateas una fecha como un número numérico, verás un número grande a la izquierda del punto decimal y un número de decimales. El número a la izquierda del punto decimal muestra el número de días a partir del 01-Enero-1900, y los decimales muestran la hora basada en 24 horas.

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

Esto formateará la fecha actual para mostrar ’08-Jul-2020′. El uso de ‘mmm’ para el mes muestra los tres primeros caracteres del nombre del mes. Si desea el nombre completo del mes, utilice ‘mmmm’

Puedes incluir tiempos en tu cadena de formato:

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

Esto mostrará ’08-Jul-2020 01:25 PM’ hh:mm’ representa las horas y los minutos y AM/PM utiliza un reloj de 12 horas en lugar de uno de 24 horas. Puede incorporar caracteres de texto en su cadena de formato:

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

Esto mostrará ’08-Jul-2020 01:25 PM hoy’ También puede utilizar caracteres literales utilizando una barra invertida delante de la misma manera que para las cadenas de formato numérico.

Formatos predefinidos

Excel tiene una serie de formatos incorporados tanto para números como para fechas que puedes utilizar en tu código. Éstos reflejan principalmente lo que está disponible en la parte frontal del formato numérico, aunque algunos de ellos van más allá de lo que normalmente está disponible en la ventana emergente. Además, no tiene la flexibilidad sobre el número de decimales, o si se utilizan separadores de miles.

Número General

Este formato mostrará el número exactamente como es

MsgBox Format(1234567.89, "General Number")

El resultado será 1234567.89

Moneda

MsgBox Format(1234567.894, "Currency")

Este formato añadirá un símbolo de moneda delante del número, por ejemplo, $, £, dependiendo de su localidad, pero también formateará el número con dos decimales y separará los miles con comas. El resultado será $1,234,567.89

Fijo

MsgBox Format(1234567.894, "Fixed")

Este formato muestra al menos un dígito a la izquierda pero sólo dos dígitos a la derecha del punto decimal. El resultado será 1234567.89

Estándar

MsgBox Format(1234567.894, "Standard")

Esto muestra el número con los separadores de mil, pero sólo con dos decimales. El resultado será 1.234.567,89

Porcentaje

MsgBox Format(1234567.894, "Percent")

El número se multiplica por 100 y se añade un símbolo de porcentaje (%) al final del número. El formato se muestra con 2 decimales El resultado será 123456789,40%

Científico

MsgBox Format(1234567.894, "Scientific")

Esto convierte el número en formato exponencial El resultado será 1.23E+06

Sí/No

MsgBox Format(1234567.894, "Yes/No")

Esto muestra «No» si el número es cero, de lo contrario muestra «Sí El resultado será «Sí»

Verdadero/Falso

MsgBox Format(1234567.894, "True/False")

Esto muestra ‘False’ si el número es cero, de lo contrario muestra ‘True’ El resultado será ‘True’

Activado/Desactivado

MsgBox Format(1234567.894, "On/Off")

Esto muestra ‘Off’ si el número es cero, de lo contrario muestra ‘On’ El resultado será ‘On’

Fecha general

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

Esto mostrará la fecha como fecha y hora utilizando la notación AM/PM. La forma en que se muestra la fecha depende de su configuración en el Panel de Control de Windows (Reloj y Región | Región). Puede mostrarse como «mm/dd/aaaa» o «dd/mm/aaaa» El resultado será «7/7/2020 3:48:25 PM»

Fecha larga

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

Esto mostrará una fecha larga como la definida en el Panel de Control de Windows (Reloj y Región | Región). Tenga en cuenta que no incluye la hora. El resultado será «Martes, 7 de julio de 2020»

Fecha media

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

Esto muestra una fecha tal y como se define en la configuración de fecha corta según la configuración regional en el Panel de Control de Windows. El resultado será «07-Jul-20»

Fecha corta

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

Muestra una fecha corta como la definida en el Panel de Control de Windows (Reloj y Región | Región). La forma en que se muestra la fecha depende de su configuración regional. Puede mostrarse como ‘mm/dd/aaaa’ o ‘dd/mm/aaaa’ El resultado será «7/7/2020»

Hora larga

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

Muestra la hora larga definida en el Panel de Control de Windows (Reloj y Región | Región). El resultado será ‘4:11:39 PM’

Hora media

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

Muestra una hora media como la definida por su configuración regional en el Panel de Control de Windows. Normalmente se establece como formato de 12 horas utilizando horas, minutos y segundos y el formato AM/PM. El resultado será «04:15 PM»

Hora corta

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

Muestra una hora media como la definida en el Panel de Control de Windows (Reloj y Región | Región). Normalmente se establece como formato de 24 horas con horas y minutos El resultado será ’16:18′

Peligros de utilizar los formatos predefinidos de Excel en fechas y horas

El uso de los formatos predefinidos para las fechas y horas en Excel VBA depende en gran medida de la configuración del Panel de Control de Windows y también de la configuración regional

Los usuarios pueden alterar fácilmente estas configuraciones, y esto tendrá un efecto en cómo se muestran las fechas y horas en Excel

Por ejemplo, si desarrolla una aplicación de Excel que utiliza formatos predefinidos dentro de su código VBA, éstos pueden cambiar completamente si un usuario está en un país diferente o utiliza una configuración regional diferente a la suya. Es posible que el ancho de las columnas no se ajuste a la definición de la fecha, o que en un formulario de usuario el control Active X, como un control de cuadro combinado (desplegable), sea demasiado estrecho para que las fechas y horas se muestren correctamente.

Debe tener en cuenta dónde se encuentra el público geográficamente cuando desarrolle su aplicación de Excel

Formatos Definidos por el Usuario para Números

Hay una serie de parámetros diferentes que puede utilizar al definir su cadena de formato:

Caracteres	Descripción
Null String	Sin formato
0	Marcador de posición de dígitos. Muestra un dígito o un cero. Si hay un dígito para esa posición entonces muestra el dígito de lo contrario muestra 0. Si hay menos dígitos que ceros, entonces obtendrá ceros iniciales o finales. Si hay más dígitos después del punto decimal que ceros, entonces el número se redondea al número de decimales que muestran los ceros. Si hay más dígitos antes del punto decimal que ceros, éstos se mostrarán normalmente.
#	Marcador de posición de dígitos. Muestra un dígito o nada. Funciona igual que el marcador de posición cero anterior, excepto que los ceros iniciales y finales no se muestran. Por ejemplo, 0,75 se mostraría utilizando marcadores de posición cero, pero sería 0,75 utilizando marcadores de posición #.
. Punto decimal.	Sólo se permite uno por cadena de formato. Este carácter depende de la configuración del panel de control de Windows.
%	Marcador de porcentaje. Multiplica el número por 100 y coloca el carácter % donde aparece en la cadena de formato
, (coma)	Separador de miles. Se utiliza si se usan marcadores de posición 0 o # y la cadena de formato contiene una coma. Una coma a la izquierda del punto decimal indica el redondeo al millar más cercano. Por ejemplo, ##0, Dos comas adyacentes a la izquierda del separador de miles indican el redondeo al millón más cercano. Por ejemplo: ##0,,
E- E+	Formato científico. Muestra el número de forma exponencial.
: (dos puntos)	Separador de tiempo: se utiliza al formatear una hora para dividir las horas, los minutos y los segundos.
/	Separador de fecha – se utiliza cuando se especifica un formato para una fecha
– + £ $ ( )	Muestra un carácter literal. Para mostrar un carácter distinto de los indicados aquí, debe precederlo de una barra invertida (\)

Formatos definidos por el usuario para fechas y horas

Todos estos caracteres pueden ser utilizados en su cadena de formato cuando se formatean fechas y horas:

Carácter	Significado
c	Muestra la fecha como ddddd y la hora como ttttt
d	Muestra el día como un número sin el cero a la izquierda
dd	Muestra el día como un número con un cero a la izquierda
ddd	Mostrar el día como una abreviatura (dom – sáb)
dddd	Mostrar el nombre completo del día (domingo – sábado)
ddddd	Mostrar un número de serie de la fecha como una fecha completa de acuerdo con la fecha corta en la configuración internacional del panel de control de Windows
dddddd	Muestra un número de serie de fecha como una fecha completa de acuerdo con la fecha larga en la configuración internacional del panel de control de Windows.
w	Muestra el día de la semana como un número (1 = domingo)
ww	Muestra la semana del año como un número (1-53)
m	Muestra el mes como un número sin el cero inicial
mm	Muestra el mes como un número con ceros a la izquierda
mmm	Muestra el mes como una abreviatura (enero-diciembre)
mmmm	Muestra el nombre completo del mes (enero-diciembre)
q	Muestra el trimestre del año como un número (1-4)
y	Muestra el día del año como un número (1-366)
yy	Muestra el año como un número de dos dígitos
yyyy	Muestra el año como un número de cuatro dígitos
h	Muestra la hora como un número sin el cero inicial
hh	Muestra la hora como un número con un cero a la izquierda
n	Muestra los minutos como un número sin cero a la izquierda
nn	Muestra los minutos como un número con un cero a la izquierda
s	Muestra el segundo como un número sin cero a la izquierda
ss	Muestra el segundo como un número con cero a la izquierda
ttttt	Muestra un número de serie de la hora como una hora completa.
AM/PM	Utiliza un reloj de 12 horas y muestra AM o PM para indicar antes o después del mediodía.
am/pm	Utilice un reloj de 12 horas y use am o pm para indicar antes o después del mediodía
A/P	Utilice un reloj de 12 horas y use A o P para indicar antes o después del mediodía
a/p	Utilice un reloj de 12 horas y use a o p para indicar antes o después del mediodía