miércoles, 19 de diciembre de 2012

VBA: Una función personalizada para mostrar el Número de página en una celda.


Hace bastante tiempo mostré cómo conseguir identificar el número de página impresa directamente en una celda de nuestra Hoja de cálculo; mediante funciones macro 4.0 de Excel conseguíamos identificar en una celda la numeración de la hoja correspondiente al área de impresión (ver).

Hoy veremos otra forma de obtener el mismo resultado, de una manera algo más sencilla, mediante una función personalizada en VBA.


La idea sigue siendo la misma, para una hoja con un área de impresión definida, con diferentes saltos de página insertados, mostrar en la misma hoja de cálculo, en las celdas correspondientes, cuál es el número de página de un total de ellas.
Partiremos de una hoja con diferentes saltos de página:

VBA: Una función personalizada para mostrar el Número de página en una celda.



Insertamos el siguiente código VBA de nuestro procedimiento Function en un módulo del Explorador de proyectos del Editor de VBA:

Function InfoPag()
Dim iPags As Integer, iPag As Integer
Dim iCols As Integer, iCol As Integer
Dim lfilas As Long, lfila As Long
Dim x As Long, y As Long

Application.Volatile
Dim direccion As Range
'controlamos la dirección de la celda donde se encuentra la función
Set direccion = Range(Application.Caller.Address)

'contamos saltos de página (horizontales y verticales)
With ActiveSheet
    lfilas = .HPageBreaks.Count
    iCols = .VPageBreaks.Count
End With
'calculamos el número total de páginas
iPags = (lfilas + 1) * (iCols + 1)

With ActiveSheet
    'recorremos columnas hasta que encontramos entre qué saltos verticales está.
    y = direccion.Column
    x = 0
    Do
        x = x + 1
    Loop Until x = iCols Or y < .VPageBreaks(x).Location.Column
    iCol = x
    If y >= .VPageBreaks(x).Location.Column Then
        iCol = iCol + 1
    End If
    
    'recorremos columnas hasta que encontramos entre qué saltos horizontales está.
    y = direccion.Row
    x = 0
    Do
        x = x + 1
    Loop Until x = lfilas Or y < .HPageBreaks(x).Location.Row
    lfila = x
    If y >= .HPageBreaks(x).Location.Row Then
        lfila = lfila + 1
    End If
    
    'Dirigimos y calculamos el número de pagina, según este la configuración de pagína
    'bien Hacia abajo y luego hacia la derecha
    'bien Hacia la derecha y luego abajo
    If .PageSetup.Order = xlDownThenOver Then
        iPag = (iCol - 1) * (lfilas + 1) + lfila
    Else
        iPag = (lfila - 1) * (iCols + 1) + iCol
    End If
End With
'devolvemos el valor a la hoja de cálculo
InfoPag = iPag & " de " & iPags
Set direccion = Nothing
End Function



El resultado es el esperado, obtenemos en nuestras celdas la numeración de página respecto del total de páginas dentro del área de impresión.
Ojo, la función está definida bajo el supuesto que existe al menos un salto vertical y uno horizontal!!!. En caso contrario devolvería un error.
Para controlar esta situación habría que condicionar el código para el caso que .HPageBreaks.Count y .VPageBreaks.Count fueran cero...

