Hojas de cálculo en Excel

Plantillas Excel

2012-01-25T18:14:00.016+01:00

Después del cierre de Megaupload, he colocado nuevamente y en los servidores de Mediafire, todos los ejemplos que tenía en el blog de Excel, y en los que había un libro o una plantilla en Excel para descargar.

Para ahorraros el trabajo, paso a relacionaros todos los artículos donde hay ejercicios o ejemplos con plantillas en Excel, listas para usar, y que están nuevamente online. Si hubiera algún problema con alguna descarga, o me he dejado alguna plantilla de Excel por incluir, por favor, dejad un comentario en este nuevo artículo, para poder corregirlo, y ponerlo a disposición de todos los lectores del blog.

Aprovecho también para comunicaros que en este mismo artículo, iré incluyendo los ficheros de los nuevos artículos que publique, con lo que será un post de actualización continua, y donde siempre podréis consultar de un simple vistazo, aquellos temas que más os interesen y de los que queráis bajaros algún ejemplo de Excel listo para usar.

Artículo de Excel publicado	Descarga del fichero de Excel
33 utilidades para Microsoft Excel (manual en PDF)	Descargar manual en PDF
Calendario emergente en Excel	Descargar plantilla de Excel
Controlar vencimientos de facturas y recibos, con descuento comercial	Descargar plantilla de Excel
Buscar valores en un rango de datos	Descargar plantilla de Excel
Mostrar imágenes en un formulario	Descargar plantilla de Excel
Medias ponderadas	Descargar plantilla de Excel
Incluir un calendario en un formulario	Descargar plantilla de Excel
Calcular vencimientos	Descargar plantilla de Excel
Controlar vencimientos de facturas y recibos	Descargar plantilla de Excel
Préstamos según el método americano	Descargar plantilla de Excel
Préstamos con amortización de capital constante	Descargar plantilla de Excel
Calculadora de tiempos	Descargar plantilla de Excel
Habilitar y deshabilitar controles de un formulario	Descargar plantilla de Excel
Calcular la TAE	Descargar plantilla de Excel
Préstamos y cálculo de hipotecas	Descargar plantilla de Excel
Modificar datos utilizando un formulario	Descargar plantilla de Excel
Protegiendo nuestros trabajos en Excel	Descargar plantilla de Excel
Introducir datos utilizando un fomrulario	Descargar plantilla de Excel
Calendarios para imprimir	Descargar plantilla de Excel
Enviar un email desde Excel	Descargar plantilla de Excel
Obtener datos de una página web	Descargar plantilla de Excel
Ejecutar macro al seleccionar un elemento de un combobox	Descargar plantilla de Excel
Espacio libre y espacio total en disco	Descargar plantilla de Excel
Validar la entrada de datos en un formulario	Descargar plantilla de Excel
Números aleatorios no repetidos	Descargar plantilla de Excel
Escribir datos de otro fichero Excel	Descargar plantilla de Excel
Formularios dinámicos	Descargar plantilla de Excel
Buscar hojas ocultas	Descargar plantilla de Excel
Informar de que está ejecutándose un macro	Descargar plantilla de Excel
Importar datos concretos de un fichero de texto	Descargar plantilla de Excel
Combinar correspondencia con Excel y Word	Descargar plantilla de Excel
Calcular vencimientos reales de facturas	Descargar plantilla de Excel
Combobox: sacándoles provecho	Descargar plantilla de Excel
Solver: cálculo de la TIR	Descargar plantilla de Excel
Combobox dependientes	Descargar plantilla de Excel
Impedir que se cambie el nombre a una hoja	Descargar plantilla de Excel
Evolución de un capital a interés simple y a interés compuesto	Descargar plantilla de Excel
Euroconversor	Descargar plantilla de Excel
Listar los ficheros de un directorio	Descargar plantilla de Excel
Convertir unidades de tiempo a formato hh:mm:ss	Descargar plantilla de Excel
Calcular el umbral de rentabilidad, con Excel	Descargar plantilla de Excel
Llenar dinámicamente un combobox	Descargar plantilla de Excel
Control horario: Horas normales y horas extras	Descargar plantilla de Excel
Mostrar y ocultar hojas, utilizando macros	Descargar plantilla de Excel
Mostrar imágenes asociadas a un desplegable	Descargar plantilla de Excel
Validación de listas dependientes	Descargar plantilla de Excel
Validación con datos en otra hoja	Descargar plantilla de Excel
Crear un gráfico, con un macro	Descargar plantilla de Excel
Formularios inteligentes	Descargar plantilla de Excel
Más sobre las funciones de bases de datos personalizadas	Descargar plantilla de Excel
Funciones de bases de datos personalizadas	Descargar plantilla de Excel
Volviendo con los números aleatorios	Descargar plantilla de Excel
Sencilla aplicación en Excel	Descargar plantilla de Excel
Leer el número de serie de los discos	Descargar plantilla de Excel
Leer una base de datos Access	Descargar plantilla de Excel
Calcular la letra del NIF (o del DNI)	Descargar plantilla de Excel
Leer un fichero de texto plano	Descargar plantilla de Excel
Impedir que se cambie el nombre al fichero	Descargar plantilla de Excel
La función BDContar	Descargar plantilla de Excel
Crear hojas con un clic	Descargar plantilla de Excel
Tiempo transcurrido entre dos fechas	Descargar plantilla de Excel

33 Utilidades para Microsoft Excel

2011-09-14T00:01:00.017+02:00

Hoy os presento un manual en pdf, de lo que creo que podrían ser, las 33 mejores utilidades para Microsoft Excel que he publicado en el blog. Quizás algunos de vosotros no estéis de acuerdo, y penséis que hay otros artículos en el blog que deberían incluirse en el manual. Muy probablemente tengáis razón, pero he escogido esas 33 utilidades, después de hacer muchos descartes.

Este manual con 33 utilidades para Microsoft Excel, no pretende ser un manual de cabecera, pero si un manual de consulta, especialmente ideado para aquellos lectores que quieran aprender las posibilidades de las macros en Excel. No obstante, este manual no solo incluye macros, sino que también podréis encontrar en él, funciones propias de Excel, como la TIR, y el VAN, por poner solo dos ejemplos.

El manual, como todo lo que encontrarás en este humilde blog de Excel, es gratuito y de libre distribución, por lo que puedes imprimirlo, enviárselo a tus amigos, compartirlo, y en definitiva, hacer lo que quieras con el :-)

En muchos de los artículos, podréis comprobar que al final de los mismos, hay un enlace para descargar un fichero con todo lo explicado, para que el usuario no tenga que partir de cero escribiendo el código fuente en Excel. Asimismo, se incluye un enlace a la entrada original de este blog, por si en algún momento el lector quiere acercarse hasta aquí, para ver si he realizado algún cambio o modificación en algún artículo del blog, como ha ocurrido recientemente por ejemplo, en el que explico como obtener datos de una página web.

Estas son las utilidades que he incluido en el pdf:

1. Obtener el nombre del archivo.
2. Obtener el nombre de la hoja.
3. Obtener la ruta, el nombre del fichero, y la hoja.
4. Mi primer macro en Excel.
5. Mi primer UserForm.
6. Introducir datos utilizando un formulario.
7. Modificar datos utilizando un formulario.
8. Mi primer ComboBox.
9. Sacándoles provecho a los ComboBox.
10. Macro al abrir o cerrar un libro.
11. Desproteger una hoja de cálculo.
12. Crear carpetas (o directorios), desde Excel.
13. Poner la hora en una celda.
14. Crear hojas con un clic.
15. Buscar hojas ocultas.
16. Mostrar y ocultar hojas, utilizando macros.
17. Leer una base de datos Access.
18. Simultanear filas de colores.
19. Validación con datos en otra hoja.
20. Validación de listas dependientes.
21. Control horario: horas normales y horas extras.
22. Números aleatorios no repetidos.
23. Préstamos y cálculo de hipotecas.
24. Préstamos según el método americano.
25. Préstamos con amortización de capital constante.
26. Calcular la TAE.
27. Calcular la TIR y el VAN.
28. Evolución de un capital a interés simple e interés compuesto.
29. Calcular la letra del NIF/DNI.
30. Controlar vencimientos de facturas y recibos.
31. Calcular vencimientos.
32. Obtener datos de una página web.
33. Calendarios para imprimir.

Aquí os dejo una imagen de una vista a 4 páginas, para que os hagáis una idea de lo que podéis encontrar en el pdf que podéis descargar más abajo:

Ya no os hago esperar más. Aquí tenéis el manual con las 33 utilidades para Microsoft Excel (cliquead en la imagen para descargar el manual en pdf):

Si te ha gustado este manual en pdf, te agradecería que dejases un comentario.

Calendario emergente en Excel

2011-08-09T23:22:00.013+02:00

Este artículo nace como respuesta a una consulta de un lector del blog en otro artículo relacionado, donde explicaba como incluir un calendario en un formulario. Concretamente se planteaba la pregunta de si se podía mostrar un calendario emergente en Excel, simplemente cliqueando en una celda.

La respuesta seguro que ya la sabéis, porque sino, no estaría escribiendo este artículo. Pues sí, se puede. En este caso, lo vamos a hacer sin necesidad de incluir el calendario en un formulario. Simplemente haremos que el calendario aparezca, sin más.

Creo que el artículo de hoy os puede ser de mucha utilidad, especialmente si trabajamos con hojas de cálculo en las que tengamos que introducir muchas fechas del mes en curso (del mes en el que estamos), pues con un solo clic en el día del calendario, nos aparecerá la fecha en la celda donde estemos. También lo podemos utilizar para introducir fechas correspondientes a otros meses y/o años, pero ya no será tan rápida la introducción de los datos, pues deberemos realizar más de un clic en el calendario (para seleccionar el mes y/o año, y el día). Puede ser interesante por ejemplo, como complemento llevar el control mensual de cobros y pagos, para hacer un presupuesto diario, o para llevar el control de asistencias del personal, por poner solo tres casos.

Para poder hacer todo esto, lo primero que tendremos que hacer es "dibujar" el calendario. Ojo, con esto de dibujar, me estoy refiriendo simplemente, a darle al calendario las dimensiones que deseemos. Para ello, si usamos Excel 2003, seleccionaremos en Herramientas --> Personalizar --> y en la pestaña de barras de herramientas, seleccionaremos "Cuadro de controles". Una vez hecho esto, nos aparecerá la barra de controles, así que cerraremos la ventana emergente de personalización. Seguidamente, seleccionaremos el icono del final que tiene un dibujillo de un martillo y una llave, y seleccionaremos "Control de calendario 11.0".

En el caso de que utilicemos Excel 2007 y Excel 2010, para mostrar el Cuadro de controles, antes debemos hacer visible la pestaña "Programador". Para ello, nos iremos a Archivo --> Opciones --> Personalizar cinta de opciones --> y pondremos una muesca en "Programador". Una vez tengamos en la cinta de opciones la ficha Programador, dentro de ella, encontraremos el Cuadro de controles, seleccionando "Insertar", cuyo icono es el martillo y la llave.

En el caso concreto de utilizar Excel 2010 es más que probable que no os aparezca el control calendario. Para solucionar este contratiempo, deberéis seguir las instrucciones del comentario que publiqué el 15 de junio de 2011, ante una pregunta de un usuario, y que veréis al final del artículo incluir un calendario en un formulario. Os remito directamente a ese comentario, para no duplicar el contenido.

Aquí tenéis una imagen de Excel 2003, que es la versión que más suelo utilizar:

Una vez tengamos todo listo, y hayamos seleccionado el control calendario, solo nos quedará dibujar el calendario en la hoja de cálculo para que tenga el tamaño que deseemos. Para ello, simplemente deberemos cliquear y arrastrar el mouse, para darle la forma que queramos. Una vez hecho eso, si usamos Excel 2003, pulsaremos en el primer icono de la barra de Cuadro de controles (la escuadra, la regla, y el lápiz), para salir del modo diseño. En Excel 2007 y 2010, simplemente cliquearemos en el icono que hay junto al de Cuadro de controles, con el lápiz, regla y escuadra, para salir del modo diseño. A continuación, cerraremos la barra de Cuadro de controles, pues ya no la vamos a necesitar más.

Una vez hecho esto, solo nos quedará incluir el código de programación, en la hoja donde queramos hacer que aparezca el en cuestión (no en un módulo). Lo que haremos en nuestro ejemplo, será mostrar el calendario siempre que cliqueemos en una celda de la columna A. Da igual que celda sea, pues cuando cliqueemos sobre la columna A, nos aparecerá el calendario.

Muchos estaréis pensando: "pero si ya tenemos visible el calendario, ¿cómo va a aparecer de nuevo cuando cliqueemos en una celda de la columna A?". Bueno, la respuesta es sencilla. Lo que haremos será ocultar el calendario siempre que cliqueemos en cualquier celda que no esté en la columna A, y mostrarlo cuando cliqueemos en cualquier celda de la columna A.

¿Cómo vamos a hacer todo esto?. Pues con un sencillo código que pondremos en el evento SelectionChange. Es decir, un código que se ejecutará al cambiar la selección de la celda. También incluiremos unas sencillas líneas de código para incluir en esa celda (la de la columna A, que ha permitido que el calendario se haga visible), la fecha que seleccionemos en el calendario. Es decir, si cliqueamos en un día del calendario, esa fecha aparecerá el la celda donde estemos situados (esto lo haremos a través del evento Click).

El código del evento SelectionChange de la hoja, será este:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Ejemplo para mostrar el calendario al seleccionar
'cualquier celda de la columna A
If Not Application.Intersect(Target, Range("A:A")) Is Nothing Then
'ahora le diremos donde queremos que aparezca el calendario
'para que no se quede fijo en las celdas de arriba, ya que al hacer scroll,
'se nos quedaría oculto en la parte de arriba.
'Podemos jugar con estos valores, para ver donde se muestra
'el calendario, y para que quede a nuestro gusto
Calendar1.Left = Target.Left + Target.Width + 60
'para que no se nos deforme el calendario, si estamos en las últimas filas
'incluimos un condicional que controlará la celda donde estamos y
'la comparará con la última fila de la hoja de cálculo
If ActiveCell.Row + 20 >= Cells.Rows.Count Then
Calendar1.Top = Target.Top - 145
Else
Calendar1.Top = Target.Top - 50
End If
'hacemos visible el calendario
Calendar1.Visible = True
'Mostramos la fecha de hoy
Calendar1.Today
Else
'ocultamos el calendario, en el caso de no
'seleccionar una celda de la columna A
Calendar1.Visible = False
End If
End Sub

Y el del evento Click del calendario será este:

Private Sub Calendar1_Click()
'Al hacer clic en una fecha del calendario
'ponemos el valor en la celda donde estemos situados
ActiveCell = Calendar1.Value
'ocultamos el calendario
Calendar1.Visible = False
End Sub

Con este sencillo código, obtendremos algo tan vistoso como esto, cada vez que nos situemos en una celda de la columna A:

Desde aquí podéis descargar el fichero de Excel, con el ejemplo que hemos visto en este artículo. Si os ha sido de utilidad, espero vuestros comentarios.

Controlar vencimientos de facturas y recibos, con descuento comercial

2011-06-27T13:52:00.004+02:00

No hace mucho tiempo, vimos una aplicación en Excel, con la que podíamos controlar vencimientos de facturas y recibos. Esta aplicación estaba pensada para controlar nuestra cartera de recibos y/o facturas pendientes de cobro, y para alertarnos llegado su vencimiento.

En el artículo de hoy, hemos añadido una funcionalidad adicional a esa aplicación en Excel, que no es otra que la del control del riesgo bancario por descuento de efectos. No entraré a detallar el código fuente, pues la mayoría de los usuarios harán caso omiso del mismo, ya que lo que buscan es una aplicación que les permita gestionar de una forma medianamente decente su cartera de recibos y de efectos descontados.

Para el que no sepa que es el descuento comercial, simplemente daré unas pinceladas, para comentar que se trata de una fórmula de financiación, por la cual el acreedor de una deuda, puede anticipar el cobro de la misma, normalmente a través de una entidad financiera, y a cambio de un tipo de interés que suele estar en función del plazo de vencimiento de la deuda. Lo normal es que las empresas soliciten a estas entidades financieras, la apertura de una línea de descuento por un determinado importe, de acuerdo a sus necesidades previamente establecidas. Las entidades financieras, una vez analizada la documentación que les solicitarán a estas empresas, determinarán si es factible la concesión de esa línea de descuento, y el importe de la misma. Las condiciones de esta línea de descuento, se deberían negociar de forma individualizada con la entidad financiera.

La aplicación que os presento hoy, y que no es más que una revisión de la que vimos en su día (la he llamado versión 2.0, por darle un toque algo más formal), consta en líneas generales de las siguientes mejoras:

Añade al libro una hoja donde se incluye una relación de las entidades financieras, así como el importe de la línea de descuento de cada una de ellas. Respecto al funcionamiento de la aplicación, en lo que respeta a este límite de descuento, comentar que lo normal es que las empresas no puedan exceder de este límite, pero como no siempre es así y cada empresa es un mundo, ya que a veces las entidades te permiten exceder ese límite, esta aplicación en Excel simplemente informa del importe del riesgo que tenemos en ese momento, del límite de la línea de descuento, y de si estamos excedidos o no, y por qué importe. Será el usuario quien decida a partir de esos datos, si la entidad financiera en cuestión, nos va a aceptar o no algún efecto comercial adicional para su descuento.

En la hoja de facturas, se ha añadido una columna, para informar a través de un desplegable, de la entidad financiera en la que vamos a negociar los efectos. En el encabezado de la columna, aparece el texto "Efecto descontado en", tal y como podéis comprobar en la siguiente imagen (si no lo veis bien, cliquead en la imagen para ampliarla):

Una vez hemos seleccionado una entidad financiera por donde descontar el efecto, veremos un MsgBox, de la siguiente forma:

El MsgBox nos informará de lo siguiente:

- Entidad financiera.
- Riesgo concedido.
- Cantidad descontada (y pendiente de vencimiento).
- Exceso/defecto sobre el límite de riesgo de la póliza de descuento.

Lo podemos comprobar en el siguiente MsgBox:

Finalmente, y una vez hayamos informado de todo lo necesario para el control de nuestras facturas, pulsaremos el botón Previsión de cobros, que nos llevará a la hoja donde tenemos la previsión de cobros, mes a mes. En esta hoja, se ha añadido una tabla en la que se incluye el riesgo por descuento que tenemos en cada entidad, y el mes de vencimiento de ese riesgo. En la tabla superior, como hasta ahora, tenemos la previsión de cobros, que incluye un cambio respecto a la versión anterior de esta misma aplicación. En esa tabla de cobros, evidentemente no aparecerán todos aquellos efectos que hayan sido negociados y por tanto descontados, pues ya habrán sido cobrados (lo cual no quiere decir que el deudor haya pagado).

En la aplicación anterior que no controlaba el riesgo por descuento de efectos, y cuyo enlace incluí al principio de este artículo, solo existía una tabla en la hoja de "Previsión de cobros", pero en esta nueva versión hay dos, una tabla para los cobros pendientes, y otra para los efectos descontados (y por tanto cobrados).

Desde aquí podéis descargar el fichero de Excel, con el ejemplo que hemos visto en este artículo (resubido, con mejoras en el código, el 09/07/2011). Espero vuestros comentarios, para saber si os ha sido útil o no :-)

Buscar valores en un rango de datos

2011-05-11T00:01:00.009+02:00

Hace poco, recibíamos una consulta de un usuario en nuestra página de facebook, en la que nos preguntaba como localizar determinados valores en un rango de datos. Concretamente, lo necesitaba para determinar la tarifa aplicable por un servicio de transporte o mensajería, en función del peso del envío.

Vamos a ver como resolver tan interesante cuestión. Para ello, como siempre, ilustraremos la explicación con un sencillo ejemplo. Supongamos que tenemos la siguiente tarifa de transportes, para las localidades que se indican en la tabla adjunta:

Supongamos ahora, que queremos saber cuanto nos costaría enviar un paquete que pesa 70 kgs. a Sevilla. Visualmente, lo tenemos fácil, pues en la tabla tenemos que localizar la fila correspondiente a los 70 kgs. y la columna correspondiente a Sevilla. Eso nos dará como resultado, que el precio de ese transporte es de 94,00 euros, dólares, pesos, o cualquier otra unidad monetaria con la que trabajemos. Pero claro, queremos obtener ese dato, de forma automática, simplemente informando en algún sitio, del destino de la mercancía, y del peso.

Para ello, montaremos esta plantilla de Excel, donde a través de dos listas de validación, podremos seleccionar el destino y el peso del producto, y una vez seleccionados estos datos, debajo nos aparecerá el importe del envío.

Para calcular el precio, todo es muy sencillo, con esta fórmula:

=SUMAPRODUCTO((B6:B15=D21)*(C5:F5=D20)*(C6:F15))

Esta fórmula, lo que viene a hacer es buscar en el rango de celdas comprendido entre B6 y B15, el dato informado en la lista de validación de D21, es decir, el peso del paquete que enviaremos. Seguidamente, nos busca en el rango de C5 a F5, la localidad informada en la lista desplegable de la celda D20. Y por último, con esos dos datos, lo que hace la fórmula es localizar el importe en el que confluyen ambos datos (peso y localidad), dentro del rango comprendido entre C6 y F15. Como veis, no es nada complicado.

También obtendremos el mismo resultado, si utilizamos las funciones INDICE y COINCIDIR, en lugar de SUMPARODUCTO, de la siguiente forma:

=INDICE(C6:F15;COINCIDIR(D21;B6:B15;0);COINCIDIR(D20;C5:F5;0))

En este caso, la fórmula la leeríamos más o menos así:

Busca en la matriz de datos que hay entre C6 y F15, la fila cuyo número es el resultado de aplicar la función COINCIDIR(D21;B6:B15;0), y cuya columna es el resultado de aplicar la función COINCIDIR(D20;C5:F5;0).

COINCIDIR(D21;B6:B15;0) nos indica que posición ocupa en valor de la celda D21, dentro del rango de datos comprendido entre B6 y B15. Una lectura similar se podría hacer de COINCIDIR(D20;C5:F5;0), pues nos indica la posición que ocupa el dato de D20, dentro del rango de datos comprendido entre C5 y F5.

Esto puede parecer algo complicadillo, pero si lo imprimís y lo leéis de nuevo, teniendo la hoja de cálculo de Excel delante (la que podéis descargar al final de este artículo), os será mucho más fácil de “digerir”.

Esta segunda opción donde utilizamos las funciones INDICE y COINCIDIR, nos sería especialmente de utilidad, en el caso de tener datos de texto en la matriz, en lugar de importes, pues la función SUMAPRODUCTO nos daría error, al no tener datos numéricos.

Para hacerlo más elegante, en el ejemplo que podrás descargarte al final de este artículo, hemos puesto un formato condicional a las celdas, para que nos coloree de azul la celda con la tarifa, tal y como se muestra en el siguiente ejemplo:

A este tipo de localización de datos dentro de un rango de datos (o una matriz de datos), lo vamos a llamar localizar datos en una matriz, con datos de entrada exactos. Es decir, introduciremos los datos exactos de la localidad y sobretodo el peso del producto a enviar, para localizar el valor en la tabla. De hecho ya habíamos visto alguna que otra utilidad para localizar datos dentro de una tabla, utilizando algunos ejemplos de funciones personalizadas de bases de datos por aquí, y más funciones personalizadas de bases de datos, por este otro lado.

Llegados a este punto, nos encontramos con que la realidad del día a día es bien distinta, pues ese tipo de tarifas no suelen proporcionárnoslo así los transportistas de paquetería. ¿Verdad que cuando enviamos un producto, la tarifa de transportista nos informa de un peso por tramos?. Es decir, el transportista nos cobra en función de si el peso está dentro de un rango (o baremo) de pesos. Por eso, la tabla anterior no nos vale, y el más claro ejemplo es el siguiente: ¿cuánto nos costaría enviar 45,46 kgs. a Sevilla?. Pues siendo estrictos, a la vista de esa tabla anterior, no lo sabemos, pues no aparece ese peso en la tarifa.

Para solucionar esto, vamos a imaginar que la tarifa del transportista es esta otra:

En este caso, ya vemos que la cosa es mucho más realista, pues el transportista, nos va a ofrecer un precio, para un rango determinado de pesos, a cada localidad. En el ejemplo, podemos ver como la empresa de transportes nos va a cobrar 93 euros, dólares, pesos, o cualquier otra unidad monetaria con la que trabajemos, si llevamos entre 60,01 kgs. y 70 kgs., a Madrid. Es decir, si solicitamos que la empresa de paquetería nos lleve un paquete a Madrid, que pesa 62 kgs., nos cobrará lo mismo que si nos lleva 68 kgs.

A este tipo de localización de datos dentro de un rango de datos (o una matriz de datos), lo vamos a llamar localizar datos en una matriz, con datos de entrada dentro de un rango de datos.

Para localizar la tarifa correspondiente a un envío de determinado peso a una localidad, montaremos una tabla como esta, donde la localidad será una lista de validación, y el peso lo introduciremos manualmente:

Para calcular el precio, solo nos quedará aplicar esta fórmula:

=DESREF(C5;COINCIDIR(D21;B6:B15);COINCIDIR(D20;D5:G5))

