Excel - MACROS MUY LENTAS

MACROS MUY LENTAS

Publicado por Jose Maria (22 intervenciones) el 16/01/2017 16:30:39

Buenas Tardes amigos Programadores, tengo una necesidad de vosotros: tengo una Macro que tarda de 5 a 6 minutos en su ejecuccion, creo que tengo algo mal me podriais ayudar en este caso, la macro es:

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

Sub COLORBASE()

Sheets("TURNO BASE").Select

Cells.FormatConditions.Delete

Sheets("TURNO BASE").Select

Range("A1:WPW1500").Select

Range("WPW1500").Activate

Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _

Formula1:="='TURNO BASE'!$BE$6"

Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority

With Selection.FormatConditions(1).Font

.Bold = True

.Italic = False

.color = -16777088

.TintAndShade = 0

End With

With Selection.FormatConditions(1).Interior

.PatternColorIndex = xlAutomatic

.ThemeColor = xlThemeColorAccent1

.TintAndShade = 0.399945066682943

End With

Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _

Formula1:="='TURNO BASE'!$BF$6"

Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority

With Selection.FormatConditions(1).Font

.Bold = True

.Italic = False

.color = -16777088

.TintAndShade = 0

End With

With Selection.FormatConditions(1).Interior

.PatternColorIndex = xlAutomatic

.ThemeColor = xlThemeColorAccent1

.TintAndShade = 0.399945066682943

End With

Selection.FormatConditions(1).StopIfTrue = False

Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _

Formula1:="='TURNO BASE'!$BE$7"

Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority

With Selection.FormatConditions(1).Font

.Bold = True

.Italic = False

.color = -16711681

.TintAndShade = 0

End With

With Selection.FormatConditions(1).Interior

.PatternColorIndex = xlAutomatic

.ThemeColor = xlThemeColorAccent5

.TintAndShade = -0.249946592608417

End With

Selection.FormatConditions(1).StopIfTrue = False

Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _

Formula1:="='TURNO BASE'!$BF$7"

Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority

With Selection.FormatConditions(1).Font

.Bold = True

.Italic = False

.color = -16711681

.TintAndShade = 0

End With

With Selection.FormatConditions(1).Interior

.PatternColorIndex = xlAutomatic

.ThemeColor = xlThemeColorAccent5

.TintAndShade = -0.249946592608417

End With

Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _

Formula1:="='TURNO BASE'!$BE$8"

Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority

With Selection.FormatConditions(1).Font

.Bold = True

.Italic = False

.ThemeColor = xlThemeColorDark1

.TintAndShade = 0

End With

With Selection.FormatConditions(1).Interior

.PatternColorIndex = xlAutomatic

.ThemeColor = xlThemeColorLight2

.TintAndShade = -0.249946592608417

End With

Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _

Formula1:="='TURNO BASE'!$BF$8"

Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority

With Selection.FormatConditions(1).Font

.Bold = True

.Italic = False

.ThemeColor = xlThemeColorDark1

.TintAndShade = 0

End With

With Selection.FormatConditions(1).Interior

.PatternColorIndex = xlAutomatic

.ThemeColor = xlThemeColorLight2

.TintAndShade = -0.249946592608417

End With

Selection.FormatConditions(1).StopIfTrue = False

Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _

Formula1:="='TURNO BASE'!$BE$9"

Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority

With Selection.FormatConditions(1).Font

.Bold = True

.Italic = False

.ThemeColor = xlThemeColorLight1

.TintAndShade = 0

End With

With Selection.FormatConditions(1).Interior

.PatternColorIndex = xlAutomatic

.ThemeColor = xlThemeColorAccent1

.TintAndShade = 0.599963377788629

End With

Selection.FormatConditions(1).StopIfTrue = False

Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _

Formula1:="='TURNO BASE'!$BF$9"

Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority

With Selection.FormatConditions(1).Font

.Bold = True

.Italic = False

.ThemeColor = xlThemeColorLight1

.TintAndShade = 0

End With

With Selection.FormatConditions(1).Interior

.PatternColorIndex = xlAutomatic

