Problema extraño con una macro copiando celdas cuyos datos provienen de otros libros

alexgs · el 26 de septiembre

La verdad que no sé ni siquiera por dónde empezar. Bueno, en primer lugar pedir perdón si este tema ya se ha tratado en otro post, me ha sido imposible encontrarlo pero puede que ya se haya hecho la misma consulta. Intentaré resumirlo lo máximo posible para no desalentar a los que se aventuren con el problema.

El libro Excel consta únicamente de una hoja en la cual hay dos tablas, una la llamo REAL y otra SIMULACIÓN.

Ambas tablas están compuestas por los mismos conceptos: HORAS, LITROS, EXCESOS y RECARGOS, aunque los únicos datos que se deben modificar manualmente son los LITROS.

La diferencia entre las dos tablas radica en que en la tabla SIMULACIÓN, los datos de la columna EXCESOS se extraen de otras hojas de cálculo que están relacionadas. Estas otras hojas son muy pesadas pues contienen registros de varias semanas y a partir de ellos que se obtienen los EXCESOS que después se muestran en dicha tabla, una vez que se suman de cada una de las hojas. Además estos EXCESOS varían en función de los datos introducidos en la columna LITROS. Es decir, cuando se modifican los LITROS de la tabla SIMULACIÓN, estos a su vez modifican los EXCESOS de cada una de las hojas relacionadas y por tanto el total de EXCESOS resultantes.

El único objetivo de la tabla REAL es poder comparar los datos simulados con los reales, por lo que en primer lugar, cuando se abre el Libro, una primera macro se encarga de copiar los LITROS por defecto en la tabla REAL a la tabla SIMULACIÓN. Las fórmulas introducidas en las celdas de EXCESOS SIMULADOS hacen su función y, gracias a la macro, los resultados son copiados a la columna EXCESOS REALES. De esta manera completamos correctamente la TABLA REAL.

Este es el código de la macro1 incluida en “ThisWorkBook”:

Private Sub Workbook_open()

Range("B10:B13").Value = Range("B3:B6").Value

Range("C3:C6").Value = Range("C10:C13").Value

End Sub

Así pues nos aseguramos que al abrir el Libro, ambas tablas tengan los mismos datos.

De esa manera se podrán ir cambiando los valores de los LITROS SIMULADOS para ver que excesos se obtendrían y que sobrecoste o ahorro representaría sobre los REALES calculados inicialmente. En ese caso no entra en funcionamiento ninguna macro puesto que inicialmente ya se han calculado los valores en la tabla SIMULACIÓN y se han copiado a la tabla REAL.

Sin embargo para hacer más operativo el proyecto se contempla la opción de modificar los litros REALES, para lo cual se necesita volver a realizar todos los cálculos nuevamente con una macro que se encarga de copiar cada nuevo valor introducido en la columna LITROS REALES a la columna LITROS SIMULADOS. Seguidamente, después de ejecutarse las fórmulas alojadas en las celdas de EXCESOS SIMULADOS (la suma de celdas de otras hojas), se copian los resultados a los EXCESOS REALES. Es decir, en eso momento y al igual que cuando abrimos el archivo, ambas tablas contienen los mismos datos.

Este es el código de la macro2 incluida en “Hoja1”.

Dim TargetOldP1, TargetOldP2, TargetOldP3 As Integer

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$B$3" Then

If Target <> TargetOldP1 Then

If Target.Validation.Value = True Then

Range("B10").Value = Range("B3").Value

Range("C3").Value = Range("C10").Value

End If

TargetOldP1 = Target

End If

If Target.Address = "$B$4" Then

If Target <> TargetOldP2 Then

If Target.Validation.Value = True Then

Range("B11").Value = Range("B4").Value

Range("C4").Value = Range("C11").Value

End If

TargetOldP2 = Target

End If

If Target.Address = "$B$5" Then

If Target <> TargetOldP3 Then

If Target.Validation.Value = True Then

Range("B12").Value = Range("B5").Value

Range("C5").Value = Range("C12").Value

End If

TargetOldP3 = Target

End If

End Sub

Aparentemente el código no representa mayores complicaciones.

El uso de las variables TargetOld es sólo para evitar que la macro2 se vuelva a ejecutar si el dato que se introduce es igual que el que se ha introducido anteriormente, evitando así que el funcionamiento se ralentice más de lo que ya lo hace por si mismo.

En resumen, ambas macros cumplen su cometido y la hoja funciona como pretendo, salvo:

1.- Debemos partir de la base que para introducir cualquier valor en la columna LITROS de la tabla REAL, se puede realizar escribiendo un número y validándolo con ENTER o bien pulsando cualquier cursor, la tecla tabulación o clicando con cualquier botón del ratón en cualquier otra celda del libro. Pues bien, si introduzco un nuevo valor y lo valido pulsando una sola vez cualquiera de las teclas indicadas anteriormente, esperando a que se realicen todos los cálculos sin tocar ninguna otra tecla ni botón del mouse, la macro2 funciona correctamente.

En cambio, si valido el valor introducido (mediante cualquiera de las formas indicadas anteriormente) y vuelvo a pulsar cualquier otra tecla o botón del mouse antes de que acabe de realizar todos los cálculos necesarios de la tabla SIMULACIÓN (tarda entre 4 o 5 segundos en calcular los EXCESOS debido a la carga de datos a procesar), la macro2 no hace lo que pretendo; copia el valor de LITROS REALES en LITROS SIMULACIÓN y aunque se calculan correctamente los EXCESOS en SIMULACIÓN, estos no son copiados posteriormente a los EXCESOS REALES como se pretende con el código. Imagino que el delay y el hecho de realizar la acción de validar en más de una ocasión son los responsables de que la macro2 no acabe de funcionar como deseo.

