Excel Excel copia e guarda "X" valores entregues pelo RTD (Real Time Data)

Midori · 26 de julho de 2022

Editei a macro, veja se copiou o código atualizado.

Alexandre José Costa · 26 de julho de 2022

2 minutos atrás, Midori disse:

Editei a macro, veja se copiou o código atualizado.

Certo. Vou ver.

Edson Luiz Branco · 26 de julho de 2022

Uma alternativa para agilizar seria também, se o problema for performance e muito recálculo na planilha principal, criar uma planilha oculta que esteja linkada apenas ao grupo de células que sofre atualização pelo RTD e usar somente nessa oculta a armadilha de evento Calculate.

Dessa forma, Calculate não é disparado em outros recálculos da principal, somente quando vêm dados externos.

Por exemplo, se a alimentação do RTD atualmente entra na planilha principal nas células A1 e A2, a planilha oculta teria uma fórmula

= Principal!A1 e outra =Principal!A2 e mais nada. Ela só sofreria recálculo na entrada de novos dados via RTD.

E como seu algoritmo nada mais é do que uma fila FIFO (First In/First Out) ou seja, o primeiro a entrar é o primeiro a sair (PEPS), daria muito bem pra aplicar um objeto Queue da biblioteca do Windows System.Collections, muito rápida e feito sob medida pra essas situações, o que poderia talvez agilizar ainda mais o processo (suponho).

Alexandre José Costa · 26 de julho de 2022

37 minutos atrás, Midori disse:

Editei a macro, veja se copiou o código atualizado.

@Midori

Realmente, eu havia copiado o código antes de sua edição. Mas após copiar o código certo, ele ainda não armazena os últimos cinco valores.

Uma observação: no código anterior, o do "Loop", eu entendia o range do código e poderia editá-lo conforme meu uso, para ele não pegar a col toda, ou não sobrecarregar com cálculos sobre céls adicionais sem necessidade, por exemplo:

For Each Valor In [D3:D71] ----> aqui eu alterava o range das céls.

Agora, com o código que você fez (copy/paste) eu não sei fazer esta edição. Se for necessário, claro.

@Edson Luiz Branco

Caro Edson, obrigado por sua ajuda.
Não sei dizer se o problema seja o peso dos cálculos.

Antes do problema, a planilha rodava bem. Atualmente tenho cerca de 10 indicadores que preciso armazenar os últimos valores. Não acho que seja muito, mas eles mudam, via RTD, aproximadamente a cada 1 segundo. Às vezes mais rápido. Cada um tem o seu tempo.

O que pode pesar, ao meu ver, é que estes 10 indicadores, estão na mesma col de outros indicadores que totalizam 70 deles. Por isso o range era de [D3:D71]. Os 10 que estou usando atualmente estão aí nesse meio.

Para não exigir processamento desnecessário, eu tiro os links RTD de todos os indicadores que não estou usando.

Um aspecto importante desde planilha realmente é velocidade, o tempo de resposta.

Midori · 26 de julho de 2022

@Alexandre José Costa Fiz outras alterações nas duas Subs. Testei e atualizou corretamente.

Private Sub Worksheet_Calculate()
    Dim Valor As Range
    
    For Each Valor In [D3:D71]
        If Not IsError(Valor) Then
            If Valor.Value <> "" Then
                Call Atualiza(Valor, 5)
            End If
        End If
    Next Valor
End Sub

Sub Atualiza(Atual As Range, Colunas As Integer)
    Dim Sequencia   As Range
    Dim Conta       As Integer
    
    If Colunas < 2 Then Exit Sub
    Set Sequencia = Atual.Offset(0, 1).Resize(1, Colunas - 1)
    Conta = WorksheetFunction.CountA(Sequencia)
    
    If Atual.Value <> Atual.Offset(0, 1).Value Then
        If Conta > 0 Then
            Set Sequencia = Atual.Offset(0, 1).Resize(1, Conta)
            If Conta < Colunas Then
                Call Sequencia.Copy(Sequencia.Offset(0, 1))
            Else
                Call Sequencia.Resize(1, Conta - 1).Copy(Sequencia.Offset(0, 1))
            End If
        End If
        Atual.Offset(0, 1).Value = Atual.Value
    End If
End Sub

Alexandre José Costa · 26 de julho de 2022

19 minutos atrás, Midori disse:

@Alexandre José Costa Fiz outras alterações nas duas Subs. Testei e atualizou corretamente.

Private Sub Worksheet_Calculate()
    Dim Valor As Range
    
    For Each Valor In [D3:D71]
        If Not IsError(Valor) Then
            If Valor.Value <> "" Then
                Call Atualiza(Valor, 5)
            End If
        End If
    Next Valor
End Sub

Sub Atualiza(Atual As Range, Colunas As Integer)
    Dim Sequencia   As Range
    Dim Conta       As Integer
    
    If Colunas < 2 Then Exit Sub
    Set Sequencia = Atual.Offset(0, 1).Resize(1, Colunas - 1)
    Conta = WorksheetFunction.CountA(Sequencia)
    
    If Atual.Value <> Atual.Offset(0, 1).Value Then
        If Conta > 0 Then
            Set Sequencia = Atual.Offset(0, 1).Resize(1, Conta)
            If Conta < Colunas Then
                Call Sequencia.Copy(Sequencia.Offset(0, 1))
            Else
                Call Sequencia.Resize(1, Conta - 1).Copy(Sequencia.Offset(0, 1))
            End If
        End If
        Atual.Offset(0, 1).Value = Atual.Value
    End If
End Sub

@Midori, vou atualizar aqui.

Atualizei o código aqui. Está informando um erro. Por favor, veja os dois prints de tela.

Midori · 26 de julho de 2022

@Alexandre José Costa Tem alguma célula mesclada? Se tiver remova a mescla. Caso não seja isso talvez a planilha esteja tirando a referência do comando copy.

Alexandre José Costa · 26 de julho de 2022

1 hora atrás, Midori disse:

@Alexandre José Costa Tem alguma célula mesclada? Se tiver remova a mescla. Caso não seja isso talvez a planilha esteja tirando a referência do comando copy.

@Midori

Certo. A mescla acho pouco provável. Mas vou verificar com cuidado. Você supõe que a mescla deva estar em algum lugar específico? Suponho que eu deva procurar na aba onde o código está e na coluna D. Certo?

Sobre a planilhar estar "tirando a referência do comando copy" como devo proceder?

Midori · 27 de julho de 2022

@Alexandre José Costa Feche esse arquivo e crie uma nova planilha (do zero), coloque apenas um link RTD (célula A1 por exemplo) e teste a macro. O Calculate vai ficar assim,

Private Sub Worksheet_Calculate()
    Dim Valor As Range
    
    Set Valor = [A1]
    
    If Not IsError(Valor) Then
        If Valor.Value <> "" Then
            Call Atualiza(Valor, 5)
        End If
    End If
End Sub

Se atualizar as colunas sem erro, coloque mais links nessa planilha de teste (5 p.ex),

Private Sub Worksheet_Calculate()
    Dim Valor As Range
    
    For Each Valor In [A1:A5]
        If Not IsError(Valor) Then
            If Valor.Value <> "" Then
                Call Atualiza(Valor, 5)
            End If
        End If
    Next Valor
End Sub

Veja se ocorre algum erro nesses testes. A Sub Atualiza é a última que postei.

Alexandre José Costa · 27 de julho de 2022

3 horas atrás, Midori disse:

Feche esse arquivo e crie uma nova planilha (do zero), coloque apenas um link RTD (célula A1 por exemplo) e teste a macro. O Calculate vai ficar assim,

Bom dia Midori!

Obrigado pelo retorno. Vou fazer isso e logo lhe digo como foi.

Alexandre José Costa · 27 de julho de 2022

Olá Midori,