.ThemeColor = xlThemeColorAccent1

.TintAndShade = 0.599963377788629

End With

Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _

Formula1:="='TURNO BASE'!$BE$10"

Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority

With Selection.FormatConditions(1).Font

.Bold = True

.Italic = False

.ThemeColor = xlThemeColorLight1

.TintAndShade = 0

End With

With Selection.FormatConditions(1).Interior

.PatternColorIndex = xlAutomatic

.ThemeColor = xlThemeColorAccent1

.TintAndShade = 0.799981688894314

End With

Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _

Formula1:="='TURNO BASE'!$BF$10"

Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority

With Selection.FormatConditions(1).Font

.Bold = True

.Italic = False

.ThemeColor = xlThemeColorLight1

.TintAndShade = 0

End With

With Selection.FormatConditions(1).Interior

.PatternColorIndex = xlAutomatic

.ThemeColor = xlThemeColorAccent1

.TintAndShade = 0.799981688894314

End With

Selection.FormatConditions(1).StopIfTrue = False

Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _

Formula1:="='TURNO BASE'!$BE$11"

Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority

With Selection.FormatConditions(1).Font

.Bold = True

.Italic = False

.ThemeColor = xlThemeColorLight1

.TintAndShade = 4.99893185216834E-02

End With

With Selection.FormatConditions(1).Interior

.PatternColorIndex = xlAutomatic

.color = 16776960

.TintAndShade = 0

End With

Selection.FormatConditions(1).StopIfTrue = False

Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _

Formula1:="=0"

Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority

With Selection.FormatConditions(1).Font

.ThemeColor = xlThemeColorDark1

.TintAndShade = 0

End With

With Selection.FormatConditions(1).Interior

.PatternColorIndex = xlAutomatic

.ThemeColor = xlThemeColorDark1

.TintAndShade = 0

End With

Range("C1").Select

End Sub

En esta macro las formulas llegan hasta:

Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _

Formula1:="='TURNO BASE'!$BE$66"

Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority

With Selection.FormatConditions(1).Font

.Bold = True

.Italic = False

.color = -16777088

.TintAndShade = 0

End With

With Selection.FormatConditions(1).Interior

.PatternColorIndex = xlAutomatic

.color = 15773696

.TintAndShade = 0

End With

Selection.FormatConditions(1).StopIfTrue = False

Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _

Formula1:="='TURNO BASE'!$BF$66"

Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority

With Selection.FormatConditions(1).Font

.Bold = True

.Italic = False

.color = -16777088

.TintAndShade = 0

End With

With Selection.FormatConditions(1).Interior

.PatternColorIndex = xlAutomatic

.color = 15773696

.TintAndShade = 0

End With

Esta macro lee en un cuadro los valores que yo relleno de antemano, trabaja con formatos condicionales ya que en el cuadrante puedo yo variar algunas valore de los que yo quiero que al rellenar las celdas aparezcan dichos valores.
Yo creo que algo tengo mal hecho, pero no lo encuentro.
Muchas gracias y un fuerte saludo
Josemaria

Valora esta pregunta

Me gusta: Está pregunta es útil y esta clara

No me gusta: Está pregunta no esta clara o no es útil

Responder

MACROS MUY LENTAS

Publicado por JuanC (1237 intervenciones) el 16/01/2017 18:13:11

¿podrias publicar el libro mejor?

Valora esta respuesta

Me gusta: Está respuesta es útil y esta clara

No me gusta: Está respuesta no esta clara o no es útil

Comentar

Pos: 2

Val: 4.908

MACROS MUY LENTAS

Publicado por Antoni Masana (2547 intervenciones) el 17/01/2017 07:01:37

El la línea 6 de la primera Macro haces una selección de celdas:

Range("A1:WPW1500").Select

Ojo al dato: estas seleccionando 15987 columnas por 1500 lineas que esto hacen un total de nada más ni nada menos que 23.980.500 celdas, solo que necesite 1 microsegundo por celda para seleccionarlas, tarda casi 24 segundos.

Otra cosa: poner comentarios cada pocas lineas ayuda a entender mejor la macro.

