2

I have a timer I am actively running through a DoEvents function. The problem is that every thirty secs I want to manually put some temperature values in my worksheet. However, if I do that while the event is running I get:

Run-time error '1004': The specified dimension is not valid for the current chart type.

Some thoughts: I was going to try turning off screen updating but that would keep me from seeing the time change in real time.

Is the active event keeping me from working in my document? And if so, is there a way around this?

Here is the body of my code (I also have a sub for the stop and reset timer but I don't think that's pertinent):

Sub START_TIMER()
Dim sh As Worksheet
Set sh = ThisWorkbook.ActiveSheet

sh.Range("AI1").Value = "Start"

If sh.Range("AI2").Value = "" Then
sh.Range("AI2").Value = Now
sh.Range("AL2").Value = Now
End If

x:
VBA.DoEvents

If sh.Range("AI1").Value = "Stop" Then Exit Sub

sh.Range("AI3").Value = Now
sh.Range("AL3").Value = Now

    If sh.Range("AL4").Value > TimeValue("00:00:29") Then
        sh.Range("A17").Interior.Color = vbYellow
        sh.Range("AL2").Value = Now
        Beep

    Else
         sh.Range("A17").Interior.Color = vbWhite

    End If

GoTo x

If sh.Range("AL4").Value = "0:00:30" Then
    sh.Range("AL2").Value = Now
End If

End

End Sub
Tim Stack
  • 3,209
  • 3
  • 18
  • 39
Calvin Dahms
  • 21
  • 1
  • Firstly, instead of using `Goto` try using `Do..While` loop. For your query: Excel is a single thread application so I don't think you can interact with it if you have code running (I could be wrong but that's my understanding). You could create a modeless `Form` and have the code run as part of the form? – Zac Apr 10 '19 at 08:56
  • **(1)** your error is not directly related to your code. Do you modify any values that are used in a chart? **(2)** You are using a "Busy wait"-loop, which means that your CPU (at least on core) eats up 100%. Have a look at the `OnTime`-function https://learn.microsoft.com/en-us/office/vba/api/excel.application.ontime – FunThomas Apr 10 '19 at 08:58
  • yeah its a short code so I opted to *cheat* and use GoTo. I know its not the best option. I see your point about the error but it only comes up as I select a cell and try to change a value while the timer is running so it must be related somehow. I took a look at the OnTime function and it might work but the challenge is that I need to add a temperature value every 30 secs for 12.5 mins. My original thinking was to add an input box for each instance but that would break the timing of the code I think. So I elected for manual entry – Calvin Dahms Apr 10 '19 at 09:17

1 Answers1

0

I figured it out!

If I just put an on error resume next then it runs well enough to work for my needs.

Calvin Dahms
  • 21
  • 1