¿Cómo se lee esta formulita?. Pues de una forma no muy complicada. Las funciones COINCIDIR ya las hemos explicado unos párrafos más arriba, y nos indican tanto la fila donde está el peso seleccionado, y la columna donde está la localidad elegida. La función DESREF, simplemente nos desplaza tantas filas y columnas como nos indican las funciones COINCIDIR, partiendo desde la celda C5.

Como veis, tenemos una forma bastante sencilla para localizar valores en una tabla, tanto, si se trata de valores exactos, como si se trata de valores dentro de un rango.

Desde aquí podéis descargar el fichero de Excel, con todo lo que hemos visto en este artículo.

Mostrar imágenes en un formulario

2011-03-21T00:01:00.002+01:00

En un artículo anterior, habíamos visto como mostrar imágenes asociadas a un desplegable, directamente en nuestra hoja de cálculo Excel. En esta ocasión, os muestro una forma de hacer lo mismo, pero incluyendo las imágenes en un formulario, es decir, en un userform.

La técnica en ambos casos es muy similar, así que esto lo que explique en este artículo, estará chupado, para aquellos lectores del blog que ya se hayan leído el artículo o que comentaba en el párrafo anterior.

Como siempre, vamos a ilustrar esta técnica con un sencillo ejemplo. Para ellos, hemos escogido una serie de construcciones, en este caso, torres, de diferentes ciudades, y de diferentes países. Solo hemos puesto unas pocas, para que vosotros añadáis las que creáis convenientes.

Lo primero que vamos a hacer es montar la lista que se muestra en la siguiente imagen, y de paso también crearemos un botón, para asociarlo a un macro, en este caso, al macro que lanza el userform.

Lo siguiente que debemos tener, es una lista con las imágenes, y que necesariamente se llamarán de la misma forma a como aparecen en la columna A de la imagen anterior (evidentemente, sin tener en cuenta la extensión correspondiente, en este caso, jpg):

Ahora procederemos a crear un formulario en Excel, donde incluiremos dos etiquetas (dos label), una para el título del formulario en sí, es decir, un rótulo con el texto "Torres famosas", y otra para mostrar la ciudad y el país donde se encuentra la torre elegida. También incluiremos un ComboBox, donde al desplegarlo, podremos elegir la imagen. Por supuesto, incluiremos también un control de imagen, para que se muestre la imagen elegida en el ComboBox, y finalmente, lo último que incluiremos será un CommandButton para que al pulsar sobre él, se cierre del formulario. Es decir, haremos algo como lo que se muestra en la siguiente imagen:

Ahora solo nos quedará incluir el código que irá asociado a los controles. Comenzaremos con el código del ComboBox, al hacer click sobre él:

Private Sub ComboBox1_Enter()
'En caso de error, que continúe
On Error Resume Next
'limpiamos los datos del Combobox
ComboBox1.Clear
'Al inciar el formulario,
'seleccionamos la hoja con los datos
Hoja1.Select
'Seleccionamos la celda B5
Range("A2").Select
'Vamos a llenar dinámicamente el combobox
'con los nombres de los artículos, hasta
'encontrar una fila vacía
Do While Not IsEmpty(ActiveCell)
'ponemos el nombre del producto
ComboBox1.AddItem ActiveCell.Value
'bajamos una fila
ActiveCell.Offset(1, 0).Select
Loop
End Sub

Ahora le toca el turno al código que nos servirá para mostrar las imágenes, cada vez que cambiemos el item del desplegable, en nuestro caso, del ComboBox:

Private Sub ComboBox1_Change()
'definimos la ruta donde tenemos el fichero de excel
'(que será la misma donde tengamos las imágenes)
ruta = ActiveWorkbook.Path
'definimos los nombres de las imágenes
imagen = ComboBox1.List(ComboBox1.ListIndex) & ".jpg"
'ahora definimos la ruta y la imagen
ruta_e_imagen = ruta & "\" & imagen
'cargamos esa imagen en el cuadro de la imagen
Image1.Picture = LoadPicture(ruta_e_imagen)
'ahora ponemos el nombre de la ciudad y el país en el label2
Cells.Find(What:=Replace(imagen, ".jpg", "")).Select
Label2 = ActiveCell.Offset(0, 1) & " (" & ActiveCell.Offset(0, 2) & ")"
End Sub

Con los dos macros anteriores, lo que hemos hecho, ha sido por un lado llenar el ComboBox al hacer clic en el desplegable (con los datos de la hoja de cálculo), y por otro lado, cada vez que cambiamos el elemento seleccionado en el ComboBox, mostrar la imagen correspondiente a ese elemento seleccionado.

Ahora solo nos quedará incluir el código que tendremos asociado al botón "Cerrar":

Private Sub CommandButton1_Click()
'descargamos el formulario de memoria
Unload Me
End Sub

Todo el código que hemos visto hasta ahora, va incluido dentro del propio UserForm, pues es el código asociado al mismo. En cambio, este último código, que os incluyo, es para lanzar el formulario en sí, por lo que lo pondremos dentro de un módulo:

Sub Lanzar_formulario()
'lanzamos el formulario
UserForm1.Show
End Sub

Con esto tenemos todo listo, para mostrar un formulario tan llamativo como este:

Desde aquí podéis descargar un fichero comprimido, que incluye el fichero Excel y las fotos de las torres, que hemos visto en este artículo.

Medias ponderadas

2011-01-26T00:01:00.013+01:00

Hoy os presento un artículo, que aunque no es estrictamente algo que podamos considerar como puramente de Excel, creo que le va a servir a muchos usuarios que utilizan esta popular hoja de cálculo. Se trata de obtener lo que se denomina media ponderada.

La Real Academia Española (RAE), define la media ponderada como el "resultado de multiplicar cada uno de los números de un conjunto por un valor particular llamado su peso, sumar las cantidades así obtenidas, y dividir esa suma por la suma de todos los pesos".

¿Está claro, verdad?. Jajajaja. Probablemente leído así a bote pronto, esa definición lo que hace es confundir más que aclarar, pero si lo vemos con un ejemplo, nos daremos cuenta que es algo bastante sencillo de entender.

Vamos a suponer que hoy día 26-01-2011, efectuamos dos inversiones, una que nos rinde el 7% de interés nominal anual, y cuyo vencimiento es el 31-12-2011, y otra que rinde el 1% de interés nominal anual, y cuyo vencimiento es dentro de 5 días, es decir, el 31-01-2011. En ambos casos, supondremos que los intereses nos los retribuyen al vencimiento de cada inversión.

Si pasáramos esta información a nuestra hoja de cálculo de Excel, nos quedaría algo así:

Si alguien nos preguntara, a qué tipo de interés medio nos retribuyen la inversión, podríamos contestarle dos cosas:

a) Al 4,00% de interés medio, pues esa cifra resulta de hacer la meda de tipos de interés: (7%+1%)/2
b) Nos faltan datos para poder dar una respuesta.

Si hemos elegido al respuesta a), sin duda estaremos cometiendo un grave error, porque entre otras cosas, las dos inversiones no tienen el mismo vencimiento. Piensa por un momento que la inversión 1 vence dentro de 339 días, es decir, todavía queda mucho, mientras que la inversión 2 está prácticamente finalizada, por lo que debemos valorar esta circunstancia, para calcular el tipo medio de interés de nuestras inversiones, pues en caso de no hacerlo, estaremos ponderando más de la cuenta el tipo de interés de la inversión 2 (la que está a punto de finalizar). Llegados a este punto, podríamos reformular nuestro esquema en Excel, de la siguiente forma:

Si nos volvieran a preguntar cual es el tipo de interés medio al que retribuyen nuestras inversiones, la respuesta que podríamos dar, sería una de estas:

a) Al 6,91% de interés medio, pues esa cifra resulta de multiplicar el 7% por los días que quedan hasta el vencimiento de esta primera inversión, es decir 339 días, multiplicando nuevamente el 1% por los días que quedan hasta el vencimiento de esta segunda inversión, es decir, 5 días, y dividiendo todo ello entre la suma de 339 y 5. Es decir, ese tipo medio del 6,91% lo obtendríamos de la siguiente fórmula:

b) Nos faltan datos para poder dar una respuesta.

Como en el caso anterior, si hubiésemos dado como respuesta la a), estaríamos cometiendo un error, pues nos falta el último dato, el importe de cada una de las inversiones (la inversión 1, y la inversión 2), y que es condición necesaria para poder determinar el tipo de interés medio al cual nos retribuyen esas inversiones. En ningún momento hemos dicho que ambas inversiones sean del mismo importe, por eso presuponer ese dato sería un error.

Vamos a suponer que la inversión 1 es de 10.000 euros, y que la inversión 2 es de 5.000 euros. Reformulemos por tanto nuestro esquema en Excel, para tenerlo todo más claro, de un vistazo:

Si ahora nos hicieran la preguntita de marras, nuestra respuesta sería que el tipo de interés medio resultante de estas inversiones, es del 6,96%, pues lo obtenemos de resolver la siguiente fórmula:

En realidad, lo que estamos haciendo, es ponderar (sopesar, o medir de forma proporcional) cada tipo de interés, con respecto al importe de cada una de las inversiones, y con respecto a la fecha de cada uno de los vencimientos. En términos matemáticos, lo que hemos hecho no es más que ponderar la media, o como reza el título de este artículo, calcular la media ponderada.

Si volvemos a mirar esta última fórmula donde obtenemos ese 6,96% de interés medio (ponderado), y releemos nuevamente la definición que da la RAE sobre la media ponderada, estoy seguro que ahora tendremos las cosas mucho más claras, y habremos entendido esta definición.

Como colofón de este artículo, vamos a suponer ahora que tenemos 20 inversiones, en lugar de 2, como era el caso que contemplábamos en el ejemplo. En ese caso, en lugar de hacer una fórmula donde el numerador y el denominador van a ser larguísimos, podemos solucionarlo muy fácilmente con el uso de la función SUMAPRODUCTO. Vamos a verlo con un ejemplo, donde podemos contemplar en la imagen, la fórmula que aparece en la barra de fórmulas:

Podemos construir dos columnas auxiliares, pare ver que efectivamente, la función SUMAPRODUCTO nos arroja el mismo resultado, que una fórmula tradicional:

En realidad, la fórmula SUMAPRODUCTO, lo que va a hacer es multiplicar cada elemento de las matrices que le indiquemos, sumando cada una de las líneas, de tal forma que si por ejemplo ponemos esto:

=SUMAPRODUCTO(C5:C24;E5:E24;F5:F24)

...le estaremos diciendo que haga esto:

=(C5*E5*F5)+(C6*E6*F6)+(C7*E7*F7)+(C8*E8*F8)+(C9*E9*F9)+(C10*E10*F10)+(C11*E11*F11)+(C12*E12*F12)+(C13*E13*F13)+(C14*E14*F14)+(C15*E15*F15)+(C16*E16*F16)+(C17*E17*F17)+(C18*E18*F18)+(C19*E19*F19)+(C20*E20*F20)+(C21*E21*F21)+(C22*E22*F22)+(C23*E23*F23)+(C24*E24*F24)

Es decir, tanto la función SUMAPRODUCTO que hemos puesto, y esta otra fórmula tradicional tan larga que acabamos de poner, hacen exactamente lo mismo.

Espero que os haya sido útil este artículo, y que lo podáis aplicar alguna vez en vuestro trabajo. Como siempre, desde aquí podéis descargar el fichero de Excel, con el ejemplo final que hemos visto en este artículo.

Incluir un calendario en un formulario

2010-11-03T00:01:00.002+01:00

Hoy veremos como incluir un calendario en un formulario. No se trata de un calendario, como aquel calendario para imprimir que vimos en un artículo anterior, sino de un control de calendario. Igual que en los formularios tenemos controles como pueden ser las etiquetas, los cuadros de texto, los cuadros combinados, o las casillas de verificación, por ejemplo, también podemos añadir un calendario.

Para ver como funciona, deberemos incluir un formulario desde VBA, seleccionando en el menú “Insertar”, la opción “Userform”. Por defecto, desde VBA, solo se nos muestran en el cuadro de herramientas, aquellos controles que son de uso más común. Para poder ver el resto de controles que podemos incluir en un formulario, deberemos cliquear con el botón derecho del ratón dentro de la ventana “Cuadro de herramientas”, donde nos aparecen los controles, pero en el espacio donde no hay controles. De esa forma, nos aparecerá un menú desplegable donde podremos seleccionar controles adicionales.

Seleccionaremos la opción “Controles adicionales”, y en la lista, escogeremos el correspondiente al control de calendario, marcándolo con una muesca. Seguidamente, pulsaremos sobre el botón “Aceptar”, y en el cuadro de herramientas nos aparecerá ahora un nuevo elemento, que será el correspondiente al calendario:

Ahora solo nos quedará pulsar sobre ese botón del calendario, y dibujar el mismo sobre el formulario. Hacer esto, es tan sencillo como pulsar el ratón sobre el formulario, mantener pulsado el botón, y arrastrar hacia abajo y hacia la derecha, para ir viendo como nos aparece un cuadro que es donde al soltar el botón del ratón, nos aparecerá dibujado el calendario. Por defecto, nos aparecerá el mes de la fecha en curso.

Ahora ya solo nos quedará darle alguna utilidad al calendario, más allá de la propia para tener visible el mes. Por ejemplo podremos insertar la fecha que seleccionemos en el calendario, en una celda. Para ello utilizaremos el evento click, que ejecutará las acciones contenidas dentro de ese evento, cada vez que cliqueemos dentro del calendario.

Por ejemplo, si queremos insertar la fecha que seleccionemos en el calendario, en una celda, utilizaremos esto dentro del código del propio formulario (en este ejemplo, insertamos la fecha en la celda A1):

Private Sub Calendar1_Click()
'Ponemos la fecha en la celda A1
Range("A1") = Calendar1.Value
End Sub

Ahora, incluiremos este otro código dentro del formulario, que lo que hará es cargarse el calendario con el mes y el día de la fecha en que lo abramos (la fecha del sistema). Esto se producirá cada vez que activemos (o carguemos) el formulario:

Private Sub UserForm_Activate()
'Al activar el formulario, que aparezca
'la fecha de hoy en el calendario
Calendar1.Today
End Sub

De esta forma tan sencilla, tendremos un calendario bien elegante, insertado dentro de nuestros formularios, para que con un solo clic, incluyamos fechas en nuestra hoja de cálculo. Y no solo eso, sino que además pasará como algo que hemos hecho nosotros, cuando en realidad es un control ya diseñado que incorpora Excel.

Desde aquí podéis descargar el fichero de Excel, con el ejemplo que hemos visto en este artículo.

Calcular vencimientos

2010-09-28T09:07:00.010+02:00

En el artículo anterior, donde os dejé una aplicación para controlar vencimientos de facturas y de recibos, más de uno os habréis dado cuenta de que en una hoja, había una columna donde informábamos del vencimiento de las facturas. Esta columna utilizaba como referencias los nombres de celdas, con lo que intentar descifrar esa fórmula podía ser un trabajo de investigación (nada difícil, pero sí, más complicado de lo normal).

Para solucionar el asunto, vamos a ver ahora en este artículo, esa misma fórmula, pero sin utilizar los nombres de celdas.

Supongamos que tenemos los siguientes datos, en las siguientes celdas:

E5: Aquí tenemos el plazo de crédito a los clientes. Puede ser 30, 45, 60, 90 o los días que hayamos acordado.

E6: Aquí tenemos la fecha de pago fija del cliente. Hay clientes que para centralizar sus pagos en una fecha determinada, suelen pagar sus facturas un único día al mes. Puede ser el día 5, el día 10, el día 15, o cualquier otro día de pago fijo que tenga el cliente. Si nuestros clientes no tienen un día de pago fijo, dejaremos esta celda vacía (sin dato).

B8: Fecha de emisión de la factura.

Si queremos calcular el vencimiento de la factura, deberemos poner esta fórmula en la celda donde queramos informar este dato:

=SI($E$6<>"";SI(DIA(B8+$E$5)>$E$6;MIN(FECHA(AÑO(C8);MES(C8)+1;DIA($E$6));FIN.MES(B8+$E$5;1));MIN(FECHA(AÑO(C8);MES(C8);DIA($E$6));FIN.MES(B8+$E$5;0)));B8+$E$5)

Podemos ver una tabla con esa información, en la siguiente imagen (clic para ampliarla):

Si os fijáis en la tabla de la imagen anterior, la segunda columna muestra el vencimiento teórico, o vencimiento inicial, con independencia de que el cliente tenga o no, un día de pago fijo. En la tercera columna, vemos ya el efecto de desplazar ese vencimiento inicial, a aquel que le correspondería realmente, teniendo en cuenta el día de pago fijo que tiene el cliente. En la cuarta columna se muestran los días de vencimiento real contados desde la fecha de emisión de la factura.

Para que nos funcione la fórmula anterior, donde aparece la función FIN.MES, debemos tener habilitadas las "Herramientas para análisis", en Excel 2003 podemos hacerlo desde el menú Herramientas, seleccionando la opción de menú Complementos. En Excel 2007 deberemos pulsar el botón de Microsoft Office (arriba a la izquierda), seleccionaremos el botón de Opciones de Excel (situado en la parte inferior de la ventana que nos aparecerá), luego seleccionaremos Complementos, y a continuación nos situaremos encima de las Herramientas para análisis. Pulsaremos sobre el botón "Ir", y habilitaremos esta opción marcando la muesca correspondiente.

Si lo que necesitamos es algo más sencillo, como simplemente sumar unos días de vencimiento a una fecha, podemos hacerlo de esta otra forma, mucho más sencilla, tal y como se muestra en la imagen, donde sumamos 30 días a la fecha de factura:

Si os fijáis, en imagen anterior podemos ver que la celda C5 tenemos como fórmula, el valor de la celda C4, y le hemos sumado 30 días. Para que en esas dos celdas los datos nos aparezcan como en la imagen anterior, simplemente deberemos darle formato personalizado a las celdas, con formato dd-mm-aaaa.

Desde aquí podéis descargar el fichero de Excel, con el ejemplo que hemos visto en este artículo. En este fichero encontraréis una tabla con todos los días del año, y el vencimiento de las facturas en función del día de emisión de las mismas, teniendo en cuenta el plazo de crédito que concedamos al cliente, y la fecha de pago fija que tiene (si es que la tiene).

Controlar vencimientos de facturas y recibos

2010-09-01T00:01:00.011+02:00

Si tienes cosas que hacer, mejor deja la lectura de este artículo para cuando dispongas de tiempo, porque una vez redactado todo, acabo de darme cuenta que ocupa la friolera de 16 páginas en DIN-A4. En cualquier caso, si prefieres descargarte la aplicación ahora, y leerte el resto más tarde, puedes hacerlo desde aquí: descargar la aplicación para el control de vencimientos de facturas y recibos. Eso sí, al menos léete los párrafos iniciales para saber de qué va esta aplicación en Excel, y los párrafos finales de este artículo, para saber cual es el procedimiento que debes seguir como usuario de la aplicación, para su correcto funcionamiento, y saber como debes introducir los datos, para no obtener errores inesperados.

Son muchas las pequeñas empresas, ya sean talleres, asociaciones, fundaciones, cooperativas, microempresas, e inclusos profesionales o empresarios individuales, que por su volumen de facturación y por su carga de trabajo administrativo, no requieren del uso de complejos programas para llevar el control de sus facturas, y saber cuando tienen que presentar los recibos al cobro, o cuando tienen que llamar al cliente para reclamar el pago de las facturas.

Normalmente estas aplicaciones de control de recibos, forman parte de los propios programas de contabilidad, pero como muchas de esas empresas, probablemente tengan externalizada su contabilidad, delegando este trabajo en una gestoría o asesoría fiscal y contable, he pensado que podría ser de utilidad a más de uno, esta sencilla pero útil aplicación.

Como siempre, se trata de una aplicación en Excel para el control de vencimientos de facturas y recibos, y es de libre distribución, como todo lo que puedes encontrar en este blog de Excel. Es una aplicación que no está protegida, por lo que podéis copiarla, enviársela a un amigo, o simplemente cambiar lo que os apetezca para adaptarla a vuestras necesidades. Respecto a esto último, solo quiero comentar que la aplicación va a servirle al 99,99% de los usuarios (por no decir al 100%), pues no está diseñada para un sector de actividad en concreto, de tal forma que servirá tanto para una empresa industrial como de servicios, ejerciten la actividad que ejerciten.

Voy a explicar un poco por encima, cómo debe utilizarse esta aplicación, para un correcto funcionamiento. No es nada complicado su uso, pues incluso los más vagos pueden saltarse este pequeño tutorial, que no va a tener problemas para hacerse con él, en menos de un minuto :-)

La aplicación consta de 5 hojas. Estas hojas no están ocultas, pero lo que sí que hemos hecho es ocultar las etiquetas de las hojas para que el usuario interactúe con los botones, y no a través de clics en las pestañas. Las hojas en cuestión, son las siguientes:

- Una hoja para el menú principal.

- Otra hoja para dar de alta, modificar, y ver los datos de nuestros clientes.

- Otra hoja para dar de alta, modificar, y ver las condiciones de pago (condiciones de cobro de nuestras facturas).

- Otra hoja para dar de alta, modificar, y ver las facturas que hemos emitido. Las facturas se tienen que generar con otra aplicación informática (programa de facturación), pues esta aplicación en Excel no es un programa de facturación, sino de control de vencimientos de facturas.

- Otra hoja para obtener una previsión de cobros, en función de los datos de las facturas que hemos introducido. Esta previsión de cobros, nos muestra los cobros previstos mes a mes, así como un total general por cliente.

Vamos a explicar para qué sirve cada hoja, y que es lo que encontraremos en cada una de ellas:

Menú principal:

Pocas cosas podemos decir sobre la funcionalidad de esta hoja, porque es más que evidente. Un pantallazo nos sacará de dudas:

El macro que tenemos en esta hoja (la hoja1), nos sirve para proteger la hoja al activarse la misma, y su código es el siguiente:

Private Sub Worksheet_Activate()
'Si hay errores, que continúe
On Error Resume Next
'protegemos la hoja
ActiveSheet.Protect
End Sub

Alta y modificación de clientes:

Desde esta hoja, daremos de alta nuestros clientes, con todos los datos fiscales, y de contacto, así como su forma de pago, seleccionándola del desplegable que se genera automáticamente al dar de alta el nombre del cliente. También se generará una validación de datos automática, por lo que si el cliente tiene fecha de pago fija, solo podremos introducir un número entre el 1 y el 31.

Este sería un pantallazo con un ejemplo donde salen nuestros clientes (clic sobre la imagen, para ampliarla):

En esta hoja tenemos dos macros. Uno que se ejecuta al activarse la hoja (la hoja2), y nos sirve para proteger la hoja:

Private Sub Worksheet_Activate()
'Si hay errores, que continúe
On Error Resume Next
'protegemos la hoja
ActiveSheet.Protect
End Sub

Este otro macro se ejecutará cuando cambiemos un dato de la hoja en cuestión. Si el cambio afecta a la columna A, entonces ocurrirá lo que comentaba anterior mente, es decir, se generará un desplegable de forma automática, y se generará también una validación de datos:

Private Sub Worksheet_Change(ByVal Target As Range)
'Si hay errores, que continúe
On Error Resume Next
'desprotegemos la hoja
ActiveSheet.Unprotect
'ocultamos el procedimiento
Application.ScreenUpdating = False
'fichamos la celda donde estamos
celda = ActiveCell.Address
'si introducimos datos en la columna A,
'entonces añadimos la validación de datos en
'las dos columnas finales
If Not Application.Intersect(Target, Range("A:A")) Is Nothing Then
'añadimos la lista de validación de las formas de pago
Cells(Target.Row, 11).Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, Formula1:="=FPA"
End With
'añadimos la lista de validación del día de pago fijo
Cells(ActiveCell.Row, 12).Select
With Selection.Validation
.Delete
.Add Type:=xlValidateWholeNumber, Operator:=xlBetween, _
Formula1:="1", Formula2:="31"
.ErrorMessage = "El día de pago debe estar entre el 1 y el 31."
End With
End If
'volvemos donde estábamos
Range(celda).Select
'mostramos el procedimiento
Application.ScreenUpdating = True
'protegemos la hoja
ActiveSheet.Protect
End Sub

Alta y modificación de formas de pago:

Esta hoja nos sirve para dar de alta las diferentes formas de pago, que luego serán las que se muestren en el desplegable al dar de alta los clientes. Un ejemplo de ello es el pantallazo que os presento a continuación (clic sobre la imagen, para ampliarla):

El macro que tenemos en esta hoja con las formas de pago (la hoja3), nos sirve para proteger la hoja al activarse la misma, y su código es el siguiente:

Private Sub Worksheet_Activate()
'Si hay errores, que continúe
On Error Resume Next
'protegemos la hoja
ActiveSheet.Protect
End Sub

Facturas emitidas:

Aquí es donde iremos introduciendo las facturas de nuestra empresa. Solo tendremos que seleccionar el cliente desde el desplegable que nos aparecerá en la columna A. Este desplegable se genera automáticamente al colocarnos en una celda de esa columna, y toma los datos de la hoja de clientes (evidentemente de los clientes que hayamos introducido). Si generamos una nueva factura, antes deberemos dar de alta al cliente, y su forma de pago si es que no la tenemos ya en nuestra hoja de forma de pago.

El aspecto que presenta esta hoja de facturas es similar a la de este ejemplo (clic sobre la imagen, para ampliarla):

En el caso de haber facturas vencidas, la fila nos saldrá de color azul celeste, para tenerlas más a la vista. Solo nos quedará llamar a los clientes que no han pagado todavía, para recordarles que su factura ha vencido, y no hemos recibido el cobro. Una vez las hayamos cobrado, eliminaremos las facturas, situándonos en cualquier celda de la fila, y pulsando el botón "Eliminar fila".

Lo primero que tenemos que hacer para dar de alta un cliente, es seleccionarlo del desplegable, tal y como se muestra en este ejemplo (clic sobre la imagen, para ampliarla):

Una vez seleccionado el cliente, automáticamente nos aparecerán a la derecha una serie de datos, algunos de ellos son propuestas que evidentemente podemos modificar, como el número de factura (nos genera el siguiente nº de factura presuponiendo que el de la fila anterior es el último número de factura), y la fecha de la factura (presuponiendo que es la misma que la fecha de la fila anterior). El vencimiento evidentemente no lo debemos introducir, pues lo obtenemos automáticamente a partir de la fecha de emisión de la factura, y de las condiciones de pago (forma de pago).

Con todo ello, y siguiendo con nuestro ejemplo, al seleccionar en el desplegable, nos aparecerá algo como lo de este ejemplo (clic sobre la imagen, para ampliarla):

Los macros que nos encontraremos en esta hoja (la hoja4), son los siguientes. Uno de ellos, para proteger la hoja, en el momento de activarse:

Private Sub Worksheet_Activate()
'Si hay errores, que continúe
On Error Resume Next
'protegemos la hoja
ActiveSheet.Protect
End Sub

Otro macro que se ejecuta al seleccionar una celda de la columna A, y que nos sirve para generar el desplegable con los clientes a seleccionar:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Si hay errores, que continúe
On Error Resume Next
'desprotegemos la hoja
ActiveSheet.Unprotect
'si seleccionamos una celda de la columna A,
'entonces añadimos la validación de datos en
'la celda en cuestión
If Not Application.Intersect(Target, Range("A:A")) _
Is Nothing And ActiveCell.Row >= 5 Then
'añadimos la lista de validación de los clientes
Cells(Target.Row, 1).Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, Formula1:="=CLI"
End With
End If
'protegemos la hoja
ActiveSheet.Protect
End Sub

Y este otro macro que se ejecutará cuando cambiemos un dato de la columna A, y que nos generará todos los datos que comentábamos anteriormente (número de factura, fecha de factura, vencimiento, días hasta el vencimiento, y días de exceso sobre el vencimiento):

