0140-Comparativo de estratégias para inserir dados em planilhas via VBA

Uma das atividades mais comuns realizadas durante a execução de macros é a inserção de valores em planilhas.

Realizei o comparativo de três diferentes estratégias para preencher as células do intervalo A1:T500 com base no cálculo:

√ (Log(N_Linha) + N_Coluna/1000).

Estratégia 1

Metodologia: Inserir as fórmulas nas células e em seguida copiá-las e colá-las como valores.

Código:

Sub CalcularNoIntervalo()

Dim i As Integer, j As Integer

Dim Start As Date, Finish As Date

Start = Time

For j = 1 To 20
For i = 1 To 500
Cells(i, j).FormulaR1C1 = "=SQRT(LOG(ROW())+COLUMN()/1000)"
Next i
Next j

Application.DisplayAlerts = False

[A1:T500].Copy: [A1:T50].PasteSpecial Paste:=xlValues

Application.CutCopyMode = False

Finish = Time

MsgBox "Tempo de processamento= " & Format(Finish - Start, "s.ssss") & " seg"

End Sub

Resultado: Foram necessários 2,0000 segundos.

Estratégia 2

Metodologia: Calcular as fórmulas no ambiente VBA, inserindo-as em seguida na planilha

Código:

Sub InserirNoIntervalo()

Dim i As Integer, j As Integer
Dim Start As Date, Finish As Date

Start = Time

For j = 1 To 20
    For i = 1 To 500
    Cells(i, j) = Sqr(Log(i) + j / 1000)
    Next i
Next j

Finish = Time

MsgBox "Tempo de processamento= " & Format(Finish - Start, "s.ssss") & " seg"

End Sub

Resultado: Foram necessários 1,0100 segundos.

Estratégia 3

Metodologia: Calcular as fórmulas no ambiente VBA, inserindo-as em uma matriz e depois transferir o conteúdo da matriz para o intervalo

Código:

Option Base 1

Sub TransferirMatrizParaIntervalo()
Dim i As Integer, j As Integer
Dim Start As Date, Finish As Date
Dim Matriz(500, 20)

Start = Time

For j = 1 To 20
    For i = 1 To 500
    Matriz(i, j) = Sqr(Log(i) + j / 1000)
    Next i
Next j

[A1:T500] = Matriz

Finish = Time

MsgBox "Tempo de processamento= " & Format(Finish - Start, "s.ssss") & " seg"

End Sub

Resultado: Foram necessários 0,0000 segundos.

Comentários:

  • A estratégia 1 é a que vejo ser utilizada com maior frequência, porém é a mais lenta, pois além de inserir os valores um por vez na planilha, necessita transferir e resgatar dados para a área de transferência do windows.
  • A estratégia 2 mostra-se mais veloz na sua execução, já que um vez inserido os valores não há retrabalho. No entanto são realizados mil acessos à planilha, o que contribui para desacelerar sua performance.
  • A estratégia 3 é de longe a mais veloz, pois todos os cálculos são realizados em tempo de processamento e a transferência das informações para a planilha é feita de uma única vez, reduzindo as necessidades de memória.
  • Sempre que for realizar atividades como as citadas aqui (cálculos e preenchimento de células) verifique se é factível o uso da estratégia 3 para que possa obter o menor tempo possível de processamento.
Anúncios

In Excel We Trust

Marcado com: , , , ,
Publicado em Nível: Avançado
2 comentários em “0140-Comparativo de estratégias para inserir dados em planilhas via VBA
  1. Victor Gamboa disse:

    Excelente artigo, nunca tinha visto uma abordagem ou um comparativo sobre este assunto0, trata-se de uma boa prática avançada em Excel, parabéns.

  2. Cau disse:

    Olá amigo, Como faço para executar esta macro somente numa planilha? Resumindo, nesta planilha preciso copiar os valores das celulas da linha A1, cola-las na linha abaixo, inserir uma nova linha e por ultimo deletar a linha 31, sem interferir nas outras planilhas. Abaixo está o exemplo e a macro que faz isso. Tens alguma outra forma de fazer isso? Obrigado!

    A B C D E
    1 12 32 26 28 20
    2 12 32 26 28 20
    3 05 08 07 56 65
    4 37 74 54 65 43
    5 21 35 43 78 54
    6
    7
    8
    .
    .
    .
    30 23 34 46 48 69
    31 46 58 58 94 53

    Sub GRAVA()

    ‘ GRAVA Macro
    ‘ Macro gravada em 18/2/2012 por cau


    Rows(“9:9”).Select
    Selection.Copy
    Rows(“20:20”).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=True, Transpose:=False
    Application.CutCopyMode = False
    Selection.Insert Shift:=xlDown
    Rows(“31:31”).Select
    Selection.Delete Shift:=xlUp
    Range(“B9”).Select
    End Sub

    Grato, Cau.

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s

Visitas dos usuários
  • 2,069,536

Clique para assinar este blog e receber notificações de novos artigos por email.

Junte-se a 1.534 outros seguidores

Aumente sua interação

Siga-nos do twitter (@usuariosdoexcel).

Acesse o nosso fórum e compartilhe as suas dúvidas.

Curta nossa página no Facebook.

Junte-se ao grupo do LinkedIn.

Visite-nos utilizando o seu smartphone ou tablet.

Recomende este Blog
Visualizar notícias
Site monitorado:
Real Time Web Analytics

Clicky

%d blogueiros gostam disto: