Array global na memória do Excel?

Question

Eu tenho uma folha grande Sheet1 no meu Excel, usando o CSE eu a filtrava e gerava uma matriz em um intervalo chamado filtered_result in Sheet2 ; filtered_result é depois usado por muitas consultas em Sheet3 till Sheet10 , principalmente por VLookUp etc, tudo funciona bem.

filtered_result em Sheet2 é grande e seu cálculo já está lento, como resultado intermediário, acelera o cálculo em Sheet3 till Sheet10 .

Agora, eu gostaria de remover Sheet2 , para evitar a visualização do resultado intermediário. Isso ainda é possível, pois eu poderia modificar a fórmula em Sheet3 till Sheet10 , mas o Excel fica muito lento.

Existe alguma maneira de armazenar de alguma forma o resultado intermediário original filtered_result em Sheet2 como algo na memória, e consultas posteriores ainda poderão recuperá-lo?

    

Answer 1

Não use fórmulas de matriz, use fórmulas regulares.

Por exemplo, digamos que você tenha uma fórmula que adicione a coluna B se o valor na coluna A for maior que 10. Não crie uma fórmula de matriz com uma condicional para fazer isso. Em vez disso, crie uma nova coluna C com a fórmula:

=If( $A > 10, $B, 0 )

Portanto, a terceira coluna contém o valor de B, se a condição for atendida, ou 0, se não for atendida. Agora, basta somar a coluna C. Esse método geralmente é muito mais rápido que uma fórmula de matriz. É chamado de "coluna auxiliar".

Se ainda for lento para calcular, use macros ou VBA para calcular os valores; Dessa forma, você só precisa computá-los uma vez, não recatá-los se as coisas não mudarem. Portanto, no exemplo acima, em vez de usar a fórmula condicional, podemos usar uma macro (ou código VBA) para calcular a fórmula If e colocar o resultado na célula. Em seguida, execute a macro sempre que precisar calcular a tabela e gerar valores. Sem condicionais (instruções If) na própria planilha, ele será calculado muito mais rápido.

Como fazer tudo na memória

Se você quiser se livrar da planilha completamente, você pode fazer tudo na memória usando variáveis estáticas em um módulo de código VBA:

Dim MyArray(1000, 2000) As Double

Sub computeMyArray()
   ... compute all the values of MyArray
End sub

Function GetValueFromMyArray( Dim x as Integer, Dim y as Integer )
   GetValueFromMyArray = MyArray( x, y )
End Function

Você pode usar essa função em qualquer célula de sua pasta de trabalho para obter valores fora da matriz que você criou, que é a memória. Por exemplo, uma célula na pasta de trabalho pode ter a fórmula:

=GetValueFromMyArray( 5, 6 )

Isso recuperaria a quinta linha, a sexta da matriz que está na memória.

    

Answer 2

A solução mais fácil e mais burra é apenas ocultar a planilha Sheet2 ( link ).

Embora esta solução não seja sofisticada, ela não exige que você mude qualquer coisa na sua aplicação e resolverá o problema de exibição lenta.

    

Answer 3

Esse snipet deve funcionar sem muita edição. Resumindo, você é melhor de fazer um sub para fazer o array, e ele deve ficar na memória contanto que você chame o sub de novo.

Public Sub MakeArrayGlobal(byRef Range as Range)
    dim MyPublicArray() as Variant
    with Range
        counter = 0
        For each cell in range
            MyPublicArray(counter) = cell.value #Or whatever you need
            counter = counter + 1
        Next
    End with
End sub