Hace poco me preguntaba un usuario, en un comentario del blog, como aplicar las listas de validación dependientes, pero en lugar de con listas de validación, con combobox. En una respuesta que le di, le colgué un ejemplo, que es el que utilizaré en este artículo, explicando su funcionamiento con detalle.

Lo primero que tendremos que preparar, son los datos de origen, y que se cargarán en los diferentes combobox. En este caso, usaremos dos combobox, aunque su funcionamiento es el mismo, si anidamos más de dos combobx dependientes.

Para explicar el funcionamiento, supondremos que tenemos una lista de países, con sus respectivas ciudades. En este ejemplo hay tres países, y tres ciudades por cada país, aunque si lo deseamos podemos añadir más países, y también más ciudades (no tienen porqué tener todos los países el mismo número de ciudades, ya que si lo deseamos, podemos poner en un país 5, 6, o 20 ciudades, y en otro 2, 3, o las que deseemos). Vamos a trabajar con el siguiente ejemplo:


A continuación, crearemos un UserForm, para lo cual accederemos al modo VBA (Alt+F11), y desde el menú Insertar, seleccionaremos la opción UserForm. Se nos presentará un UserForm en blanco, y lo que tendremos que añadir son dos etiquetas (label), y dos combobox (cuadros combinados). Si no nos apareciera el Cuadro de herramientas, para poder añadir esos controles, desde el menú Ver, seleccionaremos Cuadro de herramientas.

Tendremos que montar un UserForm como este que muestra la imagen:


A continuación, añadiremos este código a UserForm, haciendo doble clic en cualquier control del mismo (o en el propio UserForm), que nos servirá para que al cargarse el UserForm, se añadan al combobox1 (al combobox donde se cargan los nombres de los países) los datos que tenemos en la hoja1:

Private Sub UserForm_Initialize() 'Al inciar el formulario, 'seleccionamos la hoja con los datos Hoja1.Select 'Seleccionamos la celda A1 Range("A1").Select 'Hasta que no encuentre una columna vacía 'que llene todo con datos Do While ActiveCell <> Empty ComboBox1.AddItem ActiveCell.Value 'nos desplazamos una columna a la derecha ActiveCell.Offset(0, 1).Select Loop End Sub

Añadiremos también este otro código, que hará que al cambiar el combobox1 (el de los países), se carguen las ciudades en el combobox2, correspondientes al país elegido:

Private Sub ComboBox1_Change() 'Llenamos el combobox2 al recibir el foco, 'pero antes lo limpiamos ComboBox2.Clear 'seleccionamos la hoja1 Hoja1.Select 'miramos lo que hay seleccionado en el combobox1 'Listindex + 1 nos devuelve la columna del país, 'porque el listindex empieza siempre por cero columna = ComboBox1.ListIndex + 1 'seleccionamos la celda que proceda, 'a partir de la segunda fila Cells(2, columna).Select 'vamos a llenar dinámicamente el combobox 'dependiendo del país elegido Do While Not IsEmpty(ActiveCell) 'Añadimos los nombres de las hojas al combobox ComboBox2.AddItem ActiveCell 'bajamos una fila ActiveCell.Offset(1, 0).Select Loop End Sub

Insertaremos también un módulo VBA, y añadiremos este código para que al cliquear en un botón que añadiremos a nuestra hoja de cálculo, se cargue el formulario (el UserForm):

Sub UserForm() 'llamamos al userform UserForm1.Show End Sub

Una vez hayamos añadido un botón, para que al cliquear sobre él, se cargue el UserForm, obtendremos algo parecido a lo que aparece en la siguiente imagen (podéis cliquear sobre ella, para verla ampliada), donde hemos seleccionado un país (en este caso España), y donde podemos elegir una de las tres ciudades correspondientes a este país:


Desde aquí podéis descargar el fichero de excel, con el ejemplo que os presento en este artículo.

Muchos de los usuarios que llegan a este blog de excel, lo hacen buscando como trabajar con horas de 100 minutos, para que sean interpretadas como horas normales de 60 minutos. La cuestión es bastante sencilla, pues no se trata de algo directamente relacionado con excel, sino con las matemáticas básicas. Desconozco el motivo por el cual muchos usuarios trabajan con horas de 100 minutos. Quizás se trate de datos importados de otras aplicaciones, o de controles horarios especiales, pero da igual el motivo. Como son bastantes las visitas que google manda a este blog, buscando una solución al tratamiento de ese tipo de "horas especiales", he decidido que como se acerca Navidad, tenía que ser un poco condescendiente con estos -seguramente esporádicos- lectores.

Una simple regla de tres, es la solución a ese tipo de problemas. Veámoslo con unos sencillos ejemplos:

Si esos 100 minutos –llamémosles, especiales-, queremos que sean el equivalente a 1 hora, la lógica nos dice que 50 minutos especiales serán el equivalente a media hora. Veámoslo con más ejemplos de este estilo:

100 minutos de tipo "especial" = 1 hora = 60 minutos
50 minutos de tipo "especial" = 1/2 hora = 30 minutos
25 minutos de tipo "especial" = 1/4 de hora = 15 minutos
12,5 minutos de tipo "especial" = 1/8 de hora = 7,5 minutos

Para hacer la conversión correspondiente, nos bastará con utilizar una regla de tres, del siguiente tipo:

100 minutos de tipo "especial" = 60 minutos ME minutos de tipo "especial" = MM minutos

En la regla de tres anterior, ME es el número de minutos especiales que conocemos, y que queremos pasar a su equivalente en minutos convencionales (MM). Podemos convertir esa regla de tres en la siguiente ecuación:

MM = (60 x ME) / 100

Por ejemplo, si tenemos horas de 100 minutos, y queremos obtener el equivalente de 90 minutos -que están expresados en esas horas especiales-, a la cantidad de minutos convencionales, obtendremos esto:

MM = (60 x 90) / 100 = 54 minutos

Así que ya lo sabemos: 90 minutos "especiales", son exactamente 54 minutos "de los de toda la vida" :-)

Aprovechando la situación, otro numeroso grupo de usuarios llega a este blog de excel, buscando como convertir horas minutos, minutos a segundos, horas a segundos, etc. Vamos a usar de nuevo el sentido común, para dar respuesta a estas otras dudas.

Si sabemos que 1 hora son 60 minutos, no será difícil deducir que 2 horas serán 120 minutos. Usando la lógica, podremos montar una tabla como la que sigue:

1 hora = 60 minutos 2 horas = 2 x 60 minutos = 120 minutos 3 horas = 3 x 60 minutos = 180 minutos 4 horas = 4 x 60 minutos = 240 minutos 5 horas = 5 x 60 minutos = 300 minutos Y así podríamos seguir indefinidamente...

La regla que sigue esa tabla, habrás visto que es muy sencilla:

Si queremos pasar X horas a minutos, tan solo deberemos multiplicar el número de horas por 60 minutos:

Pasar de horas a minutos: Nº de minutos = Nº de horas x 60

Podemos hacer lo mismo con el resto de unidades de tiempo. Para pasar de horas a segundos, si sabemos que 1 hora tiene 60 minutos, y 1 minuto tiene 60 segundos, nos bastará con utilizar esta fórmula:

Pasar de horas a segundos: Nº de segundos = Nº de horas x 60 x 60

Pasar de minutos a segundos: Nº de segundos = Nº de minutos x 60

Pasar de minutos a horas: Nº de horas = Nº de minutos / 60

Pasar de segundos a horas: Nº de horas = Nº de segundos / 60 / 60

Pasar de segundos a minutos: Nº de minutos = Nº de segundos / 60

Es importante destacar que el dato que obtendremos en todas estas conversiones de unidades de tiempo, serán números decimales, y no fracciones de unidades de tiempo. Por ejemplo, si estamos convirtiendo 150 minutos a horas, si aplicamos la fórmula correspondiente, tendremos que dividir 150 entre 60, con lo que obtendremos 2,5 horas. Esta cifra no indica que se trata de 2 horas y 50 minutos (ni tampoco 2 horas y 5 minutos), pues esa cifra no es una fracción de tiempo, sino una fracción decimal, por eso debe interpretarse como 2 horas y media (2’5 horas).

En un artículo anterior hablé de cómo podíamos proteger nuestra aplicación, para impedir que los usuarios le cambien el nombre al fichero. En realidad, se le puede cambiar el nombre, pero si se hace eso, no se podrá trabajar con el libro de excel, pues al abrirlo, nos mostrará un mensaje, y nos indicará cual es el nombre correcto que debe tener, para poder utilizarlo, y a continuación se cerrará automáticamente el libro.

