Excel VBA função definida pelo usuário - erro #VALOR!

[HelderREC]

Olá pessoal

Tenho um problema em uma função que fiz. Sempre retorna erro "#VALOR!". 

O código da função é:

Function CDI(DataInicial As Date, DataFinal As Date, PercentualCDI As Long) As Long
Dim a, b As Date
Dim diini As Integer, difin As Integer
Dim s, e, taxas, Cell As Range
   
    With Planilha3.Range("E1:E10000")
         s = Range(.Find(DataInicial, LookIn:=xlValues).Address).Row
         e = Range(.Find(DataFinal, LookIn:=xlValues).Address).Row - 1
    End With
    
Set taxas = Planilha3.Range(Cells(diini, 6), Cells(difin, 6))
 
CDI = 1
For Each Cell In taxas
    CDI = CDI * (Cell.Value / 100 * PercentualCDI + 1)
Next Cell

End Function
 

Para testar o cálulo executado eu criei uma sub que está funcionando bem. Mas gostaria de ter a mesma funcionalidade em uma função

A Sub que fiz é essa:

Sub CDI1()
    Dim s, e, taxas As Range
    Dim DtIni, DtFim As Date
    Dim CDI, percuntualCDI As Long
    
    DtIni = Range("a1").Value
    DtFim = Range("a2").Value
    PercentualCDI = Range("a3")
    
    With Planilha3.Range("E1:E10000")
         s = Range(.Find(DtIni, LookIn:=xlValues).Address).Row
         e = Range(.Find(DtFim, LookIn:=xlValues).Address).Row - 1
    End With
    
    Set taxas = Planilha3.Range(Cells(s, 6), Cells(e, 6))
    
    CDI = 1
    For Each Cell In taxas
        CDI = CDI * (Cell.Value / 100 * PercentualCDI + 1)
    Next Cell
    
    Range("A4") = CDI

End Sub
 

Eu acho que o erro na FUNÇÃO está relacionado a declaração das variáveis...mas não sei como resolver esse erro.

 

Alguém pode me ajudar?

 

obrigado

Helder

[Edson Luiz Branco]

Boa noite, @HelderREC

Tem muitas coisas que não estão OK nesse trecho de código, mas as principais que devem estar retornando esse erro são:

• As variáveis diini e difin não foram inicializadas, portanto estão com valor zero. Mesmo assim são usadas na linha:
  Set taxas = Planilha3.Range(Cells(diini, 6), Cells(difin, 6)) --------- isso causa erro. Substituí-las ao que parece por s e e:
  Set taxas = Planilha3.Range(Cells(s, 6), Cells(e, 6))
• O método Find foi idealizado para procurar strings portanto tem problemas ao buscar datas diretamente. Melhor convertê-las ou formatá-las para String antes:
  s = .Find(CStr(DataInicial), LookIn:=xlValues).Row
  e = .Find(CStr(DataFinal), LookIn:=xlValues).Row - 1
  Ou:
  s = .Find(Format(DataInicial, "dd/mm/yyyy"), LookIn:=xlValues).Row
  e = .Find(Format(DataFinal, "dd/mm/yyyy"), LookIn:=xlValues).Row - 1
• Tem certeza que sua função CDI deve retornar um Long? você está fazendo um loop com operações intermediárias de divisão na linha:
  CDI = CDI * (Cell.Value / 100 * PercentualCDI + 1)
   Como você não anexou um arquivo, não sei que valores estão na coluna F para saber se a operação pode ou não retornar decimais. Apesar de isso não gerar erro no código, seu cálculo pode não retornar o valor esperado devido aos arredondamentos a cada loop.

[HelderREC]

Edson,

Obrigado pela resposta!

 

Dos três pontos que você citou:

 

1 - Você está certo...fiz a alteração

2 - Fiz também essa alteração para formatar a data. Tentei as duas opções segeridas: "Format" e "CStr". Ainda assim não deu certo.

3 - Eu utilizei Long por ser a de maior abrangência, mas acho que poderia ser Double (alguma sugestão diferente?  não conheço muito bem)

 

Testei o código da Função com essas alterações mas mesmo assim retorna #VALOR!

Quanto ao arquivo, não consegui anexar pois é um ".xlsm" (pelo que vi não é permitido no site)

 

Mas os dados são os seguintes:

 

Colunas E e F

Datas e Valor da taxa dirárias. (Coluna E no formato de data dd/mm/yy), Coluna F número com "0,000000")

Exemplo:

 

Resumindo o objetivo da Função:

Encontrar a taxa acumulada CDI em um range de taxas diárias * um percentual do CDI.

Os dados de entrada na função são:

1)Data inicial 

2)Data final

3)PercenualCDI (em alguma outra célula qualquer formato 0,00 - 0,98 por exemplo)

 

O código então deve buscar a data inicial e final na coluna E e retornar o range (intervalo) de taxas correspondente da coluna F. Então executa o cálculo abaixo

CDI = 1
For Each Cell In taxas
    CDI = CDI * (Cell.Value / 100 * PercentualCDI + 1)
Next Cell

 

O código do cálculo está correto. O problema está range "taxas", que não está funcionando.
 

 

[Edson Luiz Branco]

6 horas atrás, HelderREC disse:

3 - Eu utilizei Long por ser a de maior abrangência, mas acho que poderia ser Double (alguma sugestão diferente?  não conheço muito bem)

 

Long é um tipo de dados que só pode conter números inteiros, então não é adequado para seu propósito. Então deve alterar para Double ou Single.

 

6 horas atrás, HelderREC disse:

Quanto ao arquivo, não consegui anexar pois é um ".xlsm" (pelo que vi não é permitido no site)

 

Compacte/zipe o arquivo e anexe aqui que o site vai aceitar.

------------------------------------------

 

Teste assim:

Function CDI(DataInicial As Date, DataFinal As Date, PercentualCDI As Single) As Single
  Dim diini As Long, difin As Long
  Dim taxas As Range, Cell As Range
  With Plan1.Range("E1:E10000")
    diini = .Find(CStr(DataInicial), LookIn:=xlValues).Row
    difin = .Find(CStr(DataFinal), LookIn:=xlValues).Row - 1
  End With
  Set taxas = Plan1.Range(Cells(diini, 6), Cells(difin, 6))
  CDI = 1
  For Each Cell In taxas
    CDI = CDI * (Cell.Value / 100 * PercentualCDI + 1)
  Next Cell
End Function