• Revista PROGRAMAR: Já está disponível a edição #53 da revista programar. Faz já o download aqui!

MetalFatigue

[Resolvido] Melhoramento de performance de procedimento em Excel

7 mensagens neste tópico

Estou a usar um código que demora mais de 10 minutos a processar (só nesta parte) e gostava de saber o que posso fazer para o melhorar.

Sub Copiar_Calculos_Para_Movel_Acumulado(idxInicial As Integer, idxFinal As Integer, _
    celulaInicial As String)
    
    Windows(fichMovel.nome).Activate
    sheets("TX_MOVEL").Select
    range(celulaInicial).Select
    sheets("VALOR_MOVEL_BASE").Select
    range(celulaInicial).Select
    sheets("VALOR_MOVEL_PDV").Select
    range(celulaInicial).Select
    Windows(fichAcum.nome).Activate
    sheets("TX_ACUMULADO").Select
    range(celulaInicial).Select
    sheets("VALOR_ACUMULADO_BASE").Select
    range(celulaInicial).Select
    sheets("VALOR_ACUMULADO_PDV").Select
    range(celulaInicial).Select
    
    Dim idx As Integer
    
    For idx = idxInicial To idxFinal
        Windows(fichCalc.nome).Activate
        ActiveWorkbook.sheets("Criterios").range("V3").Value = 1
        ActiveWorkbook.sheets("Criterios").range("P3").Value = idx
        
        Application.StatusBar = "A Processar " & CStr(idx - idxInicial + 1) & " de " & CStr(idxFinal - idxInicial + 1)
        
        'Copia e cola os dados no sitio correcto
        Call Copy_Paste_Values("M4:M52", ActiveCell.address, fichCalc.nome, fichMovel.nome, _
            "Calculos", "TX_MOVEL", False)
            
        Call Copy_Paste_Values("E4:E52", ActiveCell.address, fichCalc.nome, fichMovel.nome, _
            "Calculos", "VALOR_MOVEL_BASE", False)
            
        Call Copy_Paste_Values("J4:J52", ActiveCell.address, fichCalc.nome, fichMovel.nome, _
            "Calculos", "VALOR_MOVEL_PDV", False)
        
        Call Copy_Paste_Values("O4:O52", ActiveCell.address, fichCalc.nome, fichAcum.nome, _
            "Calculos", "TX_ACUMULADO", False)
                
        Call Copy_Paste_Values("B4:B52", ActiveCell.address, fichCalc.nome, fichAcum.nome, _
            "Calculos", "VALOR_ACUMULADO_BASE", False)
                
        Call Copy_Paste_Values("G4:G52", ActiveCell.address, fichCalc.nome, fichAcum.nome, _
            "Calculos", "VALOR_ACUMULADO_PDV", False)
        
        'Coloca a selecção de celulas a receberem dados, uma coluna para a frente
        Windows(fichMovel.nome).Activate
        sheets("TX_MOVEL").Select
        ActiveCell.Offset(0, 1).Select
        sheets("VALOR_MOVEL_BASE").Select
        ActiveCell.Offset(0, 1).Select
        sheets("VALOR_MOVEL_PDV").Select
        ActiveCell.Offset(0, 1).Select
        Windows(fichAcum.nome).Activate
        sheets("TX_ACUMULADO").Select
        ActiveCell.Offset(0, 1).Select
        sheets("VALOR_ACUMULADO_BASE").Select
        ActiveCell.Offset(0, 1).Select
        sheets("VALOR_ACUMULADO_PDV").Select
        ActiveCell.Offset(0, 1).Select
    
    Next
    Application.StatusBar = "A Processar..."
End Sub

Sub Copy_Paste_Values(rCopiar As String, celulaColar As String, _
    livroCopiar As String, livroColar As String, folhaCopiar As String, _
        folhaColar As String, transpor As Boolean)
    
    Windows(livroCopiar).Activate
    ActiveWorkbook.sheets(folhaCopiar).Select
    range(rCopiar).Select
    Selection.Copy
    range("A1").Select
    
    Windows(livroColar).Activate
    ActiveWorkbook.sheets(folhaColar).Select
    range(celulaColar).Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=transpor
    range("A1").Select
    
End Sub

