martes, 19 de junio de 2012

VBA: Vincular datos en Excel con la hoja anterior.

En un foro, en el que suelo participar, contesté una cuestión que me pareció interesante compartir en el blog. Se trata de vincular una referencia de una celda con un dato de la hoja inmediata anterior. En definitiva el fin es vincular no a una hoja concreta, si no a la hoja de Excel anterior (a la hoja situada a la izquierda).
Esto en principio no es tan sencillo de conseguir con la formulación estándar, ya que al crear un vínculo sobre una Hoja de cálculo se hace sobre el nombre de la hoja y la celda; por ejemplo, un vínculo sobre la celda A1 de la Hoja1, se consigue, en cualquier otra hoja del libro así:
=Hoja1!A1
y siempre 'apuntará' hacia esa Hoja y celda.

Sin embargo, podemos trabajar una función personalizada en VBA que nos permite 'apuntar' el vínculo a la hoja anterior.
Nos aprovecharemos de una instrucción de VBA como ActiveSheet.Index que nos dice en qué hoja (numerada) nos encontramos; esto será una ventaja para localizar cúal es la Hoja anterior.


Supongamos un Libro de trabajo con siete hojas (Lunes, Martes, Miércoles, Jueves, Viernes, Sábado y Domingo); sobre alguna de las cuales quiero añadir un vínculo sobre otra hoja; por ejemplo, en la Hoja 'Domingo' quiero añadir un vínculo de la celda A1 de la Hoja anterior, que en esta primera situación sería la Hoja 'Sábado', pero que en cualquier momento podría ser, por que movamos las Hojas, la Hoja 'Jueves' o 'Lunes'; aquí es donde no es muy práctico el enlace: 'Sábado!A1', ya que este vínculo no se vé afectado por la movilidad de las hojas.
Para ello construimos una función que nos sitúa en cada momento en la Hoja activa, en particular en el orden (1, 2, 3, ...) que le corresponde a dicha hoja activa, el resto es sencillo, ya que como resultado de nuestra función personalizada, obtendremos el vínculo a la celda A1 de la hoja que en orden sea una menor, esto es, la anterior.

Insertaremos en un módulo del Explorador de proyectos del Editor de VBA el siguiente código, formando nuestra función VBA de Excel:

Function RefNumHoja()
'permitimos que la función actualice cada vez haya cambios en la hoja
Application.Volatile
Dim NumHoja As Long
'averiguamos la hoja activa
NumHoja = ActiveSheet.Index
    'evitamos un error para el caso de que estemos en la Hoja número 1..
    If NumHoja = 1 Then
    RefNumHoja = "Ojo es la primera hoja!!!"
    Else
    RefNumHoja = Sheets(NumHoja - 1).Range("A1").Value
    End If
End Function


Si ejecutamos esta nueva función 'RefNumHoja' en alguna de las celdas de cualquiera de las siete Hojas de nuestro libro, veremos cómo siempre nos devuelve el valor de la celda A1 de la Hoja inmediatamente anterior (menos en el caso que estemos en la Hoja número 1, que devuelve el texto definido).
Además hemos asignado el método Volatile para facilitar la actualización de la función ante cambios en la hoja donde se encuentre, o cada vez que forcemos la actualización de la hoja presionando F9.

6 comentarios:

  1. Me a sido de mucha ayuda esta informacion gracias, pero aun me queda una duda, quiero vincular datos de una hoja inmediata anterior pero no de una celda si no de un rango de celdas C5 a C11 por ejemplo, hay forma de hacerlo sin necesidad de crear un modulo por celda?

    ResponderEliminar
    Respuestas
    1. Hola Alex, bastaría implementar una ligera modficación a la Function expuesta:

      Function RefNumHoja(celda As Range)
      'permitimos que la función actualice cada vez haya cambios en la hoja
      Application.Volatile
      Dim NumHoja As Long
      'averiguamos la hoja activa
      NumHoja = ActiveSheet.Index
      'evitamos un error para el caso de que estemos en la Hoja número 1..
      If NumHoja = 1 Then
      RefNumHoja = "Ojo es la primera hoja!!!"
      Else
      RefNumHoja = Sheets(NumHoja - 1).Range(celda.Address).Value
      End If
      End Function

      Así, simplemente vinculas a una celda.

      Espero te sirva.
      Cordiales saludos

      Eliminar
  2. Hola Ismael, yo estoy buscando algo parecido!

    Expongo la situación:
    Tengo una columna en una hoja de excel que me muestra los datos contenidos en una hoja de otro documento En cada casilla de la columna pone (porque lo he puesto yo)= ruta\[nombre documento]nombre hoja y me devuelve el contenido. El nombre de la hoja corresponde a un día de entrada de datos.

    Pues bien, al día siguiente, cuando quiero que la columna de al lado me devuelva los datos de la nueva hoja con los datos del siguiente día hago un copiar y pegar, o inserto la columna copiada. Así tengo los mimos datos en dos columnas. Entonces tengo que ir a la columna nueva a cambiar el nombre de la hoja en cada casilla.

    Ahi va la cuestión, ¿Como puedo hacerlo para que coja los datos directamente de la hoja nueva y no tener que modificar cada casilla?

    ResponderEliminar
    Respuestas
    1. Hola Enrique,
      se me escapa un poco el sentido, pero por lo que parece, en ese copiado y pegado de columna, pegas un literal:
      ruta\[nombre documento]nombre hoja
      quizá asociando y contruyendo esa ruta a partir de un concatenado vinculado con diferentes celdas, podrías dirigir las nuevas hojas a los datos correctos.

      Si quieres envíame a
      excelforo@gmail.com
      el fichero y le echo un vistazo.

      Un saludo

      Eliminar
  3. HOLA ISMAEL buen dia como hago para combinar las dos funciones "buscar" y " refnumhoja" en una celda osea buscar datos en la hoja anterior de la que estoy
    agradeceria su repuesta

    ResponderEliminar
    Respuestas
    1. Hola Juan Miguel, obviamente no hace falta usar esta función para BUSCAR en otras hojas.. pero supongo necesita automatizar la búsqueda justo en la hoja 'anterior' (cuidado con añadir la fórmula en la primera!!).

      En todo caso, yo haría los siguientes cambios en la función:
      Function RefNumHoja(rango As Range)
      'permitimos que la función actualice cada vez haya cambios en la hoja
      Application.Volatile
      Dim NumHoja As Long, celda As Range
      'averiguamos la hoja activa
      NumHoja = ActiveSheet.Index
      'evitamos un error para el caso de que estemos en la Hoja número 1..
      If NumHoja = 1 Then
      RefNumHoja = "Ojo es la primera hoja!!!"
      Else
      RefNumHoja = Range(Sheets(NumHoja - 1).Name & "!" & rango.Address)
      End If
      End Function


      Luego basta apliques la función de BUSCAR normalmente (recuerda debe estar ordenado el vector comparación en A a Z), por ejemplo, en la Hoja 2 insertas:
      =BUSCAR("x3";RefNumHoja(D4:D8);RefNumHoja(E4:E8))
      lo que buscará 'x3' en el rango D4:D8 de la hoja anterior...

      Espero te oriente
      Saludos cordiales

      Eliminar