¿Podrías publicar el Iibro?

O si lo prefieres enviarlo a una persona solo puedes enviarmelo a mi o a cualquiera de mis compañeros del foro que te de más confianza.

Saludos.
\\//_

Valora esta respuesta

Comentar

Pos: 2

Val: 4.908

MACROS MUY LENTAS

Publicado por Antoni Masana (2547 intervenciones) el 17/01/2017 09:35:56

He copiado la primera macro de 182 lineas y la he ejecutado.
Tarda en ejecutarse 0.4 segundos. Quizás sea un problema de velocidad del equipo.
Me gustaría saber que es lo que hace esta macro.
Me da la sensación que se ha creado con la opción Grabar Macro.
Tendrias que explicar que es lo que deseas hacer.

Saludos.
\\//_

Valora esta respuesta

Comentar

MACROS MUY LENTAS

Publicado por Jose Maria (22 intervenciones) el 17/01/2017 21:26:46

Buenas noches Antoni, me preguntas como he hecho las macros y tu tienes razón todas las Macros están hechas con la opción de Gravar Macro, yo de otra manera no se hacerlas, yo lo que he hecho es un libro que en la hoja TURNO BASE, tengo el cuadrante de varios trabajadores que van a rotar durante 4 semanas van por grupos de dos tres o cuatro trabajadores, al final tengo un contador para saber como están ubicados dichos trabajadores y en que tiempo, para ello tengo unas celdas donde yo pongo los valores de sus tiempos de trabajo (M7=7 horas en horario de mañana) y M3..M5..(3 ó 5 horas en turno de mañana) asilo mismo pasa con los t1..t4..t5..t45 según lo valores que aite en las celdas desde BE6:BF56 en las Celdas BG6:BH56 doy el valor de la cantidad de horas que hace cada letra dicha antes T1..M1..M5..N..ect. en la misma hoja tengo los años 2016, 2017, 2.018, 2.019, 2.020, y 2.921, que esos van ya con la rotación que le corresponde de su grupo de trabajadores que puede ser el grupo de tres ò de cuatro ó de dos ect. y va corrido durante todos los años ya citados, dejando una columna libre en los días 29 de febrero de los años sin bisiesto, sobre estos años se realizan todas las irregularidades que se produzcan durante en año machacando las formulas y poniendo la realidad re las irregularidades que pudieran pasar, existe un apartado en el libro que cuando realizamos cambios en el año este nos lo copia a ese apartado WBA1:WPB100..148..ó 198
Este apartado es el que comunica con las hojas de cada operario personalmente y a cada mes de año. para mi ahora lo mas lento es la Macro A4celcolorbase y los User Formt que van muy lentos.
Como te puedo mandar el Libro? pesa mucho para pasarlo por aqui
de todas maneras muchas gracias y un fuerte saludo
Jose Maria

N.B. las macros de los años he podido reducilas y ya van rapidas lo mas lento es la Macro Base.

Valora esta respuesta

Comentar

MACROS MUY LENTAS

Publicado por JuanC (1237 intervenciones) el 17/01/2017 22:25:26

creo que el problema puede ser la aplicación de formato condicional a miles de celdas
deberías buscar otra manera de hacerlo, usando macros creo que no es necesario formato condicional...
sube el libro a www.sendspace.com u otro servidor para compartir y pega el link...

Valora esta respuesta

Comentar

Pos: 2

Val: 4.908

MACROS MUY LENTAS

Publicado por Antoni Masana (2547 intervenciones) el 18/01/2017 08:55:06

Buenos días Jose María,

Puedes comprimirlo y enviarlo por correo.

Si tu correo no te permite enviar un fichero tan grande te envío un programa para trocear los ficheros grandes y poder enviarlos con varios correos.

Funcione en Windows 7, lo he probado.

Saludos.
\\//_

Hacha.zip(14,4 KB)

Valora esta respuesta

Comentar

Pos: 2

Val: 4.908

MACROS MUY LENTAS

Publicado por Antoni Masana (2547 intervenciones) el 18/01/2017 11:01:01