Talvez uns ciclos dentro FOR para as folhas mas como o range a copiar são diferentes, para cada folha não sei se compensará. fichCalc, fichMovel. São um objecto que eu criei para definir caminhos e o nome de vários ficheiros.

logo no inicio do ciclo FOR o código:

ActiveWorkbook.sheets("Criterios").range("P3").Value = idx

vai fazer com que a com que os dados na folha "calculos" sejam alterados (funções procv na folha). Esta parte já estava implementada e é para manter, uma vez que quem me pediu o trabalho pretende manter o mesmo procedimento de actuação. Só que o código que lá estava só dava buraco então tou a refaze-lo de forma a que seja dinamico

cumprimentos

0

Partilhar esta mensagem


Link para a mensagem
Partilhar noutros sites

Olá

Uma coisa simples que podes fazer é desactivar as actualizações na worksheet, até que o trabalho esteja todo feito. Isso vai aumentar bastante o tempo de execução. Outra coisa que também melhora um pouco é desabilitar os calculos e voltar a abilitar no final.

Algo do genero:

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

' teu codigo

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

No entanto existem mais coisas que podem melhorar o tempo de processo, mas se pudesses mostrar o ficheiro, se isto não resolver, era excelente!

0

Partilhar esta mensagem


Link para a mensagem
Partilhar noutros sites

Infelizmente a minha função não é só programar, por isso só estou a ver a tua resposta em casa, por isso não dá para enviar o ficheiro/s. Mas julgo que também não será necessário.

O cálculo automático é necessário uma vez que os dados a copiar vao ser sempre diferentes. Como expliquei, este codigo,

ActiveWorkbook.sheets("Criterios").range("P3").Value = idx

vai calcular valores para serem copiados. As formulas nas celulas a serem copiadas são procvs, que dependendo do criterio ali se encontrado vao buscar certos tipos de dados que se encontram noutras folhas.

Provávelmente será estas formulas que tornam o processo lento. Não?

Será que dá para testar a performance das formulas retirando tudo o que se encontra no FOR excepto o codigo acima (que vai desencadear novos calculos)? Será um teste de performance aceitável?

Desde já obrigado mais uma vez pela atenção, Paulino.

0

Partilhar esta mensagem


Link para a mensagem
Partilhar noutros sites

O cálculo automático é necessário uma vez que os dados a copiar vao ser sempre diferentes. Como expliquei, este codigo,

Com os calculos automáticos ele actualiza todos os cálculos da folha sempre que alteras alguma coisa. Os seja, se copiares 100 vezes, ele vai actualizar todas as células 100 vezes e não apenas da ultima vez. Daí alterar-se :cheesygrin:

0

Partilhar esta mensagem


Link para a mensagem
Partilhar noutros sites

Tinhas razão (como de costume :)). Ele calculava sempre que copiava. Consegui reduzir para metade o tempo. Não pensei que funciona-se assim, ele só deveria verificar os calculos quando se copia-se formulas (tava a usar, copiar valores) ou se altera-se células com referencia nas formulas...isto seria a forma ideal de funcionar os calculos automáticos e era o que eu esperava que ele estivesse a fazer. Estava enganado.

Em relação ao screenupdating, era algo que eu já estava a ter o cuidado de fazer, simplesmente isso acontece no procedimento inicial e não nos que te mostrei.

Já agora durante a minha demanda em busca de performance encontrei 2 sites com conselhos de optimização de velocidade de código, que poderam interessar a quem pretenda uma performance exemplar:

http://www.ozgrid.com/VBA/SpeedingUpVBACode.htm

http://www.avdf.com/%5Capr98%5Cart_ot003.html

Obrigado pela ajuda.

0

Partilhar esta mensagem


Link para a mensagem
Partilhar noutros sites

Optimo!

Se quiseres reduzir mais ainda, só testando no ficheiro :)

Posso indicar como resolvido ?

0

Partilhar esta mensagem


Link para a mensagem
Partilhar noutros sites

Sim, podes colocar como resolvido. E mais uma vez obrigado pela ajuda.

0

Partilhar esta mensagem


Link para a mensagem
Partilhar noutros sites

Crie uma conta ou ligue-se para comentar

Só membros podem comentar

Criar nova conta

Registe para ter uma conta na nossa comunidade. É fácil!


Registar nova conta

Entra

Já tem conta? Inicie sessão aqui.


Entrar Agora