Application.WorksheetFunction(Now) getting error

[General Ledger]

Dear All,

I am using Excel 2003 with Windows XP on a network. I keep getting errors with some code. In a sheet, I have:

Sub GetUser()
Range("Created").Value = Application.WorksheetFunction(Now())
Range("Author").Value = Application.UserName
End Sub

The code for the Author works perfectly. The code for Created where it is suppose to get todays date, I get an error:

Run-Time error '438':
Object does not support this property or method

When I execute the code in break mode (run line by line), I hover my curosr over the Now element and do see it does contain todays date.

Does it make a difference if this code is in the sheet or in a module? I get the same errror whether the code is in either place.

What makes this worse is I swear it worked a week ago but not now. I have made many changes since. Weird but it must have been something I did.

Thanks,

GL

 

[VoG]

Try

Range("Created").Value = Now

 

[jeffreybrown]

How about...

Sub GetUser()
Range("Created").Value = Now
Range("Created").Value = Application.UserName
End Sub

It shouldn't make a difference if you place the code in sheet module or in a module

 

[General Ledger]

Once again, simplier is better.

Works: Range("Created").Value = Now

Doesn't work: Range("Created").Value = Application.WorksheetFunction(Now())

Thanks very much Jeffrey and Vog

 

[HackSlash]

The VBA command "Now" has a precision of 1 second where the WorksheetFunction Now has millisecond precision. I ended up here from a google search trying to find how to call the worksheet function from VBA. One method is to use brackets like this:

[B]Range("Created").Value = [Now()][/B]

However, bracketed expressions cannot be validated at compile-time. This is why I was looking for a way to call the worksheet function using the application object instead of a bracketed call.

 

[Rick Rothstein]

However, bracketed expressions cannot be validated at compile-time.

What do you mean by this? What kind of validation are you wanting to do?

 

[HackSlash]

A host-specific bracketed expression is only evaluated at runtime. This means that your code will compile and you might still get run-time errors. If you use the Application model then the VBE can show you errors at compile time.

SEE HERE:
http://rubberduckvba.com/Inspections/Details/HostSpecificExpressionInspection

 

[Rick Rothstein]

A host-specific bracketed expression is only evaluated at runtime. This means that your code will compile and you might still get run-time errors.

I understand that... I guess my problem is trying to image the run-time error you think you might get if you use [NOW()], which always evaluates to a Double, in your code. Can you cite an example where you think using [NOW()] would cause a run-time error for you?

 

[Tetra201]

The VBA command "Now" has a precision of 1 second where the WorksheetFunction Now has millisecond precision. I ended up here from a google search trying to find how to call the worksheet function from VBA...

Why not use the Timer function?