Buenas otra vez,

Cuando estaba desayunando he recordado una cosa, o mejor dicho un detalle del EXCEL, en fichero que ves en el disco es un fichero comprimido. Se puede ver el contenido con un WinZip o similar.

Si el tamaño en disco es de 300 MB una vez abierto se pueden convertir en 1200 MB o más, dependiendo del contenido.

Y ¿Donde se meten todas estas megas de datos? Pues en la MEMORIA y si no le cabe pagina a disco. Lo cual supone que trabajar con documentos muy grandes y con poca memoria es lento de c.j.n.s.

En mi prueba era rápida la ejecución de la macro porque tengo la tabla vacía y Windows 7 con un Procesador Core i5-2400 a 3.1 HGz y 4 GB de RAM.

Probablemente la MACRO se pueda mejorar el rendimiento, pero una buena solución sería ampliar la memoria o cambiar el ordenador por un procesador más rápido y mas GB de memoria RAM.

Tengo otro ordenador mejor que este, si me puedes enviar el fichero lo probare en los dos para ver el rendimiento.

Saludos.
\\//_

Valora esta respuesta

Comentar

MACROS MUY LENTAS

Publicado por Jose Maria (22 intervenciones) el 22/01/2017 11:54:26

Buenos dias, Antoni, te mande el libro por www.sendspace.com, te ha llegado?, quieres te lo vuelvo a mandar.
Un saludo
Jose Maria.

Valora esta respuesta

Comentar

Pos: 2

Val: 4.908

MACROS MUY LENTAS

Publicado por Antoni Masana (2547 intervenciones) el 23/01/2017 07:41:06

Buenos días,

Lo miro esta tarde y te digo algo.

Saludos.
\\//_

Valora esta respuesta

Comentar

Pos: 2

Val: 4.908

MACROS MUY LENTAS

Publicado por Antoni Masana (2547 intervenciones) el 23/01/2017 20:56:01

He entrado en http://www.sendspace.com pero no se como encontrar el documento y descarcarlo.
No he usado esto y no tengo cuenta.

Necesita mas información de como acceder o me lo envías a cachitos con el hacha a mi correo.

Valora esta respuesta

Comentar

Pos: 25

Val: 84

MACROS MUY LENTAS

Publicado por Nolberto (128 intervenciones) el 27/01/2017 21:31:11

Saludos.

Quizá la mejor opción es cambiar esa macro por una que directamente hace la comparación de la condición y pone el formato a las celdas que cumplen con dicha condición.

Lo que sucede es que el simpe hecho de poner formatos condicionales hacen mas pesado el archivo.

Puedes probar algo como esto y adaptarlo a tus necesidades.

Sub MarcaData()

    Application.ScreenUpdating = False

    ActiveSheet.Range("A2").Select

    Do While ActiveCell.Value <> ""

        If ActiveCell = Sheets("Data").Range("B3") Then

            Range(ActiveCell, ActiveCell.Offset(0, 4)).Interior.ColorIndex = 3

            Selection.Offset(1, 0).Select

        Else

            Selection.Offset(1, 0).Select

        End If

    Loop

    Application.ScreenUpdating = True

End Sub

Esa macro recorre la columna A desde la fila 2 hasta que se encuentre una celda vacía, si el dato de la celda en turno es igual que el dato que esta en Sheets("Data").Range("B3"), entonces esa fila en turno hasta 4 columnas a la derecha se pone con un fondo rojo.

Antes de ejecutar esa macro puedes usar otra que formatee las celdas, de tal modo que se elimine el formato que antes se haya usado en el rango de trabajo, ejemplo:

Sub Macro1()

    Range("A1:F20").Select

    With Selection.Interior

        .Pattern = xlNone

        .TintAndShade = 0

        .PatternTintAndShade = 0

    End With

End Sub

Espero puedas hacer la adaptación a tu caso, son pocas lineas y no debería ser problema.

Valora esta respuesta

Comentar

Cómo seleccionar varias filas que contengan un valor determinado

Funcion para promediar siempre el ultimo n numero de celdas de una fila