UDF not calculating automatically 1

[MM1963]

I created a UDF to return the modify date and time of a file located on a network drive. I entered the function in a cell and UDF returns the correct date. But, how do I get the function to automatically calculate? When I open the file, the last value of the UDF still displays in the cell even though a new version of the file that the UDF points to has updated. If I click in the cell that contains the UDF and press F2, then hit enter, the date value updates. The formula calculation method is set to automatic. Here is the UDF.

Public Function RecDate()
RecDate = FileDateTime("F:\folder1\Projections\Rec File.xlsx")
End Function

 

[Andrzejek]

Did you try to put the call to your User Defined Function in Workbook_Open event instead?

Option Explicit

Private Sub Workbook_Open()
Sheet1.Cells(1, 1).Value = RecDate
End Sub

This should place the modify date and time of a file located on a network drive in cell A1


---- Andy

There is a great need for a sarcasm font.

 

[MM1963]

Thanks for your reply. Not sure what I did wrong. The date did not appear. I want the date to appear in cell F1 on the “Report USA” tab. Here is what I entered in two separate modules

Module1
Public Function RecDate()
RecDate = FileDateTime("F:\folder1\Projections\Rec File.xlsx")
End Function

Module 2
Option Explicit
Private Sub Workbook_Open()
Sheets("Report USA").Cells(6, 1).Value = RecDate
End Sub

 

[Andrzejek]

Call your UDF [red]NOT[/red] in Module2

Put your code in here:

---- Andy

There is a great need for a sarcasm font.

 

[Andrzejek]

I am all for using UDF, but since yours is just one line of code (and probably not used anywhere else in your code), you may as well do:

and eliminate your UDF from Module1


---- Andy

There is a great need for a sarcasm font.

 

[MM1963]

Still not working. Right now cell F1 is blank. I assume the code would place the value of RecDate in cell F1. I placed the code under "ThisWorkbook" as you pictured.

 

[Andrzejek]

[tt]Cells(6, 1)[/tt] is cell A6
F1 will be [tt]Cells[blue](1, 6[/blue])[/tt]


---- Andy

There is a great need for a sarcasm font.

 

[MM1963]

I did not know that and column A was hidden so I did not see it. Thanks again.

 

[Andrzejek]

I am glad it worked for you

And don't drink the water from your faucet in NJ...


---- Andy

There is a great need for a sarcasm font.