VBA Function can't open workbook.

[doob_dn]

Hi all,

I need to run a function that use some data from an opened file.

Of course, if the file isn't opened, the function can't run. So I insert a code to check and open the file case closed.

Problem is Excel is ignoring the Open comand if called by a function or running directly from the function. To test, I run the Open sub alone and works perfectly.

How should I code the function to open the file?


My code is here. 1st part is the main function itself, 2nd is the check, 3rd is the sub to open file:

Function cdi_acumulado(Data_inicial As Long, Data_final As Long)

aDiretorio = "c:\COLABORADORES\"
aArquivo = "CDI Diário.xlsx"
aEndereco = aDiretorio & aArquivo
aSheet = "Índices Correto"
aTabela = "B2:H11230"
aNumerador = Empty
aDenominador = Empty


If Teste_Arquivo_Aberto(aArquivo, aEndereco) = False Then
[B]---> If I try to open here, doesnt work --> [/B]Application.Workbooks.Open Filename:=aEndereco, ReadOnly:=True

End If

aNumerador = Application.VLookup(Data_final, Workbooks(aArquivo).Sheets(aSheet).Range(aTabela), 5, False)
aDenominador = Application.VLookup(Data_inicial, Workbooks(aArquivo).Sheets(aSheet).Range(aTabela), 5, False)
cdi_acumulado = aNumerador / aDenominador

End Function
----------------------------------------------------------------
Function Teste_Arquivo_Aberto(aArquivo, aEndereco) As Boolean

    On Error Resume Next
    Teste_Arquivo_Aberto = Not (Application.Workbooks(aArquivo) Is Nothing)
    
If Teste_Arquivo_Aberto = False Then
[B]--> If I try to open here, doesnt work either --> [/B]Call Abrir_Arquivo_CDI

End If
End Function

----------------------------------------------------------------
[B]--> Running this alone works fine, calling this won't work at all --> [/B]Sub Abrir_Arquivo_CDI()
Application.Workbooks.Open Filename:="c:\COLABORADORES\CDI Diário.xlsx", ReadOnly:=True
End Sub

 

[Norie]

Where/how are you using the function?

 

[doob_dn]

Where/how are you using the function?

I'm using function directly in a cell. It's a piece to do some financial calculations.

The main function will be used tons of times (400.000 times or more every day) and it depends on divide one coefficient by another one. The coefficients depend on the dates I insert into the main function, that's why the vlookup stuff.

It's is working pretty fine if I open the needed file first. But if it's closed, the function can't open it at all. Or if I try to call a sub routine, it will not work either.

But if I run the sub routine alone, it works.

Maybe I can improve it to have better performance but this will be the next step.