En esta ocasión haremos algo similar, pero con las hojas de cálculo, o si lo preferís, las pestañas, y utilizando el sentido común. En realidad, lo que vamos a hacer es permitir momentáneamente que el usuario le cambie el nombre a las hojas (pestañas), pero en el momento de cerrar el fichero, todas las hojas volverán a tener el nombre que tenían inicialmente.

Veamos lo que podría hacer un usuario con las pestañas de un libro:

1.- Podría cambiar el nombre de alguna o de todas las pestañas, trabajar con el libro, y cerrarlo sin guardar los cambios.
2.- Podría cambiar el nombre de alguna o de todas las pestañas, trabajar con el libro, y cerrarlo guardando los cambios realizados.
3.- Podría dejar el nombre las pestañas tal y como están, trabajar con el libro, y cerrarlo sin guardar los cambios.
4.- Podría dejar el nombre las pestañas tal y como están, trabajar con el libro, y cerrarlo guardando los cambios realizados.

En el caso número 1, no hay problema, pues al no grabar los cambios, el fichero siempre mantendrá los nombres originales en sus hojas o pestañas. En el caso 3 y 4, sería exactamente lo mismo, pues al no cambiar los nombres de las pestañas, no tendríamos porqué preocuparnos de nada. Solo el caso 2, es el que querremos controlar, para que los usuarios no le pongan nombres distintos a las hojas, de los que tenían inicialmente.

Supongamos que tenemos un libro con 3 hojas, que se llaman CANTIDAD VENDIDA, PRECIOS UNITARIOS, e INGRESOS TOTALES, tal y como muestra la siguiente imagen:


Si un usuario le cambia el nombre de una de las pestañas, por ejemplo la primera, la que se llama CANTIDAD VENDIDA, y le pone por ejemplo el nombre de VENTAS, el usuario podrá trabajar con el libro, con todas sus hojas, y no tendremos el más mínimo problema (excepto si en nuestros macros referenciamos la selección de hojas, en función del nombre visible que tienen, cosa que veremos luego como se puede solucionar), pues en el momento de cerrar el libro, le cambiaremos el nombre, y le pondremos el que tenía inicialmente.

Para ello, nos bastará con copiar unas sencillas líneas de código, dentro de Thisworkbook, tal y como se aprecia en la siguiente imagen:


El código que tendremos que introducir es este:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) 'Esto se ejecutará justo unos instantes antes de guardar el fichero If Hoja1.Name <> "CANTIDAD VENDIDA" Then Hoja1.Name = "CANTIDAD VENDIDA" If Hoja2.Name <> "PRECIOS UNITARIOS" Then Hoja2.Name = "PRECIOS UNITARIOS" If Hoja3.Name <> "INGRESOS TOTALES" Then Hoja3.Name = "INGRESOS TOTALES" End Sub

Ese código lo que hace es evaluar el nombre de las hojas, y si éstas tienen un nombre diferente al que deberían tener, entonces se lo cambia para ponerle el nombre que les corresponde.

Muchos de los usuarios que llegan a este blog, buscan precisamente esto, para impedir que los usuarios le cambien el nombre a las pestañas. Es probable que lo hagan porque en sus macros, cuando seleccionan una hoja de cálculo, lo hacen de la siguiente forma (imaginemos que el nombre de la hoja sea PRECIOS UNITARIOS):

'Seleccionamos la hoja de PRECIOS UNITARIOS Sheets("PRECIOS UNITARIOS").Select

Si alguien le cambia el nombre a la pestaña PRECIOS UNITARIOS, y ejecutamos un macro donde seleccionemos la hoja de PRECIOS UNITARIOS llamándola tal y como figura en esa línea de código, obtendremos este bonito mensaje de error, pues la hoja PRECIOS UNITARIOS no se puede seleccionar, ya que no existe, pues se le ha cambiado el nombre:


Lo más sencillo para solucionar esto, es trabajar siempre con el nombre interno de las hojas que podemos ver en modo VBA, justo delante del nombre que se ve cuando trabajamos en modo usuario (el nombre visible de la hoja, o de la pestaña). En la siguiente imagen podemos ver dentro del recuadro rojo, los nombres internos de las hojas, y a su derecha, entre paréntesis, el nombre visible de las mismas:


El nombre interno que acabamos de ver, también se puede cambiar, pero para ello deberemos acceder al modo VBA, y por supuesto deberá estar desprotegido nuestro código. Si protegemos el código VBA, no tendremos ningún problema, pues ese nombre no estará al alcance de los usuarios y por tanto, no lo podrán manipular.

Si deseamos seleccionar una hoja de cálculo desde un macro, os recomiendo siempre que trabajéis con el nombre interno. Fijaos que la forma de llamar a las hojas varía un poco, si lo hacemos llamándolas por su nombre interno o por su nombre visible. Si por ejemplo queremos seleccionar la hoja llamada PRECIOS UNITARIOS, nos bastará con hacerlo de la siguiente forma, y así nos evitaremos problemas con los macros si un usuario le cambia el nombre a la pestaña:

'Seleccionamos la hoja de PRECIOS UNITARIOS Hoja2.Select

Como veis, es algo bastante sencillo, y nos va a servir para solucionar algunos posibles errores dentro de nuestros macros.

Desde aquí podéis descargar el fichero de excel, con el ejemplo que os presento en este artículo.

Si trabajas en el ámbito financiero, seguramente te habrás preguntado alguna vez, como evoluciona un capital durante X años, si éste se retribuye a interés simple, o si se retribuye a interés compuesto. La diferencia entre ambas opciones, reside en que en la capitalización compuesta, se retribuye no solo el capital, sino también los intereses generados.

Vamos a verlo con un sencillo ejemplo. Supongamos que tenemos un depósito con 10.000 euros, y el banco A nos lo retribuye al 5% anual, a interés simple, y el banco B nos lo retribuye también al 5%, pero a interés compuesto anual. Supongamos también que esta imposición la mantendremos durante 8 años. ¿Qué opción es económicamente más rentable?.

El banco A nos da anualmente el 5% de 10.000 euros, a interés simple, es decir:

Año 1: 10.000 x 5% = 500 euros
Año 2: 10.000 x 5% = 500 euros
Año 3: 10.000 x 5% = 500 euros
Año 4: 10.000 x 5% = 500 euros
Año 5: 10.000 x 5% = 500 euros
Año 6: 10.000 x 5% = 500 euros
Año 7: 10.000 x 5% = 500 euros
Año 8: 10.000 x 5% = 500 euros

Al finalizar el octavo año, recuperaremos el capital, más los intereses, es decir:

10.000 + 500 + 500 + 500 + 500 + 500 + 500 + 500 + 500 = 14.000 euros

El banco B nos da anualmente el 5% con capitalización compuesta, es decir, cada año se calcularán los intereses, sobre el capital inicial, más los intereses devengados hasta la fecha:

Año 1: 10.000 x 5% = 500 euros
Año 2: (10.000 + 500) x 5% = 525 euros
Año 3: (10.000 + 500 + 525) x 5% = 551,25 euros
Año 4: (10.000 + 500 + 525 + 551,25) x 5% = 578,81 euros
Año 5: (10.000 + 500 + 525 + 551,25 + 578,81) x 5% = 607,75 euros
Año 6: (10.000 + 500 + 525 + 551,25 + 578,81 + 607,75) x 5% = 638,14 euros
Año 7: (10.000 + 500 + 525 + 551,25 + 578,81 + 607,75 + 638,14) x 5% = 670,05 euros
Año 8: (10.000 + 500 + 525 + 551,25 + 578,81 + 607,75 + 638,14 + 670,05) x 5% = 703,55 euros

Al finalizar el octavo año, recuperaremos el capital, más los intereses, es decir:

10.000 + 500 + 525 + 551,25 + 578,81 + 607,75 + 638,14 + 670,05 + 703,55 = 14.774,55 euros

Es decir, una inversión en el banco B (14.774,55 euros), es más rentable que en el banco A (14.000 euros). De hecho, si la inversión en el banco A permaneciera un año más (9 años, en lugar de 8 años), todavía seguiría siendo una inversión menos rentable que la del Banco B, a 8 años, pues obtendríamos 500 euros más de intereses por ese noveno año, pero aún así, no llegaríamos a los 14.774,55, ya que 14.000 + 500 = 14.500 euros.

Las fórmulas que necesitaremos para los cálculos a interés simple, serán las siguientes:

Intereses anuales = capital · tipo de interés Intereses acumulados = capital · tipo de interés · nº de años Capital + intereses acumulados = capital · [1 + (tipo de interés · nº de años)]

Las fórmulas que necesitaremos para los cálculos a interés compuesto, serán las siguientes:

