web analytics

Plantilla para controlar los cambios en un archivo Excel con VBA

Una vez me pidieron si era posible controlar los cambios que se realizan en un libro Excel sin que el usuario lo sepa, como siempre digo, es solo cuestión de tiempo, con Office y VBA se pueden hacer muchísimas cosas antes de llegar a un límite que difícilmente se alcanzará en un entorno ofimático normal, no hablamos de un ERP para gestionar un negocio de millones de euros ni de un videojuego de última generación, hablamos de ofimática, escribir, calcular y presentar, y para estos menesteres y para el común de los mortales las macros con VBA son más que suficiente.

Obviamente, Excel incorpora un sistema de control de cambios (p.e. aquí), pero además de ser más obvio para el usuario nos obliga a compartir el libro y nos puede limitar en ciertas funciones, pero por otro lado este método con VBA me provocó un problema importante y es que no se pueden deshacer los cambios, cada vez que se ejecuta código VBA la pila de deshacer (undo stack para los amigos) se vacía, y en este tipo de plantilla que se ejecuta VBA todo el rato es un problema, incluso los usuarios mas avezados se darán cuenta que algo sucede con la opción deshacer, desgraciadamente se tiene que decir aquello tan manido de:  «no es un bug es una feature«, hay soluciones más o menos completas para parchear esta característica que pasan por hacer un copia del estado del libro antes de ejecutar la macro, pero como tampoco me acababa de gustar y no tenia tiempo no lo programé.

Entonces hay que valorar que queremos, deshacer las cosas o secretismo, como en este caso se trataba más de investigar sobre el mal uso de un archivo Excel la opción fue hacerlo mediante VBA.

Vamos a la cuestión

Nada más poner en marcha la plantilla nos solicitará que activemos las macros, una vez activadas nos mostrará la Hoja1 tal y como se ve en la segunda imagen siguiente.

Aviso de activación de macros
Captura2
Hoja inicial de la plantilla

Borraremos esta hoja base o la modificaremos para usarla como cualquier libro por ejemplo creamos una tabla y la modificamos

Tabla de ejemplo
Tabla de ejemplo modificada

Presionando CTRL+SHIFT+L ejecutaremos la macro que nos mostrará la hoja de LOG y veremos en ella los cambios realizados

Ventana de contraseña para ver la hoja de LOG
Muestra del fichero de LOG con los cambios en la tabla de ejemplo

El LOG nos mostrará lo siguiente:

  • Fecha en que se realizó el cambio.
  • Ordenador y nombre de usuario del sistema en que se realizó el cambio.
  • La acción realizada en concreto. Guarda lo siguiente:
    • Se cambia el nombre de una hoja.
    • Se borra una hoja
    • Se crea una hoja
    • Se cambia el contenido de una celda
    • Se cambia el contenido de un rango
    • Se guarda el libro
  • Hoja afectada por el cambio.
  • Rango afectado por el cambio.
  • Columna y filas afectadas por el cambio.
  • Valor anterior al cambio y el actual.

A tener en cuenta

  • Usando esta plantilla se pierde la posibilidad de deshacer los cambios (ya lo he comentado más arriba en este mismo post)
  • Hay una limitación en la cantidad de celdas individuales simultáneas de las que se pueden registrar los cambios. Es un parámetro que se puede modificar en el código fuente, por defecto es 500 celdas. Depende de la potencia del equipo poder poner más o menos. Si es superior simplemente nos indicará que hay un cambio en un rango completo.
  • La contraseña para ejecutar la macro es «manteniendo»
  • La contraseña para poder editar y visualizar el código VBA la puedes descargar si estás suscrito a mi blog.
  • Puedes modificar la plantilla como te plazca, sólo te pido que respetes parte de la autoría del código. Vamos que me menciones si la distribuyes.
  • Te vas a tener que inventar una excusa para que el usuario ejecute esta hoja con macros (o hacerlo un archivo de confianza antes de que se entere).


