Passa valores em vez de intervalo para a função VBA

Question

Estou criando funções VBA no Excel 2007. Um deles tem um protótipo:

Function ToLevelCode_Range(val As Double, ByRef R As Range)

Eu posso chamar assim muito bem e obtém o resultado correto:

=ToLevelCode_Range(B2, Categories!D1:D4)

Existe uma instância em que quero que o segundo parâmetro seja um conjunto específico de valores:

=ToLevelCode_Range(B2, { 0.38, 0.78, 1.18, 1.58 })

Isso resulta em #VALUE e não consigo nem chegar ao primeiro ponto de interrupção em minha função.

Estou programaticamente gerando a planilha, e o número de valores na matriz é variável, então não posso fazer isso com uma função com 5 parâmetros, etc. Não sei se o VBA tem uma sintaxe de lista de argumentos variável , mas isso seria uma opção.

Como posso fazer isso funcionar?

    

Answer 1

ByRef significa que você tem que passar uma referência para (o endereço de) um objeto existente como argumento, para que ele possa trabalhar com esse objeto real; vs. ByVal, o que significa que você passaria um valor, e esse valor seria copiado para o argumento de recebimento.

Assim, você não pode passar uma lista de valores como referência, você precisa criar um objeto Range que mantenha seus valores e, em seguida, passar o objeto (referência a ele) como esse argumento.

O problema aqui é que um objeto Range no Excel é uma representação de um conjunto real de Células, mas acredito que o NamedRange permitirá que você crie programaticamente um objeto Range" virtual ", com células virtuais que contenham seus valores, mas eu nunca usei isso pessoalmente . :)

Acredito que esta questão no StackOverflow é (basicamente) a mesma que a sua e vale a pena conferir:

Microsoft Excel: crie programaticamente um intervalo nomeado oculto

    

Answer 2

Se você alterar os parâmetros da função para

Function ToLevelCode_Range(val As Double, R As Variant)

ele aceitará um intervalo e uma matriz

É claro que você precisará lidar com os diferentes tipos de dados no código de função

Quando chamado como =ToLevelCode_Range(B2, Categories!D1:D4) R, o tipo será Variant/Object/Range

Quando chamado como =ToLevelCode_Range(B2, { 0.38, 0.78, 1.18, 1.58 }) R, o tipo será Variant/Variant(1 to 4)