Intereses anuales = [capital · (1 + tipo de interés)i] – [capital · (1 + tipo de interés)(i - 1)] Intereses acumulados = [capital · (1 + tipo de interés)i] – capital Capital + intereses acumulados = capital · (1 + tipo de interés)i

Vamos a hacer una tabla en excel, donde calcularemos la evolución de dos capitales, y sus correspondientes intereses, en función de esas dos modalidades: con capitalización simple, y con capitalización compuesta. Podríamos hacer una tabla con fórmulas, pero no lo haremos así, porque si queremos calcular la evolución de ese capital en 100 años, tendríamos que poner las fórmulas en 100 filas, y si quisiéramos hacerlo con 1000 años, tendríamos que poner las fórmulas en 1000 filas. Lo haremos a través de un macro, y así nos quedará más elegante, pues solo ocuparemos las celdas con datos, estando el resto libres de fórmulas.

Esta será la plantilla que utilizaremos para el cálculo de los capitales:


Y este es el macro que tendremos que incluir en un módulo de VBA:

Sub interes_simple_y_compuesto() 'Ocultamos el procedimiento Application.ScreenUpdating = False 'Si hay errores, que continúe On Error Resume Next 'fichamos la celda donde estamos, 'para volver a ella posteriormente celda_actual = ActiveCell.Address 'Pasamos los datos a variables capital = Range("D3") anios = Range("D4") tipo_de_interes = Range("D5") 'Comenzaremos evaluando si los datos son correctos If capital If anios If tipo_de_interes If datos_incorrectos = True Then 'Mostramos un mensaje respuesta = MsgBox("Existen datos incorrectos, por lo que no se puede continuar:" & _ Chr(10) + Chr(10) + "1.- El capital debe ser > 0" & _ Chr(10) + "2.- El nº de años debe ser > 0" & _ Chr(10) + "3.- El tipo de interés anual debe ser > 0", vbExclamation, "Datos incorrectos") 'finalizamos el macro Exit Sub End If 'Eliminamos todos los datos que hubiese 'para lo cual nos situamos en B9 Range("B9").Select 'seleccionamos el rango contínuo de datos que encuentre Range(Selection, Selection.End(xlDown)).Select 'eliminamos las filas Selection.EntireRow.Delete 'Si los datos introducidos son correctos, 'comenzaremos poniendo los datos Range("B9").Select For i = 1 To anios 'ponemos el año ActiveCell = i 'ponemos el interés simple anual ActiveCell.Offset(0, 1) = capital * tipo_de_interes 'ponemos el interés simple acumulado ActiveCell.Offset(0, 2) = capital * tipo_de_interes * i 'ponemos el total acumulado a interés simple ActiveCell.Offset(0, 3) = capital * (1 + (tipo_de_interes * i)) 'ponemos ese dato en negrita ActiveCell.Offset(0, 3).Font.Bold = True 'ponemos el interés compuesto ActiveCell.Offset(0, 4) = (capital * (1 + tipo_de_interes) ^ i) - (capital * (1 + tipo_de_interes) ^ (i - 1)) 'ponemos el interés compuesto acumulado ActiveCell.Offset(0, 5) = (capital * (1 + tipo_de_interes) ^ i) - capital 'ponemos el total acumulado a compuesto acumulado ActiveCell.Offset(0, 6) = (capital * (1 + tipo_de_interes) ^ i) 'ponemos ese dato en negrita ActiveCell.Offset(0, 6).Font.Bold = True 'ponemos la diferencia entre los tipo_de_intereses anuales 'por utilizar los dos métodos (simple y compuesto) ActiveCell.Offset(0, 7) = ActiveCell.Offset(0, 6) - ActiveCell.Offset(0, 3) 'bajamos una fila ActiveCell.Offset(1, 0).Select Next 'volvemos a la celda donde estábamos al principio Range(celda_actual).Select 'Mostramos el procedimiento Application.ScreenUpdating = True End Sub

Si tenéis curiosidad, mirad la evolución de un capital de 10.000 euros a 100 años, tanto a interés simple, como a interés compuesto. El resultado os va a sorprender, ya lo veréis.

Desde aquí podéis descargar el fichero de excel, con el ejemplo que os presento en este artículo.

Aunque hace ya muchos años que entró en vigor el euro, son muchas las personas, que cuando ven un precio o un importe en euros, hacen mentalmente la conversión a pesetas. ¿Por qué?. Pues porque han sido muchos años los que hemos convivido con la peseta, como para que nos olvidemos de ella de golpe. Especialmente, es la gente mayor la que más dificultades tiene para saber realmente el "valor" de un precio o de un importe en euros.

Para facilitar esta tarea de conversión -y también, porque esta semana no estaba my inspirado, y he tenido que echar mano del baúl de los recuerdos-, vamos a programar un sencillo conversor que nos servirá para convertir una cantidad en pesetas, a su contravalor en euros. De la misma forma, también podremos convertir una cifra en euros, a su contravalor en pesetas. Con alguna ligera adaptación, esto también nos puede servir, para convertir diferentes monedas que tengan una relación de cambio fija.

Necesitaremos crear un UserForm (un formulario de usuario), donde insertaremos:

• 3 etiquetas de texto (Label), de las cuales, una de ellas será para introducir el texto "Introduce una cantidad". Otra de las etiquetas servirá mostrar el contravalor calculado de la moneda que deseamos convertir (a esta etiqueta, le daremos color azul celeste, para que podamos localizarla en el UserForm, y para darle un toque distinto), y la tercera etiqueta servirá para mostrar el texto "Ptas" o "Euros". A estas etiquetas les pondremos por nombre Texto1, Texto2, y Texto3, respectivamente


• 1 cuadro de texto (TextBox), al que le pondremos por nombre Cantidad.


• 2 botones de opción (OptionButton), de los cuales, uno servirá para convertir de euros a pesetas, y el otro de pesetas a euros. Les pondremos por nombre DeEurosAPtas, y DePtasAEuros, respectivamente.


• 1 imagen de una moneda de 1 euro y de 1 peseta (todo ello en una misma imagen). Le pondremos por nombre Image1


• 1 CommandButton, para mostrar el mensaje de "Cerrar ventana", y al que le pondremos por nombre Cerrar.

Al UserForm, le daremos el nombre de Euroconversor, para lo cual simplemente tendremos que introducir ese nombre en la propiedad "Nombre", tal y como figura en la siguiente imagen (ver la zona remarcada en rojo):


Para darle nombre al resto de elementos (TextBox, OptionButton, etc.), desde el modo VBA, cliquearemos encima de cada uno de los
Para que se ejecute el macro, hemos habilitado un botón en la hoja de cálculo. Cada vez que se cliquee sobre el mismo, se nos cargará el UserForm:


Para que se cargue el UserForm, en un módulo de VBA, crearemos este macro:

Sub Cargar_conversor() 'Que se cargue el UserForm del Euroconversor EurosPesetas.Show End Sub

Para que funcione el euroconversor, deberemos añadir todos estos códigos que a continuación os incluyo. Para colocar el código, deberéis cliquear en el UserForm, cuando lo tengáis a la vista, desde el modo VBA (ver la primera imagen que hay en este artículo).

Para convertir de euros a pesetas, añadiremos esto (en el código correspondiente al formulario):

Private Sub DeEurosAPtas_Click() 'Si hay errores, que continúe On Error Resume Next 'Si la cantidad está vacía... If Cantidad = Empty Then 'ponemos el foco en el textbox 'al que hemos llamado Cantidad Cantidad.SetFocus 'mostramos un mensaje MsgBox (Chr(13) + " Por favor, introduce una cantidad. " _ + Chr(13) + Chr(13)), vbOKOnly, " Datos incompletos" End If 'Si Cantidad no es numérica If Not IsNumeric(Cantidad) Then 'eliminamos la entrada Cantidad = Empty 'ponemos el foco en el textbox 'al que hemos llamado Cantidad Cantidad.SetFocus Else 'si es numérica,le damos formato con 2 decimales Cantidad = Format(Cantidad, "##,##0.00") 'que ponga el texto "Euros", en la etiqueta correspondiente Texto3.Caption = "Euros" 'le damos color negro a la cifra convertida, 'es decir, al resultado obtenido Texto2.ForeColor = RGB(0, 0, 0) 'que añada el texto "Ptas", al resultado Texto2.Caption = Format(Cantidad * 166.386, "#,##0") & " Ptas" End If End Sub

Para convertir de pesetas a euros, añadiremos esto (en el código correspondiente al formulario):