18 comentarios:

  1. Hola Compañero.

    Tengo una macro que, a partir de una hoja índice, copia los elementos de cada hoja de excel que le indica el índice hasta que encuentra FIN.
    En el índice le indico mediante SI o NO si quiero o no que se pasee por esa hoja.

    Estaba buscando indicar el número de página en una columna (es decir, todos los elementos tendrían un número de página (num_pag_aux), de forma que pueda crear un índice, pero no he encontrado manera de hacerlo.
    ¿Habría la posibilidad de hacerlo?
    Antes lo que hacía era pasar a Word y allí paginar (he creado en Excel título1 (en el índice, no hay hojas con este nombre, pero pone ese título y salta a la siguiente celda), título2 (el nombre de la hoja) con diferentes tamaños que luego en word al recorrerlos el tamaño lo pasa a titulo1, título2 o normal), pero ahora tengo unos 5.000 artículos y la verdad es que... bueno, digamos que no hay manera porque se equivoca demasiado en word.

    ¿Podrías echarme una mano?

    Gracias y saludos

    ResponderEliminar
    Respuestas
    1. Hola,
      la función del post lo que hace es indicar en qué posición del total de páginas impresa se sitúa, por lo que entiendo quieres construir un Índice paginado (como en Word)... por tanto, una manera sería en las diferentes paginas de trabajo colocar esta función y luego en la página Principal (Indice) referirte a cada celda con función...

      No sé si te he entendido correctamente
      :-(

      Sdls

      Eliminar
  2. Creo que has entendido la idea general, pero la cuestión es que yo tengo una hoja índice (hoja_index), muchas hojas (hoja_1, ... hoja_n) y una hoja final (hoja_final).

    Hoja_n tienen información en las 6 primeras columnas, pero a mí sólo me interesa copiar 4. La última fila, en la primera columna pone FIN, de forma que tengo un indicador de que he llegado al final.

    Hoja_final está inicialmente vacía (o aunque esté llena, la macro lo primero que hace es vaciarla) y ES ESTA HOJA LA ÚNICA QUE QUIERO IMPRIMIR EN PDF INDICANDO UN ÍNDICE, por tanto, es aquí donde me gustaría indicar índice (índice que tengo y puedo sacar, ver siguiente)

    Hoja_index tiene 2 columnas (tiene más, pero que interesen para ponerte al caso, sólo 2). En 1 indico un nombre (Titulo_general, Título_particular, Hoja_n), en la otra indico una de estos 4 valores:
    -SÍ (quiero que vaya a la hoja, es decir, existe una hoja con el nombre de esa celda, y la recorre para copiar sus datos en Hoja_final)
    -NO (me la salto, no quiero usarla pero en un futuro o en otra ocasión, sí que quiero recorrerla)
    -TÍTULO1 (copia solo las 2 celdas del índice en Hoja_final y a tamaño mucho mayor, inicialmente lo hice para poder indexar en word)
    -TÍTULO2 (copia solo las 2 celdas del índice en Hoja_final y a tamaño mayor, inicialmente lo hice para poder indexar en word)

    La modificación "facil" que podría hacer de la macro es:
    1-copiar el índice que quiero (en vez de recorrer cada hoja, sólo poner el índice, está chupado hacerlo porque ya está implementado)
    2-hacer lo que hacía hasta ahora la macro (es decir, poner de nuevo los títulos, nombre de hojas y contenido de hojas)
    3-poner el número de página en el índice creado en (1), para ello podría ponerse en todas las filas, en una columna auxiliar, el número de página, y luego, dado que no hay ningún nombre de página repetido, buscar en el ´´indice esa palabra en toda la hoja y devolverle en la columna que elijamos el número de página...

    No sé si me he explicado correctamente... O quizá prefieras te envíe una parte del archivo para que puedas entender lo que hago hasta ahora para entender qué es lo que quiero hacer

    ResponderEliminar
    Respuestas
    1. Hola,
      bueno... creo que yo optarí apor la modificación 3.
      De todas formas si quieres puedes enviarme el fichero a
      excelforo@gmail.com

      Slds

      Eliminar
  3. Muchas gracias por la ayuda.

    Finalmente encontré esta respuesta por internet que estoy integrando en el programa:

    Sub DefineNombres()
    Names.Add Name:= "Paginas", RefersToR1C1:= "=GET.DOCUMENT(50)"
    Names.Add Name:= "PaginadePaginas", RefersToR1C1:= "=""Pagina "" & IF(ISNA(MATCH(ROW(), GET.DOCUMENT(64),1)),1, MATCH(ROW(), GET.DOCUMENT(64),1)+1) & "" de "" & GET.DOCUMENT(50) + 0*now()"
    Names.Add Name:= "Pagina", RefersToR1C1:= "=IF(ISNA(MATCH(ROW(), GET.DOCUMENT(64),1)),1, MATCH(ROW(), GET.DOCUMENT(64),1)+1)"
    End Sub

    despues basta con teclear en cualquier celda:

    =pagina
    =paginas
    =paginadepaginas

    ResponderEliminar
    Respuestas
    1. Hola Mérida,
      esa opción la podías haber aplicado siguiendo el link de la primera línea de esta entrada.
      http://excelforo.blogspot.com.es/2012/03/numero-de-pagina-en-una-celda-de-excel.html

      pero me alegro dieras con ella.
      Un cordial saludo

      Eliminar
    2. Hola Merida, hola buen dia, acabo de toparme con esto, y es algo que me es útil, pero, puse el código tal cual, y lo que hace es ponerme en todas las hojas, el mismo dato, ose a no va cambiando de acuerdo al numero de hoja.
      Si me pueden apoyar por favor.

      Intente también el método sugerido por Ismael, pero, se me hace mas extenso y complicado.
      gracias

      Eliminar
    3. Hola...
      es más simple emplear el código de la función de este mismo post...
      Un saludo!

      Eliminar
  4. Como conidiciono el codigo para que .VPageBreaks.Count sea cero ???

    ResponderEliminar
    Respuestas
    1. Hola Danilo,
      un placer saludarte igualmente, con gusto respondo tu cuestión
      .VPageBreaks.Count
      realiza el conteo de saltos verticales, asi que:
      if .VPageBreaks.Count=0 then ... else ....

      Saludos cordiales

      Eliminar
  5. no me da el codigo solo me queda #valor

    ResponderEliminar
    Respuestas
    1. Hola,
      con gusto te ayudaré.
      Un placer saludarte igualmente.

      Asegúrate tienes tu hoja dividida en varias páginas

      Saludos

      Eliminar
  6. Set direccion = Range(Application.Caller.Address)

    Estimado Ismael, tengo la siguiente dificultad al copiar Tu codigo:
    error 91
    variable de objeto o bloque with no establecido
    gracias
    Luis

    ResponderEliminar
    Respuestas
    1. Hola Luis,
      te has asegurado existan varias hojas??
      'Ojo, la función está definida bajo el supuesto que existe al menos un salto vertical y uno horizontal!!!. En caso contrario devolvería un error...'
      Lo he vuelto a probar (el código del post) y funciona correctamente
      ¿¿??

      Saludos

      Eliminar
  7. que tal buenas tardes, oiga me gustaria que me explicara como condicionar la funcion a 0 ya que la verdad desconozco de programación y quisiera, si fuera usted tan amable, que me proporcionara el codigo condicionado a 0. ya que le estube moviendo a como crei que podia ser, pero no consegui hacer que funcione el codigo.
    de antemano muchas gracias.
    saludos.

    ResponderEliminar
    Respuestas
    1. Hola, que tal?
      Lo siento pero no comprendo ¿?, a qué te refieres con 'condicionar la función a 0'??

      Quedo atento a la aclaración

      Slds

      Eliminar
    2. buentas tardes.

      A lo que me refiero es condicionar la funcion para que me enumere las hojas solo hacia abajo, ya que no tengo ninguna hoja a la derecha; por lo tanto, la función me marca un error.
      gracias y saludos.

      Eliminar
    3. Hola,
      como se indica la función opera correctamente bajo el supuesto de que exista al menos un salto vertical y otro horizontal.
      Si necesitas variar las condiciones tendrás que incluir condicionales IF... THEN para cubrir las distintas posibilidades:
      1-sin saltos verticales
      2-sin saltos horizontales

      Saludos

      Eliminar

Nota: solo los miembros de este blog pueden publicar comentarios.