×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

UDF not calculating automatically

UDF not calculating automatically

UDF not calculating automatically

(OP)
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

RE: UDF not calculating automatically

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

CODE

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.

RE: UDF not calculating automatically

(OP)
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

RE: UDF not calculating automatically

Call your UDF NOT in Module2

Put your code in here:


---- Andy

There is a great need for a sarcasm font.

RE: UDF not calculating automatically

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 smile


---- Andy

There is a great need for a sarcasm font.

RE: UDF not calculating automatically

(OP)
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.

RE: UDF not calculating automatically

Cells(6, 1) is cell A6
F1 will be Cells(1, 6)


---- Andy

There is a great need for a sarcasm font.

RE: UDF not calculating automatically

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

RE: UDF not calculating automatically

I am glad it worked for you thumbsup2

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


---- Andy

There is a great need for a sarcasm font.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close