Private Sub DePtasAEuros_Click() 'Si hay errores, que continúe On Error Resume Next 'Si la cantidad está vacía... If Cantidad = Empty Then 'ponemos el foco en el textbox 'al que hemos llamado Cantidad Cantidad.SetFocus 'mostramos un mensaje MsgBox (Chr(13) + " Por favor, introduce una cantidad. " _ + Chr(13) + Chr(13)), vbOKOnly, " Datos incompletos" End If 'Si Cantidad no es numérica If Not IsNumeric(Cantidad) Then 'eliminamos la entrada Cantidad = Empty 'ponemos el foco en el textbox 'al que hemos llamado Cantidad Cantidad.SetFocus Else 'si es numérica,le damos formato sin decimales Cantidad = Format(Cantidad, "##,##0") 'que ponga el texto "Euros", en la etiqueta correspondiente Texto3.Caption = "Ptas" 'le damos color azul a la cifra convertida, 'es decir, al resultado obtenido Texto2.ForeColor = RGB(23, 48, 141) 'que añada el texto "Ptas", al resultado, 'y con formato de 2 decimales Texto2.Caption = Format(Cantidad / 166.386, "#,##0.00") & " Euros" End If End Sub

Cada vez que cliqueemos en el TextBox llamado Cantidad, reiniciaremos los datos del UserForm. El código que necesitaremos es este:

Private Sub Cantidad_Enter() 'Si hay errores, que continúe On Error Resume Next 'que elimine todas las entradas Cantidad = Empty Texto2.Caption = Empty Texto3.Caption = Empty 'desmarcamos las opciones de los botones DeEurosAPtas.Value = False DePtasAEuros.Value = False End Sub

Y finalmente, para cerrar el UserForm, si cliqueamos en el botón habilitado a tal efecto, necesitaremos este código:

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

Aquí os dejo dos pantallazos tras introducir la cifra de 1.275. Primero convertiremos esa cifra a pesetas, y en la otra imagen, convertiremos esa cifra, de pesetas a euros:



Desde aquí podéis descargar el fichero de excel, con el ejemplo que os presento en este artículo.

Hoy trataré de explicar, como obtener el nombre de todos los ficheros existentes en un directorio. Esto nos puede servir para muchas cosas, por ejemplo, para saber existe o no determinado fichero, o para grabar un fichero y que no se grabe con el mismo nombre de otro ya existente. Con independencia de la utilidad que queramos darle, estoy seguro que nos puede ser útil en más de una ocasión.

Para que este macro funcione, tendremos que tener grabado el fichero en nuestro disco duro. El código para devolvernos los ficheros existentes en la misma carpeta donde hayamos grabado el fichero, es el que aparece a continuación. El nombre de los ficheros, lo imprimiremos en la propia hoja de cálculo, aunque podemos imprimirlos en un combobox, por ejemplo:

Sub ficheros_del_directorio() 'Si hay errores, que continúe On Error Resume Next 'Ocultamos el procedimiento Application.ScreenUpdating = False 'Creamos el objeto FileSystemObject Set fso = CreateObject("Scripting.FileSystemObject") 'Informamos de la ruta de donde vamos a obtener 'los ficheros, en este caso, el mismo directorio 'donde tengamos grabado este fichero con el macro ruta = ActiveWorkbook.Path 'definimos dos variables que necesitaremos, 'para recuperar el nombre de la carpeta, y 'los ficheros que haya dentro Set directorio = fso.GetFolder(ruta) Set ficheros = directorio.Files 'escribimos un encabezado en la celda A6 Range("A6").Select ActiveCell = "Ficheros del directorio:" 'lo ponemos en negrita y subrayado ActiveCell.Font.Bold = True ActiveCell.Font.Underline = xlUnderlineStyleSingle 'escribimos los ficheros, a partir de A7 Range("A7").Select For Each archivo In ficheros 'escribimos el nombre del fichero ActiveCell = archivo.Name 'bajamos una fila ActiveCell.Offset(1, 0).Select Next 'Limpiamos los objetos Set fso = Nothing Set directorio = Nothing Set ficheros = Nothing 'Mostramos el procedimiento Application.ScreenUpdating = True End Sub

Si lo que deseamos es escribir los ficheros del directorio, pero sin incluir el nombre del fichero activo, es decir, del fichero desde donde estamos ejecutando el macro -porque por supuesto, al estar en el directorio, se listará-, entonces deberemos cambiar el bucle For Each anterior, por este otro que os incluyo a continuación, y que incorpora un condicional (el resto del código es el mismo, así que me ahorro el copiar y pegar). Esto solo nos servirá si el directorio del cual consultamos los ficheros, es el mismo donde tenemos guardado el fichero que ejecuta el macro, es decir, si se trata del mismo path:

For Each archivo In ficheros 'escribimos el nombre del fichero If archivo.Name <> ActiveWorkbook.Name Then ActiveCell = archivo.Name 'bajamos una fila ActiveCell.Offset(1, 0).Select End If Next

Si lo que deseamos es listar solo los nombres de los subdirectorios existentes en el directorio donde tenemos guardado el fichero activo, entonces el macro que tendremos que utilizar, será este:

Sub subdirectorios_del_directorio() 'Si hay errores, que continúe On Error Resume Next 'Ocultamos el procedimiento Application.ScreenUpdating = False 'Creamos el objeto FileSystemObject Set fso = CreateObject("Scripting.FileSystemObject") 'Informamos de la ruta de donde vamos a obtener 'los ficheros, en este caso, el mismo directorio 'donde tengamos grabado este fichero con el macro ruta = ActiveWorkbook.Path 'definimos dos variables que necesitaremos, 'para recuperar el nombre de la carpeta, y 'los subdirectorios que haya dentro Set directorio = fso.GetFolder(ruta) Set subdirectorios = directorio.subfolders 'escribimos un encabezado en la celda C6 Range("C6").Select ActiveCell = "Subdirectorios del directorio:" 'lo ponemos en negrita y subrayado ActiveCell.Font.Bold = True ActiveCell.Font.Underline = xlUnderlineStyleSingle 'escribimos los subdirectorios ActiveCell.Offset(1, 0).Select For Each subdirectorio In subdirectorios 'escribimos el nombre del subdirectorio ActiveCell = subdirectorio.Name 'bajamos una fila ActiveCell.Offset(1, 0).Select Next 'Limpiamos los objetos Set fso = Nothing Set directorio = Nothing Set subdirectorios = Nothing 'Mostramos el procedimiento Application.ScreenUpdating = True End Sub

Si lo que deseamos es listar los nombres de los subdirectorios y los ficheros existentes en el directorio donde tenemos guardado el fichero activo, entonces el macro que tendremos que utilizar, será este:

Sub ficheros_y_subdirectorios_del_directorio() 'Si hay errores, que continúe On Error Resume Next 'Ocultamos el procedimiento Application.ScreenUpdating = False 'Creamos el objeto FileSystemObject Set fso = CreateObject("Scripting.FileSystemObject") 'Informamos de la ruta de donde vamos a obtener 'los ficheros, en este caso, el mismo directorio 'donde tengamos grabado este fichero con el macro ruta = ActiveWorkbook.Path 'definimos dos variables que necesitaremos, 'para recuperar el nombre de la carpeta, y 'los subdirectorios y ficheros que haya dentro Set directorio = fso.GetFolder(ruta) Set subdirectorios = directorio.subfolders Set ficheros = directorio.Files 'escribimos un encabezado en la celda D6 Range("D6").Select ActiveCell = "Subdirectorios del directorio:" 'lo ponemos en negrita y subrayado ActiveCell.Font.Bold = True ActiveCell.Font.Underline = xlUnderlineStyleSingle 'escribimos los subdirectorios ActiveCell.Offset(1, 0).Select For Each subdirectorio In subdirectorios 'escribimos el nombre del subdirectorio ActiveCell = subdirectorio.Name 'bajamos una fila ActiveCell.Offset(1, 0).Select Next 'a continuación escribimos los ficheros 'pero antes, escribiremos el encabezado ActiveCell = "Ficheros del directorio:" 'lo ponemos en negrita y subrayado ActiveCell.Font.Bold = True ActiveCell.Font.Underline = xlUnderlineStyleSingle 'pasamos a la siguiente fila ActiveCell.Offset(1, 0).Select For Each archivo In ficheros 'escribimos el nombre del fichero ActiveCell = archivo.Name 'bajamos una fila ActiveCell.Offset(1, 0).Select Next 'Limpiamos los objetos Set fso = Nothing Set directorio = Nothing Set subdirectorios = Nothing Set ficheros = Nothing 'Mostramos el procedimiento Application.ScreenUpdating = True End Sub

Desde aquí podéis descargar el fichero de excel, con el ejemplo que os presento en este artículo.

El otro día me preguntaba un lector del blog, como podía convertir por ejemplo 66 minutos, en formato horario, del tipo 1:06, y le contesté en otro comentario de este blog. Le contesté, complicándome la vida, porque a pesar de haberle dado una respuesta correcta, le dí la respuesta más complicada, ya que la propuesta que le sugerí incluía una larga fórmula.

