Hipervínculos dinámicos dentro de un cuaderno Excel

Hemos tratado el tema de los hipervínculos en Excel con bastante detalle (o como dicen algunos de mis más ibéricos lectores, "rizando el rizo"). Pero siempre queda algo en el tintero (o en rioplatense, "otra vuelta de tuerca").
En una nota pasada discutimos el tema de las referencias en los hipervínculos (absolutas y relativas). Pero, como decíamos, siempre queda algún detalle por tratar.

Por ejemplo, un lector me comenta que tiene un cuaderno con una hoja que sirve de plantilla. Esta hoja contiene hipervínculos a celda dentro de la misma hoja. Cada vez que crea una nueva hoja en el cuaderno a partir de la plantilla, los hipervínculos siguen refiriéndose a la plantilla y no a la nueva hoja.

Esto se debe que al crear el hipervínculo con Inserta-Vínculos-Hipervínculo (o con el menú contextual, o con el atajo de teclado), Excel guarda la información como texto que incluye la referencia a la hoja.

La solución es usar la función HIPERVINCULO con un pequeño truco. Si queremos que el hipervínculo apunte siempre a la celda E5, por ejemplo, de la hoja activa, usamos esta fórmula

=HIPERVINCULO("#E5","texto en la celda")

Al usar el símbolo # en la referencia a la celda, Excel apunta siempre a la celda en la hoja donde se encuentra la fórmula.

En este video, la hoja Plantilla contiene dos hipervínculos que apuntan a la celda E5. Uno creado con con Ctrl+Alt+K (en la celda B2, con el texto "estático") y el otro creado con la función HIPERVINCULO (en la celda B4, con el texto "dinámico"). Creamos una nueva hoja (Mover o Copiar-Crear Copia). Al pulsar el hipervínculo en la celda B2, volvemos a la celda E5 en la hoja Plantilla; al hacerlo en B4, llegamos a E5 en la hoja activa.

Encontrar la última celda en una hoja de Excel

Una de las tareas frecuentes al escribir código Vba, muchas veces al modificar código de una macro grabada, es tener que determinar la última fila (o celda o columna) usada en la hoja.
Si nos basamos en una macro que hemos grabado al usar el método Ir-Especial-Última celda,

veremos esta sentencia

Range("A1").SpecialCells(xlCellTypeLastCell).Select

El problema con este método es que Excel recalcula la ubicación de la última celda usada sólo cuando guardamos el archivo. Esto significa que si ingresamos un valor en una celda remota y luego lo borramos, Excel seguirá considerando esa celda como la última. Y como si esto no fuera poco, también si cambiamos el formato de una celda remota, aún si ingresar ningún valor, Excel la considerará la última celda usada.

Para asegurarnos de encontrar siempre la última celda usada, es decir, la celda más remota que contiene algún valor, tenemos que usar un código basado en el método Range.Find

Sub ultima_celda()
    Dim UltimaFila As Long

    If WorksheetFunction.CountA(Cells) > 0 Then

        UltimaFila = Cells.Find(What:="*", After:=[A1], _
              SearchOrder:=xlByRows, _
              SearchDirection:=xlPrevious).Row

         MsgBox UltimaFila, vbInformation, "Ultima fila"

    End If

End Sub

Este código encuentra la última celda y nos informa cuál es la fila con un mensaje.

Este video nos muestra la diferencia entre ambos métodos

Manejo de fechas y tiempo en Excel.

En los albores de este blog, allí por el año 2006, publiqué una nota sobre cómo Excel maneja los datos de tiempo (horas y fechas). Por cuestiones que no vienen al caso, esa nota ya no está disponible. Si bien el tema resultará muy elemental para muchos de mis lectores, recibo no pocas consultas relacionadas con dificultades para realizar cálculos de horas o fechas en Excel.  Por lo que he resuelto reeditar la nota.

La forma en que Excel maneja las fechas y horas no es intuitiva, en particular por el hecho que cuando ingresamos una fecha en  una celda, o una hora, lo que Excel nos muestra es distinto de lo que Excel "ve".

width

Cuando ingresamos en una celda un valor que Excel interpreta como fecha (por ejemplo: 30/05/2013), en la celda y en la barra de fórmulas veremos "30/05/2013", pero si cambiamos el formato de la celda a "General" veremos el número 41424

Esto se debe a que en Excel las fechas son números de una serie. En esta serie el 1 representa el 1ro. de enero de 1900, el 2 el 02/01/1900 y así sucesivamente. Desde el 01/01/1900 al 30/05/2013 han transcurrido 41424 días.

De la misma manera podemos ingresar un número de la serie en la celda y  aplicar el formato de fecha para verla como tal

La primer fecha en la serie corresponde al 0 (el 00/01/1900). El motivo de la existencia de la fecha inexistente 00/01/1900 es representar horas que no están asociadas a una fecha en particular.

Esto último nos lleva a ver cómo Excel maneja las horas. Los datos horarios son la parte decimal del número que representa la fecha. Por ejemplo, las 12:00 del 30/05/2013 es el número 41424.5 (o 41424,5 para los países que usan la coma como separador de decimales).  Como en el ejemplo anterior podemos ingresar el número como tal y al cambiar el formato a fecha-hora, veremos

Los números de las horas resultan de dividir 1 por la cantidad de horas de un día (1/24).

Los números de la serie de minutos resultan de dividir 1 por la cantidad de minutos que hay en un día (24 X 60 = 1440).

Los segundos: 1/ 86400  (24 X 60 X 60 = 86400)

Cuestiones a tener en cuenta:

1 -  Excel convierte en fechas/horas todo dato que puede ser interpretado como tal. Por ejemplo, si tenemos un número de catálogo "25-4-2015", Excel lo convertirá en esa fecha y no en un código alfanumérico, que era nuestra intención

2 – Si ingresamos una fecha con una hora asociada mayor de 24 horas, Excel incrementa automáticamente la fecha al día siguiente. Por ejemplo, si ingresamos "30/05/2013 25:00" Excel lo convertirá automáticamente en "31/05/2013 01:00:00"

3 – Para obtener sumas de tiempos mayores a 24 horas sin que Excel los convierta en fecha + horas tenemos que usar el formato personalizado "[hh]:00"¨.

4 – La forma en que Excel reconoce como fechas depende de las definiciones regionales del sistema. Por ejemplo, en los Estados Unidos el valor "04/12/2012" será interpretado como el 12 de abril del 2012; en la Argentina como el 4 de diciembre del 2012.

5 – Excel usa ciertas reglas para determinar el siglo cuando ingresamos fechas usando solamente dos dígitos para el año. Por ejemplo, "05-12-29" será convertido en "05-12-2029"; "05-12-30" en "05-12-1930".