Formatear números en Excel VBA
Los números vienen en todo tipo de formatos en hojas de cálculo de Excel. Es posible que ya esté familiarizado con la ventana emergente en Excel para hacer uso de diferentes formatos numéricos:
El formato de los números hace que los números sean más fáciles de leer y comprender. El formato predeterminado de Excel para los números ingresados en las celdas es "General", lo que significa que el número se muestra exactamente como lo ingresó.
Por ejemplo, si ingresa un número redondo, p. Ej. 4238, se mostrará como 4238 sin punto decimal ni separadores de miles. Se mostrará un número decimal como 9325.89 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 qué tan grande es realmente un número sin contar los dígitos individuales. ¿Está en millones o decenas de millones?
Desde el punto de vista de un usuario que mira hacia abajo una columna de números, esto hace que sea bastante difícil de leer y comparar.
En VBA, tiene acceso a exactamente la misma gama de formatos que tiene en la interfaz de Excel. Esto se aplica no solo a un valor ingresado en una celda en una hoja de trabajo, sino también a cosas como cuadros de mensaje, controles de UserForm, cuadros y gráficos, y la barra de estado de Excel en la esquina inferior izquierda de la hoja de trabajo.
La función Formato 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 que ofrece la forma en que se muestran los números.
Cómo utilizar la función de formato en VBA
Si muestra un cuadro de mensaje, la función de formato se puede utilizar directamente:
| 1 | Formato de MsgBox (1234567.89, "#, ## 0.00") |
Esto mostrará un número grande usando comas para separar los miles y mostrar 2 lugares decimales. El resultado será 1.234.567,89. Los ceros en lugar del hash aseguran que los decimales se muestren como 00 en números enteros y que haya un cero a la izquierda para un número menor que 1
El símbolo de hashtag (#) representa un marcador de posición de dígitos que muestra un dígito si está disponible en esa posición, o nada.
También puede usar la función de formato para abordar una celda individual o un rango de celdas para cambiar el formato:
| 1 | Hojas ("Hoja1"). Rango ("A1: A10"). NumberFormat = "#, ## 0.00" |
Este código establecerá el rango de celdas (A1 a A10) en un formato personalizado que separa los miles con comas y muestra 2 lugares decimales.
Si comprueba el formato de las celdas en la interfaz de Excel, encontrará que se ha creado un nuevo formato personalizado.
También puede formatear números en la barra de estado de Excel en la esquina inferior izquierda de la ventana de Excel:
| 1 | Application.StatusBar = Formato (1234567.89, "#, ## 0.00") |
Borra esto de la barra de estado usando:
| 1 | Application.StatusBar = "" |
Crear una cadena de formato
Este ejemplo agregará el texto "Ventas totales" después de cada número, además de incluir un separador de miles.
| 1 | Hojas ("Hoja1"). Rango ("A1: A6"). NumberFormat = "#, ## 0.00" "Ventas totales" "" |
Así es como se verán sus números:
Tenga en cuenta que la celda A6 tiene una fórmula "SUMA", que incluirá el texto "Ventas totales" sin necesidad de formato. Si se aplica el formato, como en el código anterior, no colocará una instancia adicional de "Ventas totales" en la celda A6.
Aunque las celdas ahora muestran caracteres alfanuméricos, los números todavía están presentes en forma numérica. La fórmula "SUMA" todavía funciona porque utiliza el valor numérico en segundo plano, no cómo se formatea el número.
La coma en la cadena de formato proporciona el separador de miles. Tenga en cuenta que solo necesita poner esto en la cadena una vez. Si el número asciende a millones o miles de millones, aún separará los dígitos en grupos de 3
El cero en la cadena de formato (0) es un marcador de posición de dígitos. Muestra un dígito si está ahí, o un cero. Su posicionamiento es muy importante para asegurar la uniformidad con el 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.
Al usar un solo cero a la izquierda del punto decimal y dos ceros a la derecha del punto decimal en la cadena de formato, esto dará el resultado requerido (0,80).
Si solo hubiera un cero a la derecha del punto decimal, el resultado sería "0,8" y todo se mostraría con un decimal.
Usar una cadena de formato para la alineación
Es posible que queramos ver todos los números decimales en un rango alineados en sus puntos decimales, de modo que todos los puntos decimales estén directamente uno debajo del otro, sin importar cuántos lugares de decimales haya en cada número.
Puede usar un signo de interrogación (?) Dentro de su cadena de formato para hacer esto. El "?" Indica que se muestra un número si está disponible, o un espacio
| 1 | Hojas ("Hoja1"). Rango ("A1: A6"). NumberFormat = "#, ## 0.00 ??" |
Esto mostrará sus números de la siguiente manera:
Todos los puntos decimales ahora se alinean uno debajo del otro. La celda A5 tiene tres lugares decimales y esto arrojaría la alineación normalmente, pero el uso del carácter "?" Alinea todo perfectamente.
Uso de caracteres literales dentro de la cadena de formato
Puede agregar cualquier carácter literal en su cadena de formato precediéndola con una barra invertida (\).
Suponga que desea mostrar un indicador de moneda particular para sus números que no se basa en su localidad. El problema es que si usa un indicador de moneda, Excel automáticamente se refiere a su local y lo cambia a uno apropiado para la localidad que está configurada en el Panel de control de Windows. Esto podría tener implicaciones si su aplicación de Excel se distribuye en otros países y desea asegurarse de que, sea cual sea la ubicación, el indicador de moneda sea siempre el mismo.
También puede indicar que los números están en millones en el siguiente ejemplo:
| 1 | Hojas ("Hoja1"). Rango ("A1: A6"). NumberFormat = "\ $ #, ## 0.00 \ m" |
Esto producirá los siguientes resultados en su hoja de trabajo:
Al usar una barra invertida para mostrar caracteres literales, no es necesario usar una barra invertida para cada carácter individual dentro de una cadena. Puedes usar:
| 1 | Hojas ("Hoja1"). Rango ("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 caracteres reservados como 0, # ,?
Uso de comas en una cadena de formato
Ya hemos visto que las comas se pueden usar para crear separadores de miles para números grandes, pero también se pueden usar de otra manera.
Al usarlos 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 de ejemplo, lo estamos mostrando con un indicador de que está en millones. Insertando una coma en la cadena de formato, podemos mostrar esos números divididos por 1,000.
| 1 | Hojas ("Hoja1"). Rango ("A1: A6"). NumberFormat = "\ $ #, ## 0.00, \ m" |
Esto mostrará los números divididos por 1,000, aunque el número original seguirá estando en segundo plano en la celda.
Si pones dos comas en la cadena de formato, los números se dividirán por un millón.
| 1 | Hojas ("Hoja1"). Rango ("A1: A6"). NumberFormat = "\ $ #, ## 0.00 ,, \ m" |
Este será el resultado usando solo una coma (dividir por 1,000):
Creación de formato condicional dentro de la cadena de formato
Puede configurar el formato condicional en la interfaz de Excel, pero también puede hacerlo dentro de su código VBA, lo que significa que puede manipular la cadena de formato mediante programación para realizar 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
| 1 | Rango ("A1: A7"). NumberFormat = "#, ## 0.00; [Rojo] - #, ## 0.00; [Verde] #, ## 0.00; [Azul]” |
En este ejemplo, usamos el mismo hash, coma y cero caracteres para proporcionar miles de separadores y dos puntos decimales, pero ahora tenemos diferentes secciones para cada tipo de valor.
La primera sección es para números positivos y no es diferente a lo que ya hemos visto anteriormente en términos de formato.
La segunda sección para 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, excepto que se ha agregado un signo menos (-) al frente.
La tercera sección para números cero usa un color (verde) entre corchetes con la cadena numérica igual que para los números positivos.
La sección final es para valores de texto, y todo lo que necesita es un color (azul) nuevamente 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. Suponga que desea mostrar cada número positivo por encima de 10,000 como verde, y todos los demás números como rojo, podría usar esta cadena de formato:
| 1 | Rango ("A1: A7"). NumberFormat = "[> = 10000] [Verde] #, ## 0,00; [<10000] [Rojo] #, ## 0,00" |
Esta cadena de formato incluye condiciones para> = 10000 entre corchetes para que el verde solo se use cuando el número sea mayor o igual a 10000
Este es el resultado:
Usar fracciones en el formato de cadenas
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 tienen un propósito. Este ejemplo mostrará dólares y centavos:
| 1 | Rango ("A1: A7"). NumberFormat = "#, ## 0" "dólares y" "00/100" "centavos" "" |
Este es el resultado que se producirá:
Recuerde que a pesar de que los números se muestran como texto, todavía están en segundo plano como números y todas las fórmulas de Excel aún se pueden usar en ellos.
Formatos de fecha y hora
Las fechas son en realidad números y puede usar formatos en ellas de la misma manera que para los números. Si formatea una fecha como un número numérico, verá un número grande a la izquierda del punto decimal y una cantidad de lugares decimales. El número a la izquierda del punto decimal muestra el número de días a partir del 1 de enero de 1900, y los lugares decimales muestran el tiempo basado en 24 horas.
| 1 | Formato de MsgBox (Now (), "dd-mmm-aaaa") |
Esto formateará la fecha actual para mostrar "08 -Jul-2020". El uso de "mmm" para el mes muestra los primeros tres caracteres del nombre del mes. Si desea el nombre completo del mes, utilice "mmmm"
Puede incluir horas en su cadena de formato:
| 1 | Formato de MsgBox (Now (), "dd-mmm-aaaa hh: mm AM / PM") |
Esto mostrará "08 -Jul-2020 01:25 PM"
"Hh: mm" representa horas y minutos y AM / PM utiliza un reloj de 12 horas en lugar de un reloj de 24 horas.
Puede incorporar caracteres de texto en su cadena de formato:
| 1 | Formato MsgBox (Ahora (), "dd-mmm-aaaa hh: mm AM / PM" "hoy" "") |
Esto mostrará "08 -Jul-2020 01:25 PM hoy"
También puede usar caracteres literales usando una barra invertida al frente de la misma manera que para las cadenas de formato numérico.
Formatos predefinidos
Excel tiene varios formatos integrados para números y fechas que puede usar en su código. Estos reflejan principalmente lo que está disponible en la interfaz de formato de números, 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 lugares decimales o si se utilizan separadores de miles.
Número general
Este formato mostrará el número exactamente como es
| 1 | Formato de MsgBox (1234567.89, "Número general") |
El resultado será 1234567.89
Divisa
| 1 | Formato MsgBox (1234567.894, "Moneda") |
Este formato agregará un símbolo de moneda delante del número, p. Ej. $, £ dependiendo de su ubicación, pero también formateará el número a 2 lugares decimales y separará los miles con comas.
El resultado será $ 1,234,567.89
Reparado
| 1 | Formato de MsgBox (1234567.894, "Fijo") |
Este formato muestra al menos un dígito a la izquierda pero solo dos dígitos a la derecha del punto decimal.
El resultado será 1234567.89
Estándar
| 1 | Formato MsgBox (1234567.894, "Estándar") |
Esto muestra el número con separadores de miles, pero solo con dos decimales.
El resultado será 1.234.567,89
Por ciento
| 1 | Formato de MsgBox (1234567.894, "Porcentaje") |
El número se multiplica por 100 y se agrega un símbolo de porcentaje (%) al final del número. El formato se muestra con 2 decimales.
El resultado será 123456789.40%
Científico
| 1 | Formato MsgBox (1234567.894, "Científico") |
Esto convierte el número a formato exponencial
El resultado será 1.23E + 06
Sí No
| 1 | Formato MsgBox (1234567.894, "Sí / No") |
Esto muestra "No" si el número es cero; de lo contrario, muestra "Sí"
El resultado será "Sí"
Verdadero Falso
| 1 | Formato de MsgBox (1234567.894, "Verdadero / Falso") |
Esto muestra "Falso" si el número es cero, de lo contrario muestra "Verdadero"
El resultado será "Verdadero"
Encendido apagado
| 1 | Formato de MsgBox (1234567.894, "Activar / Desactivar") |
Esto muestra "Apagado" si el número es cero; de lo contrario, muestra "Encendido"
El resultado será "Activado"
Fecha general
| 1 | Formato de MsgBox (Now (), "Fecha general") |
Esto mostrará la fecha como fecha y hora usando la notación AM / PM. La forma en que se muestra la fecha depende de la 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 p.m."
Fecha larga
| 1 | Formato de MsgBox (Now (), "Fecha larga") |
Esto mostrará una fecha larga como se define 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 2022".
Medio Fecha
| 1 | Formato de MsgBox (Now (), "Fecha media") |
Esto muestra una fecha como se define en la configuración de fecha corta según lo definido por la configuración regional en el Panel de control de Windows.
El resultado será '07 -Jul-20 '
Cita corta
| 1 | Formato de MsgBox (Now (), "Fecha corta") |
Muestra una fecha corta como se define en el Panel de control de Windows (Reloj y región | Región). La forma en que se muestra la fecha depende de su ubicación. Puede mostrarse como "mm / dd / aaaa" o "dd / mm / aaaa".
El resultado será "7/7/2020"
Largo tiempo
| 1 | Formato de MsgBox (Now (), "Long Time") |
Muestra un tiempo largo como se define en el Panel de control de Windows (Reloj y región | Región).
El resultado será "4:11:39 p.m."
Medio tiempo
| 1 | Formato de MsgBox (Now (), "Tiempo medio") |
Muestra un tiempo medio según lo definido por su configuración regional en el Panel de control de Windows. Por lo general, se establece en formato de 12 horas con horas, minutos y segundos y el formato AM / PM.
El resultado será "04:15 p. M."
Poco tiempo
| 1 | Formato de MsgBox (Now (), "Short Time") |
Muestra un tiempo medio como se define en el Panel de control de Windows (Reloj y región | Región). Por lo general, se establece en formato de 24 horas con horas y minutos.
El resultado será '16: 18 '
Peligros de usar formatos predefinidos de Excel en fechas y horas
El uso de formatos predefinidos para fechas y horas en Excel VBA depende en gran medida de la configuración en el Panel de control de Windows y también de la configuración regional.
Los usuarios pueden modificar fácilmente esta configuración, y esto tendrá un efecto en cómo se muestran sus fechas y horas en Excel.
Por ejemplo, si desarrolla una aplicación de Excel que usa formatos predefinidos dentro de su código VBA, estos pueden cambiar por completo si un usuario está en un país diferente o usa una configuración regional diferente a la suya. Puede encontrar que los anchos de columna no se ajustan a la definición de fecha, o en un formulario de usuario, el control Active X, como un cuadro combinado (menú desplegable), es demasiado estrecho para que las fechas y horas se muestren correctamente.
Debe considerar dónde está la audiencia geográficamente cuando desarrolle su aplicación de Excel
Formatos definidos por el usuario para números
Hay varios parámetros diferentes que puede usar al definir su cadena de formato:
| Personaje | Descripción |
| Cadena nula | 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, muestra el dígito; de lo contrario, muestra 0. Si hay menos dígitos que ceros, 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 lugares decimales mostrados por los ceros. Si hay más dígitos antes del punto decimal que ceros, estos se mostrarán normalmente. |
| # | Marcador de posición de dígitos. Esto 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 cero marcadores de posición, pero esto sería 0,75 utilizando # marcadores de posición. |
| . Punto decimal. | Solo se permite uno por cadena de formato. Este carácter depende de la configuración del Panel de control de Windows. |
| % | Marcador de posición 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 utilizan marcadores de posición 0 o # y la cadena de formato contiene una coma. Una coma a la izquierda del punto decimal indica redondeo al millar más cercano. P.ej. ## 0, Dos comas adyacentes a la izquierda del separador de miles indican redondeo al millón más cercano. P.ej. ## 0 ,, |
| E- E + | Formato científico. Esto muestra el número de forma exponencial. |
| : (colon) | Separador de hora: se utiliza al formatear una hora para dividir horas, minutos y segundos. |
| / | Separador de fecha: se utiliza al especificar un formato para una fecha. |
| - + £ $ ( ) | Muestra un carácter literal.Para mostrar un carácter que no sea el listado aquí, preceda con una barra invertida (\) |
Formatos definidos por el usuario para fechas y horas
Todos estos caracteres se pueden usar en su cadena de formato al formatear fechas y horas:
| Personaje | Sentido |
| C | Muestra la fecha como ddddd y la hora como ttttt |
| D | Muestra el día como un número sin cero a la izquierda |
| dd | Muestra el día como un número con cero a la izquierda |
| ddd | Muestra el día como una abreviatura (dom - sáb) |
| dddd | Muestra el nombre completo del día (domingo a sábado) |
| ddddd | Muestra un número de serie de fecha como una fecha completa de acuerdo con 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 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) |
| metro | Muestra el mes como un número sin cero a la izquierda. |
| mm | Muestra el mes como un número con ceros a la izquierda. |
| mmm | Muestra el mes como 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) |
| aa | Muestra el año como un número de dos dígitos. |
| aaaa | Muestra el año como un número de cuatro dígitos. |
| h | Muestra la hora como un número sin cero a la izquierda. |
| S.S | Muestra la hora como un número con cero a la izquierda. |
| norte | Muestra el minuto como un número sin cero a la izquierda. |
| nn | Muestra el minuto como un número con 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 | Utilice un reloj de 12 horas y muestre AM o PM para indicar antes o después del mediodía. |
| am PM | Use un reloj de 12 horas y use am o pm para indicar antes o después del mediodía |
| A / P | Use un reloj de 12 horas y use A o P para indicar antes o después del mediodía |
| a / p | Use un reloj de 12 horas y use a o p para indicar antes o después del mediodía |