Luego me acordé que en otro artículo de este blog, ya hablaba algo parecido. Concretamente cómo trabajar con horas, minutos, y segundos, y como controlar diferencias horarias, o como pasar de formato horario a decimal (y por lógica, sabiendo eso, también podemos pasar de formato decimal, a formato horario), por ejemplo. En otro artículo profundizaba sobre aluno de estos temas, y hablaba específicamente sobre como convertir unidades horarias, a formato decimal (por ejemplo, como pasar 7:30 h. a su valor decimal, que es 7,5).

El artículo del primer enlace, más la consulta de ese usuario que os mencionaba al principio, me han llevado a escribir este artículo donde explico como convertir una cantidad horaria (ya seas segundos, minutos, u horas), a su formato hh:mm:ss, de una forma muy sencilla, sin necesidad de aplicar fórmulas complicadas.

Aquí os dejo un pantallazo, para que os hagáis idea de lo que quiero transmitiros:


Si por ejemplo tenemos en la celda C7 un número de horas determinadas, y queremos pasarlas a formato hh:mm:ss, tan solo tendremos que aplicar ese formato de celda, a la celda donde queremos mostrar el resultado, y además, aplicar esta sencilla fórmula:

=C7/24

Si tenemos en la celda C8 un número de minutos determinados, y queremos pasarlos a formato hh:mm:ss, tan solo tendremos que aplicar ese formato de celda, a la celda donde queremos mostrar el resultado, y además, aplicar esta sencilla fórmula:

=C8/24/60

Si tenemos en la celda C9 un número de segundos determinados, y queremos pasarlos a formato hh:mm:ss, tan solo tendremos que aplicar ese formato de celda, a la celda donde queremos mostrar el resultado, y además, aplicar esta sencilla fórmula:

=C9/24/60/60

Para una cantidad de segundos, minutos, u horas, que sean igual o superior a las 24 horas, se computará un nuevo ciclo horario, como si se tratase de un nuevo día (partiendo de cero), por lo que el resultado no tendrá en cuenta los segundos, minutos, días acumulados con anterioridad. Por eso, que si trabajamos con horas que sean iguales o superiores al ciclo horario del día (24 h), los resultados que obtengamos pueden no ser correctos. En un comentario de este blog -no recuerdo si era en alguno de los dos artículos cuyos links que incluía al principio–, y en respuesta a otra pregunta que me hacía un usuario del blog sobre como trabajar con unidades de tiempo superiores a las 24 h., le daba una opción para solventar ese problema.

Desde aquí podéis descargar el fichero de excel, con el ejemplo que os presento en este artículo. Esta hoja de cálculo está protegida, para que solo podáis cambiar los datos de tres celdas, pero no tiene password, así que con un simple "intro" basta para desprotegerla, desde Herramientas, Proteger, Desproteger hoja….

Con el umbral de rentabilidad, inicio una serie de artículos, que de manera no periódica iré publicando, referidos específicamente a temas de gestión empresarial, y cómo solventarlos o como resolverlos, aplicando una sencillas fórmulas, funciones (o macros, si llegara el caso), con nuestra estupenda hoja de cálculo excel.

Sin entrar en analizar de forma exhaustiva, el concepto de umbral de rentabilidad (o también llamado, punto muerto), intentaré sintetizarlo, definiéndolo como el volumen de ventas, a partir del cual la empresa empieza a generar beneficios. Cuando una empresa vende 0 (cero) unidades, está perdiendo dinero, cuando vende 10 unidades de su producto, es muy probable que siga perdiendo dinero, y si vende 50.000 unidades, es probable que esté empezando a ganar dinero. El umbral de rentabilidad, nos determina el volumen de ventas donde la empresa obtiene un beneficio cero, es decir, es el punto exacto a partir del cual la empresa entra en beneficios (gana dinero).

Vamos a ilustrar el concepto de umbral de rentabilidad o punto muerto, con un gráfico, donde la línea azul indica el volumen de ingresos, al aumentar las ventas, y la línea roja, el volumen de gastos totales, al aumentar también las ventas.


Con excel, podemos determinar esta cifra de ventas de una forma muy sencilla. Antes de entrar en ello, cogeremos el concepto económico del umbral de rentabilidad, y lo analizaremos matemáticamente. Para ello, explicaremos antes el significado de una serie de variables que necesitaremos:

I = ingresos
C = cantidad vendida
p = precio de venta unitario (precio de cada unidad C)
G = gastos totales
GF = gastos fijos
GV = gastos variables
gv = gastos variables unitarios (gastos variables de cada unidad C)
R = resultado (beneficio o pérdida de la empresa)

Los costes variables son aquellos que varían, al cambiar la referencia sobre la que son estudiados. Normalmente, esta referencia, es el volumen de producción (o también el volumen de ventas), por lo que los costes variables son aquellos que varían al cambiar (aumentar o disminuir) la cantidad producida (o la cantidad vendida). En una empresa que vende productos, el coste de las materias primas es el coste variable más importante, aunque hay muchos más. Los costes fijos, son aquellos que permanecen invariables al variar la cantidad producida. Un ejemplo de coste fijo, es el coste de personal, pues el coste fijo de fabricar 10 unidades, será el mismo que el de fabricar 15 unidades, ya que el coste de personal, es un coste fijo sin dependencia directa respecto del volumen de producción (excepto por la parte del coste de las horas extras, que se consideraría un coste variable). En el ámbito interno de la empresa, los gastos fijos y los gastos variables, suelen denominarse costes fijos y costes variables.

Una vez definidas esas variables, entraremos a formular matemáticamente el concepto, antes de programar nuestra hoja de cálculo excel.

Sabemos que el resultado de una empresa, es la diferencia entre sus ingresos y sus gastos, por tanto:

R = I - G

Sabemos también que los ingresos, son igual a la cantidad vendida, por el precio de cada unidad:

I = C · p

Y que los gastos totales son la suma de los gastos fijos, más los gastos variables:

G = GF + GV

Los gastos variables son igual a la cantidad vendida, multiplicada por el coste variable de cada unidad:

GV = C · gv

Entonces, podemos sustituir todo eso, en la ecuación inicial, R = I - G:

R = (C · p) - (GF + (C · gv))

O lo que es lo mismo:

R = (C · p) - GF - (C · gv)

Como en el umbral de rentabilidad, el resultado es cero, entonces sustituimos R, por 0:

0 = (C · p) - GF - (C · gv)

Pasamos GF al lado izquierdo de la ecuación:

GF = (C · p) - (C · gv)

O lo que es lo mismo:

GF = C · (p - gv)

Es decir:

GF / (p - gv) = C

Ya tenemos la fórmula que nos determina, a partir de qué cantidad de unidades vendidas, empezaremos a tener beneficios. Cambiando los términos de la ecuación de lado, esta es la fórmula:

C = GF / (p - gv)

Como veis, el umbral de rentabilidad es un concepto económico muy sencillo, pero de gran utilidad, pues nos sirve para determinar el volumen de ventas que debe tener la empresa, para poder entrar en beneficios. Si sabemos que en el umbral de rentabilidad el resultado es cero, vendiendo una unidad más, la empresa entrará en beneficios, pues los ingresos son mayores que los gastos, como podéis ver en el gráfico anterior, mientras que vendiendo una unidad menos, la empresa estaría todavía en pérdidas, pues los gastos son mayores que los ingresos, como podéis ver también en el gráfico anterior.

He aquí un ejemplo de cálculo del umbral de rentabilidad, para el caso de una empresa que fabrica tres productos diferentes. He sintetizado mucho los conceptos, para que sea más inteligible, pero en la vida real, no te encontrarás con una partida de gastos fijos, así, sin detalle, sino todo lo contrario, te encontrarás con múltiples gastos de carácter fijo, que sumándolos, te permitirán obtener el total de los gastos fijos, y que será la cifra que necesitaremos. Puedes ampliar la imagen adjunta donde se muestra una tabla de excel con los resultados de nuestros cálculos, haciendo clic sobre ella:


Desde aquí podéis descargar el fichero de excel, con el ejemplo que os presento en este artículo.

Para alimentar con datos, un combobox, tenemos varias opciones:

a) Llenar el combo con los datos de determinado rango, y cuyas filas son siempre las mismas (por ejemplo, con los datos del rango E1:E10, con los datos de B12:B15, etc.). En este caso, sabremos que nuestro rango siempre tiene 10 filas, o 4 filas, o las que sean, pero sabremos que siempre es una cantidad fija en invariable.

