Utilizar o comando Application.WorksheetFunction.Index com um vetor criado no gerenciador de nomes do Excel

Question

É possível aplicar o comando Application.WorksheetFunction.Index em um vetor montando a partir de fórmula pelo gerenciador de nomes do Excel?

Ao utilizar o comando com um nome do gerenciador de nomes que se refira a um intervalo de células, o resultado é retornado normalmente, mas ao utilizar o mesmo comando com um nome do gerenciador de nomes que é formado a partir de um cálculo, dá erro de tempo de execução '1004'.

Por exemplo, criei um nome que gera um vetor com 0 ou 1 baseado em critérios de filtro de um BD, chamado "Filtro_BD" então o vetor fica neste padrão: {0,1,0,0,0,0,1,0,1,0}

Utilizando a função ÍNDICE na planilha, consigo retornar qualquer valor (Ex: =ÍNDICE(Filtro_BD;2) -> retorna o resultado 1). Mas tentando obter este resultado com o comando Application.WorksheetFunction.Index dá erro

Application.WorksheetFunction.Index(Range("Filtro_BD"), 2)

Testei de várias maneiras, mas acho que este comando só aceita um range de células.

---

Rafael Kamimura

Answer 1

Rafael, não consegui reproduzir seu problema. Criei um nome definido cuja expressão era =DESLOC(Plan1!$H$7;;;11) e pra mim seus dois exemplos funcionaram normalmente.

Teria como disponibilizar sua pasta de trabalho para darmos uma olhada?

---

Felipe Costa Gualberto - http://www.ambienteoffice.com.br

Answer 2

Rafael,

faltou somente indicar em qual guia está esse range...

debug.Print Application.WorksheetFunction.Index(Sheets("Plan1").Range("Filtro_BD"), 2)

---

Natan

Answer 3

Olá Felipe!

Na verdade eu estava fazendo diversos testes em um projeto que estou desenvolvendo no trabalho, e li num site americano que uma das formas de deixar um código mais rápido é buscar calcular funções direto no código via comando worksheetFunction ao invés de mandar o código escrever a função na planilha. Nestes testes me deparei com a função índice e neste projeto estou trabalhando muito com o gerenciador de nomes de forma matricial.

Da forma que você expôs, de fato funciona, pois a função DESLOC retorna um intervalo novo na planilha. O que estou tentando fazer é aplicar o comando worksheetFunction numa matriz gerada. Por exemplo, veja a imagem abaixo:

Observe que a função ÍNDICE na planilha funciona normalmente para a matriz Filtro_BD. A matriz Filtro_BD pode ser observada na avaliação da fórmula:

A minha dúvida é aplicar no VBA a função worksheetFunction.Index no Filtro_BD.

Eu acho que não é possível...

---

Rafael Kamimura

Answer 4

Rafael,

tu testou a forma que enviei?

---

Natan

Answer 5

"uma das formas de deixar um código mais rápido é buscar calcular funções direto no código via comando WorksheetFunction ao invés de mandar o código escrever a função na planilha."

Não fiz benchmark aqui, mas não sei se essa é a melhor forma de ganhar tempo. Eu preferiria utilizar o método Evaluate nesse caso, como por exemplo:

Range("A1") = Evaluate("=SUM(B1:B3)")

De qualquer forma, já que o método Worksheet.Index não é exatamente igual à função ÍNDICE, sugiro que faça algo como:

Debug.Print WorksheetFunction.Index(Evaluate("Filtro_BD"), 1, 1)

Em que o 2o. argumento é a linha e o 3o. argumento é a coluna do nome definido.

---

Felipe Costa Gualberto - http://www.ambienteoffice.com.br

Answer 6

Valeu a dica Felipe!

Como eu disse, estava apenas realizando alguns testes para testar o desempenho do cálculo no VBA vs Excel.

Apenas como informação, segue link do artigo que estava lendo com as dicas para tentar deixar o código mais rápido.

http://www.databison.com/how-to-speed-up-calculation-and-improve-performance-of-excel-and-vba/

Abraços!

---

Rafael Kamimura

Answer 7

Natan, obrigado pela disposição, mas a sua proposta não funciona para este caso do exemplo que apresentei, pois o nome filtro_BD não se refere a um intervalo na planilha, mas é um vetor de resposta a uma fórmula matricial.

---

Rafael Kamimura

Answer 8

Opa, obrigado Rafael, depois vou dar uma olhada.

---

Felipe Costa Gualberto - http://www.ambienteoffice.com.br