Problema extraño con una macro copiando celdas cuyos datos provienen de otros libros
publicado
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
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
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 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.
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
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
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 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