b) Llenar el combo con los datos de un rango que puede crecer o decrecer, es decir, que en un momento dado el combo puede tener 3 elementos, y al cabo de cinco segundos puede tener 20 elementos. En este caso, estamos hablando de que nuestro combobox se alimenta de un rango dinámico.

Para insertar un combobox directamente en nuestra hoja de cálculo, sin tener que insertarlo en un UserForm, haremos lo siguiente: Desde excel, seleccionaremos Ver, a continuación Barras de herramientas, y seguidamente Cuadro de controles, tal y como aparece en la figura de la izquierda.

Seleccionaremos el control llamado Cuadro combinado (acercando el ratón a cada control, nos aparecerá el nombre de cada uno de ellos). Una vez seleccionado, dibujaremos el combobox en la hoja de cálculo, dándole el tamaño y posición que deseemos. Arriba a la izquierda, en la barra de fórmulas, nos aparecerá su nombre, …por defecto ComboBoxY, donde Y es un número correlativo, de tal forma que si es nuestro primer combobox, se llamará ComboBox1, si es el 2º, se llamará ComboBox2, y así sucesivamente.

Para el primer caso, es decir, si queremos llenar el combobox con los datos de un rango fijo, entonces lo tenemos bien fácil. Imaginemos que tenemos un rango con datos que va de I8 a I10, y queremos que esos valores almacenados en esas celdas (en las 3 celdas: I8, I9, e I10), sean los que aparezcan en nuestro combobox. Supongamos que estamos trabajando con un combobox que se llama ComboBox3. En ese caso, el código que podríamos utilizar sería este (estoy suponiendo que el combobox no está insertado dentro de un UserForm, porque en ese caso, el código sería algo distinto, ya que utilizaríamos RowSource, en lugar de usar ListFillRange), y desde la pantalla para VBA, lo pegaríamos en la hoja1, que es donde tenemos nuestro combobox, y no en un módulo:

Private Sub ComboBox3_GotFocus() 'Este código se ejecutará cada vez que 'nos situemos sobre el combobox ComboBox3.ListFillRange = "I8:I10" ComboBox.RowSource End Sub

Si por el contrario, el rango que alimenta el combobox es dinámico, deberemos añadir los elementos al combobox, mediante el método AddItem.

Supongamos por ejemplo que tenemos un libro con 3 hojas y queremos que en nuestro combobox nos aparezcan los nombres de esas tres hojas. En ese caso, el código del combobox (en este caso, nuestro ComboBox1) sería el siguiente (estamos haciendo un combobox directamente sobre nuestra hoja de cálculo, y no insertado dentro de un formulario), y que tendríamos que pegar desde VBA, en la hoja1, que es donde tenemos nuestro combobox, y no en un módulo:

Private Sub ComboBox1_GotFocus() 'Esto se producirá cuando nos 'situemos sobre el combobox1 '********* Llenamos el combobox1 ********* 'antes quitaremos todo lo que haya en el combobox1 ComboBox1.Clear 'Vamos a llenar dinámicamente el combobox 'con los nombres de las hojas For i = 1 To Sheets.Count 'Añadimos los nombres de las hojas al combobox ComboBox1.AddItem Sheets(i).Name Next End Sub

Con ese código, recorremos los nombres de las hojas, y se cargarán en el combobox, cada vez que éste reciba el foco (cada vez que nos situemos sobre él).

Si deseamos que se ejecute cierta acción, cada vez que elijamos un elemento de la lista de ese ComboBox2, añadiremos el código que proceda, también dentro de la hoja1. En nuestro caso, cada elemento corresponde a una hoja de cálculo del libro, y eso será lo que nos aparezca en el desplegable del combobox. Nosotros mostraremos un mensaje, aunque podríamos haber hecho cualquier cosa: ir a la hoja en cuestión, eliminar la hoja, copiarla, etc:

Private Sub ComboBox1_Click() 'Mostramos un mensaje MsgBox ("Has hecho clic sobre: " & ComboBox1.List(ComboBox1.ListIndex)) End Sub

Si lo que deseamos es llenar dinámicamente un combobox a través de los datos que hay en un rango de celdas contínuo, entonces haremos lo siguiente (suponemos que nuestro combobox se llama ComboBox2, y que está insertado directamente en la hoja de cálculo, y no en un formulario), pegándolo en la hoja1 que es donde tenemos nuestro combobox:

Private Sub combobox2_gotfocus() 'Ocultamos el procedimiento Application.ScreenUpdating = False 'fichamos la celda donde estamos, para volver a ella celda = ActiveCell.Address 'Esto se producirá cuando nos 'situemos sobre el combobox2 '********* Llenamos el combobox2 ********* 'antes quitaremos todo lo que haya en el combobox2 ComboBox2.Clear 'Vamos a llenar dinámicamente el combobox 'con los datos de la celda F8 en adelante '(siempre que los datos sean contínuos) Range("F8").Select Do While Not IsEmpty(ActiveCell) 'Añadimos los nombres de las hojas al combobox ComboBox2.AddItem ActiveCell 'bajamos una fila ActiveCell.Offset(1, 0).Select Loop 'volvemos donde estábamos Range(celda).Select 'Mostramos el procedimiento Application.ScreenUpdating = True End Sub

Si deseamos que se ejecute cierta acción, cada vez que elijamos un elemento de la lista de ese ComboBox3, añadiremos el código que proceda, también dentro de la hoja1. En nuestro caso, cada elemento corresponde a los datos que hay desde F8 en adelante (rango contínuo de datos), y eso será lo que nos aparezca en el desplegable del combobox. Nosotros mostraremos un mensaje, aunque podríamos haber hecho cualquier otra cosa:

Private Sub ComboBox2_Click() 'Mostramos un mensaje MsgBox ("Has hecho clic sobre: " & ComboBox1.List(ComboBox2.ListIndex)) End Sub

A modo de resumen, hoy hemos visto varias cosas nuevas:

a) Cómo crear un combobox, sin necesidad de insertarlo dentro de un UserForm.
b) Cómo llenar un combobox de forma dinámica, sin tener que tocar el código cada vez que cambien las dimensiones del rango de datos que lo alimentan.

Desde aquí podéis descargar el fichero de excel, con el ejemplo que os presento en este artículo.

Sois muchos los que llegáis a este blog buscando una solución que os permita controlar la hora de entrada y salida de los empleados de la empresa. En concreto, muchos usuarios buscan una hoja de cálculo que les permita obtener tanto el número de horas normales de la jornada laboral (horas ordinarias), como el número de horas extras. Pues bien, atendiendo a estas solicitudes, he preparado una hoja de cálculo, que podréis descargar al final de este artículo, y que servirá de ejemplo. Espero que esta hoja de cálculo cubra todas nuestras necesidades, a la hora de calcular jornadas laborales ordinarias, horas extras, y remuneraciones por horas extraordinarias.

La hoja de cálculo no contiene macros, sólo contiene fórmulas de excel, y además no son nada complejas. Quizás algunas de las fórmulas sean un poquito farragosas, y requieran algo de tiempo para interpretarlas, pero una vez asimiladas, veréis que el tema no era tan complejo. Fijaos bien en el formato que tienen las celdas. Algunas de ellas tienen formato de fecha, numérico, y otras formato personalizado.

En esta hoja de cálculo, tenemos una serie de datos de entrada:

En la celda D4 introduciremos la hora de entrada.
En la celda D5, haremos lo mismo pero con la hora de salida.
En la celda D6 introduciremos el número de horas dedicadas a comer.
En la celda D7, y con una simple fórmula, obtendremos la jornada laboral ordinaria (sin contar horas extras). Si introdujéramos un dato manualmente, en esta celda D7, y fuera un dato inconsistente, en la celda D8 nos aparecería un mensaje informándonos que el número de horas ordinarias no es correcto.
En la celda D10, introduciremos el nombre del empleado.
En la celda D11, informaremos el precio de la hora extra, ya sea bruto, o neto. Dependiendo de si hemos introducido el importe bruto, o neto, la cifra total obtenida por las horas extras trabajadas, evidentemente atenderá también a ese mismo criterio, bruto o neto.

En esta hoja de cálculo, podemos observar también varias columnas:

La columna B y C, corresponden a la fecha y hora de entrada del empleado. Deberemos informar de ello diariamente, bien introduciendo el dato manualmente, o importándolo a través de algún otro programa (con un macro podríamos automatizar esta tarea).
La columna D y E, corresponden a la fecha y hora de salida del empleado. Deberemos informar también introduciendo el dato manualmente, o importándolo a través de algún otro programa.
La columna F nos informa de la cantidad de horas normales (horas dentro de la jornada laboral ordinaria), y es una fórmula.
La columna G nos informa de la cantidad de horas extraordinarias (las horas que exceden del horario laboral normal), y es una fórmula.
La columna H nos informa de las horas totales trabajadas (suma de horas normales, más horas extras). Se trata de una simple fórmula que suma las dos columnas anteriores.
La columna I nos informa de las horas y fracciones, en números decimales (ya sabes que por ejemplo 7:30, es decir, siete horas y treinta minutos, son en números decimales 7,5 horas). Nos bastará con multiplicar la columna inmediatamente anterior, por 24 (aunque también le hemos añadido la coletilla " h." en el formato de la celda.
La columna J nos informa del coste de las horas extraordinarias, siempre y cuando hayamos informado del precio por hora, en la celda D23. En la celda E23 informaremos de la moneda (en nuestro ejemplo, euros). Dependiendo del tipo de moneda elegido, la columna J nos mostrará el importe de las horas extras, expresado en la moneda elegida. La columna J nos muestra las cifras en formato texto, así que no intentéis sumar los datos de la columna, porque no obtendréis ningún resultado. Si deseáis que los importes siempre sean tratados como números, tan solo tendríais que multiplicar la columna F de las horas extras, por el coste de cada hora extra, que es el que figura en la celda D11.

Finalmente, en la celda E25 tendríamos el número de horas que el empleado no ha justificado, es decir, el número de horas que el empleado ha faltado (se ha ausentado), dentro del horario laboral normal de la empresa. Esta cifra la obtendremos comparando las horas de la columna F, con el número de horas ordinarias que aparece en la celda D7.

Aquí os dejo un pantallazo de la hoja de cálculo. Si deseáis ver la imagen más grande, tan solo tenéis que cliquear sobre de ella:


Desde aquí podéis descargar el fichero de excel, con el ejemplo que os presento en este artículo.

Hoy os presento mi nuevo y caro juguete. Se trata de la iPaq 614C Business Navigator, de Hewlett-Packard. Un aparato que entre otras cosas, tiene:

• Pantalla táctil de 2,8", orientable vertical y horizontalmente.


• Teléfono.


• PDA: agenda, tareas, etc.


• Lleva Windows Mobile 6 professional, e incluye Excel mobile, Word mobile, y Power Point mobile, entre otras aplicaciones. En las PDA's con excel, no se pueden ejecutar macros -o al menos eso creo-, pero es lo de menos. Puedes llevar encima un montón de hojas de cálculo, por si tienes que pasárselas a algún cliente, proveedor, amigo, etc.


• Incorpora receptor de GPS, e incorpora la aplicación Google Maps. El problema es que cada vez que quieres calcular una ruta, debes conectarte a internet para hacer una consulta a la cartografía de Google Maps. A mí me ha llegado con el "Trip Guide" de HP, que incluye la cartografía de Tele Atlas. También venía con una batería extra de doble capacidad. Ambos "extras" son debidos a la oferta que HP tiene durante este mes de octubre.


• Cámara de fotos de 3 Mpx, con flash incorporado. También graba video, con su correspondiente audio.


• Reproduce música y video.


• Lleva Bluetooth.


• Lleva conexión wifi, por si te quieres conectar a internet "by the face" en la biblioteca, en algún centro público que emita en abierto, o simplemente para conectarte en casa a internet, desde la PDA, a través de tu router wifi

¿Teniendo esto, quien quiere un iphone?. En realidad el único modelo de la competencia que tiene la HP 614C, es la Asus P750, pero para mi gusto, esta es mejor. Al menos en mi caso, mi unidad carece de los problemas que han tenido algunos usuarios (fallos de wifi, problemas con el gps, etc.).

He aquí algunas fotos de la bestia:





Como véis en las dos últimas fotos, se aprecia una especie de circunferencia sobre el teclado. Se trata del "smart touch", que es una especie de touch pad, con la que puedes seleccionar las diferentes opciones que se te presentan en pantalla. Si queréis saber más sobre este aparato, os remito a la información oficial que tiene HP.

Solo hace 5 días que la tengo, pero de momento no tengo ninguna queja. Todo funciona como tiene que funcionar, aunque el punto débil es la batería (la normal), pues su duración, sin trastear mucho con la PDA, y con el Bluetooh y la Wifi desconectadas (se pueden deshabilitar, de la misma forma que se puede deshabilitar el teléfono), es de unos 2,5 días aproximadamente (apagándola por la noche).

En otras entregas, habíamos visto como proteger un libro de excel mediante la solicitud de un password, a través de un Inputbox, como proteger y desproteger hojas de cálculo sin macros, y como proteger y desproteger hojas de cálculo con macros. Hoy nos centraremos en como mostrar u ocultar hojas de cálculo, si se introduce correctamente o no, la contraseña solicitada.

Para usar el ejemplo de hoy, podíamos haber diseñado un Userform bien bonito, donde se le pidiera al usuario un password cada vez que quiere acceder a una hoja de cálculo determinada, pero para simplificarlo, he optado por utilizar un Inputbox, que nos hará las mismas funciones, con menos código, y con menos trabajo de “diseño”.

En este artículo, he incluido dos formas diferentes para proteger una hoja de cálculo. En el primer caso, el más sencillo, consiste en que cada vez que el usuario accede a la protegida (en el ejemplo, la Hoja2), se le presenta un Inputbox, solicitando una contraseña. Si el usuario no introduce ninguna contraseña, o introduce una contraseña incorrecta, el usuario es redirigido a la primera hoja (en nuestro caso, a la Hoja1). A través de este método de protección, el usuario ve parte del contenido de la Hoja3 (el Inputbox aparece en primer plano, delante del contenido de la Hoja3), pues ha accedido a ella, …solo que si quiere interactuar con la misma (ver todo el contenido, cambiar datos, imprimir, etc.), deberá introducir el password correcto.

El segundo caso, mucho más elegante, consiste en tener la hoja oculta (en nuestro caso, la Hoja3), y cada vez que queramos acceder a ella, se nos pida un password. En caso de no introducirlo, de introducirlo de forma incorrecta, no podremos acceder a la hoja. En el caso de introducir el password correcto, se mostrará la hoja de cálculo al usuario.


Para el primer método de protección, nos bastará con copiar y pegar este código en la Hoja2, desde VBA (no hay que copiarlo en un módulo, sino en la Hoja2):

Private Sub Worksheet_Activate() 'Este mensaje aparecerá cada vez que 'accedamos a la Hoja2 respuesta = InputBox("Introduce el password", "Password") 'si la respuesta introducida es distinta de "tariro-tariro" 'devolvemos al usuario a la Hoja1 (ojo, Hoja1 es el nombre 'interno de VBA, no el nombre visible de la pestaña) If LCase(respuesta) <> "tariro-tariro" Then 'Vamos a la Hoja1 Hoja1.Activate End If End Sub

Para el segundo método de protección, deberemos copiar y pegar dos macros en un módulo VBA. El primero consiste en ocultar de forma muy estricta, la Hoja3, cada vez que abramos el fichero de excel, para lo cual crearemos el macro llamado Auto_open():

Sub Auto_open() 'Ocultamos la Hoja3 Hoja3.Visible = xlSheetVeryHidden End Sub

El otro macro que tendremos que copiar es este que os incluyo a continuación (hay que copiarlo en un módulo). El macro está pensado para asociarlo a un botón, para que se ejecute, y que nos pida el password, y su funcionamiento, como veréis al leer el código fuente, que está comentado, es realmente sencillo:

Sub Ir_a_la_hoja3() 'Este mensaje aparecerá cada vez que 'accedamos a la Hoja3 respuesta = InputBox("Introduce el password", "Password") 'si la respuesta introducida es distinta de "tariro-tariro" 'devolvemos al usuario a la Hoja1 (ojo, Hoja3 es el nombre 'interno de VBA, no el nombre visible de la pestaña) If LCase(respuesta) <> "tariro-tariro" Then 'Vamos a la Hoja1 Hoja1.Activate 'Ocultamos la Hoja3 Hoja3.Visible = xlSheetVeryHidden Else 'Mostramos la Hoja3 Hoja3.Visible = True 'Vamos a la Hoja3 Hoja3.Activate End If End Sub

Como complemento, en el fichero que os podéis descargar y que contiene todo este código, se incluye un tercer macro, que sirve para asociar el primer método de protección a un botón (el método que nos presenta el Inputbox, al acceder a la Hoja2):

Sub Ir_a_la_hoja2() 'Vamos a la Hoja2 Hoja2.Select End Sub

Evidentemente, como siempre que hagamos un macro, deberemos proteger con un password, el módulo VBA, para salvaguardarlo de los curiosos. Desde aquí podéis descargar el fichero de excel, con el ejemplo que os presento en este artículo.

Muchos de vosotros trabajáis en red, y compartís determinados ficheros que hay alojados en vuestro servidor (o en alguna carpeta compartida), con varios usuarios. A veces os interesa saber quien le ha "metido mano" al mismo, para depurar responsabilidades, o simplemente para tener constancia de todas aquellas personas que han aportado algo a la realización del trabajo conjunto.

Para poder controlar eso, he creado un pequeño macro que se ejecutará justo unos instantes antes de salvar (o guardar) el fichero. El macro nos registrará el PC del usuario, y la fecha, y la hora en que ha guardado los cambios que haya introducido en el fichero. Todos estos cambios, se grabarán en una hoja oculta (en el ejemplo que podéis descargar al final de este artículo, se graba en la Hoja3). Esta hoja se hará visible antes de grabar los cambios, y se volverá invisible, una vez hayamos guardado los datos correspondientes a la persona que ha manipulado el fichero, pero en ningún momento la hoja será visible para el usuario.

El código que tenéis que copiar y pegar en un módulo, es este:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) '***************************************** 'Pondremos la fecha de las modificaciones, ' antes de que se guarde el fichero '***************************************** 'Ocultamos el procedimiento Application.ScreenUpdating = False 'Mostramos la hoja3, que será donde grabaremos 'todos los datos de modificación del fichero Hoja3.Visible = xlSheetVisible 'Seleccionamos la hoja3 Hoja3.Select 'Nos situamos en B4 Range("B4").Select 'Bajamos una fila, hasta encontrar una vacía Do While Not IsEmpty(ActiveCell) ActiveCell.Offset(1, 0).Select Loop 'Escribimos los datos, siempre y cuando las 'celdas donde escribiremos los datos, estén vacías ActiveCell = Application.UserName ActiveCell.Offset(0, 1) = Date ActiveCell.Offset(0, 2) = Hour(Now) & "h. " & Minute(Now) & "m. " & Second(Now) & "s." 'Ocultamos la hoja3, donde hemos grabado 'todos los datos de modificación del fichero Hoja3.Visible = xlSheetVeryHidden 'Pasamos a la hoja1 Hoja1.Select 'Mostramos el procedimiento Application.ScreenUpdating = True End Sub

Desde aquí podéis descargar el fichero de excel, con el ejemplo que os presento en este artículo.

Quizás te hayas preguntado alguna vez, como podrías seleccionar un dato de un desplegable, y que al lado salga su foto, es decir, que salga la foto asociada al elemento seleccionado del desplegable. Esto es especialmente útil por ejemplo, si tenemos un listado desplegable de productos o de empleados, y queremos que al seleccionar cada empleado o artículo, nos salga al lado la foto de cada uno de ellos.

¿Verdad que parece un tema interesante?. Pues nada, vamos a tratar de explicarlo, porque no tiene mucho secreto.

Lo primero que tendremos que hacer, es que las imágenes (fotos de los empleados, de los productos, etc.) sean todas del mismo tamaño, para que no se muestren unas imágenes grandes y otras pequeñas, y nos quede poco elegante. Además, es importante que las fotos no tengan espacios vacíos, ni acentos (tampoco los elementos del desplegable), para que los usuarios de todos los países puedan utilizar el código sin problemas. En lugar de utilizar espacios vacíos, usaremos guiones. Por ejemplo, si tenemos un listado de artículos, y hay uno que se llama Bidones de 50 litros, a la foto le pondremos como nombre Bidones-de-50-litros.jpg. Lo recomendable -para tener un código bastante simple, como el que os muestro en este artículo-, es que las fotos tengan todas la misma extensión, …o todas "jpg", o todas "gif", o todas "png", etc. En el caso de que utilicéis otra extensión distinta a "jpg", tendréis que cambiar la línea del código fuente que veréis al final de este artículo, para que en su lugar aparezca "gif", o la extensión que utilicéis para vuestras imágenes.

Las fotos y el libro de excel con el desplegable, los guardaremos en la misma carpeta o directorio. Si estuvieran en directorios diferentes, deberíamos modificar el código VBA para informar de la ruta correcta de las imágenes.

Vamos a explicarlo con un ejemplo. Yo he utilizado una serie de vehículos clásicos (de los cuales soy un apasionado, todo sea dicho de paso), de tal forma que cada vez que seleccionemos uno de los coches, nos saldrá debajo su foto correspondiente. Si por ejemplo seleccionases en el desplegable, el Golf GTI mk1, aparecería esto en tu pantalla:


Y si seleccionases por ejemplo el Seat 124 Sport, aparecería esto de forma automática:


Lo primero será crear en la Hoja2, un listado con los coches que tenemos. En este caso tenemos 5 vehículos, tal y como se muestra en la imagen siguiente:


Ahora, le pondremos un nombre al rango de datos comprendido entre B4 y B8, para lo que seleccionaremos ese rango, y definiremos un nombre para el mismo. En este caso, le he puesto como nombre coches, tal y como se puede ver en la zona enmarcada en rojo, en la siguiente imagen (para definir un nombre, la forma más sencilla es escribir el nombre en esa zona enmarcada en rojo, y pulsar intro):


Ahora ya sabemos que el rango de celdas que hay entre B4 y B8 de la Hoja2, se llama coches.

A continuación pasaremos a la Hoja1, y por ejemplo, en la celda C4, crearemos la lista desplegable con los coches, para que cada vez que seleccionemos un modelo, nos salga debajo su foto correspondiente. Para ello, seleccionaremos en el menú Datos, la opción Validación…, y en la pestaña Configuración, que es la que nos saldrá por defecto en primer plano, elegiremos en Permitir la opción Lista, y en Origen introduciremos el nombre definido anteriormente, precedido del signo igual, tal y como se muestra en la siguiente imagen:


De tal forma que tendremos algo como esto que se muestra en la siguiente imagen, al pulsar en la flechita del desplegable:


Una vez hecho esto, solo nos quedará copiar y pegar el código que hará que se nos muestre la foto, pero no lo incluiremos en un módulo, sino dentro de la Hoja1, tal y como se muestra en esta imagen:


El código que tenemos que copiar y pegar en la Hoja1, es este:

Private Sub Worksheet_Change(ByVal Target As Range) 'Si ha errores, que continúe On Error Resume Next 'Si cambiamos el dato de la celda C4, 'mostramos la foto de ese vehículo If Target.Cells = Range("C4") Then 'Ocultamos el procedimiento Application.ScreenUpdating = False 'pasamos a una variable, el nombre de la foto, 'que será el mismo que el nombre del coche, pero 'separado con guiones, y sin acentos, para que 'todos los usuarios puedan verlo correctamente foto = Range("C4").Value 'en la foto, reemplazamos los espacios, por guiones foto = Replace(foto, " ", "-") 'ahora le añadimos la extensión "jpg" foto = foto & ".jpg" 'ahora buscamos la foto en el mismo directorio 'donde tenemos este fichero de excel ruta = ActiveWorkbook.Path & "\" & foto 'borramos la foto del coche (si hubiera alguna) Me.Shapes("foto_del_coche").Delete 'creamos el objeto fotografia, con la foto insertada Set fotografia = Me.Pictures.Insert(ruta) 'haremos que la foto ocupe desde B6 hasta D21, 'para que no salgan fotos supergrandes, o 'superpequeñas, y salgan más "normalitas" With Range("B6:D21") Arriba = .Top Izquierda = .Left Ancho = .Offset(0, .Columns.Count).Left - .Left Alto = .Offset(.Rows.Count, 0).Top - .Top End With 'le ponemos un nombre al objeto "fotografia" 'para poder borrarla cuando cambie la celda D6 '(ver que borramos la foto que hubiese, antes de insertar la nueva) With fotografia .Name = "foto_del_coche" .Top = Arriba .Left = Izquierda .Width = Ancho .Height = Alto End With 'eliminamos el objeto Set fotografia = Nothing 'ponemos todo como estaba Application.ScreenUpdating = True End If End Sub

Ese código lo que hace es insertar la foto, de cada uno de los coches, cada vez que seleccionemos uno de ellos. Para ello, debemos tener presente una serie de variables que he utilizado, para simplificar el código VBA:

1.- Las fotos y el fichero deben estar en el mismo directorio.
2.- Las fotos y los modelos de vehículos, deben tener el mismo nombre (en ambos casos sin acentos, y en el caso de las fotos, sustituyendo los espacios por guiones).
3.- Las fotos deben tener todas la misma extensión (en el código verás que aparece por ahí la extensión ".jpg").

Desde aquí podéis descargar el fichero de excel, con el ejemplo que os presento en este artículo, junto con las imágenes de los vehículos clásicos, para que podáis ver en funcionamiento como se crean listas de validación con imágenes asociadas a cada elemento del desplegable.