Private Sub Worksheet_Change(ByVal Target As Range)
'Si hay errores, que continúe
On Error Resume Next
'desprotegemos la hoja
ActiveSheet.Unprotect
'si modificamos una celda de la columna A, añadimos
'las fórmulas en las columnas correspondientes
If Not Application.Intersect(Target, Range("A:A")) Is Nothing Then
'si no tenemos cliente o fecha de fra., borramos el vto. si
'lo hay así como los días de exceso, y los días hasta el vto.
If ActiveCell = "" Then
ActiveCell.Offset(0, 1) = ""
ActiveCell.Offset(0, 2) = ""
ActiveCell.Offset(0, 4) = ""
ActiveCell.Offset(0, 5) = ""
ActiveCell.Offset(0, 6) = ""
End If
'si seleccionamos un cliente en el desplegable
'añadimos la fra. previsible, la fecha de fra. previsible,
'el vto., los días de exceso, y los días hasta el vto.
If ActiveCell <> "" Then
'añadimos el nº de fra. previsible
If ActiveCell.Offset(-1, 1) <> "" And ActiveCell.Offset(0, 1) = "" Then
ActiveCell.Offset(0, 1) = ActiveCell.Offset(-1, 1) + 1
End If
'añadimos la fecha de fra. previsible
If ActiveCell.Offset(-1, 2) <> "" And ActiveCell.Offset(0, 2) = "" Then
ActiveCell.Offset(0, 2) = ActiveCell.Offset(-1, 2)
End If
'añadimos el vencimiento
If ActiveCell.Offset(-1, 4) <> "" And ActiveCell.Offset(0, 4) = "" Then
ActiveCell.Offset(0, 4).FormulaR1C1 = "=IF(VLOOKUP(RC[-4]," & _
"TCLI,12,0)<>"""",IF(DAY(RC[-2]+VLOOKUP(VLOOKUP(RC[-4],TCLI,11,0)" & _
",TFPA,2,0))>VLOOKUP(RC[-4],TCLI,12,0),MIN(DATE(YEAR(RC[-2]" & _
"+VLOOKUP(VLOOKUP(RC[-4],TCLI,11,0),TFPA,2,0)),MONTH(RC[-2]" & _
"+VLOOKUP(VLOOKUP(RC[-4],TCLI,11,0),TFPA,2,0))+1,DAY(VLOOKUP" & _
"(RC[-4],TCLI,12,0))),FIN.MES(RC[-2]+VLOOKUP(VLOOKUP(RC[-4]," & _
"TCLI,11,0),TFPA,2,0),1)),MIN(DATE(YEAR(RC[-2]+" & _
"VLOOKUP(VLOOKUP(RC[-4],TCLI,11,0),TFPA,2,0)),MONTH(RC[-2]" & _
"+VLOOKUP(VLOOKUP(RC[-4],TCLI,11,0),TFPA,2,0)),DAY(VLOOKUP" & _
"(RC[-4],TCLI,12,0))),FIN.MES(RC[-2]+VLOOKUP(VLOOKUP" & _
"(RC[-4],TCLI,11,0),TFPA,2,0),0))),RC[-2]+VLOOKUP(VLOOKUP" & _
"(RC[-4],TCLI,11,0),TFPA,2,0))"
End If
'añadimos los días hasta el vencimiento
If ActiveCell.Offset(-1, 5) <> "" And ActiveCell.Offset(0, 5) = "" Then
ActiveCell.Offset(0, 5) = "=IF(TODAY()-RC[-1]<0,RC[-1]-TODAY(),0)"
End If
'añadimos los días de exceso sobre el vencimiento
If ActiveCell.Offset(-1, 6) <> "" And ActiveCell.Offset(0, 6) = "" Then
ActiveCell.Offset(0, 6) = "=IF(TODAY()-RC[-2]>=0,TODAY()-RC[-2],0)"
End If
End If
End If
'protegemos la hoja
ActiveSheet.Protect
End Sub

Previsión de cobros:

Una vez tengamos dadas de alta todas las facturas, simplemente deberemos acceder a la hoja donde se nos genera de forma automática la previsión de cobros. Para ello, pulsaremos el botón "Previsión de cobros".

Lo que obtendremos será algo similar a lo que se muestra en el siguiente ejemplo:

En esta hoja no tenemos ningún macro, pues todo el código que genera la previsión de cobros, lo tenemos en un macro dentro del Módulo1.

Vamos a entrar ahora a comentar los macros que tenemos en el Módulo1. Lo primero que hay es el macro Auto_open(), que como sabéis es el macro que se ejecuta al abrir el fichero. El código de nuestro macro Auto_open() es el siguiente (lo he modificado una vez publicado el artículo, para que se carguen automáticamente las herramientas para análisis, y no tener problemas con la función FIN.MES):

Sub Auto_open()
'Si hay errores, que continúe
On Error Resume Next
'Ocultamos el procedimiento
Application.ScreenUpdating = False
'activamos las herramientas para análisis,
'para no tener problemas con la función FIN.MES
AddIns("Herramientas para análisis").Installed = True
'no mostramos las pestañas (las hojas)
ActiveWindow.DisplayWorkbookTabs = False
'buscamos si hay alguna factura vencida, y también
'si hay facturas para vencer en los próximos 7 días
Hoja4.Select
Range("G5").Select
'si no hay facturas, saltamos a la línea correspondiente
If ActiveCell.Offset(0, -6) = "" Then GoTo sinfacturas
'para todo el rango de datos de la columna J
For i = 5 To Selection.End(xlDown).Row
'miramos si hay facturas vencidas
If ActiveCell > 0 Then vencidas = vencidas + 1
'miramos si hay facturas que
'vencen en los próximos 7 días
If ActiveCell.Offset(0, -1) <= 7 And _
ActiveCell.Offset(0, -1) > 0 Then proximas = proximas + 1
'bajamos una fila
ActiveCell.Offset(1, 0).Select
Next
'nos situamos para escribir la siguiente factura
ActiveCell.Offset(0, -6).Select
'mostramos un mensaje si hay facturas vencidas
If vencidas = 1 Then MsgBox ("Hoy es " & FormatDateTime(Date, vbLongDate) & _
"," + Chr(10) + "y hay " & vencidas & " factura vencida.") _
, , "Facturas vencidas"
If vencidas > 1 Then MsgBox ("Hoy es " & FormatDateTime(Date, vbLongDate) & _
"," + Chr(10) + "y hay " & vencidas & " facturas vencidas.") _
, , "Facturas vencidas"
'mostramos un mensaje si hay facturas próximas a vencer
If proximas = 1 Then MsgBox ("Hay " & proximas & " factura que vence " _
+ "en los próximos 7 días."), , "Facturas para vencer"
If proximas > 1 Then MsgBox ("Hay " & proximas & " facturas que vencen " _
+ "en los próximos 7 días."), , "Facturas para vencer"
'nos situamos en el menú principal
sinfacturas:
Hoja1.Select
Range("E12").Select
'Mostramos el procedimiento
Application.ScreenUpdating = True
End Sub

No comentaré mucho sobre lo que hace el macro Auto_open(), porque con leer los comentarios del propio macro, lo tenemos todo chupado. Dentro de ese código, hay dos partes importantes que sí que me gustaría recalcar. Una de ellas, es que se nos mostrará un aviso informándonos de las facturas que tenemos vencidas. Este aviso se nos mostrará con tan solo abrir el fichero, siempre y cuando tengamos facturas vencidas, claro está. En el caso de haber facturas vencidas, se nos mostrará un mensaje similar a este que os muestro a continuación, donde nos avisa que hay 9 facturas vencidas:

En el caso de tener facturas que venzan en los próximos 7 días, la aplicación también nos mostrará un aviso como el que se nos muestra a continuación, donde nos avisa que tenemos 1 factura que nos vence en los próximos 7 días:

Aparte del macro Auto_open(), tenemos estos otros macros cuyo código os incluyo a continuación. Un macro para imprimir:

Sub Imprimir()
'Imprimimos la hoja
ActiveWindow.SelectedSheets.PrintOut Copies:=1
End Sub

Otro macro para guardar el fichero pero sin cerrar la aplicación, es decir, para guardar los datos, y continuar trabajando:

Sub Guardar()
'Guardamos el libro
ActiveWorkbook.Save
End Sub

Otro macro para ir a la hoja de clientes:

Sub Clientes()
'Vamos a la hoja2
Hoja2.Select
Range("A5").Select
'nos situamos en la primera fila libre
Selection.End(xlDown).Offset(1, 0).Select
End Sub

Otro macro para acceder a la hoja con las formas de pago:

Sub Formas_de_pago()
'Vamos a la hoja3
Hoja3.Select
Range("B5").Select
'nos situamos en la primera fila libre
Selection.End(xlDown).Offset(1, 0).Select
End Sub

Otro macro para acceder a la hoja de facturas:

Sub Facturas()
'Vamos a la hoja4
Hoja4.Select
Range("A5").Select
'nos situamos en la primera fila libre
If ActiveCell.Offset(0, 1) <> "" Then
Selection.End(xlDown).Offset(1, 0).Select
End If
End Sub

Otro macro para volver al menú principal:

Sub Volver_al_menu()
'volvemos al menú, pero dependiendo
'de la hoja donde estemos, ordenaremos
'también los datos
If ActiveSheet.CodeName = "Hoja2" Then Ordenar_clientes
If ActiveSheet.CodeName = "Hoja3" Then Ordenar_formas_de_pago
'volvemos al menú
Hoja1.Select
Range("E12").Select
End Sub

Otro macro para ordenar alfabéticamente los clientes:

Sub Ordenar_clientes()
'Si hay errores, que continúe
On Error Resume Next
'ocultamos el procedimiento
Application.ScreenUpdating = False
'desprotegemos la hoja
ActiveSheet.Unprotect
'fichamos la celda donde estamos, para volver a ella
celda_donde_estamos = ActiveCell.Address
'seleccionamos la primera fila con datos
Range("A5:K5").Select
'Ordenamos las celdas hasta el final
Range(Selection, Selection.End(xlDown)).Select
Selection.Sort Key1:=Range("A5")
'seleccionamos todo el rango continuo
'desde A5 hasta abajo del todo
Range("A5").Select
Selection.End(xlDown).Select
'le ponemos un nombre a ese rango
ActiveWorkbook.Names.Add Name:="CLI", RefersToR1C1:="='" _
& ActiveSheet.Name & "'!R5C1:R" & ActiveCell.Row & "C1"
'ahora le ponemos un nombre a toda la tabla
Range("A5").Select
Selection.End(xlDown).Select
'le ponemos un nombre a ese rango
ActiveWorkbook.Names.Add Name:="TCLI", RefersToR1C1:="='" _
& ActiveSheet.Name & "'!R5C1:R" & ActiveCell.Row & "C12"
'volvemos donde estábamos
'protegemos la hoja
ActiveSheet.Protect
'volvemos a la celda donde estábamos
Range(celda_donde_estamos).Select
'mostramos el procedimiento
Application.ScreenUpdating = True
'mostramos un mensaje
MsgBox ("Se han ordenado alfabéticamente los clientes.") _
, , "Clientes ordenados"
End Sub

Otro macro para ordenar alfabéticamente las formas de pago:

Sub Ordenar_formas_de_pago()
'Si hay errores, que continúe
On Error Resume Next
'ocultamos el procedimiento
Application.ScreenUpdating = False
'desprotegemos la hoja
ActiveSheet.Unprotect
'fichamos la celda donde estamos, para volver a ella
celda_donde_estamos = ActiveCell.Address
'seleccionamos la primera fila con datos
Range("B5:C5").Select
'Ordenamos las celdas hasta el final
Range(Selection, Selection.End(xlDown)).Select
Selection.Sort Key1:=Range("B5")
'seleccionamos todo el rango continuo
'desde B5 hasta abajo del todo
Range("B5").Select
Selection.End(xlDown).Select
'le ponemos un nombre a ese rango
ActiveWorkbook.Names.Add Name:="FPA", RefersToR1C1:="='" _
& ActiveSheet.Name & "'!R5C2:R" & ActiveCell.Row & "C2"
'ahora le ponemos un nombre a toda la tabla
Range("B5").Select
Selection.End(xlDown).Select
'le ponemos un nombre a ese rango
ActiveWorkbook.Names.Add Name:="TFPA", RefersToR1C1:="='" _
& ActiveSheet.Name & "'!R5C2:R" & ActiveCell.Row & "C3"
'volvemos donde estábamos
'protegemos la hoja
ActiveSheet.Protect
'volvemos a la celda donde estábamos
Range(celda_donde_estamos).Select
'mostramos el procedimiento
Application.ScreenUpdating = True
'mostramos un mensaje
MsgBox ("Se han ordenado alfabéticamente las formas de pago.") _
, , "Formas de pago ordenadas"
End Sub

Tomad un poco de aire, que aún quedan unos cuantos macros más. El siguiente que nos encontraremos es para eliminar clientes:

Sub Eliminar_cliente()
'Ocultamos el procedimiento
Application.ScreenUpdating = False
'si hay errores, que continúe
On Error Resume Next
'fichamos el nombre del cliente a buscar
'y la celda donde estamos
cliente = Cells(ActiveCell.Row, 1)
celda = ActiveCell.Address
'antes de borrar un cliente, buscaremos que no tengamos
'facturas emitidas a nombre de ese cliente a eliminar
Hoja4.Select
'buscamos el cliente, y si existe en las facturas
'creamos una variable
If Not Cells.Find(cliente) Is Nothing And cliente <> "" Then
'si ese cliente tiene facturas,
'creamos una variable
borrar_fila = "no"
End If
'volvemos a la hoja2
Hoja2.Select
'y a la celda donde estábamos
Range(celda).Select
'si borrar_fila = "no", mostramos un mensaje
'de que no podemos borrar la fila
If borrar_fila = "no" Then
MsgBox ("Antes de borrar este cliente, debes borrar sus facturas." _
+ Chr(10) + "Por favor, accede a la hoja de Faturas, para eliminarlas.") _
, , "Imposible borrar este cliente"
'finalizamos el macro
Exit Sub
End If
'mostramos el procedimiento
Application.ScreenUpdating = True
'si estamos en la fila 5 o superior,
'eliminamos la fila
If Selection.Row >= 5 Then
'desprotegemos la hoja
ActiveSheet.Unprotect
'eliminamos la fila donde estamos
Selection.EntireRow.Delete
'mostramos un mensaje
MsgBox ("Los datos de este cliente, han sido eliminados.") _
, , "Cliente eliminado"
'protegemos la hoja
ActiveSheet.Protect
End If
End Sub

Otro macro para eliminar formas de pago:

Sub Eliminar_forma_de_pago()
'Ocultamos el procedimiento
Application.ScreenUpdating = False
'si hay errores, que continúe
On Error Resume Next
'fichamos el nombre de la forma de pago a buscar
'y la celda donde estamos
formadepago = Cells(ActiveCell.Row, 2)
celda = ActiveCell.Address
'antes de borrar una forma de pago, buscaremos
'que no tengamos facturas emitidas a clientes
'con esa forma de pago a eliminar
Hoja2.Select
'buscamos la forma de pago, y si existe en
'algún cliente, creamos una variable
If Not Cells.Find(formadepago) Is Nothing And formadepago <> "" Then
'si ese cliente tiene facturas,
'creamos una variable
borrar_fila = "no"
End If
'volvemos a la hoja3
Hoja3.Select
'y a la celda donde estábamos
Range(celda).Select
'si borrar_fila = "no", mostramos un mensaje
'de que no podemos borrar la fila
If borrar_fila = "no" Then
MsgBox ("Antes de borrar esta forma de pago, debes borrar o cambiar la forma" _
+ Chr(10) + "de pago de los clientes que utilizan esta modalidad de pago a borrar." _
+ Chr(10) + Chr(10) + "Por favor, accede a la hoja de Clientes, para editarlos.") _
, , "Imposible borrar esta forma de pago"
'finalizamos el macro
Exit Sub
End If
'mostramos el procedimiento
Application.ScreenUpdating = True
'si estamos en la fila 5 o superior,
'eliminamos la fila
If Selection.Row >= 5 Then
'desprotegemos la hoja
ActiveSheet.Unprotect
'eliminamos la fila donde estamos
Selection.EntireRow.Delete
'mostramos un mensaje
MsgBox ("Esta forma de pago, ha sido eliminada.") _
, , "Forma de pago eliminada"
'protegemos la hoja
ActiveSheet.Protect
End If
End Sub

Otro macro para eliminar facturas:

Sub Eliminar_factura()
'Si estamos en la fila 5 o superior,
'eliminamos la fila
If Selection.Row >= 5 Then
'desprotegemos la hoja
ActiveSheet.Unprotect
'eliminamos la fila donde estamos
Selection.EntireRow.Delete
'mostramos un mensaje
MsgBox ("La factura seleccionada, ha sido eliminada.") _
, , "Factura eliminada"
'protegemos la hoja
ActiveSheet.Protect
End If
End Sub

Y el último macro que nos encontraremos, y también el más largo, es este, que nos sirve para generar nuestra previsión de cobros, detallando los vencimientos por meses y por clientes:

Sub Prevision_de_cobros()
'Si hay errores, que continúe
On Error Resume Next
'cambiamos el texto del botón de la hoja1 y hoja4
If ActiveSheet.CodeName = "Hoja1" Or _
ActiveSheet.CodeName = "Hoja4" Then
'creamos una variable
If ActiveSheet.CodeName = "Hoja1" Then hoja = "menu"
If ActiveSheet.CodeName = "Hoja4" Then hoja = "facturas"
'desprotegemos la hoja
ActiveSheet.Unprotect
'seleccionamos el botón
ActiveSheet.Shapes("Botón 2").Select
'le cambiamos el nombre, y lo ponemos en rojo
Selection.Characters.Text = "Procesando..."
With Selection.Font
.ColorIndex = 3
End With
End If
'ocultamos el procedimiento
Application.ScreenUpdating = False
'comprobamos que tengamos facturas
If Hoja4.Range("A5") = "" Then
'si no hay facturas, mostramos un mensaje
MsgBox ("Por favor, revisa todo, para poder continuar.") _
+ Chr(10) + Chr(10) + "Al parecer no hay facturas, y por tanto no se" _
+ Chr(10) + "puede generar la previsión de cobros." _
, , "Hay errores"
'seleccionamos el botón2
ActiveSheet.Shapes("Botón 2").Select
'le cambiamos el nombre
Selection.Characters.Text = "Previsión de cobros"
If Hoja = "menu" Then
With Selection.Characters(Start:=1, Length:=13).Font
.ColorIndex = xlAutomatic
End With
With Selection.Characters(Start:=14, Length:=6).Font
.ColorIndex = 3
End With
ElseIf Hoja = "facturas" Then
With Selection.Font
.ColorIndex = xlAutomatic
End With
End If
'protegemos la hoja
ActiveSheet.Protect
'finalizamos el macro
Exit Sub
End If
'fichamos la celda donde estamos
celda = ActiveCell.Address
'eliminamos todo lo que haya en la hoja5
Hoja5.Select
'desprotegemos la hoja
ActiveSheet.Unprotect
'seleccionamos la celda A5
Range("A5").Select
'seleccionamos todo el rango continuo
Range(Selection, Selection.End(xlDown)).Select
'borramos las filas (los clientes)
Selection.EntireRow.Delete
'borramos ahora las fechas
Range("B4").Select
'seleccionamos todo el rango continuo por la derecha
Range(Selection, Selection.End(xlToRight)).Select
'borramos las filas (los clientes)
Selection.EntireColumn.Delete
'seleccionamos la hoja2
Hoja2.Select
'nos situamos en la primera celda con datos
Range("A5").Select
'seleccionamos todo el rango continuo
Range(Selection, Selection.End(xlDown)).Select
'copiamos los datos
Selection.Copy
'seleccionamos la celda A5
Range("A5").Select
'seleccionamos la hoja5
Hoja5.Select
'seleccionamos la celda A5
Range("A5").Select
'pegamos los datos
Selection.PasteSpecial Paste:=xlPasteValues
'fichamos la fila máxima
fila_maxima = Selection.End(xlDown).Row
'vamos a la hoja4, donde tenemos las facturas
'y seleccionamos la fecha menor y mayor
Hoja4.Select
'seleccionamos todo el rango continuo
'desde A5 hasta abajo del todo
Range("A5").Select
Selection.End(xlDown).Select
'le ponemos un nombre a ese rango
ActiveWorkbook.Names.Add Name:="FRAS", RefersToR1C1:="='" _
& ActiveSheet.Name & "'!R5C1:R" & ActiveCell.Row & "C1"
'seleccionamos todo el rango continuo
'desde D5 hasta abajo del todo
Range("D5").Select
Selection.End(xlDown).Select
'le ponemos un nombre a ese rango
ActiveWorkbook.Names.Add Name:="IMPORTES", RefersToR1C1:="='" _
& ActiveSheet.Name & "'!R5C4:R" & ActiveCell.Row & "C4"
'seleccionamos todo el rango continuo
'desde E5 hasta abajo del todo
Range("E5").Select
Selection.End(xlDown).Select
'le ponemos un nombre a ese rango
ActiveWorkbook.Names.Add Name:="VTOS", RefersToR1C1:="='" _
& ActiveSheet.Name & "'!R5C5:R" & ActiveCell.Row & "C5"
'volvemos a E5
Range("E5").Select
'definimos dos variables (fecha mínima y máxima)
minimo = ActiveCell
maximo = ActiveCell
Do While Not IsEmpty(ActiveCell)
'seleccionamos la fecha mínima y máxima
If ActiveCell < minimo Then minimo = ActiveCell
If ActiveCell > maximo Then maximo = ActiveCell
'controlamos que no falten datos: nombre del cliente,
'fecha de factura, vencimiento e importe.
'Comenzamos controlando el vencimiento
If IsDate(ActiveCell) <> True Then incorrecto = True
'controlamos la fecha de la factura
If IsDate(ActiveCell.Offset(0, -2)) <> True Then incorrecto = True
'controlamos el importe
If Not IsNumeric(ActiveCell.Offset(0, -1)) Or _
ActiveCell.Offset(0, -1) = "" Then incorrecto = True
'controlamos que exista el cliente
If ActiveCell.Offset(0, -4) = "" Then incorrecto = True
'bajamos una fila
ActiveCell.Offset(1, 0).Select
Loop
'si hay errores, mostramos un mensaje
If Err.Number <> 0 Then
'mostramos un mensaje
MsgBox ("Existen errores." _
+ Chr(10) + Chr(10) + "Por favor, revisa todo, para poder continuar.") _
, , "Datos incorrectos"
'volvemos a la hoja4
Hoja4.Select
'nos situábamos en la celda donde estábamos
Range(celda).Select
'vamos a la línea "boton"
GoTo boton
End If
'si hay errores, mostramos un mensaje
If incorrecto = True Then
'mostramos un mensaje
MsgBox ("Existen errores en algunas de estas columnas:" _
+ Chr(10) + Chr(10) + "- Nombre de los clientes." _
+ Chr(10) + "- Fecha de las facturas." _
+ Chr(10) + "- Importe de las facturas." _
+ Chr(10) + "- Vencimiento de las facturas." _
+ Chr(10) + Chr(10) + "Por favor, revísalo, para poder continuar.") _
, , "Datos incorrectos"
'volvemos a la hoja4
Hoja4.Select
'nos situábamos en la celda donde estábamos
Range(celda).Select
'vamos a la línea "boton"
GoTo boton
End If
'ahora seleccionamos el primer día del mes del mínimo y máximo
minimo = "01" & "/" & Month(minimo) & "/" & Year(minimo)
maximo = "01" & "/" & Month(maximo) & "/" & Year(maximo)
'recuerda que esta aplicación ha salido de
'http://hojas-de-calculo-en-excel.blogspot.com
'calculamos la diferencia en meses entre el mínimo y el máximo
meses = DateDiff("m", minimo, maximo)
'si la diferencia de meses es superior a 120
'mostramos un mensaje de error
If meses > 120 Then
'mostramos un mensaje
MsgBox ("Hay más de 10 años de diferencia entre el " _
+ Chr(10) + "primer vencimiento, y el último vencimiento." _
+ Chr(10) + Chr(10) + "Por favor, revísalo, para poder continuar.") _
, , "Vencimientos incorrectos"
'volvemos a la hoja4
Hoja4.Select
'nos situábamos en la celda donde estábamos
Range(celda).Select
'vamos a la línea "boton"
GoTo boton
End If
'volvemos a la hoja5
Hoja5.Select
'escribimos los meses para planificar los cobros,
'primero escribiendo los meses
Range("A4").Select
For i = 0 To meses
'seleccionamos la columna de la derecha
ActiveCell.Offset(0, 1).Select
'escribimos el mes
ActiveCell = DateAdd("m", i, minimo)
Next
'escribimos el total
ActiveCell.Offset(0, 1) = "TOTAL"
'fichamos la columna máxima
columna_maxima = Selection.End(xlToRight).Column
'copiamos el formato de la celda A4
Range("A4").Select
ActiveCell.Copy
'seleccionamos todo el rango continuo por
'la derecha, desde la segunda columna
Range(Selection.Offset(0, 1), Selection.End(xlToRight)).Select
'pegamos los formatos
Selection.PasteSpecial Paste:=xlPasteFormats
'ponemos los formatos de fecha (mes y año)
Selection.NumberFormat = "[$-340A]mmm yyyy"
'alineamos esos encabezados a la derecha
Range("B4").Select
'seleccionamos todo el rango continuo por la derecha
Range(Selection, Selection.End(xlToRight)).Select
With Selection
.HorizontalAlignment = xlRight
End With
'nos situamos en la celda B5
Range("B5").Select
'escribimos la fórmula
ActiveCell.FormulaR1C1 = _
"=SUMPRODUCT((FRAS=RC1)*(MONTH(VTOS)=MONTH(R4C))*(YEAR(VTOS)=YEAR(R4C))*IMPORTES)"
'copiamos y pegamos la fórmula en toda la tabla
Selection.Copy
Range(Cells(5, 2), Cells(fila_maxima, columna_maxima - 1)).Select
ActiveSheet.Paste
'ponemos los totales
Range("A5").Select
'nos situamos en la primera fila libre
Selection.End(xlDown).Offset(1, 0).Select
ActiveCell = "TOTALES"
'pasamos a la siguiente columna
ActiveCell.Offset(0, 1).Select
'escribimos las sumas totales de cada mes
For i = 2 To columna_maxima
'escribimos el total
ActiveCell.FormulaR1C1 = "=SUM(R[-" & fila_maxima - 4 & _
"]C:R[-1]C)"
'nos movemos a la derecha
ActiveCell.Offset(0, 1).Select
Next
'volvemos a la última columna, y
'seleccionamos toda la fila
ActiveCell.Offset(0, -1).Select
Range(Selection, Selection.End(xlToLeft)).Select
'ponemos la fila en negrita, con
'bordes, y con la trama de color amarillo
Selection.Font.Bold = True
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
End With
With Selection.Interior
.ColorIndex = 36
End With
'nos vamos a la última columna,
'es decir, la de los totales
Cells(5, columna_maxima).Select
'escribimos las sumas totales de cada cliente
For i = 5 To fila_maxima
'escribimos el total
ActiveCell.FormulaR1C1 = "=SUM(RC[-" & columna_maxima - 2 & _
"]:RC[-1])"
'nos movemos hacia abajo
ActiveCell.Offset(1, 0).Select
Next
'ponemos todas esas sumas de cada cliente, en negrita
Range(Selection, Selection.End(xlUp)).Select
Selection.Font.Bold = True
'le volvemos a poner el nombre correcto al botón
boton:
If hoja = "menu" Or hoja = "facturas" Then
'seleccionamos la hoja1 o la hoja4
If hoja = "menu" Then Hoja1.Select
If hoja = "facturas" Then Hoja4.Select
'desprotegemos la hoja
ActiveSheet.Unprotect
'seleccionamos el botón2
ActiveSheet.Shapes("Botón 2").Select
'le cambiamos el nombre
Selection.Characters.Text = "Previsión de cobros"
If hoja = "menu" Then
With Selection.Characters(Start:=1, Length:=13).Font
.ColorIndex = xlAutomatic
End With
With Selection.Characters(Start:=14, Length:=6).Font
.ColorIndex = 3
End With
ElseIf hoja = "facturas" Then
With Selection.Font
.ColorIndex = xlAutomatic
End With
End If
'protegemos la hoja
ActiveSheet.Protect
'finalizamos el macro, si hay errores
If Err.Number <> 0 Or incorrecto = True Or meses > 120 Then Exit Sub
End If
'nos situamos en la fila con el rótulo de los
'totales, de la hoja5
Hoja5.Select
Range("A5").Select
Selection.End(xlDown).Select
'protegemos la hoja
ActiveSheet.Protect
'mostramos el procedimiento
Application.ScreenUpdating = True
End Sub

Finalmente comentar que el procedimiento correcto para inicializar la aplicación sería este, y necesariamente en este orden que os incluyo a continuación, pues en caso de no seguir este procedimiento, la aplicación puede responder con errores o de forma inesperada:

1. Dar de alta las formas de pago.
2. Dar de alta los clientes.
3. Dar de alta las facturas.

A partir de ese momento, y si nuestros clientes son los que son, y no generamos nuevos clientes, tan solo tendremos que preocuparnos por introducir las facturas. Si tenemos un cliente nuevo, deberemos darlo de alta previamente, y si su forma de pago es nueva, deberemos dar de alta ésta lo primero de todo, siguiendo el esquema explicado en el párrafo anterior.

Espero que os sea de utilidad esta aplicación. Desde aquí podéis descargar el fichero de Excel, con el ejemplo que hemos visto en este artículo (resubido, con mejoras en el código, el 09/07/2011).

Si hacéis algún cambio a la aplicación, os ruego que no me pidáis modificaciones personalizadas para vuestro caso en concreto o el de vuestra empresa, porque mi tiempo es muy limitado, y prefiero utilizarlo en resolver cuestiones de uso común. Si por el contrario, crees que sería interesante introducir alguna mejora en la aplicación, para que sea de uso público y los demás puedan aprovechar esa utilidad, entonces gustoso intentaré darle solución, si mis limitados conocimientos me lo permiten.

Si en vuestra empresa trabajáis con descuento comercial (descuento de efectos), en este otro artículo podréis descargar la aplicación para controlar vencimientos de facturas y recibos, con descuento comercial.

Por cierto, al hilo de esta aplicación para el control de vencimientos de facturas y recibos, solo quiero recordaros a los que realizáis actividades empresariales o profesionales en territorio español, que la reciente publicación de la ley de lucha contra la morosidad comercial, solo permite un máximo de 60 días de crédito para aquellas operaciones que se realicen a partir del 1 de enero de 2013. Es decir, a partir de esa fecha, no podréis darle a vuestros clientes un plazo de pago superior a los 60 días (si los clientes tienen fecha fija de pago, deberéis tener en cuenta esta circunstancia, para adaptar las condiciones de pago, y que no exceda del límite marcado en la ley). No obstante, hasta esa fecha (1 de enero de 2013) uno no puede hacer lo que desee, ya que desde la entrada en vigor de la ley (7 de julio de 2010), existe un periodo transitorio para adaptarnos a ese máximo de 60 días, y no tener que bajar de golpe de los 180, 150, 120, 90 días, o los que vuestra empresa de a sus clientes. De esta forma, todas las empresas competirán en las mismas condiciones crediticias. Algo muy importante que como novedad incorpora la ley (en sustitución de la anterior ley antimorosidad), es que no admite el acuerdo entre las partes, para alargar los plazos de crédito a los clientes, es decir, bajo ningún concepto se podrá superar el límite de los 60 días de crédito comercial, pues no se permite que pactes alargar ese límite con tus clientes.

Préstamos según el método americano

2010-06-09T00:01:00.007+02:00

Existe un método de amortización de préstamos, que consiste en liquidar únicamente intereses en cada cuota, excepto en la cuota final, que aparte de los intereses, también amortizaremos el préstamo, y además lo haremos de golpe. Este método de amortización de préstamos, se denomina método americano, y su uso aunque no está muy extendido, siempre puede sernos útil.

Las características básicas de este tipo de préstamos, son las siguientes:

Tienen casi todas las cuotas constantes (excepto la última).

El capital se amortiza en la última cuota.

Los intereses que se pagan, son constantes en cada cuota.

Como siempre, el que quiera descargar la aplicación sin tener que leer todo este artículo, puede hacerlo desde el enlace que acabo de incluir, pero es recomendable una lectura rápida, al menos para saber de qué estamos hablando.

Como en el método francés (que es el sistema habitual para el cálculo de préstamos e hipotecas), expliqué detenidamente todo el código fuente, aquí solo incluiré la parte del código que varía con respecto al resto de métodos, que no es otra que las fórmulas de la tabla de amortización que se obtiene.

Los más avispados se darán cuenta que hemos eliminado todo lo relativo a la carencia en este método de amortización de préstamos, pues implícitamente la carencia ya viene incluida en la propia metodología de cálculo del sistema americano, ya que no se amortiza el principal hasta la última cuota.

Antes os mostraré unos pantallazos, tanto del formulario de información del método americano, como del propio formulario donde introduciremos los datos necesarios para realizar los cálculos:

El código fuente correspondiente a la parte más importante (las fórmulas del préstamo americano), es este:

'Seguimos poniendo el capital vivo
'antes del pago de la 1ª cuota
Range("E18").Select
ActiveCell.Formula = "=R7C[1]"
'seguimos poniendo el capital amortizado
'en la primera cuota (que es cero)
Range("F18").Select
ActiveCell = 0
'seguimos poniendo los intereses pagados
Range("G18").Select
ActiveCell.Formula = "=IF(RC[-5]<=R9C6,RC[-2]*R8C6/R10C6,0)"
'seguimos poniendo el capital amortizado acumulado
Range("H18").Select
ActiveCell = 0
'seguimos poniendo los intereses acumulados
Range("I18").Select
ActiveCell.Formula = "=IF(RC[-2]<>0,RC[-2],0)"
'seguimos poniendo la cuota total
Range("J18").Select
ActiveCell.Formula = "=RC[-4]+RC[-3]"
'seguimos poniendo el resto de datos, es decir
'el capital vivo antes del pago de cada cuota,
'el capital amortizado, los intereses, el capital
'amortizado acumulado, los intereses acumulados,
'y el importe de las cuotas
Range("E18").Select
For i = 1 To CuotasTotales - 1
'hasta la penúltima cuota...
If i < CuotasTotales - 1 Then
'el capital vivo
ActiveCell.Offset(1, 0).Formula = "=R[-1]C-R[-1]C[1]"
'el capital amortizado
ActiveCell.Offset(1, 1).Formula = "=R[-1]C"
'los intereses pagados
ActiveCell.Offset(1, 2).Formula = "=IF(RC[-5]<=R9C6,RC[-2]*R8C6/R10C6,0)"
'el capital amortizado acumulado
ActiveCell.Offset(1, 3).Formula = "=R[-1]C"
'los intereses acumulados
ActiveCell.Offset(1, 4).Formula = "=IF(RC[-7]<>0,R[-1]C+RC[-2],0)"
'la cuota total
ActiveCell.Offset(1, 5).Formula = "=RC[-4]+RC[-3]"
'bajamos a la fila siguiente
'y seguimos con el bucle
ActiveCell.Offset(1, 0).Select
'si estamos en la última cuota...
Else
'el capital vivo
ActiveCell.Offset(1, 0).Formula = "=R[-1]C-R[-1]C[1]"
'el capital amortizado
ActiveCell.Offset(1, 1).Formula = "=RC[-1]"
'los intereses pagados
ActiveCell.Offset(1, 2).Formula = "=IF(RC[-5]<=R9C6,RC[-2]*R8C6/R10C6,0)"
'el capital amortizado acumulado
ActiveCell.Offset(1, 3).Formula = "=RC[-2]"
'los intereses acumulados
ActiveCell.Offset(1, 4).Formula = "=IF(RC[-7]<>0,R[-1]C+RC[-2],0)"
'la cuota total
ActiveCell.Offset(1, 5).Formula = "=RC[-4]+RC[-3]"
'bajamos a la fila siguiente
'y seguimos con el bucle
ActiveCell.Offset(1, 0).Select
End If
Next

Y el resultado que obtendríamos sería algo como lo que os muestro en este ejemplo:

Desde aquí podéis descargar el fichero de Excel, con el ejemplo que hemos visto en este artículo. Espero que lo disfrutéis.

Préstamos con amortización de capital constante

2010-05-17T10:57:00.019+02:00

Hoy utilizaremos nuestra potente hoja de cálculo Excel, para montar un sistema de amortización de préstamos, siguiendo el mismo mecanismo y la metodología que ya aplicamos en su momento para el modelo de amortización de préstamos siguiendo el sistema francés, que es el modelo estándar para el cálculo de préstamos e hipotecas. En esta ocasión, lo que haremos será calcular la amortización del préstamo, pero amortizando en cada cuota la misma cantidad de capital, es decir, en esta ocasión trabajaremos los préstamos con amortización de capital constante. Como siempre, tendremos también en cuenta la posibilidad de que exista un periodo de carencia en el que no se amorticen cuotas del principal del préstamo.

Las características básicas de este tipo de préstamos, son las siguientes:

Los intereses se devengan al vencimiento de cada cuota.

El capital que se amortiza es constante en cada cuota, es decir, el principal del préstamo que se va pagando, es siempre del mismo importe, a medida que va transcurriendo el tiempo, y a medida que vamos liquidando las cuotas.

Los intereses van disminuyendo y son menores en cada cuota.

Las cuotas totales que se pagan, son variables y cada vez menores, debido a que los intereses son cada vez menores.

Como siempre, el que quiera descargar la aplicación sin tener que leer todo este artículo, puede hacerlo en cualquier momento, pero es recomendable al menos, una lectura rápida por encima, para hacernos una idea y saber de que estamos hablando.
Lo primero que haremos en nuestra aplicación financiera para el cálculo de préstamos con amortización de capital constante, será construir un formulario con información sobre el préstamo, tal y como aparece en la siguiente imagen:

Para no escribir líneas de código innecesarias, solo añadiré aquí el código fuente (o la parte del código fuente) que sea diferente a la aplicación que ya vimos en su momento cuando estudiamos el método francés de amortización de préstamos.

Para lanzar el formulario con la información sobre este tipo de préstamos, tal y como muestra la imagen anterior, simplemente tendremos que añadir estas líneas de código en un módulo (al formulario le hemos puesto por nombre InfoPrestamoCapitalConstante):

Sub Prestamo_Capital_constante()
'Lanzamos el formulario con info sobre
'el préstamo con amortización de capital constante
InfoPrestamoCapitalConstante.Show
End Sub

Una vez hayamos lanzado ese formulario, y pulsemos el botón “Si”, nos aparecerá este segundo formulario, para informar directamente de las características del préstamo.

Para no ser excesivamente aburrido, aquí os dejo solo la parte del código que está directamente relacionada con los préstamos con amortización de capital constante, y más concretamente la parte correspondiente a las fórmulas de la tabla resultante:

'Seguimos poniendo el capital vivo
'antes del pago de la 1ª cuota
Range("E18").Select
ActiveCell.Formula = "=IF(RC[-3]<=R14C6+1,R7C6,IF(RC[-3]<=R7C6,R[-1]C-R[-1]C[1],0))"
'seguimos poniendo el capital amortizado
'en la primera cuota
Range("F18").Select
ActiveCell.Formula = "=IF(RC[-4]<R14C6+1,0,IF(RC[-4]<=(R9C6+R14C6),R7C6/R9C6,0))"
'seguimos poniendo los intereses pagados
Range("G18").Select
ActiveCell.Formula = "=IF(RC[-5]<(R14C6+1),RC[-2]*R13C6/R10C6,IF(RC[-5]<=(R9C6+R14C6)," & _
"RC[-2]*R8C6/R10C6,0))"
'seguimos poniendo el capital amortizado acumulado
Range("H18").Select
ActiveCell.Formula = "=IF(RC[-2]<>0,RC[-2],0)"
'seguimos poniendo los intereses acumulados
Range("I18").Select
ActiveCell.Formula = "=IF(RC[-2]<>0,RC[-2],0)"
'seguimos poniendo la cuota total
Range("J18").Select
ActiveCell.Formula = "=IF(RC[-8]<=R18C5,RC[-3]+RC[-4],0)"
'seguimos poniendo el resto de datos, es decir
'el capital vivo antes del pago de cada cuota,
'el capital amortizado, los intereses, el capital
'amortizado acumulado, los intereses acumulados,
'y el importe de las cuotas
Range("E18").Select
For i = 1 To CuotasTotales - 1
'el capital vivo
ActiveCell.Offset(1, 0).Formula = "=IF(RC[-3]<=R14C6+1,R7C6,IF(RC[-3]<=R7C6,R[-1]C-R[-1]C[1],0))"
'el capital amortizado
ActiveCell.Offset(1, 1).Formula = "=IF(RC[-4]<R14C6+1,0,IF(RC[-4]<=(R9C6+R14C6),R7C6/R9C6,0))"
'los intereses pagados
ActiveCell.Offset(1, 2).Formula = "=IF(RC[-5]<(R14C6+1),RC[-2]*R13C6/R10C6,IF(RC[-5]<=(R9C6+R14C6)," & _
"RC[-2]*R8C6/R10C6,0))"
'el capital amortizado acumulado
ActiveCell.Offset(1, 3).Formula = "=IF(RC[-2]<>0,RC[-2],0)"
'los intereses acumulados
ActiveCell.Offset(1, 4).Formula = "=IF(RC[-2]<>0,RC[-2],0)"
'la cuota total
ActiveCell.Offset(1, 5).Formula = "=IF(RC[-8]<=R18C5,RC[-3]+RC[-4],0)"
'bajamos a la fila siguiente
'y seguimos con el bucle
ActiveCell.Offset(1, 0).Select
Next

Lo que obtendremos será una tabla como la que podemos ver en este ejemplo (aquí solo sale una parte de la tabla). En ella podréis ver que en cada cuota, se amortiza siempre la misma cantidad de capital (del principal del préstamo):

Llegados a este punto, solo me queda por comunicaros a todos los lectores del blog, que desde aquí podéis descargar el fichero de Excel, con el ejemplo que hemos visto en este artículo.

Calcular el VAN, para periodos irregulares

2010-04-21T00:01:00.003+02:00

En un artículo anterior, ya explicamos como calcular la TIR de una inversión, para periodos de tiempo irregulares o no constantes. Ahora le toca el turno a la VAN (Valor Actual Neto), en el caso de encontrarnos con periodos también irregulares.

Lo más habitual es que nos encontremos con periodos de tiempo siempre constantes, como por ejemplo, cuando invertimos una cantidad X hoy, y cobramos otra cantidad Y cada final de año, durante una cantidad de años determinada. En ese caso, con utilizar la función TIR, tenemos resuelto el tema cómo calcular la tasa interna de rendimiento. Lo mismo nos pasará con el VAN, si utilizamos la función VNA, para calcular el valor actual neto, en el caso de que queramos saber que cantidad deberíamos invertir (supondremos en un principio, que la inversión inicial es cero, para que el valor actual neto sea precisamente el valor actual, es decir, la inversión que tenemos que hacer), cobrando esas cantidades Y cada año, a medida que vamos jugando y probando con diferentes tipos de interés.

Ahora vamos a ver como calcular el valor actual de una inversión, para periodos que no siempre son constantes, sabiendo los cobros futuros, y el tipo de interés, pero sin determinar el valor inicial de la inversión (para obtener ese valor actual, utilizaremos la función VNA.NO.PER). Para ello, utilizaremos un ejemplo, que es la mejor forma de explicar el funcionamiento de una función en Excel. Vamos a determinar cual debería ser la cantidad a invertir hoy (suponemos que hoy es 21 de abril de 2010), en el caso de que recibamos los siguientes cobros, en las fechas en las que se indica en la siguiente tabla, y suponiendo que el tipo de interés efectivo de mercado, sea del 4,50%:

Si nos fijamos bien, lo que queremos determinar es el valor de la celda C4, que es la inversión inicial que tenemos que hacer, para cobrar todos esos importes en las fechas dadas, sabiendo que el tipo de interés es del 4,50%. Si observamos la tabla anterior con detenimiento, veremos que en la columna de la fecha, los periodos de tiempo no son constantes, por lo que para calcular el valor actual, lo apropiado es utilizar la función VNA.NO.PER de la siguiente forma:

=VNA.NO.PER(tipo de interés; flujos de caja; fechas)

Eso sí, antes de utilizar esta función aplicada a nuestro ejemplo, deberemos introducir en la celda C4, un cero, pues no debe quedar en blanco, ya que sino, la función siguiente nos daría error:

=VNA.NO.PER(C12;C4:C9;B4:B9)

De tal forma que obtendríamos algo como esto que nos aparece en la siguiente imagen:

Si nos fijamos lo que acabamos de obtener es el valor actual, es decir, el importe que debemos invertir hoy 21 de abril de 2010, sabiendo que el tipo de interés efectivo es del 4,50%, y sabiendo también que cobraremos o recuperaremos las cantidades indicadas en la tabla y en las fechas indicadas.

Si en lugar de utilizar la función como os ponía en el ejemplo, la cambiamos y ponemos esto (fijaos que cambiamos el rango, empezando desde la fila 5, en lugar de la fila 4), estaríamos haciendo las cosas mal:

=VNA.NO.PER(C12;C5:C9;B5:B9)

Lo que obtendríamos sería el valor actual, peroooooooo a la fecha del 14 de Julio de 2010, y no al 21 de abril de 2010, que es lo que nos interesa evaluar. De ahí que debamos incluir la fecha en la que queremos obtener el valor actual, poniendo cero como fujo de caja (ni cobro, ni pago).

Podemos hacer una comprobación de que necesitaremos invertir esos 35.043,98 euros (pesos, soles, dólares, o la moneda que queráis), al 4,50% de interés efectivo anual, sabiendo que cobraremos en las fechas indicadas, las cantidades que aparecen en las tablas anteriores. Para ello, utilizaremos la función TIR.NO.PER que vimos en otro artículo. Solo tendremos que construir la tabla de la siguiente forma:

Si os fijáis bien, como siempre a la hora de calcular la TIR, hemos incluido la inversión inicial en negativo, para diferenciarla de los cobros (es como si pagáramos la inversión inicial, para cobrarla posteriormente poco a poco, en los plazos indicados).

Calculadora de tiempos

2010-04-07T11:14:00.011+02:00

Dado que son muchos los usuarios que llegan a este blog, buscando como operar con unidades de tiempo, y como complemento a los artículos donde explicaba como convertir horas de 100 minutos, o como convertir unidades de tiempo a formato hh:mm:ss, o como convertir unidades de tiempo: horas, minutos, y segundos, o incluso como calcular horas normales y horas extras, hoy os presento una pequeña utilidad para convertir horas, minutos y segundos, a unidades decimales, todo ello presentado a través de un formulario, pero que se puede adaptar para cualquier otro modo de presentación, como por ejemplo incluyendo los resultados en diferentes celdas, de nuestra hoja de cálculo Excel.

Lo que tendremos que hacer es diseñar un UserForm (un formulario), con este aspecto:

No penséis que hay una etiqueta (label) supergrande. En realidad hay varias etiquetas, lo que pasa es que están ligeramente superpuestas, de ahí que parezca que solo hay una.

Para lanzar el UserForm, simplemente tendremos que añadir estas líneas a un módulo:

Sub lanzar_userform()
'lanzamos el userform
UserForm1.Show
End Sub

Y este sería el código para que cuando se cargue el formulario, nos llene los tres ComboBox, el de las horas, el de los minutos, y el de los segundos:

Private Sub UserForm_Initialize()
'Si hay errores, que continúe
On Error Resume Next
'Al cargar el Userform, mostramos
'el listado de horas, minutos y segundos,
'para ello le pondremos un tope de 1000
'horas en el combobox, aunque se puede cambiar
For i = 0 To 1000
'Añadimos las horas
ComboBox1.AddItem i
If i <= 59 Then
'Añadimos los minutos
ComboBox2.AddItem i
'Añadimos los segundos
ComboBox3.AddItem i
End If
Next
End Sub

Este otro será el código que se ejecutará, al cliquear sobre el botón que hay en el formulario, y que tiene como etiqueta el nombre "Calcular":

Private Sub CommandButton1_Click()
'Si hay errores, que continúe
On Error Resume Next
'Hacemos los cálculos que mostraremos
'en las diferentes etiquetas, para lo cual
'pasamos el item seleccionado, a variables
horas = ComboBox1.List(ComboBox1.ListIndex)
minutos = ComboBox2.List(ComboBox2.ListIndex)
segundos = ComboBox3.List(ComboBox3.ListIndex)
If IsEmpty(horas) Then horas = 0
If IsEmpty(minutos) Then minutos = 0
If IsEmpty(segundos) Then segundos = 0
'mostramos los años
Label5 = Format((horas + (minutos / 60) + _
(segundos / 60 / 60)) / 24 / 365, "#,##0.0000") & " años"
'mostramos los meses
Label6 = Format((horas + (minutos / 60) + _
(segundos / 60 / 60)) / 24 / 30, "#,##0.0000") & " meses"
'mostramos las semanas
Label7 = Format((horas + (minutos / 60) + _
(segundos / 60 / 60)) / 24 / 7, "#,##0.0000") & " semanas"
'mostramos los días
Label8 = Format((horas + (minutos / 60) + _
(segundos / 60 / 60)) / 24, "#,##0.0000") & " días"
'mostramos las horas
Label9 = Format(horas + (minutos / 60) + _
(segundos / 60 / 60), "#,##0.0000") & " horas"
'mostramos los minutos
Label10 = Format((horas * 60) + minutos + segundos / 60, _
"#,##0.0000") & " minutos"
'mostramos los segundos
Label11 = Format((horas * 60 * 60) + (minutos * 60) + _
segundos, "#,##0") & " segundos"
'mostramos un mensaje al pie del formulario
Label12 = "Los datos presentados, están en formato decimal."
End Sub

Como veis, no tiene ninguna complejidad montar un formulario de este tipo, pues solamente tenemos que tener claro las fórmulas para convertir las horas en minutos, en segundos, etc. Aquí os dejo varios pantallazos con el formulario al cargarse, y con un ejemplo, una vez seleccionadas las horas, minutos y segundos, y tras pulsar el botón "Calcular":

Desde aquí descargar el fichero de excel, con el ejemplo que hemos visto en este artículo.

Habilitar y deshabilitar controles de un formulario

2010-03-17T00:01:00.003+01:00

Quien haya tenido la santa paciencia de mirarse con detalle el artículo en el que explicaba como calcular préstamos y cuotas de una hipoteca, se habrá dado cuenta que hay algunas cosillas que no he explicado, pero que a simple vista son fácilmente digeribles. Concretamente me estoy refiriendo al hecho de habilitar o deshabilitar controles (cuadros de texto especialmente), dependiendo como interactúa el usuario con el formulario.

En ese artículo al que hacía referencia, podéis ver que hay un apartado para informar de si el préstamo tiene carencia o no. Si no tiene carencia, no tiene sentido completar la información relacionada con la misma, y que se solicita en dos TextBox, concretamente los que hay debajo del ComboBox donde se pregunta si el préstamo tiene carencia, y que corresponden al tipo de interés durante el periodo de carencia, y al número de cuotas de carencia. Parece evidente que no tiene sentido permitir que el usuario introduzca un tipo de interés para la carencia, y un número de cuotas de carencia, si el préstamo carece de esta última, ¿verdad?.

Pues eso es lo que vamos a explicar de una forma muy sencilla, para que no tengáis que investigar a ciegas a través del código de ese artículo sobre préstamos.

Para explicar como funciona esto, lo haremos con un ejemplo sencillo. Lo primero que haremos será crear un UserForm como el que vemos en la siguiente imagen, y que consta de tres Label, un ComboBox, un TextBox, y un CommandButton).

Como curiosidad, y antes de seguir con el tema, para poder escribir varias líneas en el TextBox, debemos cambiar la propiedad MultiLine a true, tal y como se muestra en la siguiente imagen:

Una vez tengamos diseñado el formulario, solo nos quedará incluir el código asociado a los controles. En nuestro caso necesitaremos los tres códigos siguientes. El primero de ellos, corresponde a lo que tiene que suceder al activarse el formulario:

Private Sub UserForm_Activate()
'Si hay errores, que continúe
On Error Resume Next
'al activarse sl formulario, añadimos
'las opciones del desplegable
ComboBox1.AddItem "SI"
ComboBox1.AddItem "NO"
'bloqueamos por defecto el TextBox2,
'si no se ha seleccionado en el desplegable
TextBox1.Enabled = False
End Sub

Como veis, en el código anterior, estamos añadiendo dos elementos (ítems) al ComboBox, y que son las respuestas que el usuario puede seleccionar en el Combo, un SI y un NO. Además, al activarse el formulario, también se deshabilita el TextBox1.

El siguiente código que necesitaremos será el que corresponde al ComboBox, y que se ejecutará al seleccionar alguno de los ítems del desplegable:

Private Sub ComboBox1_Change()
'Si hay errores, que continúe
On Error Resume Next
'activamos o desactivamos el TextBox1
'dependiendo de la respuesta del desplegable
If ComboBox1.ListIndex = 0 Then
'si se elige SI habilitamos el TextBox1
'(el primer valor es cero), lo activamos
TextBox1.Enabled = True
Else
'en caso contrario, si se elige NO habilitar
'el TexBox1, entonces lo desactivamos
TextBox1 = ""
TextBox1.Enabled = False
End If
End Sub

Como veis, se trata del código asociado al ComboBox, de tal manera que si el usuario elige el primer elemento del ComboBox (el ListIndex = 0, porque comienza a numerarse por 0), entonces se habilita el TextBox1 con esa línea donde pone TextBox1.Enabled = True

En caso contrario, si se selecciona el segundo elemento del desplegable (o el 3º, o el 4, o el n, si lo hubiera), eliminaremos el contenido del TextBox1, y además lo deshabilitaremos.

Por último, el tercer código es simplemente para descargar de memoria el formulario, al pulsar sobre el CommandButton:

Private Sub CommandButton1_Click()
'Descargamos el formulario de memoria
Unload Me
End Sub

Esos tres códigos, como ya sabéis, deben ir incluidos dentro del UserForm. El código que a continuación os muestro es el que lanza el formulario, y que podemos asociar a un botón. Este código debe ir en un módulo:

Sub lanzar_userform()
'Lanzamos el UserForm1
UserForm1.Show
End Sub

Como veis es una muy técnica sencilla, y muy útil, que le dará un aspecto más profesional a nuestros formularios:

Desde aquí podéis descargar el fichero de Excel, con el ejemplo que hemos visto en este artículo.

La hoja de cálculo

2010-03-01T11:25:00.006+01:00

Quizás a estas alturas, y después de haber cumplido dos años publicando en el blog sobre las virtudes la hoja de cálculo Excel, plantearse una pregunta del tipo "¿Qué es una hoja de cálculo?", puede parecer fuera de lugar. Pero no, no creo que lo esté, precisamente porque pocas veces se ha intentado dar respuesta a preguntas tan básicas como esta.

Como son muchos los usuarios que llegan a este blog de Excel, buscando información sobre la hoja de cálculo, vamos a dar unas pinceladas sin entrar en excesivos tecnicismos, intentando explicar para qué sirve una hoja de cálculo, o incluso qué es una hoja de cálculo.

Lo primero que tenemos que decir, es que si utilizas un ordenador hoy en día, ya sea para tu trabajo o incluso para tu ocio, se hace imprescindible el uso de determinados programas, y sin los cuales es muy difícil poder decir que somos usuarios de las herramientas informáticas.

No es difícil encontrarse hoy en día, gente que domina el uso de las redes sociales, los gadgets electrónicos del tipo de los iphones, los smatphones, las pdas, etc., los programas de retoque fotográfico como Photoshop o Paint Shop Pro, la edición de video digital, los juegos online multijugador, y mil cosas más, pero en cambio, no saben hacer un sencillo presupuesto de ingresos y gastos del mes, con el programa más adecuado para ello.

La hoja de cálculo forma parte de ese grupo de programas que no debería faltar nunca en cualquier ordenador, ya sea del ordenador de nuestro trabajo, o del ordenador de nuestra casa. La hoja de cálculo, junto con el procesador de textos, son -o deberían ser- básicos para cualquier usuario de la informática. Si sabes encender un ordenador, pulsando el botón on/off, deberías tener las nociones mínimas para poder escribir una carta con un procesador de textos, o para hacer unas sumas y unas restas, con una hoja de cálculo. Estos dos tipos de herramientas (la hoja de cálculo y el procesador de textos), son básicos en cualquier paquete ofimático (un paquete de programas, o una suite de programas, básico para su uso en oficinas y despachos).

¿Qué es una hoja de cálculo?

Una hoja de cálculo, es un documento informático formado por filas (horizontales) y columnas (verticales), donde cada intersección entre ambas (entre una fila y una columna), recibe el nombre de celda (un grupo de celdas, recibe el nombre de rango). Hacer referencia a cada celda, es tan fácil como jugar al juego de la "guerra de barcos", pues cada celda tiene unas coordenadas. De la misma forma a como jugamos a la guerra de barcos, cuando damos unas coordenadas del tipo A4, o B6, y decimos que estamos tocados, o hundidos, lo mismo podemos hacer, para referirnos a una celda, pues además esta intersección entre columna y fila, nos aparece resaltada. Podemos averiguarlo, haciendo referencia a la columna, que está formada por letras, y a la fila, que está formada por números. De tal forma, por ejemplo, podemos saber que la celda B6 nos está haciendo referencia a la segunda columna (la columna B, pues están por orden alfabético: A, B, C, D, etc...) y a la sexta fila.

Este es el aspecto general de una hoja de cálculo (concretamente una hoja de cálculo de Excel), donde no tenemos ningún dato escrito:

En esta otra pantalla, podemos ver que estamos situados en la celda B6, pues se muestra resaltada tanto la celda, como la referencia a la columna y a la fila que forman esa intersección:

Desplazarnos de una celda a otra, es tan sencillo como colocar el cursor en la celda deseada. Nos situamos con el puntero del ratón en la intersección de la columna y la celda donde queramos situarnos, y pulsamos el botón izquierdo del ratón. Tan sencillo como eso, para situarnos en esa nueva celda o esa nueva ubicación, dentro de la hoja de cálculo.

Las hojas de cálculo están integradas en lo que se denominan libros (libros de cálculo), de tal forma que en cada libro podemos tener desde una hoja de cálculo, hasta tantas como nos admita la aplicación que estamos utilizando. Por ejemplo, si ampliamos la imagen anterior, veremos que en la parte inferior de la misma, aparecen 3 pestañas, y que son cada una de las hojas que conforman el libro. Como mínimo, un libro debe contener una hoja, es decir, una pestaña, y se les puede cambiar el nombre que tienen por defecto (Hoja1, Hoja2, Hoja3, etc.).

¿Para qué sirve una hoja de cálculo?

La hoja de cálculo es una herramienta informática, que nos ahorra muchísimo trabajo diario a todos aquellos que trabajamos habitualmente con números, cifras, y datos. Una hoja de cálculo nos puede servir para elaborar un presupuesto, para hacer facturas, para calcular las cuotas de un préstamo, para hacer un balance y una cuenta de resultados, o para llevar el control de notas de unos alumnos, por poner unos cuantos ejemplos. Todo aquello que sea cuantificable, y valorable económicamente, puede ser tratado, medido, ordenado y controlado, mediante una hoja de cálculo.

Un ejemplo sencillo de la utilidad de la hoja de cálculo, podría ser la elaboración de un presupuesto mensual, como el que podemos ver en la siguiente imagen:

Como podéis ver, se trata de un fragmento de una hoja de cálculo, donde solo he incluido unas pocas columnas y filas, para que lo veáis mejor. Si os fijáis, también podemos ver en esa imagen, que se pueden ampliar las columnas, para hacerlas más anchas, y ajustarlas al contenido que tengamos. También os podéis fijar que le podemos dar color a las celdas, pero lo más importante, y la verdadera utilidad de la hoja de cálculo, viene ahora...¿Qué pasaría si nos subiese la hipoteca o el alquiler de 820 euros mensuales, a 880 euros?. Si esta operación la hiciésemos sobre un papel, si ayuda de un ordenador, deberíamos hacer un par de operaciones. La primera de ellas, sería saber cuánto nos ha subido la hipoteca o el alquiler (60 euros = 880 euros – 820 euros), y segundo, sería restar al saldo neto del mes (el ahorro), esos 60 euros extras de aumento de la hipoteca o alquiler, con lo que nos quedaría un ahorro mensual, de 20 euros en lugar de 80 (20 euros = 80 euros – 60 euros).

Si os fijáis bien, veréis que la columna C está resaltada, y la fila 14 también, con lo que la intersección entre ambas, es decir, la celda C14, está remarcada, para indicarnos que estamos situados sobre ella. Si os fijáis, precisamente arriba a la izquierda de esa imagen anterior, se ve precisamente la celda donde estamos (C14), y a la derecha, se nos muestra el contenido de esa celda. En nuestro caso pone =C5-C6. Sin saber demasiado de fórmulas, ya nos estamos haciendo una idea de lo que hay en C14, y no es otra cosa que una resta entre lo que hay en C5 y lo que hay en C6, que son los ingresos y los gastos mensuales.

Una hoja de cálculo nos va a servir para cambiar el dato de una celda cualquiera, y que se nos recalcule todo de forma automática, sin hacer absolutamente nada. Lo primero que tenemos que saber, y siguiendo con el ejemplo anterior, es que en la celda C6 tenemos una suma de todos los gastos que hay justo debajo. Si cambiamos una de esas cifras (por ejemplo el de la hipoteca o alquiler, que aparece en la celda C7), y en lugar de tener un gasto de 820 euros, tenemos un gasto mensual de 880 euros, ocurrirán dos cosas, la primera de ellas es que se recalculará la suma de gastos, y ahora tendremos 60 euros más (1.480 euros de gasto total), y segundo, que el ahorro se reducirá en esos 60 euros de aumento de hipoteca o alquiler, pasando de 80 euros de saldo neto, a solo 20 euros. Miradlo en esa imagen que os adjunto:

Como podéis ver, el ahorro de tiempo es considerable, ya que con tan solo cambiar un dato, la suma de gastos y el saldo neto se recalculan automáticamente, pues tiene fórmulas (sumas y restas) que evalúan esos nuevos datos que hemos cambiado, para mostrar el nuevo resultado. Por tanto, la utilidad de la hoja de cálculo es más que evidente, tanto más, cuanto más complejos sean los cálculos y las operaciones, con el consiguiente ahorro de tiempo que ello comporta.

Este es un sencillo ejemplo de la utilidad de las hojas de cálculo, pero podemos hacer de todo lo que se nos ocurra, y que conlleve el tratamiento de datos numéricos.

¿Qué es Excel?

Pues Excel es un programa con el que podemos hacer hojas de cálculo. Igual que con Photoshop podemos retocar imágenes, y con Internet Explorer podemos navegar por Internet, con Excel podemos hacer hojas de cálculo. De la misma forma que Photohop no es el único programa para retoque de fotos o imágenes, ni Internet Explorer es el único navegador para acceder a Internet, Excel tampoco es el único programa para hacer hojas de cálculo.

Excel es un producto del fabricante Microsoft (el mismo que hace Windows XP, Windows Vista, o Windows 7, por ejemplo), y se suministra dentro del paquete Microsoft Office, en el que incluye también, entre otras aplicaciones Microsoft Word, que es un programa para tratamiento de textos (un programa para hacer escritos, cartas, informes, etc.).

Microsoft Office, y por tanto el programa Excel que incluye, es un programa de pago, pero aún así, se trata del software para elaborar hojas de cálculo más extendido del mercado, y toda una referencia para su competencia, pues en mi opinión, es la mejor herramienta de este tipo que podemos encontrar en el mercado.

¿Qué otros programas hay para hacer hojas de cálculo, aparte de Excel?

Pues aparte de Excel, podemos recurrir a otros interesantes programas para el tratamiento de hojas de cálculo. Los más extendidos, aparte de Excel, son los siguientes:

Lotus 123, propiedad de Lotus Development Corporation, adquirida por IBM. Fue una de las aplicaciones para hoja de cálculo, más famosas y extendidas en la década de los años 80, pero Excel acabaría con su hegemonía en la década siguiente:

Gnumeric, desarrollado para Gnome, dentro de lo que se denomina software libre, aunque también existe una versión para Windows:

Kspread, desarrollado también como software libre, para el proyecto KDE, e incorporado dentro de la suite ofimática KOffice:

Quatro Pro, desarrollado originalmente por Borland, ahora propiedad de Corel Corporation. Esta aplicación se suministra dentro del paquete Corel WordPerfect Office. La principal funcionalidad de esta aplicación es que ya desde su versión 9 (allá por 1999), permitía trabajar con hojas de cálculo de hasta 1.000.000 de filas, mientras que Excel tenía limitado el número de filas en 65.536 filas, hasta su versión Excel 2007:

Calc, del paquete StarOffice, de Sun Microsystems, y base de la suite ofimática de código libre OpenOffice:

Calc, que forma parte del paquete OpenOffice, software de código libre. Se trata del único software para hojas de cálculo que actualmente puede plantarle algo de cara a Excel (más que por la potencia del mismo, por lo extendido que está entre los usuarios), aunque en lo personal, creo que todavía le queda mucho para poder hacerle sombra a la todopoderosa hoja de cálculo de Microsoft:

Aparte de esas aplicaciones para hoja de cálculo que hemos mencionado, y que son las más destacables, en el mercado podemos encontrar infinidad de programas, que nos van a ser igualmente útiles, incluyendo el software para hojas de cálculo online como el que integra Google Docs.

Macros

Cuando en el uso avanzado de hojas de cálculo, nos vemos ante una tarea repetitiva o compleja, donde las funciones propias que incorpora el programa en sí, no son suficientes para resolver el problema o la tarea, es cuando entra en escena la programación mediante macros. A través de los macros, podemos realizar tareas repetitivas o ciertamente complejas, añadiendo líneas de programación. No todas las aplicaciones para hojas de cálculo admiten esta programación mediante macros, pero Excel concretamente sí, y de una forma ciertamente potente.

Algunos ejemplos del uso de macros en Excel, los tenéis en este mismo blog de Excel, por ejemplo con esta aplicación para calcular las cuotas de una hipoteca, o esta otra para introducir datos en una hoja de cálculo, utilizando formularios.

Las macros pueden ser muy sencillas o extremadamente complejas, dependiendo de nuestras necesidades, y del problema que pretendamos resolver. Si el primero de los macros de ejemplo del párrafo anterior tiene cierta complejidad, este otro en el que podemos contar las hojas de un libro, sería sumamente sencillo.

Como veis, la hoja de cálculo es una herramienta imprescindible en cualquier ordenador de hoy en día, se llame Excel, Calc, Quattro Pro, o incluso la hoja de cálculo que Google a través de Google Docs, pone a disposición de todos los usuarios, de forma gratuita.

Calcular la TAE

2010-02-23T01:00:00.009+01:00

La TAE es la tasa anual equivalente, o también llamada, tasa anual efectiva. Lo que realmente nos interesa, es saber que significa eso, y como se calcula.

Antes de entrar en materia, comentaros que os dejo una mini aplicación que os permitirá calcular la TAE de una operación financiera, con intereses pagaderos al vencimiento. Tan solo tendréis que informar el tipo de interés nominal, y el plazo de pago de los intereses:

En un artículo anterior ya hablamos sobre el cálculo de la TIR con Solver, así que gran parte de lo que aquí explicaremos, lo podéis ampliar con la información contenida allí, aunque en principio no será necesario. También podéis ver el uso de la TAE en una operación financiera como es el caso de un contrato de préstamo, pues en el artículo donde colgué la plantilla para el cálculo de préstamos, está incorporada esta función.

Vamos a explicar qué es la TAE con un sencillo ejemplo. Imaginemos que disponemos de 5.000 euros, y queremos invertirlos en un depósito a plazo fijo a un año, pero tenemos dos opciones:

El Banco A nos ofrece un tipo de interés nominal del 7% anual, pagadero al vencimiento.

El Banco B nos ofrece un tipo de interés nominal del 6,95% anual, pagadero mensualmente.

Así a bote pronto, podemos pensar que la opción que nos plantea el Banco A, es más interesante, porque nos da 350 euros al cabo de ese año (5.000 x 0,07), mientras que el Banco B nos da solamente 347,50 euros (5.000 x 0,0695). Es decir, el Banco A nos da 2,50 euros más de intereses al año (350 – 347,50).

Desde un punto de vista financiero, nuestro objetivo es siempre maximizar el beneficio procedente de una inversión, por lo que hay que analizar la rentabilidad efectiva de ambas operaciones que nos plantean los bancos, antes de decidirnos. Vamos a ver como analizar la rentabilidad efectiva, es decir, obtener la TAE de ambas operaciones.

Si el Banco A nos paga los intereses al vencimiento, lo que está claro, es que solo podremos disponer de ellos a la finalización del contrato que hemos firmado con el banco, es decir, al vencimiento del depósito (al cabo de ese año). El Banco B en cambio, nos paga cada mes los intereses, por lo que podemos disponer de ellos mensualmente. Esto implica que podemos retirar esas cantidades (los intereses mensuales) para gastarlos en lo que queramos, o mejor aún, para invertirlos nuevamente. Es aquí donde entra en juego el concepto de la TAE, pues el propio concepto de la TAE para lleva implícito que vamos a reinvertir esos intereses.

La opción del Banco A es muy clara, pues al cabo de un año, podremos retirar el capital inicialmente invertido (5.000 euros), más los intereses (350 euros). Es decir, al cabo de 1 año, dispondremos de 5.350 euros.

En cambio la opción del Banco B tenemos que analizarla con más detenimiento, pues nos pagan intereses cada mes. Para saber que intereses mensuales obtendremos, tenemos dos opciones:

Podemos coger el tipo de interés nominal anual, dividirlo entre los 12 meses, y aplicar ese tipo de interés a los 5.000 euros: 0,0695 / 12 x 5.000 = 28,9583 euros de intereses mensuales.

O también podemos calcularlo, aplicando a los 5.000 euros, el tipo de interés que nos da el Banco B, y dividiendo esos intereses totales, por 12, porque los cobraremos mensualmente: 5.000 x 0,0695 / 12 = 28,9583 euros mensuales.

Como habéis visto, ambas opciones para calcular los intereses mensuales son idénticas, porque matemáticamente son lo mismo.

Pero... ¿qué pasaría si esos intereses de 28,9583 que cobraríamos también al cabo de un mes, y los invirtiéramos al mismo tipo de interés del 6,95%, durante los 11 meses que nos quedan hasta finalizar el contrato del depósito a plazo fijo?. Esta pregunta es básica, y es la que tendríamos que hacernos siempre, para comprender el auténtico significado de la TAE.

Vamos a ver en una tabla de Excel, que pasaría, si los intereses que cobramos mensualmente con la opción del Banco B, los invertimos por el tiempo que reste hasta finalizar ese año, en el que recuperaríamos la inversión inicial de los 5.000 euros.

La columna bajo el título de "Capital invertido" incluye los 5.000 euros invertidos inicialmente, más los intereses que nos van pagando cada mes, y que también se reinvierten por el periodo que resta hasta finalizar la imposición. Por ejemplo, la recuperación del capital, más los intereses que nos pagan el primer mes, los podemos reinvertir durante 11 meses. La recuperación del capital, más los intereses que nos pagan al cabo del segundo mes, los podemos reinvertir durante 10 meses, y así sucesivamente, hasta que al final, cuando cobramos los intereses del mes nº 12, ya no podemos reinvertir nada nuevamente, porque ha finalizado el plazo de tiempo por el que abrimos el depósito a plazo fijo.

También podemos ilustrarlo con este otro ejemplo, donde más claramente se puede observar como reinvertimos los intereses:

Como veis, hemos conseguido 347,50 euros de intereses con la opción del Banco B, y reinvirtiéndolos, nos darían 11,29 euros adicionales, es decir, obtendríamos un total de 358,79 euros, si optimizásemos la inversión (reinvirtiendo los intereses). También hemos calculado la TAE de la operación, que no es más que dividir los intereses totales que obtendríamos (358,79 euros), entre el capital invertido (5.000 euros), porque es a un año, lo que nos da una TAE del 7,1757%.

Llegados a este punto, ya estamos en condiciones de saber que inversión es más rentable desde un punto de vista financiero (optimizando la inversión). El Banco A nos da unos intereses de 350 euros al año, mientras que con la opción del Banco B podemos obtener 358,79 euros.

En el caso del Banco A, el tipo de interés nominal del 7%, es también la TAE, porque no podemos reinvertir los intereses, ya que los obtenemos al final, cuando finaliza el depósito a plazo fijo. En el caso del Banco B, el tipo de interés nominal es del 6,95%, pero la TAE es del 7,1757%, lo cual desde un punto de vista financiero, nos deja las cosas muy claras: el Banco B es la mejor opción.

Como habéis visto, aunque aparentemente el Banco A inicialmente era la mejor opción, hemos demostrado que no es así, y que el Banco B nos ofrecía desde un punto de vista financiero, la mejor opción para maximizar nuestros beneficios (nuestros intereses).

Para calcular la TAE de una operación financiera, os dejo una hoja de cálculo para descargar, que os simplificará mucho la tarea, pues tan solo tenéis que introducir el tipo de interés nominal anual, y los periodos de pago al año (pagos semestrales, pagos trimestrales, pagos mensuales, etc.). Es la plantilla de Excel cuyas imágenes podéis ver al principio de este artículo.

Desde aquí podéis descargar los dos ficheros de Excel, comprimidos en formato zip, con los ejemplos que hemos visto en este artículo. En uno de ellos tenéis la tabla con el estudio de rentabilidad de la opción ofrecida por el Banco B (la tabla de la última imagen de este artículo), y en el otro fichero Excel tenéis la calculadora de la TAE.

Calcular la TIR de una inversión, para periodos irregulares

2010-02-08T11:09:00.006+01:00

Cuando calculamos la TIR de una inversión, lo normal es que los flujos de caja estén todos referidos a unos mismos periodos de tiempo, es decir, que todos los momentos donde se producen los flujos guardan siempre la misma periodicidad. Por ejemplo, cuando calculamos la TIR de una inversión, lo normal es que los flujos sean siempre iguales, y si se trata de años, los flujos serán anuales, si se trata de meses, los flujos serán, y así sucesivamente.

En el ejemplo siguiente podéis ver que hay una serie de periodos, y una serie de flujos. El primer periodo, corresponde al momento cero, es decir, al momento actual, o momento presente, y corresponde al desembolso inicial (como es la inversión inicial, es decir, un pago, debemos ponerlo en negativo, para distinguirlo de los cobros periódicos que recibiremos). Podemos ver que no nos interesan los periodos de tiempo (no sabemos a la vista de ese pantallazo, si se trata de meses, años, semestres, o cualquier otro periodo), porque se da por hecho que son todos iguales. En la fórmula que podéis ver en el pantallazo siguiente, podéis observar que hemos puesto un 5% como tipo de interés estimado, a partir del cual Excel obtendrá la TIR correcta, aunque si se omite, Excel parte de un tipo de interés del 10%, como interés inicial a partir del cual hará las estimaciones. La TIR obtenida es del 6,7075%, pero no decimos si se trata de una TIR anual, semestral, mensual, etc. ¿Por qué no lo decimos?. Pues porque esa TIR estará expresada en función del periodo. Es decir, si esos periodos 0, 1, 2, 3, 4, 5, y 6, son meses, es decir, pagos y cobros mensuales, la TIR será mensual, y si se trata de años (como es lo más habitual), pues la TIR será anual:

Suponiendo que estamos hablando de la TIR anual, la imagen anterior, podemos hacerla más legible, de la siguiente forma, donde hemos añadido las fechas. Aparte, podéis observar que en la fórmula de la TIR, hemos omitido el tipo de interés inicial a partir del cual Excel hará la estimación de la TIR:

Todo esto, junto con el cálculo del valor actual neto, ya lo habíamos explicado en un artículo donde precisamente hablábamos de cómo calcular la TIR y el VAN. También habíamos visto como calcular la TIR con Solver (una potente herramienta de Excel, para optimización).

Ahora la cuestión es la siguiente: ¿Qué pasaría si en lugar de tener los periodos uniformes, se tratase de periodos con diferente periodicidad?. Vamos a suponer que en lugar de recibir esos flujos de cada el día 10 de cada año (ver imagen anterior), se reciben en las siguientes fechas:

¿Sería correcto utilizar la función TIR, para evaluar la tasa interna de rentabilidad de esta inversión, teniendo en cuenta que los periodos no son homogéneos?. Efectivamente, no. Para ello, deberemos utilizar la función TIR.NO.PER, de la siguiente forma:

=TIR.NO.PER(flujos de caja; fechas; tipo de interés estimado)

Como ocurre con la función TIR, podemos omitir el último argumento, donde se nos solitita el tipo de interés a partir del cual Excel hará las estimaciones. Si lo omitimos, Excel presupone que es el 10%.

En nuestro ejemplo anterior, donde hay periodos que no guardan la misma periodicidad, deberemos introducir esta fórmula para calcular la TIR:

=TIR.NO.PER(D6:D12;C6:C12;5%)

El resultado sería que nuestra TIR es de 10,8779%, como podéis ver en la siguiente imagen:

Si os fijáis en los dos ejemplos que ilustran este artículo, veréis que los flujos son los mismos (idénticas cantidades, tanto en pagos como en cobros), pero no son los mismos los periodos donde se producen. Es por ello, que utilizar correctamente las funciones para calcular la TIR, es básico para hacer correctamente las cosas. Solo hay que controlar si los periodos son uniformes o no, para elegir la función TIR o la función TIR.NO.PER.

Préstamos y cálculo de hipotecas

2010-01-19T00:05:00.024+01:00

Antes de entrar en materia, anticiparos que este artículo que estáis comenzando a leer, ocupa ni más ni menos que quince páginas en DIN A-4 (este primer párrafo lo he redactado, una vez tenía escrito todo lo demás), así que espero que tengáis paciencia, tiempo, y un poco de voluntad.

Haré una mínima introducción, para comentaros que ha pasado algo más de un mes desde la última entrada que publiqué en el blog de Excel, y ya era hora de ofreceros a todos los usuarios que seguís fielmente estos artículos, una nueva entrega. En esta ocasión, tocaremos un tema de carácter económico y financiero, que no solo va a serle útil a quien se dedique a estos temas, sino que va a serle útil a todo el mundo. ¿Quién no tiene una hipoteca hoy en día?. ¿Quien no paga un préstamo bancario?. ¿Quién no tiene una deuda porque ha comprado algo a plazos?. Casi todos nos encontramos o nos podemos encontrar en cualquier momento de nuestra vida, en una situación así, ¿verdad?. Pues para todos vosotros, está especialmente indicado este artículo.

A aquellos usuarios a los que no les interesen los macros, y quieran descargarse el libro de Excel para calcular préstamos, e hipotecas, o simplemente quieran hacer simulaciones de préstamos (esta aplicación que os presento, también es un simulador de préstamos, o lo que es lo mismo, una calculadora de préstamos avanzada), pueden saltarse todo lo que explicaré a continuación, e ir directamente al final del artículo, donde encontrarán un enlace para descargar el simulador de préstamos, es decir, el fichero de Excel, con todo lo que veremos aquí. Y a aquellos usuarios que copian y pegan los artículos de este blog, en sus webs o blogs, sin mencionar la fuente, recordarles que la fuente original es http://hojas-de-calculo-en-excel.blogspot.com

Antes de seguir, quiero comentaros que la metodología que se utiliza para el cálculo de préstamos, sigue el método francés. Los que no sepan que es esto del método francés, simplemente daré un par de pinceladas. El cálculo de préstamos según el método francés, se caracteriza por lo siguiente:

Los intereses se devengan al vencimiento de cada cuota.

El capital que se amortiza va creciendo en cada cuota, es decir, el principal del préstamo que se va pagando, es cada vez más alto, a medida que va transcurriendo el tiempo, y a medida que vamos liquidando las cuotas.

Los intereses por el contrario, van disminuyendo y son menores en cada cuota.

Las cuotas totales que se pagan, son todas del mismo importe. El capital que se va pagando aumenta, y los intereses disminuyen, pero las cuotas son siempre iguales.

Es importante reseñar que no todas las operaciones financieras se rigen por el método francés, como por ejemplo las operaciones de arrendamiento financiero o leasing, que siguen otra metodología distinta, pero a pesar de eso, también es importante indicar que el método francés es el más extendido para el cálculo de la mayoría de operaciones de financiación.

Ahora sí, vamos a entrar en materia. Para calcular préstamos con esta aplicación en Excel, utilizaremos un formulario para la entrada de datos. Antes de eso, crearemos otro formulario donde informaremos de las características del préstamo francés.

Los dos formularios que utilizaremos serán estos:

Aquí os dejo un pantallazo, con un ejemplo de lo que obtendremos con esta aplicación en Excel.

Entrando ya en los macros, veréis que tenemos cuatro. Uno para acceder al menú principal (desde la hoja donde calcularemos el préstamo), otro macro para imprimir, otro macro para hacer una presentación preliminar (como si utilizáramos la lupa), y otro para cargar el formulario con información sobre el préstamo francés (el formulario que vemos en la primera de las imágenes anteriores).

Vamos a ver el código de los cuatro macros, y que tendremos que copiar en un módulo:

Sub menu_principal()
'Si hay errores que continúe
On Error Resume Next
'ocultamos el procedimiento
Application.ScreenUpdating = False
'desprotegemos la hoja
ActiveSheet.Unprotect
'eliminamos desde la fila 6 hasta el máximo
'que podemos tener, y que ocupa hasta la
'fila número 3021
Rows("6:3021").Select
Selection.Delete
'ponemos el ancho estandar de 12,14 en la columna E
Columns("E:E").Select
Selection.ColumnWidth = 12.14
'ponemos el ancho estandar de 11 en
'las columnas desde la F a la J
Columns("F:J").Select
Selection.ColumnWidth = 11
'nos situamos en la celda B2
Range("B2").Select
'protegemos la hoja
ActiveSheet.Protect
'vamos a la primera hoja
Hoja1.Select
Range("B10").Select
'mostramos el procedimiento
Application.ScreenUpdating = True
End Sub

Sub imprimir()
'Si hay errores que continúe
On Error Resume Next
'imprimimos la hoja activa
ActiveWindow.SelectedSheets.PrintOut Copies:=1
End Sub

Sub presentacion_preliminar()
'Si hay errores que continúe
On Error Resume Next
'presentación preliminar de la hoja activa
ActiveWindow.SelectedSheets.PrintPreview
End Sub

Sub prestamo_frances()
'Lanzamos el formulario con info sobre
'el préstamo según el método francés
InfoPrestamoFrances.Show
End Sub

Ahora dentro del formulario con la información sobre el cálculo de préstamos mediante el método francés, colocaremos los siguientes códigos, uno para cuando cliqueemos en el botón "Si", y otro para cuando cliqueemos en el botón "No" (así precisamente se llaman los CommandButton):

Private Sub Si_Click()
'Si hay errores, que continúe
On Error Resume Next
'descargamos el formulario de memoria
Unload Me
'llamamos al formulario del préstamo francés
'para rellenar los datos
PrestamoFrances.Show
End Sub

Private Sub No_Click()
'Si hay errores, que continúe
On Error Resume Next
'descargamos el formulario de memoria
Unload Me
End Sub

A los TextBox y botones del segundo formulario, es decir, del formulario donde rellenaremos los datos del préstamo, les he puesto nombres bien descriptivos. En lugar de llamarlos TextBox1, TextBox2, TextBox3, etc., los he llamado Principal, InteresPrestamo, CuotasAmortizacion, etc., pues así nos será más sencillo saber de qué estamos hablando, cuando leamos el código fuente del formulario.

Este es el segundo formulario que veremos, cuando cliqueemos en el botón "Si", del formulario anterior:

Y todo que viene a continuación, esto será el código que nos encontraremos dentro del formulario (aparte de una pequeña reseña informando que el código es de libre distribución, que está prohibida su venta y su explotación con fines comerciales, y que ha sido obtenido del blog http://hojas-de-calculo-en-excel.blogspot.com). No hace falta que comente para que sirve cada cosa, porque está todo debidamente comentado, y los procedimientos son muy claros. Comenzaremos con el código que nos permitirá controlar los datos introducidos en el formulario:

Private Sub UserForm_Activate()
'Si hay errores, que continúe
On Error Resume Next
'al activarse sl formulario, añadimos
'las opciones del desplegable relativos
'a la carencia del préstamo (SI/NO)
Carencia.AddItem "SI"
Carencia.AddItem "NO"
'bloqueamos por defecto, las opciones de la
'carencia (interés y cuotas), para que no se
'pueda escribir, si no se ha seleccionado en
'el desplegable de carencia (SI/NO)
InteresCarencia.Enabled = False
CuotasCarencia.Enabled = False
End Sub

Private Sub Carencia_Change()
'Si hay errores, que continúe
On Error Resume Next
'activamos o desactivamos los TextBox
'relacionados con la carencia del préstamo
If Carencia.ListIndex = 0 Then
'si se elige Carencia=SI (el primer valor es cero),
'activamos los restantes TextBox
InteresCarencia.Enabled = True
CuotasCarencia.Enabled = True
Else
'en caso contrario, si se elige Carencia=NO,
'desactivamos los restantes TextBox
InteresCarencia = ""
CuotasCarencia = ""
InteresCarencia.Enabled = False
CuotasCarencia.Enabled = False
End If
End Sub

Private Sub Principal_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
'Si hay errores, que continúe
On Error Resume Next
'si el valor introducido en el principal
'del préstamo es numérico...
If IsNumeric(Principal) Then
'y además de ser numérico es menor
'o igual que cero...
If Principal <= 0 Then
'eliminamos el dato introducido
Principal = Empty
Else
'en caso contrario, que le de formato con
'separador de miles y dos decimales
Principal = Format(Principal, "#,##0.00")
End If
'si no es numérico...
Else
'eliminamos el dato introducido
Principal = Empty
End If
End Sub

Private Sub InteresPrestamo_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
'Si hay errores, que continúe
On Error Resume Next
'si el valor introducido en el interés
'del préstamo es numérico...
If IsNumeric(InteresPrestamo) Then
'y además de ser numérico es menor o igual
'que 100, y mayor que cero...
If InteresPrestamo <= 100 And InteresPrestamo > 0 Then
'que divida el valor entre 100 (para que sea %), y
'que le de formato decimal y con cuatro decimales
InteresPrestamo = Format(InteresPrestamo / 100, "##0.0000%")
Else
'en caso contrario, eliminamos
'el dato introducido
InteresPrestamo = Empty
End If
'si no es numérico...
Else
'eliminamos el dato introducido
InteresPrestamo = Empty
End If
End Sub

Private Sub CuotasAmortizacion_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
'Si hay errores, que continúe
On Error Resume Next
'si el valor es numérico...
If IsNumeric(CuotasAmortizacion) Then
'y además de ser numérico
'es menor o igual que cero...
If CuotasAmortizacion <= 0 Then
'eliminamos la entrada
CuotasAmortizacion = Empty
Else
'en caso contrario, que le de formato con
'separador de miles, siempre y cuando
'sea menor que 1500
If CuotasAmortizacion <= 1500 Then
'si es menor o igual que 1500, le
'damos el formato con separador de miles
CuotasAmortizacion = Format(CuotasAmortizacion, "#,##0")
Else
'si es mayor que 1500, eliminamos
'el dato introducido
CuotasAmortizacion = Empty
End If
End If
'si no es numérico...
Else
'eliminamos el dato introducido
CuotasAmortizacion = Empty
End If
End Sub

Private Sub CuotasAnio_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
'Si hay errores, que continúe
On Error Resume Next
'si el valor no es numérico, o es menor
'o igual que cero, o mayor que 52...
If Not IsNumeric(CuotasAnio) Or CuotasAnio <= 0 Or CuotasAnio > 52 Then
'eliminamos el dato introducido
CuotasAnio = Empty
End If
End Sub

Private Sub Fecha_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
'Si hay errores, que continúe
On Error Resume Next
'si el valor no es una fecha...
If Not IsDate(Fecha) Then
'eliminamos el dato introducido
Fecha = Empty
'si es una fecha, que le de formato de fecha
Else
Fecha = Format(Fecha, "dd-mm-yyyy")
'si la fecha es menor que el 01-01-1900, o mayor
'que el 31-12-3000, borramos el dato introducido
'(hay que ponerlo con formato mes-día-año)
If Fecha < #1/1/1900# Or Fecha > #12/31/3000# Then
'eliminamos el dato introducido
Fecha = Empty
End If
End If
End Sub

Private Sub InteresCarencia_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
'Si hay errores, que continúe
On Error Resume Next
'si el valor es numérico...
If IsNumeric(InteresCarencia) Then
'y además de ser numérico es menor o
'igual que 100, y mayor que cero...
If InteresCarencia <= 100 And InteresCarencia > 0 Then
'que divida el valor entre 100, y
'que le de formato con cuatro decimales
InteresCarencia = Format(InteresCarencia / 100, "##0.0000%")
Else
'en caso contrario, eliminamos
'el dato introducido
InteresCarencia = Empty
End If
'si no es numérico
Else
'eliminamos el dato introducido
InteresCarencia = Empty
End If
End Sub

Private Sub CuotasCarencia_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
'Si hay errores, que continúe
On Error Resume Next
'si el valor no es numérico, o es menor
'o igual que cero, o mayor que 1500...
If Not IsNumeric(CuotasCarencia) Or CuotasCarencia <= 0 Or CuotasCarencia > 1500 Then
'eliminamos el dato introducido
CuotasCarencia = Empty
Else
'si es menor o igual que 1500, le damos formato
CuotasCarencia = Format(CuotasCarencia, "#,##0")
End If
End Sub

Sub QueEsLaCarencia_Click()
'Si hay errores, que continúe
On Error Resume Next
'mostramos un mensaje, informando
'de lo que es la carencia
MsgBox (Chr(13) & " La carencia es el periodo de tiempo durante " _
& Chr(13) & " el cual no se amortiza nada del principal del " _
& Chr(13) & " préstamo, pero en cambio, sí que se deven- " _
& Chr(13) & " gan y amortizan intereses. " _
& Chr(13) & Chr(13)), vbOKOnly, " ¿Qué es la carencia?"
End Sub

Y ahora el código que se ejecutará cuando cliqueemos en los dos botones del formulario, empezando por el código del botón que nos hará los cálculos, y cuyo código es más extenso, y a continuación con el otro botón cuyo código es muy sencillo, y que nos permite cerrar el formulario:

Private Sub Calcular_Click()
'Si hay errores, que continúe
On Error Resume Next
'si hay algún campo vacío, o si se ha seleccionado SI
'en la Carencia, pero faltan el interes y/o las cuotas
'de carencia, que muestre un mensaje
If Principal = Empty Or InteresPrestamo = Empty Or CuotasAmortizacion = Empty Or _
CuotasAnio = Empty Or Fecha = Empty Or Carencia.ListIndex = -1 Or _
(Carencia.ListIndex = 0 And (InteresCarencia = Empty Or CuotasCarencia = Empty)) Then
'mostramos el mensaje
MsgBox (Chr(13) & " Por favor, revisa el formulario. " _
& Chr(13) & Chr(13) & " Debes completar los datos necesarios, para " _
& Chr(13) & " poder llevar a cabo el análisis del préstamo. " _
& Chr(13) & Chr(13)), vbOKOnly, " Datos incompletos"
'en caso contrario, si todos los datos están completos...
Else
'informamos que estamos efectuando
'los cálculos, en el label llamado "Informacion"
Informacion = "Calculando..."
DoEvents
'ocultamos el proceso
Application.ScreenUpdating = False
'seleccionamos la Hoja2 (hoja del préstamo francés)
Hoja2.Select
'desprotegemos la hoja
ActiveSheet.Unprotect
'eliminamos desde la fila 6 hasta el máximo
'que podemos tener, y que ocupa hasta la
'fila número 3021, por si acaso no hemos
'vuelto al menú principal usando los botones
Rows("6:3021").Select
Selection.Delete
'escribimos en las celdas, lo que nos
'interesa, en negrita, y de color granate
Range("B6").Select
ActiveCell = "CÁLCULO DE PRÉSTAMOS (método francés)"
Selection.Font.Bold = True
Selection.Font.ColorIndex = 9
'ponemos una doble línea
Range("B6:F6").Select
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlDouble
End With
'escribimos los títulos del cuadro
'resumen que colocaremos en la parte
'superior de la página
Range("B7") = "Principal del préstamo:"
Range("F7") = Principal
Range("B8") = "Tipo de interés durante la amortización del préstamo:"
Range("F8") = InteresPrestamo
Range("B9") = "Número de cuotas de amortización:"
Range("F9") = CuotasAmortizacion
Range("B10") = "Número de cuotas de amortización, al año:"
Range("F10") = CuotasAnio
Range("B11") = "Número de años hasta la amortización del préstamo:"
Range("F11") = Format(CuotasAmortizacion / CuotasAnio, "#,##0.00")
Range("B12") = "Fecha del primer pago:"
Range("F12") = Fecha
'ponemos la TAE de la amortización,
'alineando el dato a la derecha, pero antes
'miraremos si hay carencia o no, para elegir
'donde escribimos el dato de la TAE.
If Carencia.ListIndex = 0 Then
Range("J14").Select
Else
Range("J15").Select
End If
With Selection
.HorizontalAlignment = xlRight
End With
'ponemos la TAE de la operación
TaePrestamo = (((1 + (CDec(Replace(InteresPrestamo, "%", "") / 100) / CuotasAnio)) ^ CuotasAnio) - 1) * 100
ActiveCell = "TAE: " & Format(TaePrestamo / 100, "##0.0000%")
'seguimos escribiendo, dependiendo de si
'tenemos o no carencia en el préstamo
If Carencia.ListIndex = 0 Then
Range("B13") = "Tipo de interés durante la carencia:"
Range("F13") = InteresCarencia
Range("B14") = "Número de cuotas de carencia:"
Range("F14") = CuotasCarencia
Range("B15") = "Número de años de carencia:"
Range("F15") = Format(CuotasCarencia / CuotasAnio, "#,##0.00")
'ponemos la TAE de la carencia
TaeCarencia = (((1 + (CDec(Replace(InteresCarencia, "%", "") / 100) / CuotasAnio)) ^ CuotasAnio) - 1) * 100
Range("J15") = "TAE carencia: " & Format(TaeCarencia / 100, "##0.0000%")
With Selection
.HorizontalAlignment = xlRight
End With
End If
'ponemos una doble línea,
'dependiendo de si hay carencia o no
If Range("B13") = Empty Then
'si no hay carencia, ponemos la doble línea
'debajo de la fila 12
Range("B12:F12").Select
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlDouble
End With
Else
'si hay carencia, ponemos la doble línea
'debajo de la fila 15
Range("B15:F15").Select
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlDouble
End With
End If
'alineamos los datos numéricos a la derecha
Range("F7:F15").Select
With Selection
.HorizontalAlignment = xlRight
End With
'seguimos escribiendo los encabezados de la tabla
Range("B17") = "Cuota nº"
Range("C17") = "Concepto"
Range("D17") = "Fecha"
Range("E17") = "Capital vivo antes del pago de la cuota"
Range("F17") = "Capital amortizado"
Range("G17") = "Intereses a pagar"
Range("H17") = "Capital amortizado acumulado"
Range("I17") = "Intereses acumulados"
Range("J17") = "Cuota total"
'alineamos los textos básicos a la izquierda (puesto que se
'centran por defecto) al estar toda la columna centrada
Range("B6:B15").Select
With Selection
.HorizontalAlignment = xlGeneral
End With
'alineamos los encabezados, vertical y horizontalmente,
'los ajustamos a su celda, y los ponemos en negrita
Range("B17:J17").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.Font.Bold = True
End With
'ponemos valores y fórmulas, empezando
'por numerar las cuotas del préstamo
Range("B18").Select
'si no hay carencia (si está vacía), ponemos
'que el nº de cuotas de carencia es cero
If CuotasCarencia = "" Then CuotasCarencia = 0
'le quitaremos el separador de miles al nº de
'cuotas de carencia y de amortización del préstamo,
'pues en los textbox aparecen con el separador.
'Como no en todos los países se usa el punto, sino que
'se utiliza la coma, tendremos en cuenta esta circunstancia
CuotasCarencia = Replace(CuotasCarencia, ",", "")
CuotasCarencia = Replace(CuotasCarencia, ".", "")
CuotasAmortizacion = Replace(CuotasAmortizacion, ",", "")
CuotasAmortizacion = Replace(CuotasAmortizacion, ".", "")
'pasamos el nº total de cuotas de amortización
'y de carencia a una variable
CuotasTotales = CInt(CuotasAmortizacion) + CInt(CuotasCarencia)
'ponemos el nº de las cuotas de amortización y de
'carencia, siempre que CuotasAmortizacion + CuotasCarencia
'sea mayor o igual que 1
If CuotasTotales >= 1 Then
For i = 1 To CuotasTotales
'ponemos el nº de la cuota
ActiveCell = i
'bajamos a la fila siguiente
'y seguimos con el bucle
ActiveCell.Offset(1, 0).Select
Next
End If
'seguimos poniendo los conceptos
Range("C18").Select
'si no hay carencia...
If CuotasCarencia = 0 Then
'ponemos como concepto "Amortización"
'y debajo, comillas dobles
ActiveCell = "Amortización"
For i = 1 To CInt(CuotasAmortizacion) - 1
'ponemos el nº de la cuota
ActiveCell.Offset(1, 0) = """"
'bajamos a la fila siguiente
'y seguimos con el bucle
ActiveCell.Offset(1, 0).Select
Next
'si hay carencia...
Else
'ponemos como concepto "Carencia"
ActiveCell = "Carencia"
'ponemos comillas dobles, si las cuotas
'de carencia son mayores que 1
For i = 1 To CInt(CuotasCarencia) - 1
'ponemos el nº de la cuota
ActiveCell.Offset(1, 0) = """"
'bajamos a la fila siguiente
'y seguimos con el bucle
ActiveCell.Offset(1, 0).Select
Next
'ponemos debajo como concepto "Amortización"
ActiveCell.Offset(1, 0).Select
ActiveCell = "Amortización"
'ponemos comillas dobles, si las cuotas
'de amortización son mayores que 1
For i = 1 To CInt(CuotasAmortizacion) - 1
'ponemos el nº de la cuota
ActiveCell.Offset(1, 0) = """"
'bajamos a la fila siguiente
'y seguimos con el bucle
ActiveCell.Offset(1, 0).Select
Next
End If
'recuerda que todo esto ha salido del blog
'http://hojas-de-calculo-en-excel.blogspot.com
'seguimos poniendo las fechas
Range("D18").Select
'pasamos los primera fecha a una variable
FechaDelPrimerPago = CDate(Range("F12"))
Range("D18") = FechaDelPrimerPago
'si las CuotasAmortizacion + CuotasCarencia son
'mayores que 1, seguimos poniendo las fechas
If CuotasTotales > 1 Then
'bajamos una fila
ActiveCell.Offset(1, 0).Select
For i = 1 To CInt(CuotasTotales) - 1
'miramos el nº de cuotas anuales para
'poner la fecha dependiendo de eso
Select Case CuotasAnio
'cuotas semanales
Case 52
'sumamos 7 días al dato de la celda anterior
ActiveCell.Formula = "=R[-1]C+7"
'cuotas mensuales, bimensuales, trimestrales,
'cuatrimestrales, semestrales, o anuales
Case 12, 6, 4, 3, 2, 1
'que coincida el día exacto (si es primer
'pago es el día 12, por ejemplo, que cada
'pago coincida con el día 12)
ActiveCell.Formula = "=IF(DATE(YEAR(R18C),MONTH(R18C),DAY(R18C))" & _
"=DATE(YEAR(R18C),MONTH(R18C)+1,),DATE(YEAR(R[-1]C),MONTH(R[-1]C)+(12/R10C[2])+1,)" & _
",DATE(YEAR(R[-1]C),MONTH(R[-1]C)+(12/R10C[2]),MIN(DAY(R18C4),DAY(DATE(YEAR(R[-1]C)," & _
"MONTH(R[-1]C)+(12/R10C[2])+1,)))))"
'si es otro tipo de cuota
Case Else
ActiveCell.Formula = "=IF(R10C[2]=12,DATE(YEAR(R[-1]C),MONTH(R[-1]C)+(12/R10C6)," & _
"IF(R10C6=12,DAY(R[-1]C))),R[-1]C+INT(365/R10C[2]))"
End Select
'bajamos una fila
ActiveCell.Offset(1, 0).Select
Next
End If
'Seguimos poniendo el capital vivo
'antes del pago de la 1ª cuota
Range("E18").Select
ActiveCell.Formula = "=IF(RC[-3]<R14C6+1,R7C6,R7C6)"
'Seguimos poniendo el capital amortizado
'en la primera cuota
Range("F18").Select
ActiveCell.Formula = "=IF(RC[-4]<(R14C6+1),0,IF(RC[-4]<=(R9C6+R14C6),RC[4]-RC[1],0))"
'seguimos poniendo los intereses pagados
Range("G18").Select
ActiveCell.Formula = "=IF(RC[-5]<(R14C6+1),RC[-2]*R13C6/R10C6,IF(RC[-5]<=(R9C6+R14C6)," & _
"RC[-2]*R8C6/R10C6,0))"
'seguimos poniendo el capital amortizado acumulado
Range("H18").Select
ActiveCell.Formula = "=IF(RC[-2]<>0,RC[-2],0)"
'seguimos poniendo los intereses acumulados
Range("I18").Select
ActiveCell.Formula = "=IF(RC[-2]<>0,RC[-2],0)"
'seguimos poniendo la cuota total
Range("J18").Select
ActiveCell.Formula = "=IF(RC[-8]<(R14C6+1),RC[-4]+RC[-3],R7C6*(R8C6/R10C6)/(1-(1+(R8C6/R10C6))^-R9C6))"
'seguimos poniendo el resto de datos, es decir
'el capital vivo antes del pago de cada cuota,
'el capital amortizado, los intereses, el capital
'amortizado acumulado, los intereses acumulados,
'y el importe de las cuotas
Range("E18").Select
For i = 1 To CuotasTotales - 1
'el capital vivo
ActiveCell.Offset(1, 0).Formula = "=IF(RC[-3]<=R14C6+1,R7C6,IF(RC[-3]<=R7C6,R[-1]C-R[-1]C[1],0))"
'el capital amortizado
ActiveCell.Offset(1, 1).Formula = "=IF(RC[-4]<(R14C6+1),0,IF(RC[-4]<=(R9C6+R14C6),RC[4]-RC[1],0))"
'los intereses pagados
ActiveCell.Offset(1, 2).Formula = "=IF(RC[-5]<(R14C6+1),RC[-2]*R13C6/R10C6,IF(RC[-5]" & _
"<=(R9C6+R14C6),RC[-2]*R8C6/R10C6,0))"
'el capital amortizado acumulado
ActiveCell.Offset(1, 3).Formula = "=IF(RC[-6]<>0,R[-1]C+RC[-2],0)"
'los intereses acumulados
ActiveCell.Offset(1, 4).Formula = "=IF(RC[-7]<>0,R[-1]C+RC[-2],0)"
'la cuota total
ActiveCell.Offset(1, 5).Formula = "=IF(RC[-8]<(R14C6+1),RC[-4]+RC[-3],R7C6*(R8C6/R10C6)/(1-" & _
"(1+(R8C6/R10C6))^-R9C6))"
'bajamos a la fila siguiente
'y seguimos con el bucle
ActiveCell.Offset(1, 0).Select
Next
'ponemos las sumas totales, lo ponemos en negrita
'y le ponemos un nombre a la celda
Range("F17").End(xlDown).Offset(1, 0).Select
ActiveCell.Formula = "=SUM(R[-1]C:R18C)"
ActiveCell.Name = "SumaDelCapitalAmortizado"
ActiveCell.Font.Bold = True
'sumamos los intereses a pagar, y ponemos
'el valor de la celda en negrita
ActiveCell.Offset(0, 1).Formula = "=SUM(R[-1]C:R18C)"
ActiveCell.Offset(0, 1).Font.Bold = True
'sumamos las cuotas totales, y ponemos
'el valor de la celda en negrita
ActiveCell.Offset(0, 4).Formula = "=SUM(R[-1]C:R18C)"
ActiveCell.Offset(0, 4).Font.Bold = True
'ponemos las tramas alternas, es decir, celdas
'sombreadas y blancas desde B17 hasta el final
Range("B17", Range("B17").End(xlDown).End(xlToRight)).Select
'borramos el formato que tengan
Selection.FormatConditions.Delete
'añadimos los formatos condicionales
'a los datos de la tabla
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=SI(RESIDUO(FILA();2)=0;VERDADERO;FALSO)"
Selection.FormatConditions(1).Interior.ColorIndex = 15
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=SI(RESIDUO(FILA();1)=0;VERDADERO;FALSO)"
'hacemos lo mismo con los totales
Range(Range("F17").End(xlDown), Range("G17").End(xlDown)).Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=SI(RESIDUO(FILA();2)=0;VERDADERO;FALSO)"
Selection.FormatConditions(1).Interior.ColorIndex = 15
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=SI(RESIDUO(FILA();1)=0;VERDADERO;FALSO)"
Range("J17").End(xlDown).Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=SI(RESIDUO(FILA();2)=0;VERDADERO;FALSO)"
Selection.FormatConditions(1).Interior.ColorIndex = 15
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=SI(RESIDUO(FILA();1)=0;VERDADERO;FALSO)"
'ponemos bordes alrededor de los conceptos
Range("B17:J17").Select
With Selection.Borders
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
'seguimos poniendo bordes desde B18 hasta el final
'si solo hay 1 cuota ponemos la fila 19 con bordes
If CuotasTotales = 1 Then
Range("B18:J18").Select
With Selection.Borders
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Else
'si hay más de una cuota, ponemos
'todos los datos con bordes
Range("B18", Range("B18").End(xlDown).End(xlToRight)).Select
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
End If
'le seguimos poniendo bordes a los totales
'del capital amortizado, e intereses a pagar
Range(Range("F17").End(xlDown), Range("G17").End(xlDown)).Select
With Selection.Borders
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
'hacemos lo mismo para la suma
'de las cuotas totales
Range("J17").End(xlDown).Select
With Selection.Borders
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
'configuramos página y ponemos la fila 17 fija,
'por si hay más de una página a imprimir, para
'que nos salgan los encabezados correctamente
With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$17"
.PrintTitleColumns = ""
End With
'si la SumaDelCapitalAmortizado no cuadra exactamente hasta
'el segundo decimal, con el principal del préstamo, ponemos
'un mensaje al final de la tabla
If Format(Range("SumaDelCapitalAmortizado"), "#,##0.00") <> Format(Range("E18"), "#,##0.00") Then
Range("B17").End(xlDown).Offset(3, 0).Select
ActiveCell = "Excel provoca un error en el cálculo, a nivel decimal, en la suma total del capital amortizado."
'lo alineamos dándole formato general
With Selection
.HorizontalAlignment = xlGeneral
End With
End If
'borramos el nombre de la suma
'total del capital amortizado
ActiveWorkbook.Names("SumaDelCapitalAmortizado").Delete
'liberamos memoria
Principal = Empty
InteresPrestamo = Empty
CuotasAmortizacion = Empty
CuotasAnio = Empty
Fecha = Empty
Carencia = Empty
InteresCarencia = Empty
CuotasCarencia = Empty
Informacion = Empty
Unload Me
'autoajustamos desde la columna E a la J
Columns("E:J").Select
Selection.Columns.AutoFit
'nos situamos en la celda B2
Range("B2").Select
'protegemos la hoja
ActiveSheet.Protect
'mostramos el proceso
Application.ScreenUpdating = True
End If
End Sub