Si quieres poder editar el archivo, en el siguiente enlace tienes la contraseña para la hoja excel, para descargarla deberás estar suscrito a mi blog.




Comparte con


12 comentarios en “Plantilla para controlar los cambios en un archivo Excel con VBA”

  1. Hola, esta muy buena la planilla, gracias por compartirla. Me gustaria saber como puedo cambiar la contraseña para mostrar las hojas ocultas. La busco en el código pero no esta, en las propiedades del libro hay unas contraseñas pero no estoy seguro que sea eso.

  2. Hola amigo muy Buena aportacion y felicitaciones, solo una pregunta como puedo poner nuevamente funcionar la macros porque corro la marcros y no registra cambios.

    Saludos

    • Hola,

      Las macros las debes activar (habitualmente) al abrir el archivo. Si no le das el permiso para hacerlo no funcionará hasta que lo cierres y lo vuelvas a abrir y aceptes la ejecución de macros.

      Me das poca información para poder ayudarte.

      Saludos!

  3. Hola, buenas tardes… felicitaciones por tu control de cambios, está bien útil.

    Una consulta, tu comentas que el control de cambios de excel es super robusto, etc., pero al mismo tiempo mas arriba habías comentado que tiene varias limitantes, podrías decirnos a tu criterio cuales son esas limitantes?

    • Hola Ricardo,

      Primero, gracias por tus comentarios.

      Y al lío, el problema es que para habilitar el control de cambios propio de Excel te obliga a compartir el libro y hacerlo implica unas limitaciones que vienen bien explicadas en el siguiente enlace: https://support.office.com/es-es/article/Usar-un-libro-compartido-para-colaborar-49b833c0-873b-48d8-8bf2-c1c59a628534

      Está en el uso que se vaya a hacer del libro si queremos seguir manteniendo funcionalidades o sacrificarlas en pos de un mejor (o más oculto) control de los cambios.

      Otro motivo es que el control de cambios propio de Excel es muy obvio, en cambio el mio puede pasar desapercibido. Vale que se necesitan permisos para ejecutar macros pero nadie sabe a ciencia cierta si esos cambios se están registrando.

      Espero que haya sido de ayuda.

      Saludos!

  4. hola me encanta tu trabajo, tengo una duda, como puedo reiniciar el control de cambios, es decir que se borren todos y vuelva a comenzar, agradeceria tu respuesta

    • Hola Luis,

      En esta misma entrada al final de todo, encontrarás un enlace para descargar la clave. Para descargarla debes estar suscrito a mi blog.
      Y me alegra que la plantilla sea de tu interés.

      Saludos!

  5. Hola, me encanta su blog, me parece sumamente útil.
    En concreto esta plantilla está genial, solo quería preguntarle una cosa. En mi caso necesito el control de cambios para controlarme a mí mismo, no necesito que sea secreto, y además me sería muy útil que lo hiciese automáticamente sin tener que activar la macro cada vez. ¿Sabe si eso sería posible?

    Gracias
    Saludos

    • Hola,

      Primero me alegro mucho de que mi blog le resulte útil.

      Por otro lado, respecto a su comentario, esta plantilla está ideada para que el usuario -un tercero, no uno mismo- no tenga conocimiento de que en el libro Excel que está usando hay una macro que controla los cambios realizados, no tiene mucho sentido hacer las modificaciones por las que me pregunta cuando el propio Excel, como indico en el mismo texto de mi entrada, ya tiene su propia característica de control de cambios que es mucho más robusta y versátil que lo que esta macro le puede ofrecer (En https://goo.gl/Vt43Br encontrará información al respecto)

      De todas maneras si hay algún motivo que se me escapa para preferir usar esta plantilla en lugar del control de cambios que lleva el propio Excel ruego me lo indique.

      Saludos!

Si tienes alguna duda, crítica o sugerencia lo leeré encantado

Este sitio usa Akismet para reducir el spam. Aprende cómo se procesan los datos de tus comentarios.