As UDFs do Excel são atualizadas quando você aplica a classificação

Question

Como a pergunta sugere, estou vendo efeitos colaterais (lentidão) dos UDFs que criei quando aplico uma classificação à minha planilha. Eu criei algumas UDFs no .NET e implantei para teste. A função, nos bastidores, faz uma chamada de API e retorna os dados de volta à célula. Então, estou esperando algum atraso quando criar a função para ser executada inicialmente. MAS estou vendo alguma lentidão no aplicativo quando eu classifico as colunas como se as UDFs estivessem recalculando e chamando as APIs. Este comportamento normal da planilha?

Minha função tem referências de célula e se parece com o seguinte:

=wsStateMiles("ALKCUR",G45,K45,1,J45,$G$2)

Quando removo a função, a planilha é classificada rapidamente, já que a primeira coisa que tentei deduzir foi a lentidão real do Microsoft Excel.

(informações adicionais)

Isso não tem nada a ver com a classificação, mas quando adiciono UDFs adicionais, quase parece que o Excel está sendo executado tão lentamente que recalcula novamente as UDFs da planilha inteira. Novamente, esse comportamento normal do Excel?

    

Answer 1

Para a classificação, infelizmente, esse é o comportamento esperado.

Quando você classifica uma tabela, o Excel suga todas as células da tabela. Um UDF com qualquer argumento referenciando uma célula dentro da tabela será recalculado.

Para o caso de adicionar UDFs adicionais, não sei por que ele recalcula todos os UDFs. Um VDF UDF normal e não volátil só fará com que a célula inserida seja calculada. Talvez seus UDFs .NET sejam sempre voláteis ou haja uma configuração / atributo definido como "volátil"? Eu sei que as UDFs de código gerenciado têm um atributo para ligar / desligar a volatilidade.

Felizmente, há boas notícias. Mesmo que não seja possível parar as UDFs de recalcular, é possível acelerá-las.

Requer uma matriz estática na UDF para armazenar o estado atual de todos os valores de retorno (ou seja, a coluna "Miles", L:L , no seu exemplo) e uma célula "Suspend" usada como um argumento para o UDF Antes de classificar, defina a célula Suspender como TRUE e depois volte para FALSE .

Quando suspenso, a UDF retornará o resultado correspondente à célula do chamador dos valores armazenados. Quando reiniciado, ele funcionará normalmente, mas também atualizará o valor armazenado.

O truque para fazer isso funcionar é também implementar a histerese. A primeira chamada para o UDF por célula após reiniciar, também deve retornar o valor armazenado.

Como alternativa, em vez de usar uma célula "Suspender", armazenando todos os argumentos transmitidos na matriz estática, a UDF sempre poderia retornar o valor armazenado, a menos que os argumentos mudassem. Depois que eles forem alterados, a API será chamada e o resultado será armazenado antes de ser devolvido.

Essa segunda técnica também aceleraria automaticamente o recálculo ao adicionar UDFs adicionais. (A primeira técnica também pode ser usada ao adicionar UDFs por "suspensão" antes de adicioná-los e "resumir" depois).