How do I make the Now function display miliseconds in VBA macro

[NeuroSpace]

Hello everyone!

I am using a VBA macro suggested by one of you guys (http://www.mrexcel.com/forum/excel-...se-clicks-inputs-neuroscience-experiment.html).

What the macro does is it inputs a timestamp every time a keyboard combination is used (alt+right arrow in this case).

The part of the macro which inputs the timestamp is like this:

Sub AltRight_Sub()
On Error Resume Next
Cancel = True
Cells(Rows.Count, 2).End(xlUp).Offset(1) = Format(Now, "HH:MM:SS")
End Sub

I would like the timestamp to be displayed as HH:MM:SS.00, i.e. show the milliseconds. I tried playing around with the timestamp format (changing it to = Format(Now, "HH:MM:SS.00" or = Format(Now, "HH:MM:SS.0;@")) or = Format(Now, "MM:SS.000") and I tried using = Evaluate("=NOW()") instead of = Format(Now, "HH:MM:SS".

None of this worked so again, I kindly ask you for your help!

Thanks!

 

[Joe4]

See: VBA Tips & Tricks: Excel VBA TimeStamp – Milliseconds using Excel VBA

 

[NeuroSpace]

See: VBA Tips & Tricks: Excel VBA TimeStamp – Milliseconds using Excel VBA

Thanks for the reply! Unfortunately my knowledge of excel VBA is fairly limited and I don't know how to use this information in my macro.

I could benefit also from just receiving hundredths of second or tenth of second data. The now function can normally be formatted to show tenths of a second (MM:SS.0). How could I modify my macro so that the times are shown in this format? As I already said, simply using MM:SS.0 in the macro instead of HH:MM:SS appends a "0" after each timestamp.

Thanks!

 

[NeuroSpace]

Can anybody offer any further advice on this?

I should also mention that I run this on a Mac OS 10.9.1

 

[Joe4]

All you need to do is make a slight modification to the Function provided in the link I sent you to remove the date piece. Then just paste the resulting Function in the VB Editor in a Standard Module (or the module where your macro exists), i.e.

Public Function TimeInMS() As String
    TimeInMS = Strings.Format(Now, "HH:nn:ss") & "." & Strings.Right(Strings.Format(Timer, "#0.00"), 2)
End Function

Then just call that function in your code:

Sub AltRight_Sub()
    On Error Resume Next
    Cancel = True
    Cells(Rows.Count, 2).End(xlUp).Offset(1) = TimeInMS()
End Sub

That's all there is to it!

 

[NeuroSpace]

That's all there is to it!

It sure is!! Thanks for walking me through it!

Cheers!

 

[Joe4]

Your welcome! Glad you got it working out!

 

[bs0d]

All you need to do is make a slight modification to the Function provided in the link I sent you to remove the date piece. Then just paste the resulting Function in the VB Editor in a Standard Module (or the module where your macro exists), i.e.

Public Function TimeInMS() As String
    TimeInMS = Strings.Format(Now, "HH:nn:ss") & "." & Strings.Right(Strings.Format(Timer, "#0.00"), 2)
End Function

Then just call that function in your code:

[COLOR=#574123]Sub AltRight_Sub()[/COLOR]
[COLOR=#574123]    On Error Resume Next[/COLOR]
[COLOR=#574123]    Cancel = True[/COLOR]
[COLOR=#574123]    Cells(Rows.Count, 2).End(xlUp).Offset(1) = [/COLOR]TimeInMS()
[COLOR=#574123]End Sub[/COLOR]

That's all there is to it!

How might you modify to compare the difference between two time readings from the same function in VBA?