A customised function with a timer

[tanababa]

Hi,
I am not familiar with VBA and I probaly make a nice salad ...
However, I am trying to connect a timer to a customised function that reverse a string (as an example for timing a function or a macro).
My original function that works is:

Function reverse(str)
reverse = StrReverse(str)
End Function

The timer code I found is:

Sub TimeTest()
Dim t As Single
t = Time()

'your code here

Debug.Print Second(Time() - t)
End Sub


How do I put the timer code with in the function? It did not work for me.

Thanks,
Tamir

 

[Ruddles]

In what way did it "not work"? Did it stop with an error? Did it return zero?

It works here but always returns zero seconds because it works so quickly. Try this:-

Sub TimeTest()
 
  Dim t As Single
 
  t = Time()
[B][COLOR=blue]  MsgBox "wait a few moments then click 'OK'"[/COLOR][/B]
  Debug.Print Second(Time() - t)
 
End Sub

 

[tanababa]

Hi,
I got an error message when I tried to merge the codes.
I want to have the timer message box appear when I perform the reverse function.
How do I combine it?
Tx,
Tamir

 

[Ruddles]

Sorry, I misunderstood you. Try this:-

Function reverse(str)

  Dim t As Single

  t = Time()

  reverse = StrReverse(str)
 
  Debug.Print Second(Time() - t) ; " seconds"

End Function

 

[tanababa]

I don't get an error but I don't get any time indication as well...
Tamir

 

[Ruddles]

Are you looking in the Immediate window (Ctrl-G)? That's where Debug.Print output is written.

If you want a message box you will have to code it:-

[B]MsgBox[/B] Second(Time() - t) ; " seconds"

 

[tanababa]

When I added that line of the msg box I got an error in the compilation.
My code now is:

Function reverse2(str)
Dim t As Single
t = Time()
reverse2 = StrReverse(str)

Debug.Print Second(Time() - t); " seconds"

End Function


I found the immediate window. Can I have a finer time resolution? I nwould like to see 0.001 of a second.
Tx,
Tamir

 

[Ruddles]

When I added that line of the msg box I got an error in the compilation.

Sorry, brain failure! I should have written:-

MsgBox Second(Time() - t) & " seconds"

Can I have a finer time resolution? I would like to see 0.001 of a second.

Not using the Time() function. Google vba time milliseconds.

 

[tanababa]

Great
Still... one problem,
The code now is this:

Function reverse2(str)
Dim t As Single
t = Time()
reverse2 = StrReverse(str)

Debug.Print Second(Time() - t); " seconds"

MsgBox Second(Time() - t) & " seconds"
End Function


When I run it I get 3 times the message box as also 3 times the timer in the immidiate window. How can I make it only 1 time?
Tx,
Tamir

 

[Ruddles]

In that case you must be calling it three times. Are you calling it from a worksheet formula? Is that formula used three times in your worksheet?

Generally, functions do not interact with the user in the way you have coded: they are normally called from subroutines which process the values they return and the subroutine interacts with the user if necessary.

Functions called from the worksheet shouldn't react with the user at all, only with the worksheet. They can, but they shouldn't. Imagine using your function on thousands of cells and having to dismiss thousands of message boxes whenever your worksheet recalculated!