Mixed messages

[mikecox39]

I know that [=NOW ()] delivers the current date and time; depending on how the cell is formatted, but that function is dynamic and, if left in the cell, changes over time.


I read somewhere that you can copy the result of that formula the paste it back, as data, to convert it to as static date.


So I tried to create a macro that does that and was successful. However I got a lot of mixed messages in the process.

Is this the easiest way to create a current date and time? I was thinking of using this macro as a kind of Function, that I could "call" from other macros to enter my dates.


https://app.box.com/s/l9ejhyrxoamweuvf649dg9le58iu21zc

 

[mikecox39]

mike you could also use

sub test()
    with range("a1")
        .value = now()
        .value = .value
    end with
end sub

Bingo

Thanks Michael, that's exactly what I was hoping for, clean and simple!

Would you mind explaining how that works? Why with/ end with? Is .value a variable? Why is it pref with a (.)?

 

[My Aswer Is This]

If you only want the current Time put into a cell that is static and will never change and your using Vba to do this I always use:
This puts the value into the cell not a formula so it will never change.

Cells(5, 5).Value = Time

 

[Michael M]

Hey Mike
The with / end with is just a simplication of

sub test()
   range("a1") .value = now()
   range("a1") .value = range("a1") .value
end sub

Shorter, cleaner, easier to read.....

 

[My Aswer Is This]

I normally use With when I want to perform several different task to the same Range

Like this:

Sub My_With()
'Modified 5/20/2019 3:52:43 AM  EDT
With Cells(1, 1)
    .Value = "Alpha"
    .Font.Size = 16
    .Font.Bold = True
    .Font.Color = vbRed
    .Interior.ColorIndex = 4
End With
End Sub

 

[eduzs]

Yes, date written is non volatile, you're writing in the cell a value (date), not a formula.

 

[mikecox39]

Hey Mike
The with / end with is just a simplication of

sub test()
   range("a1") .value = now()
   range("a1") .value = range("a1") .value
end sub

Shorter, cleaner, easier to read.....

Nice! Thanks