2.- Esta misma situación se da cuando ocurre el evento Workbook_open y el usuario no espera a que los cálculos se acaben de efectuar. Como en el caso anterior, si se realiza una validación de cualquiera de las formas expuestas, aunque la macro1 copia el valor de LITROS REALES en LITROS SIMULACIÓN y se calculan correctamente los EXCESOS SIMULACIÓN, estos no son copiados posteriormente a los EXCESOS REALES.

He probado varios métodos para evitar este funcionamiento irregular pero no le encuentro solución. Tal vez se podría deshabilitar tanto el teclado como el mouse una vez hecha la validación inicial para evitar que el usuario vuelva a validar antes de que acaben de realizarse los cálculos oportunos. He realizado una prueba deshabilitando la tecla cursor hacia abajo sin éxito, sigue dándose el mismo fallo.

No he adjuntado las hojas relacionadas de donde se captan los valores de EXCESOS SIMULACIÓN puesto que su tamaño es enorme, cada uno por encima de 1 mega y como digo serían mínimo 4 o 5 archivos. En caso de necesitarlos agradecería que me lo indicaseis y no tendré mayor problema en hacerlo.

Espero que alguien se tome la molestia de leer este tocho y me podáis dar una explicación, ayuda o alternativa que arregle este defecto en el funcionamiento.

Muchas gracias por adelantado.

Ejemplo Macro.xls

alexgs · el 27 de septiembre

Creo que la explicación que hice lo único que ha hecho ha sido asustar a cualquier interesado por lo que intentaré resumir lo que yo creo que es el origen del problema del mal funcionamiento y lo que pretendo conseguir.

Hay alguna manera de deshabilitar el evento Worksheet_Change, hasta que no finalicen todos los cálculos que se realizan una vez se introduce un nuevo valor en una celda? (Los cálculos asociados pueden durar entre 5 y 10 segundos)

Es decir, en el código que indicaba en mi anterior post:

Este es el código de la macro2 incluida en “Hoja1”.

Dim TargetOldP1, TargetOldP2, TargetOldP3 As Integer

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$B$3" Then

If Target <> TargetOldP1 Then

If Target.Validation.Value = True Then

Range("B10").Value = Range("B3").Value

Range("C3").Value = Range("C10").Value

End If

TargetOldP1 = Target

End If

¿Como consigo deshabilitar un nuevo evento Worksheet_Change hasta que no finalice los cálculos y la copia de valores indicada en negrita en el código anterior?

Espero haber resumido mejor lo que necesito y que alguien me pueda echar un cable.

Muchas gracias!

Antoni · el 27 de septiembre

Vamos por partes:

Deshabilitar teclado y mouse, obtenido del link:

Habilitar o no Teclado y Ratón. Visual Basic

En un módulo:

Declare Function BlockInput Lib "user32" (ByVal fBlock As Long) As Long

BlockInput True para deshabilitar

BlockInput False para habilitar

Para cancelar los eventos de hoja y libro:

Application.EnableEvents = False para cancelar eventos

Application.EnableEvents = True para restablecer eventos

alexgs · el 27 de septiembre

Gracias por la respuesta, Macro Antonio!

Probaré la primera opción que me propones. Respecto a la opción de cancelar eventos de hoja y libro, ya realicé la prueba pero no conseguí resultados positivos, si validaba más de una vez el valor introducido, la macro no me copiaba las celdas tal como se ordena en el código. Supongo que utilizaba correctamente esta propiedad... si le puedes echar un ojo:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$B$6" Then

Application.EnableEvents = False

If Target <> TargetOldP1 Then

If Target.Validation.Value = True Then

Range("B16").Value = Range("B6").Value

Range("C6:D6").Value = Range("C16:D16").Value

'O bien Worksheets ("Hoja1").Cells (6, 3).Value = Worksheets ("Hoja1").Cells (16, 3).Value

End If

TargetOldP1 = Target

End If

Application.EnableEvents = True

End If

Lo acabo de volver a probar variando su colocación en los diferentes IF y el resultado sigue siendo el mismo, no se soluciona el problema. Intento probar la primera solución que me propones y te cuento.

Saludos y gracias!!!

- - - - - Mensaje combinado - - - - -

Hola de nuevo Macro Antonio,

Acabo de probar la primera opción:

Declare Function BlockInput Lib "user32" (ByVal fBlock As Long) As Long

Incluyo en un módulo únicamente esa declaración, lo que no me queda claro es si debo añadir algo más.

Entiendo que entonces debo escribir BlockInput True en la parte del código de la Hoja1 donde quiero que comience la desactivación del teclado y mouse, verdad? Quedaría así:

Dim TargetOldP1, TargetOldP2, TargetOldP3 As Integer

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$B$3" Then

BlockInpunt True

If Target <> TargetOldP1 Then

If Target.Validation.Value = True Then

Range("B10").Value = Range("B3").Value

Range("C3").Value = Range("C10").Value

End If

TargetOldP1 = Target

End If

BlockInput False

End If

Ignoro si hago algo mal pero el teclado y el ratón están operativos en todo momento. He intentado probar el BlockInput True en cualquier otra parte del código pero en ningún momento se deshabilita el teclado y el ratón...

Se te ocurre que puede estar pasando?

Saludos y gracias por tu tiempo.