Private Sub cerrar_Click()
'Descargamos el formulario de memoria
Unload Me
End Sub

Si os habéis fijado bien (y no os habéis cansado leyendo tanto código fuente), he utilizado fórmulas de matemáticas financieras, omitiendo las funciones propias de Excel, como por ejemplo la función PAGO. Personalmente me gusta más utilizar las fórmulas matemáticas, que estas funciones que lo encapsulan todo, y en las que no se sabe exactamente que es lo que está haciendo la aplicación (bueno, sí se sabe, porque sabemos para que sirven esas funciones, pero el control sobre lo que estamos haciendo, no es el mismo). También os habréis fijado, que he utilizado las fórmulas como si las estuviéramos escribiendo directamente en las celdas de Excel, entrecomillándolas dentro del código, y escribiéndolas en inglés. El secreto de esto último, no es otro que crearlas utilizando la grabadora de macros, ...así no nos equivocaremos.

Otra cuestión que me gustaría remarcar, es que si en el formulario donde entraremos los datos, escogemos que las cuotas del préstamo sean mensuales, bimensuales, trimestrales, cuatrimestrales, semestrales, o anuales, los cálculos se realizarán escogiendo el mismo día de pago para todas las cuotas. Vamos a explicar esto con un ejemplo sencillo. Imaginad que escogemos amortizar el préstamo de forma mensual (12 cuotas al año). Si el primer pago lo realizamos por ejemplo el día 20-01-2010, si le sumamos 30 días, las cuotas siguientes se pagarían en estas fechas:

20/01/2010
19/02/2010
21/03/2010
20/04/2010
...

Como vemos, no coinciden todas el día 20, que es lo normal (lo que nos aplicará la entidad financiera). En el código está contemplado todo esto, y si escogemos alguno de los periodos que os he indicado antes, el día de pago siempre será fijo (en el caso del ejemplo anterior, todos los pagos se realizarían el día 20 de cada mes).

Como veis, ya son varios los artículos sobre temas financieros, los que he ido publicando en el blog. Espero que este tenga tan buena aceptación, como la ha tenido el artículo donde explicaba como calcular la tir y el van. A partir de ahora, ya no tienes excusa, para calcular las cuotas de la hipoteca, la letra del coche, o cualquier otro préstamo, porque esta aplicación se constituye como un excelente simulador de préstamos.

Desde aquí podéis descargar el fichero de Excel, con el ejemplo que hemos visto en este artículo.

Modificar datos utilizando un formulario

2009-12-16T00:01:00.006+01:00

En una entrega anterior, habíamos visto como introducir datos a través de un formulario. Si vamos a utilizar la técnica de los formularios para introducir datos en Excel, es más que probable que necesitemos en alguna ocasión, modificar los datos introducidos, bien porque nos hayamos equivocado al darlos de alta, o por cualquier otro motivo.

