CODEWITHSUNDEEP
excelvbatimer
Heuigi Son
Heuigi Son

Reputation: 3

Allocating the timevalue from a cell entry

I am trying to make sub's in vba to repeat a process processC after some time as designated in the Cell K8 = 00:10:00 (text format).

Dim RunTimer As Date

Sub repeatA()
    'RunTimer = Now + TimeValue("00:10:00")
    RunTimer = Now + TimeValue(K8)
    Application.OnTime RunTimer, "repeatB"
End Sub

Sub repeatB()
    call processC
    call repeatA
End Sub

Sub processC()
    ...
End Sub

It works fine when I use RunTimer = Now + TimeValue("00:10:00"), but when I tried as above I got below error message. I want to assign "00:10:00" from a cell K8 entry. enter image description here

Upvotes: 0

Views: 236

Answers (1)

pgSystemTester
pgSystemTester

Reputation: 9917

You didn't type Range which is probably your main issue. It also matters if your cell's value is numeric or text.

Numeric Examples

In this example below, I have typed 00:10:00 this into the cell (no equals sign). Excel converts this to decimal (.0069444 -- essentially the percentage of a full day). The display may be the decimal or a time such as 12:10:00 AM. Regardless this is a numeric value that does not need to be converted with VBA.

Another numeric situation would be if the cell formula was: =TIMEVALUE("00:10:00") which converts exactly to the same .0069444.

All numeric situation should work with this VBA code:

RunTimer = Now + Range("K8").Value

Numeric entry

Text Examples

If your cell is actually text with formulas/displays such as:

  • ="00:10:00"
  • '00:10:00"

Then you SHOULD use your TimeValue Vba method: RunTimer = Now + TimeValue(Range("K8").Value)

text entry

Test if Numeric or Text

You could also try to make it dynamic by testing using isNumeric (though this might have some issues, depending on excel file)

If IsNumeric(Range("K8")) Then
    RunTimer = Now + Range("K8").Value

Else
    RunTimer = Now + TimeValue(Range("K8"))

End If

Hope that helps.

Upvotes: 1

Related Questions