Demorei para retornar porque enfrentei problemas com arquivos "somente leitura". Além disso, o Excel pedia para salvar em outras extensões que permitissem rodar as macros. Mas só posso usar a extensão ".xls" porque o software que entrega o RTD só enxerga ".xls".

Achei bom lhe dizer isso porque as interferências podem vir de outros lugares e pode ser que devemos considerá-las. Dito isso, vamos ao resultados dos testes que você me orientou.

Teste 1 ("nova planilha com um link RTD")

Usei o primeiro Calculate e rodou o único link RTD perfeitamente, armazenando as últimas cinco alterações. (V-1, V-2, V-3, V-4, V-5).

Teste 2 ("mais links RTD na planilha de teste")

Usei o segundo Calculate e rodou cinco links RTD perfeitamente. Da mesma forma ele armazenou as últimas alterações.

Eu também coloquei o VBA na planilha antiga (que deu problema)

Aí não vai. Ela de cara dá problema de navegação, trava e só fecha matando o processo via o gerenciador de tarefas. Já tentei fazer uma cópia dela, salvando-a com outro nome, e apresenta o mesmo problema.

Tentei copiar algumas abas da planilha antiga para a planilha nova (já que preciso das fórmulas), mas não consigo. A saber cliquei no canto superior esquerdo da planilha (antes do "A" e antes do "1"), mas não obedece.

Estou pensando se o Excel não perdeu nenhum suplemento, ou DLL, ou registro. Também pensei em reinstalar o Office 2016. Porque agora, mesmo sem o VBA, a planilha antiga está travando.

21 minutos atrás, Alexandre José Costa disse:

Tentei copiar algumas abas da planilha antiga para a planilha nova (já que preciso das fórmulas), mas não consigo.

Agora, consegui copiar algumas abas para a planilha nova. Para isso eu desliguei o VBA da plan nova e deletei o VBA da plan antiga. Vou tentar rodar as fórmulas da antiga na plan nova. Logo volto para dizer se deu certo.

Midori · 27 de julho de 2022

@Alexandre José Costa Se os testes rodaram sem erro então a sua planilha pode estar corrompida (você comentou que ficou assim após um fechamento repentino). Se for isso talvez seja possível reparar, mas acho melhor criar outra do zero ou pegar algum backup. Arquivos xls também aceitam macros, mas se preferir pode deixar um arquivo só para atualizar o RTD e outro com a macro. Dependendo da quantidade de Loops em um procedimento, a planilha pode deixar de responder. Normalmente a função DoEvents resolve esses casos, mas só testando a planilha real para entender se isso é necessário e adequado para o caso.

Alexandre José Costa · 27 de julho de 2022

@Midori

Entendi. Vou percorrer cada um dos casos que você menciona. Já estou com falando com o técnico que instalou o Office 2016 há uns 15 dias. Não acho que seja o caso, mas estou vendo.

De qualquer forma, vou mantê-la informada dos meus passos aqui. Assim, você poderá acompanhar.

Posso voltar com você aqui, assim que eu eliminar outras possíveis ocorrências? Assim, podemos explorar o DoEvents ou outras coisas.

Alexandre José Costa · 28 de julho de 2022

@Midori

Boa tarde.

Espero que tudo esteja bem.

Como prometi, eu trago novas informações para mantê-la informada dos passos que dei aqui.

De ontem para hoje fiz uma extensa bateria de testes e documentei cada passo. Montei um relatório deste trabalho ingrato, porém, detalhado.

Se você puder, deixei o relatório anexo para que veja. Se quiser, você pode pular direto para o item conclusão, no fim do documento. Ele não é definitivo e, por isso, trago-o até você.

Se se interessar você poderá ver os detalhes, em cada passo a passo que foi relatado no decorrer do documento.

Acredito que as infos do relatório, com prints, possam ajudar a entender melhor o porquê dos travamentos.

Sou muito agradecido por sua ajuda e aguardo seu retorno.

Obrigado!

Relatório de Teste_Planilha vs VBA.docx

Alexandre José Costa · 29 de julho de 2022

6 horas atrás, Alexandre José Costa disse:

@Midori

Boa tarde.

Espero que tudo esteja bem.

Como prometi, eu trago novas informações para mantê-la informada dos passos que dei aqui.

De ontem para hoje fiz uma extensa bateria de testes e documentei cada passo. Montei um relatório deste trabalho ingrato, porém, detalhado.

Se você puder, deixei o relatório anexo para que veja. Se quiser, você pode pular direto para o item conclusão, no fim do documento. Ele não é definitivo e, por isso, trago-o até você.

Se se interessar você poderá ver os detalhes, em cada passo a passo que foi relatado no decorrer do documento.

Acredito que as infos do relatório, com prints, possam ajudar a entender melhor o porquê dos travamentos.

Sou muito agradecido por sua ajuda e aguardo seu retorno.

Obrigado!

Relatório de Teste_Planilha vs VBA.docx 555 kB · 6 downloads

@Midori

Uma suspeita adicional q percebi após os testes:

O link RTD que da linha D63 deve ter uma taxa de carregamento mais acelerada devido ao indicador q ele reporta. Digo Isso porque é comum ele preencher os cinco campos quase de uma única vez. Isso pode estar causando o travamento. Campos todos iguais de uma única vez.

Midori · 29 de julho de 2022

@Alexandre José Costa Antes de atualizar os valores o Calculate pode desativar os eventos com EnableEvents, veja se assim resolve o problema do travamento,

Private Sub Worksheet_Calculate()
    Dim Valor As Range
    
    Application.EnableEvents = False
    For Each Valor In [D3:D71]
        If Not IsError(Valor) Then
            If Valor.Value <> "" Then
                Call Atualiza(Valor, 5)
            End If
        End If
    Next Valor
    Application.EnableEvents = True
End Sub

Se ainda não resolver e se acha que o problema pode ser a taxa de atualização você pode tentar modificar isso com ThrottleInterval (Application.RTD.ThrottleInterval). Nas documentações diz que o intervalo de atualização do RTD é a cada 2 segundos.

https://docs.microsoft.com/en-us/office/vba/api/excel.rtd.throttleinterval

Alexandre José Costa · 29 de julho de 2022

@Midori

Bom dia e obrigado pelo retorno. :-)

Vou aplicar as alterações que você sugere e logo falo como se comportou.
Sobre o intervalo de atualização do RTD você tem razão. O manual do TRYD (plataforma que se conecta a plan e entrega o RTD), diz:

"Por padrão, utilizando o RTD, o intervalo de atualização dos dados no Excel é de 2 segundos. Este intervalo pode ser alterado executando um comando simples no Excel.

Seguem os passos para executar o comando:

...

3. Digitar o seguinte comando na tela Immediate: Application.RTD.ThrottleInterval = 200 (onde 200 é o tempo, em milissegundos, a ser definido)

4. Teclar Enter estando com o cursor de edição de texto imediatamente após o comando digitado.

Após executar estes passos corretamente o intervalo de atualização dos dados no Excel via RTD já estará alterado. Você já pode fechar a tela Microsoft Visual Basic."

Eu prefiro a taxa seja rápida, se for possível.
Logo volto aqui para dizer com foi. Tks!

Alexandre José Costa · 29 de julho de 2022

@Midori

4 horas atrás, Midori disse:

Antes de atualizar os valores o Calculate pode desativar os eventos com EnableEvents

Eu fiz esta alteração que você sugeriu. Está rodando. Senti que a plan está mais fluida. Espero que esta alteração no VBA estabilize. Vou continuar observando nas próximas horas e ver como a plan se comporta conforme os links RTD vão entrando, então retorno aqui para lhe dizer como foi.

Muito, muito agradecido por sua atenção.

Alexandre José Costa · 13 de agosto de 2022

@Midori

Boa tarde.
Espero que você esteja bem.

Estou retornando depois de um bom tempo para dizer que sua última alteração tem funcionado perfeitamente. Sua ajuda foi incrível!

Muito obrigado, mais uma vez.