Pues hoy vamos a ver precisamente eso, como modificar datos de una hoja de cálculo, utilizando un formulario. He utilizado un ejemplo muy parecido al que habíamos visto en aquella entrega, para que sea más sencillo. Eso sí, una observación: si queréis integrar de forma conjunta el alta de datos y la modificación de datos, utilizando estos ejemplos, copiando y pegando los macros, deberéis tener en cuenta que en el artículo donde os hablaba de cómo introducir datos a través de un formulario, los controles (ComboBox, TextBox, etc.) no tienen el mismo nombre que en este artículo que estáis leyendo. Por ejemplo, en aquel artículo, el TextBox de la cantidad de producto, se llamaba TextBox2, y en este artículo lo llamaremos TextBox1.

Una vez dicho todo esto, vamos a ponernos manos a la obra. Necesitaremos una tabla con datos -eso es evidente-, pues lo que haremos será modificarlos. Necesitaremos que los datos estén de forma continua, sin líneas en blanco, para que nos funcione este ejemplo, aunque se puede utilizar para otros ejemplos donde no se cumpla esta condición. Simplemente tendremos que cambiar un par de líneas. Más adelante os comentaré de qué estamos hablando.

Los dos macros que colocaremos dentro de un módulo serán estos:

Sub Auto_open()
'Protegemos la hoja
ActiveSheet.Protect
End Sub

Sub introducir_datos()
'llamamos al formulario
UserForm1.Show
End Sub

Dentro del Formulario (UserForm), tendremos el resto del código. Utilizaremos un macro llamado ComboBox1_Enter() que se ejecutará como su nombre indica, al hacer clic en el ComboBox1. Tendremos también otro llamado ComboBox1_Change(), que nos servirá para cargar en los TextBox los datos correspondientes al productos elegido. También utilizaremos dos macros que evalúen si estamos cambiando la cantidad de producto, y el precio unitario, para recalcular el total, a través de un TextBox1_Change() y de un TextBox2_Change(). Utilizaremos otro macro llamado CommandButton1_Click() que contendrá el código al pulsar el botón para grabar datos, y otro llamado CommandButton2_Click() que servirá, no para borrar los datos del formulario, como en el ejemplo donde introducíamos datos a través de un formulario, sino para eliminar el artículo o producto, de la tabla de Excel.

Vamos a ver el código de todos ellos:

Private Sub ComboBox1_Enter()
'En caso de error, que continúe
On Error Resume Next
'limpiamos los datos del Combobox
ComboBox1.Clear
'Al inciar el formulario,
'seleccionamos la hoja con los datos
Hoja1.Select
'Seleccionamos la celda B5
Range("B5").Select
'Vamos a llenar dinámicamente el combobox
'con los nombres de los artículos, hasta
'encontrar una fila vacía
Do While Not IsEmpty(ActiveCell)
'ponemos el nombre del producto
ComboBox1.AddItem ActiveCell.Value
'bajamos una fila
ActiveCell.Offset(1, 0).Select
Loop
End Sub

En el caso de que no tengamos los datos de forma continua, y hayan filas en blanco, si usamos el bucle Do While del macro anterior, solo se nos cargarán en el ComboBox1 los datos que haya hasta esa fila vacía. Si queremos usarlo correctamente y tenemos filas vacías en la tabla cuyos datos queremos modificar, tendremos que sustituir el bucle Do While por un bucle For para que recorra todas las filas, desde la fila inicial hasta la fila final. Eso sí, se nos cargarán también en el ComboBox1 las filas vacías, aunque podemos omitirlas, añadiendo solo aquellas celdas al combo que contengan datos (que no estén vacías), con un sencillo condicional que evalúe si la celda está vacía o no.

Bien, seguimos con el resto del código que utilizaremos, y que será el siguiente:

Private Sub ComboBox1_Change()
'Miramos en qué fila está el producto
'seleccionado en el ComboBox, y le sumaremos
'4 porque empezamos los productos en la fila
'5 (celda B5). Sumaremos también un 1, porque
'los elementos del combobox empiezan a numerarse
'desde cero. Con lo cual sumaremos 4 + 1 = 5.
'Seleccionaremos esa fila, de la columna B, es
'decir, de la columna 2 (el producto)
Cells(ComboBox1.ListIndex + 5, 2).Select
'cargamos los datos correspondientes
'al producto elegido
TextBox1 = ActiveCell.Offset(0, 1)
TextBox2 = ActiveCell.Offset(0, 2)
TextBox3 = ActiveCell.Offset(0, 3)
End Sub

Private Sub TextBox1_Change()
'Si cambiamos la cantidad, modificaremos
'el precio total
If TextBox1 <> "" And IsNumeric(TextBox1) And _
TextBox2 <> "" And IsNumeric(TextBox2) Then
TextBox3 = TextBox1 * TextBox2
End If
End Sub

Private Sub TextBox2_Change()
'Si cambiamos el precio unitario, modificaremos
'el precio total
If TextBox1 <> "" And IsNumeric(TextBox1) And _
TextBox2 <> "" And IsNumeric(TextBox2) Then
TextBox3 = TextBox1 * TextBox2
End If
End Sub

Private Sub CommandButton1_Click()
'Desprotegemos la hoja
ActiveSheet.Unprotect
'Miramos en qué fila está el producto
'seleccionado en el ComboBox, y le sumaremos
'4 porque empezamos los productos en la fila
'5 (celda B5). Sumaremos también un 1, porque
'los elementos del combobox empiezan a numerarse
'desde cero. Con lo cual sumaremos 4 + 1 = 5.
'Seleccionaremos esa fila, de la columna B, es
'decir, de la columna 2 (el producto)
Cells(ComboBox1.ListIndex + 5, 2).Select
'grabamos los datos modificados
'comenzando con la cantidad
ActiveCell.Offset(0, 1) = CDbl(TextBox1)
'el precio unitario
ActiveCell.Offset(0, 2) = CDbl(TextBox2)
'y ahora el precio total
ActiveCell.Offset(0, 3) = CDbl(TextBox3)
'limpiamos los datos
ComboBox1.Clear
TextBox1 = ""
TextBox2 = ""
TextBox3 = ""
'ponemos el focus en el TextBox1
TextBox1.SetFocus
'protegemos la hoja
ActiveSheet.Protect
End Sub

Private Sub CommandButton2_Click()
'Desprotegemos la hoja
ActiveSheet.Unprotect
'borramos esa línea entera
Selection.EntireRow.Delete
'reiniciamos los datos del formulario
ComboBox1.Clear
TextBox1 = ""
TextBox2 = ""
TextBox3 = ""
End Sub

Desde aquí podéis descargar el fichero, con el ejemplo que hemos visto en este artículo.

Protegiendo nuestros trabajos en Excel

2009-12-02T00:01:00.007+01:00

En anteriores ocasiones ya he comentado en el blog, algunas formas para proteger nuestros trabajos de Excel. Entre ellas, una que consiste en leer el número de serie de un disco duro donde se abre el archivo Excel, para evitar que nuestro fichero corra de mano en mano, en el caso de que deseemos limitar el uso del mismo a una serie de usuarios registrados, o a aquellos que hayan pagado por utilizar el fichero.

Entiendo que hay gente que proteja sus trabajos, porque en muchas ocasiones, ha sido descomunal el esfuerzo y el tiempo invertido, como para que un día encontremos nuestros trabajos, circulando por Internet, y que sea otro quien acredite la propiedad intelectual de ese trabajo. Yo personalmente, no he protegido ningún fichero, y ni tan siquiera incluyo un copyright en el mismo. Alguna vez he incluido un enlace hacia este blog, simplemente para que el usuario no se olvide de pasarse por aquí de vez en cuando :-) pero nada más, en relación a la protección de ficheros.

En otra ocasión también hablé de cómo proteger un fichero Excel, para que no se pueda trabajar con él, si el usuario no introduce correctamente la contraseña que se solicita, cuando se abre el fichero. Esta opción es similar a la que ya incorpora Excel de forma nativa, y que también se explica al final de ese mismo artículo.

En esta ocasión vamos a ver como podemos utilizar otra técnica de protección que utilizan algunos programas en sus versiones trial o de demo. En esta ocasión, protegeremos nuestros trabajos de dos formas distintas:

Limitando el número de veces que se usa el fichero.

Limitando el uso del fichero a X días contados desde el primer uso.

Comenzaremos aplicando la primera de las técnicas, es decir, limitaremos el número de veces que se utiliza el fichero. Para ello, crearemos una hoja de control. Es decir, una hoja donde anotaremos las veces que se ha abierto el fichero. En el ejemplo que podéis descargar al final del artículo, el fichero consta de dos hojas normales, más esa tercera de control, que evidentemente tendremos oculta.

El macro que deberemos grabar en ThisWorkbook (no en un módulo), es este:

Cuando abramos el fichero la 11ª vez, nos aparecerá este mensaje, y se cerrará el libro:

Vamos a ver ahora como podemos controlar el uso de nuestro fichero limitando el uso del fichero, a X días contados desde el primer uso.

Private Sub Workbook_Open()
'Si hay errores, que continúe
On Error Resume Next
'Ocultamos el procedimiento
Application.ScreenUpdating = False
'Omitimos los mensajes de aviso
Application.DisplayAlerts = False
'Desactivamos las teclas de cancelacion de macros
Application.EnableCancelKey = xlDisabled
'Mostramos la Hoja3 que tenemos oculta
Hoja3.Visible = True
'Nos situamos en la Hoja3
Hoja3.Select
'Si es la primera vez que abrimos el fichero,
'ponemos la fecha de ese primer uso
If Range("B1") = "" Then
'escribimos lo que nos interesa
Range("A1") = "1ª apertura:"
'ponemos la fecha del sistema
Range("B1") = Date
Else
'si ya lo hemos abierto antes ese fichero,
'controlamos si hay más de 30 días de diferencia
'entre la primera apertura, y la apertura actual.
'También controlaremos si han caducado los 30 días,
'pero puede que el usuario haya puesto una fecha
'de sistema anterior a la del primer uso. En este caso
'fecha actual será anterior a esa del primer uso,
'por lo que también cerraremos el libro
'porque la fecha del sistema ha sido manipulada
If Date - Range("B1") >= 30 Or Date - Range("B1") < 0 Then
'mostramos un mensaje indicando que
'ha caducado el uso del fichero
MsgBox ("Lo siento, pero ya has trabajado con el fichero" & _
Chr(10) + "durante 30 días, desde su primer uso." & _
Chr(10) + Chr(10) + "Ha llegado el momento de comprarlo :-)")
'cerramos el fichero
ThisWorkbook.Close
End If
End If
'Ocultamos de nuevo la Hoja3
Hoja3.Visible = xlSheetVeryHidden
'grabamos los cambios
ActiveWorkbook.Save
'Nos situamos en la Hoja1
Hoja1.Select
'Mostramos el procedimiento
Application.ScreenUpdating = True
End Sub

Cuando abramos el fichero transcurridos 30 días o más desde el primer uso del fichero, nos aparecerá este mensaje, y se cerrará el libro:

El mensaje anterior también nos aparecerá en el caso de que el usuario haya sido espabilado, y haya cambiado la fecha del sistema y haya puesto una fecha anterior a la del primer uso.

No obstante, este último sistema de protección mediante control de fechas, tiene un pequeño inconveniente. ¿Qué pasa si el usuario manipula y pone como fecha de su sistema (fecha de su PC), una fecha que esté entre la fecha del primer uso, y esos 30 días de caducidad?. Pues efectivamente, que el usuario puede utilizar la aplicación indefinidamente, simplemente cambiando la fecha de su ordenador, y poniendo una fecha que esté dentro de los 30 días contados desde la primera vez que uso el fichero. Este mismo defecto, es habitual encontrarlo en el software de demo, o trial que los fabricantes ponen e nuestra disposición, para evaluar si cubre nuestras necesidades y en tal caso comprarlo. También ellos adolecen de este error de protección.

¿Cómo podemos solucionar este pequeño contratiempo?. Pues haciendo un mix entre el primer sistema de protección, es decir, controlando la caducidad a los 10 usos, más la limitación de uso a 30 días más allá de la fecha del primer uso. Mezclando ambos sistemas, tenemos todo controlado. Este es el macro:

Private Sub Workbook_Open()
'Si hay errores, que continúe
On Error Resume Next
'Ocultamos el procedimiento
Application.ScreenUpdating = False
'Omitimos los mensajes de aviso
Application.DisplayAlerts = False
'Desactivamos las teclas de cancelacion de macros
Application.EnableCancelKey = xlDisabled
'Mostramos la Hoja3 que tenemos oculta
Hoja3.Visible = True
'Nos situamos en la Hoja3
Hoja3.Select
'Si hemos llegado a los 10 usos (ponemos esta
'cantidad como límite, aunque la podéis cambiar)
'entonces mostraremos un mensaje y cerraremos
'el libro por haber cubierto el cupo de veces
'que podemos utilizar el fichero
If Range("B1") >= 10 Then
'mostramos un mensaje
MsgBox ("Lo siento, ya has trabajado con el fichero 10 veces," & _
Chr(10) + "tiempo más que suficiente para evaluar esta aplicación." & _
Chr(10) + Chr(10) + "Ha llegado el momento de comprarlo :-)")
'cerramos el fichero
ThisWorkbook.Close
Else
'si todavía no hemos llegado a los 10 usos,
'escribimos lo que nos interesa
Range("A1") = "Veces abierto:"
'ponemos la fecha y hora del sistema
Range("B1") = Range("B1") + 1
'Ahora controlamos la fecha y el
'límite de los 30 días de uso
If Range("B2") = "" Then
'escribimos lo que nos interesa
Range("A2") = "1ª apertura:"
'ponemos la fecha del sistema
Range("B2") = Date
Else
'si ya lo hemos abierto antes ese fichero,
'controlamos si hay más de 30 días de diferencia
'entre la primera apertura, y la apertura actual.
'También controlaremos si han caducado los 30 días,
'pero puede que el usuario haya puesto una fecha
'de sistema anterior a la del primer uso. En este caso
'fecha actual será anterior a esa del primer uso,
'por lo que también cerraremos el libro
'porque la fecha del sistema ha sido manipulada
If Date - Range("B2") >= 30 Or Date - Range("B2") < 0 Then
'mostramos un mensaje indicando que
'ha caducado el uso del fichero
MsgBox ("Lo siento, pero ya has trabajado con el fichero" & _
Chr(10) + "durante 30 días, desde su primer uso." & _
Chr(10) + Chr(10) + "Ha llegado el momento de comprarlo :-)")
'cerramos el fichero
ThisWorkbook.Close
End If
End If
End If
'Ocultamos de nuevo la Hoja3
Hoja3.Visible = xlSheetVeryHidden
'grabamos los cambios
ActiveWorkbook.Save
'Nos situamos en la Hoja1
Hoja1.Select
'Mostramos el procedimiento
Application.ScreenUpdating = True
End Sub

Con todo lo explicado en las otras entradas del blog a las que hacía referencia al principio de este artículo, más lo explicado aquí, creo que ya tenéis las herramientas imprescindibles para proteger la propiedad intelectual de vuestros trabajos. Si combináis todas las técnicas juntas, y encima protegéis las hojas y el código VBA con unas contraseñas que combinen letras, números, y signos (exclamación, arroba, comas, etc.), le pondréis las cosas más difíciles a quien intente apropiarse de vuestro trabajo intelectual.

Desde aquí podéis descargar un fichero comprimido, con tres libros de Excel, cada uno de ellos con los ejemplos que hemos visto en este artículo: protección para 10 usos, protección para 30 días de uso, y protección de ficheros Excel con ambos sistemas.

Introducir datos utilizando un formulario

2009-11-18T00:01:00.005+01:00

Una de las formas más vistosas para introducir datos en una hoja de cálculo, es utilizando un formulario. Cuando utilizamos esta técnica, a través del uso de un UserForm, le pedimos al usuario que introduzca una serie de datos, y una vez cumplimentado todo, se graban en la hoja de cálculo, en la línea que le corresponda. Si la primera celda vacía es la B14, pues será en esa celda donde empezaremos a escribir. Si la primera celda vacía es la J4, pues será esa la celda elegida.

De la misma forma que digo que es una forma vistosa de introducir datos, también he de decir que es una de las formas menos útiles de introducir datos en una hoja de cálculo, pues es mucho más rápido hacerlo directamente sobre la celda donde queremos escribir, que sobre un formulario. Eso sí, si los datos a introducir son pocos, puede sernos de cierta utilidad.

Vamos a ver como funciona la entrada de datos en una hoja de cálculo, a través de un formulario, utilizando un sencillo ejemplo para llevar un control de compras. Solicitaremos el nombre del producto, la cantidad, y el precio unitario. El sistema nos calculará el total, y grabaremos todos los datos en la primera fila vacía.

Una vez hecho el formulario con sus correspondientes Label y TextBox, así como los dos botones, nos bastará con introducir el código VBA. Para ello, lo primero que haremos será proteger la hoja, al arrancar el libro, utilizando el macro Auto_open():

Sub Auto_open()
'Protegemos la hoja
ActiveSheet.Protect
End Sub

Este otro sencillo macro nos lanzará el formulario:

Sub introducir_datos()
'llamamos al formulario
UserForm1.Show
End Sub

Ahora dentro del UserForm (recordemos que los dos macros anteriores deben estar en un módulo VBA), introduciremos estos otros procedimientos. El primero de ellos evalúa si estamos cambiando el TextBox3, para calcular el total, siempre y cuando el TextBox2 y el TextBox3 sean datos numéricos:

Private Sub TextBox3_Change()
'si hay errores, que continúe
On Error Resume Next
'Cuando cambiemos el TextBox3 (precio unitario)
'miraremos si hay datos también en el TextBox2 (cantidad)
'para multiplicar ambos datos, siempre que sean números
If TextBox2 <> "" And IsNumeric(TextBox2) And _
TextBox3 <> "" And IsNumeric(TextBox3) Then
TextBox4 = TextBox2 * TextBox3
End If
End Sub

Este otro, será el código del botón que tiene por rótulo el texto "Grabar datos" (el CommandButton1):

Private Sub CommandButton1_Click()
'Desprotegemos la hoja
ActiveSheet.Unprotect
'Nos situamos al principio
Range("B4").Select
'Bajamos hasta encontrar la fila vacía
Do While Not IsEmpty(ActiveCell)
'Bajamos una fila
ActiveCell.Offset(1, 0).Select
Loop
'grabamos los datos en la primera fila vacía
'primero el nombre del producto
ActiveCell = TextBox1
'ahora la cantidad
ActiveCell.Offset(0, 1) = CDbl(TextBox2)
'ahora el precio unitario
ActiveCell.Offset(0, 2) = CDbl(TextBox3)
'ahora el total
ActiveCell.Offset(0, 3) = CDbl(TextBox4)
'limpiamos los textbox
TextBox1 = ""
TextBox2 = ""
TextBox3 = ""
TextBox4 = ""
'ponemos el focus en el TextBox1
TextBox1.SetFocus
'protegemos la hoja
ActiveSheet.Protect
End Sub

Y este es el código del botón que borra el contenido de los TextBox:

Private Sub CommandButton2_Click()
'borramos los datos
TextBox1 = ""
TextBox2 = ""
TextBox3 = ""
TextBox4 = ""
End Sub

Desde aquí podéis descargar el fichero, con el ejemplo que hemos visto en este artículo.

Calendarios para imprimir

2009-11-04T00:01:00.015+01:00

Suele ser habitual que cuando finaliza un año, mucha gente empieza a planificar sus proyectos para el año siguiente: dejar de fumar, apuntarse al gimnasio, hacer dieta, viajar fuera del país, etc. Seguramente, muchos de los afortunados que tenemos empleo, lo primero que hacemos es buscar un calendario para mirar en qué días cae la Semana Santa, miramos también las vacaciones de verano, los días de Navidad, fin de año, y algún que otro puente, porque lo que más deseamos es disfrutar esos días, desconectando alejados de la fábrica o de la oficina.

Lo normal es que no dispongamos de un calendario del año que viene, hasta unos pocos días antes de la llegada del nuevo año, bien porque nos manda una agenda un proveedor, bien porque nos regala un calendario de pared una entidad financiera, o bien porque nos lo manda una empresa que no conocemos y que quiere vendernos algún producto o servicio.

Pues bien, hoy vamos a ver como podemos generar nuestros propios calendarios para imprimir y poder tenerlos siempre a mano, para cuando los necesitemos. Para ello utilizaremos Excel, esa potente herramienta que nos permite tratar no solo datos numéricos, o de texto, sino también fechas y unidades de tiempo. Excel tiene una limitación en cuanto al tratamiento de fechas, que hace que solo podamos operar con ellas, si están comprendidas entre el 01-01-1900 y el 31-12-9999. En el caso de utilizar Excel en su versión para un ordenador Macintosh, el rango de fechas permitidas, pasa a ser desde el 01-01-1904, hasta el 31-12-9999.

Nosotros vamos a generar un calendario para imprimir que cubra el rango desde el año 1920 hasta el 2100. ¿Parece suficiente, verdad?. Pues bueno, si todavía quieres más años, simplemente cambiando una línea del código fuente, podrás alterar ese rango, por si deseas acomodarlo adaptándolo completamente las posibilidades de Excel (si usas una versión para PC, desde el año 1900 al 9999, y si utilizas Excel para Mac, desde el año 1904 al 9999).

Lo primero que haremos será crear los 12 cuadros, cada uno para cada mes. Cada cuadro estará formado por una celda combinada, donde incluiremos el nombre del mes, y 49 celdas de 7x7, para los días de la semana.

Lo que haremos, será solicitar vía un InputBox, el año para el cual queremos crear el calendario. Una vez introducido, se nos generarán los días de cada mes, colocándose donde les corresponde. Si el día 1 de enero cae en martes, pues aparecerá él martes, y si cae en viernes, pues aparecerá en viernes. Para hacer todo esto, utilizaremos exclusivamente funciones de Excel. Concretamente, para el nombre del mes, tomaremos como referencia el año, que está en la celda K2. En el caso concreto del mes de enero, por ejemplo, la función será esta:

=FECHA($K$2;1;1)

Para la primera fila, y en el caso del mes de enero, bajo el rótulo lunes pondremos esta fórmula:

=SI(ELEGIR(DIASEM(B5);"Do";"Lu";"Ma";"Mi";"Ju";"Vi";"Sa")=B6;1;"")

Para el resto de días de la semana, pondremos un condicional. En el caso del martes, utilizaremos por ejemplo, esta:

=SI(B7="";SI(ELEGIR(DIASEM($B$5);"Do";"Lu";"Ma";"Mi";"Ju";"Vi";"Sa")=C6;1;"");B7+1)

En la segunda fila, bajo el lunes, y en el caso de estar trabajando con el mes de enero, pondremos esta fórmula:

=SI(H7<>"";SI(H7+1>DIA(FIN.MES($B$5;0));"";H7+1);"")

Para el resto de días de la semana, haremos algo parecido, cambiando en la fórmula anterior, la celda H7, por la celda inmediatamente anterior al día de la semana con el que estemos trabajando.

El modelo, como veis, no tiene mucha complicación, Tan solo hay que utilizar la fórmula ELEGIR, para saber junto con DIASEM, el día de la semana, y compararlo con los datos que aparecen arriba y que contienen el día de la semana (Lu, Ma, Mi, etc.), aunque podéis utilizar otras fórmulas, para obtener esos mismos resultados. También utilizaremos la función FIN.MES, para saber cual es el último día del mes, y que no siga rellenando el calendario más allá del día 30 o 31 (en el caso de febrero, del 28 o 29, según proceda).

Si la función FIN.MES os devuelve el error #¿NOMBRE?, hay que tener habilitadas las herramientas para análisis. Eso es tan sencillo como marcar la opción Herramientas para análisis, desde el menú Herramientas, y seleccionando a continuación Complementos...

Por otra parte, me comentan, que al abrir el fichero desde una versión de Excel en inglés, la función FIN.MES no la traduce de forma automática. En ese caso hay que hacer un reemplazo (desde Edición, seleccionando la opción Reemplazar), y sustituir FIN.MES por EOMONTH.

Como parece que estar leyendo todo esto, puede parecer un poco lioso, si no tenemos delante el fichero de Excel, lo mejor es que lo descarguéis, y lo probéis. Al final de este artículo, hay un enlace para descargarlo.

Los macros que utilizaremos, son realmente sencillos. El primero será este, que hará que se proteja la hoja, para que nadie cambie ni borre nada. La protección no tiene ningún password:

Sub Auto_open()
'Al abrir el libro,
'protegemos la hoja (sin ningún password)
ActiveSheet.Protect
End Sub

El segundo macro es para imprimir el calendario, definiendo el área a imprimir, ajustado a las dimensiones de 1 página de alto y 1 de ancho, y encajándolo todo, horizontal y verticalmente:

Sub imprimir_calendario()
'Seleccionamos el área para imprimir
ActiveSheet.PageSetup.PrintArea = "$A$1:$Y$40"
'definimos las preferencias de impresión
With ActiveSheet.PageSetup
'Centramos la impresión
.CenterHorizontally = True
.CenterVertically = True
'Ajustamos la impresión a 1 página
'tanto de alto como de ancho
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
End With
'Imprimimos el calendario
ActiveWindow.SelectedSheets.PrintOut Copies:=1
End Sub

Este último macro, es para el año. Pondremos el texto en un tamaño de fuente de 18 pixels, lo centraremos en un rango de celdas, y le añadiremos delante el texto "Año", entre otras cosas:

Sub anio()
'Ocultamos el procedimiento
Application.ScreenUpdating = False
'Desprotegemos la hoja
ActiveSheet.Unprotect
'fichamos la celda donde estamos
celda = ActiveCell.Address
'Presentamos un inputbox para preguntar el año
respuesta = InputBox("Introduce el año:", "Año")
'Si el año está entre 1950 y 2100, lo escribimos
'en la celda K2, en caso contrario, mostramos un mensaje
If Not IsNumeric(respuesta) Or respuesta < 1920 Or respuesta > 2100 Then
'mostramos un mensaje
MsgBox ("No es un año válido. Debe estar entre 1920 y 2100")
Exit Sub
Else
'si el año es válido, lo escribimos en la celda K2
Range("K2").Select
ActiveCell = respuesta
Selection.NumberFormat = """Año"" ###0"
'ponemos el año en grande, y lo centramos
With Selection.Font
.Size = 18
End With
Range("K2:O2").Select
With Selection
.HorizontalAlignment = xlCenter
End With
Selection.Merge
End If
'volvemos donde estábamos
Range(celda).Select
'protegemos la hoja
ActiveSheet.Protect
'Mostramos el procedimiento
Application.ScreenUpdating = True
End Sub

Por cierto, a este último macro, le he puesto por nombre “anio” en lugar de “año”, por si algún usuario no tiene en su teclado la letra ñ, o por si usa una versión inglesa de Excel, y para evitar posibles problemas al convertir las funciones y los procedimientos.

Si deseáis añadir el complemento de las herramientas para análisis directamente a través de un macro, podéis añadir esta primera línea al macro Auto_open():

AddIns("Herramientas para análisis").Installed = True

Aquí podéis ver un pantallazo de cómo nos quedaría el calendario, una vez elegido el año. Evidentemente esto no será lo que saldrá por la impresora cuando imprimamos el calendario, pues se trata de un pantallazo para ilustrar este artículo, y aquí salen visibles los botones para los macros:

Para los más curiosos, en el calendario se utiliza también el formato condicional, con el fin de poner de color amarillo suave, aquellas celdas que no contienen ningún día, y diferenciarlas así, de aquellas que tienen un número. Si nos situamos en cualquier celda, y vamos a la opción de menú Formato, y a continuación a Formato condicional…, veremos esto (aunque no se pueda apreciar muy bien, aparece en color amarillo pálido, el fondo de la celda):

Si preferís utilizar calendarios con más decoración, con imágenes, y que no estén hechos en Excel, sino con otras aplicaciones, podéis pasaros por algunos sitios de la red. Entre otros, podéis visitar por ejemplo, este blog que contiene abundante información sobre cómo obtener calendarios para imprimir, aunque posiblemente este último sitio esté dirigido más a un público infantil, al que seguramente Excel todavía les suena a chino :-)

Desde aquí podéis descargar el fichero, con el ejemplo que hemos visto en este artículo, para que podáis elaborar vuestros propios calendarios para imprimir.

Enviar un email desde Excel

2009-10-21T00:01:00.011+02:00

Hace ya un tiempo, en forosdelweb.com, un usuario preguntaba si era posible enviar un email desde nuestra aplicación Excel. Otro usuario le contestaba que sí, que se podía hacer, y le remitía a una página web en inglés, donde se explicaba paso a paso, como llevar a cabo esta operación.

Como hace solo unos pocos días, otro usuario volvió a preguntar sobre el tema en ese mismo foro, se me despertó la curiosidad, y me puse a mirar aquella web en inglés, y a la que en su día no le había prestado mucha atención. La web en cuestión era esta: http://www.rondebruin.nl/sendmail.htm.

Allí hay abundante información sobre como enviar un email desde Excel (la documentación está en inglés), y hay ejemplos de cómo adjunta un archivo, como mandar una copia del email a otro usuario, etc. Yo me limitaré a hacer lo más sencillo, que no es otra cosa que enviar un email de texto, haciendo algunas adaptaciones muy sencillas al código original, para facilitar la entrada de datos por parte del usuario. Solo necesitaremos estos 6 datos:

La cuenta de correo desde la que vamos a mandar nuestro email: Este dato lo informaremos directamente en el código fuente del macro.

El password de nuestra cuenta de correo: Este dato lo informaremos directamente en el código fuente del macro.

El nombre y cuenta de correo, para el "reply": Este dato lo informaremos directamente en el código fuente del macro.

La cuenta de correo del destinatario: Este dato lo informaremos a través de un UserForm.

El asunto del email: Este dato lo informaremos a través de un UserForm.

El cuerpo del mensaje (el mensaje en sí): Este dato lo informaremos a través de un UserForm.

Como veis, hay 3 datos que siempre serán fijos, y son nuestra cuenta de correo, el password, y el nombre más el email (esto va junto, así que lo consideraremos como un solo dato), por si contesta o hace un "reply" el usuario. Los otros 3 datos que son variables, los introduciremos a través de un UserForm, es decir, informaremos del email del destinatario, el asunto del email, y

Vamos a enviar un email utilizando el servicio de correo electrónico de Google, es decir, el servicio de su aplicación Gmail. Podemos utilizar una cuenta de Yahoo, Hotmail, o cualquier otra, pero el correo de Gmail, es en mi opinión el mejor de todos. Si utilizáis otro servicio de correo, habrá que cambiar el servidor de correo saliente smtp, cambiando en el código fuente, la línea correspondiente (el código que veréis en este ejemplo, está debidamente comentado).

Lo primero que haremos será crear un macro desde el que llamaremos al UserForm. Tan sencillo, como hacer esto:

Sub Enviar_email()
'llamamos al userform1
UserForm1.Show
End Sub

El UserForm constará de 4 etiquetas de texto o Label (Label1, Label2, Label3, y Label4), 3 cuadros de texto o TextBox (TextBox1, TextBox2, TextBox3), y un botón de comando o CommandButton, tal y como podéis ver en la siguiente imagen:

Cuando diseñéis el UserForm, al situaros encima del TextBox3, que es el del cuerpo del mensaje, fijaos que en las propiedades (ventana de la izquierda), habéis cambiado EnterKeyBehavior a true, y MultiLine también a true, para que cuando usemos la tecla intro en el cuerpo del mensaje, bajemos a la línea siguiente, y para que cuando escribamos un email largo, automáticamente salte de línea al llegar al final por la derecha. Debéis cambiarlo, para que quede como en la siguiente imagen (ver lo marcado en color rojo atenuado):

El botón de comando (ese que pone "Enviar el email"), tendrá este código:

Private Sub CommandButton1_Click()
'Si hay errores, que continúe
On Error Resume Next
'Creamos el Message y Configuration, para
'enviar emails a través del objeto CDO de Windows
Set oMsg = CreateObject("CDO.Message")
Set oConf = CreateObject("CDO.Configuration")
'Cargamos los valores por defecto
oConf.Load -1
'Configuramos el objeto CDO, con los datos
'de nuestra cuenta de correo
Set Flds = oConf.Fields
With Flds
.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
'ponemos nuestra cuenta de correo de GMAIL
.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "mi_email@gmail.com"
'ponemos nuestro password de GMAIL
.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "mi_password"
'este es el smtp de GMAIL. Si usamos Hotmail, yahoo, o cualquier otro
'servicio de correo, deberemos configurar el smtp, y evidentemente
'también el nombre de usuario y el password para esa cuenta
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com"
.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 465
.Update
End With
'recogemos el mensaje del TextBox3 del Userform
mensaje = TextBox3
With oMsg
Set .Configuration = oConf
'ponemos el nombre y correo para el reply
.From = """mi_nombre"" <mi_email@gmail.com>"
'recogemos el resto de datos del Userform,
'es decir, el destinatario y el asunto del mensaje
.To = TextBox1
.Subject = TextBox2
.TextBody = mensaje
.Send
End With
'Mostramos un mensaje, tanto si hay
'errores como si no los hay
If Err <> 0 Then
MsgBox ("Se ha producido un error, y no se ha podido enviar el email.")
Else
MsgBox ("El email se ha enviado correctamente.")
End If
End Sub

Cuando tengáis todo listo, tan solo deberéis llamar al macro Enviar_email(), para que os presente un formulario como este, para poder enviar un correo electrónico desde nuestra aplicación Excel:

Como ya sabéis, este macro podéis guardarlo en nuestro libro personal de Excel, para poder utilizarlo desde cualquier hoja de cálculo.

Desde aquí podéis descargar el fichero, con el ejemplo que hemos visto en este artículo.

Ejecutar una macro a una hora determinada

2009-10-07T09:04:00.006+02:00

Son muchos los usuarios que llegan a este blog, buscando como se puede ejecutar una macro a una hora determinada. Realmente hasta el día de hoy, no había explicado como se hacía esto, así que aprovecharé la oportunidad para dar respuesta a las inquietudes de estos usuarios.

Antes de nada, quiero daros mi opinión sobre este macro que hoy veremos. Sinceramente no me gusta nada eso de ejecutar una macro a una hora determinada, porque puede interferir en otros usos que estemos haciendo con la hoja de cálculo. Por ejemplo, podemos estar trabajando con un fichero Excel, y no acordarnos de que a las 18:00 h., se va a cerrar automáticamente Excel sin previo aviso, y sin grabar los últimos cambios que hayamos realizado. Claro, si te sucede eso, pillas un buen cabreo...

En fin, comentarios aparte, vamos a ver como podemos ejecutar una macro a una hora determinada. Como siempre, lo veremos con un ejemplo. En este caso, lo que haremos será mostrar un mensaje cuando den las 18:00 h., avisándonos de que finaliza nuestra jornada laboral. El macro que grabaremos en un módulo, será este:

Sub Auto_open()
'Lanzamos el macro llamado mensajillo,
'cuando den las 6 de la tarde
Application.OnTime TimeValue("18:00:00"), "mensajillo"
End Sub

Como podéis ver, entre comillas aparece un texto llamado mensajillo. Pues bien, ese es el nombre del macro que se ejecutará a las 18:00 h., y que no es otro que este (que tendremos que grabar también en un módulo):

Sub mensajillo()
'lanzamos un mensaje, para avisar que es la
'hora de tomarse una cervecita fuera de la oficina
MsgBox ("Son las 18:00 h., hora de salir de la oficina. ¡Yuhuuuu!")
End Sub

Cuando lleguen las 18:00 h., nos aparecerá este bonito mensaje en pantalla, siempre que no hayamos cerrado el fichero que ejecuta estas macros (o no hayamos cerrado Excel, si tenemos los tenemos en el libro personal de macros):

Con esto, ya lo tenemos todo hecho. ¿Facilito, verdad?. Pues ahora solo nos queda por dar respuesta a algunas dudas.

¿Por qué tiene ese nombre tan raro el macro?. ¿Qué es eso de Auto_open()?. Pues es el nombre que obligatoriamente debe tener el macro para que al abrir el fichero, empiece a contar el tiempo, y que cuando den las 18:00 h. nos avise. Evidentemente, deberá estar abierto el fichero, para que eso suceda, pues en caso contrario no nos aparecerá el aviso. En lugar de crear ese macro Auto_open() en un módulo VBA, podemos hacer lo mismo llamando al evento Open a través de un macro llamado Workbook_Open(), dentro de ThisWorkbook, en lugar de hacerlo en un módulo.

¿Tengo que tener siempre abierto -aunque sea minimizado-, el fichero donde tengo ese "contador" de tiempo?. No necesariamente. Puedes grabar el macro en el libro personal de macros. Este libro está siempre "latente" para cuando lo necesitemos, pero oculto, por lo que el macro se ejecutará a la hora programada, siempre y cuando tengas abierto Excel, claro está.

Si en lugar de ejecutar el macro con ese mensaje a las 18:00h., quisiéramos que se ejecutase al cabo de 30 minutos contados a partir de ese mismo instante, es decir, a partir de la hora que pongamos en marcha el "contador", entonces deberemos cambiar el macro inicial, por este otro:

Sub Auto_open()
'Lanzamos el macro llamado mensajillo,
'30 minutos después de ejecutar este Auto_open,
'que es cuando ponemos en marcha el contador de tiempo
Application.OnTime Now + TimeValue("00:30:00"), "mensajillo"
End Sub