VBA UDF function causes excel to "not respond"

Question

I have some very simple code that is causing Excel to crash.

I have debugged the variables as can be seen in the code and they look fine except that after only a few seconds Now() does not change and waitTime does not change - although the times are different from each other i.e. the time has not moved forward (for example, Now might be stuck at 3:00:05 and waitTime is stuck at 3:00:09).

And application.wait does not wait the 5 seconds I've asked for.

And the cell font color does not change either.

I do not know how to debug any further than this.

In the worksheet "sheet1" I have the following cell entries - in C8 I have a number that I change manually. In D8 I have

=if(C8>25,"yup",startFlash(C8))

And this works fine. It calls the function with no problem. Here is the macro code:

Dim waitTime As Date, stopTime As Date


Function startFlash(x As String)
    Beep
    stopTime = TimeSerial(Hour(Now()), Minute(Now()) + 2, Second(Now()))
    Call sflash
    MsgBox "done"  
End Function

Sub sflash()

    Do While waitTime <= stopTime

        With Sheet1.Range("c8").Font
            If .ColorIndex = 3 Then
              .ColorIndex = 5
             Else
             .ColorIndex = 3
            End If
        End With

        newHour = Hour(Now())
        newMinute = Minute(Now())
        newSecond = Second(Now()) + 5
        waitTime = TimeSerial(newHour, newMinute, newSecond)

        Debug.Print Now(); waitTime; stopTime

        Application.Wait waitTime
    Loop

End Sub

Any suggestions on what code to change to stop Excel from crashing?

user4039065 · Accepted Answer · 2016-07-09 23:42:42Z

1

Don't rely on time alone if there is any chance of 'walking over' midnight; include the date in your start and stop datetimes.

Option Explicit

Dim waitTime As Date, stopTime As Date

Function startFlash(x As String)
    Beep
    stopTime = Now + TimeSerial(0, 2, 0)
    'Debug.Print stopTime
    Call sflash
    MsgBox "done"
End Function

Sub sflash()

    Do While waitTime <= stopTime

        With Sheet1.Range("c8").Font
            If .ColorIndex = 3 Then
              .ColorIndex = 5
             Else
             .ColorIndex = 3
            End If
        End With

        waitTime = Now + TimeSerial(0, 0, 5)
        'Debug.Print Now; waitTime; stopTime

        Do While Now < waitTime: DoEvents: Loop
    Loop

End Sub

Looping through the DoEvents Function until your times meet is a better method.

answered Jul 9, 2016 at 23:42

user4039065

Sign up to request clarification or add additional context in comments.

11 Comments

Les Programmer Over a year ago

Thank you. I've run it 2 times and it hasn't crashed. However, the cell color does not change until I hit 'ok' after the msgbox i.e. the colors do not flash/blink which is the other thing I need to happen.

user4039065 Over a year ago

This might have to do with the video load on your computer. I had to zoom in to notice the difference (old eyes) but C8 was alternating font color between red and blue.

Les Programmer Over a year ago

Hmmm. I'll have to check the video issue. I debugged the colorIndex and it does change i.e. the if statement works. Something else to play with and learn about. Thanks for your help.

Les Programmer Over a year ago

I've just tried the code on another computer with the same result of cell C8 not changing color until I hit ok at the msgbox "done". I realize this is another question, but can you offer any suggestions?

user4039065 Over a year ago

Perhaps it's a version thing; I was using xl2010 originally but my xl2013 and xl2016 are sporadic. Putting Application.Screenupdating = False Before the With... and Application.Screenupdating = True after the End With seems to help.

|

Collectives™ on Stack Overflow

VBA UDF function causes excel to "not respond"

1 Answer 1

11 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

11 Comments

Your Answer

Sign up or log in

Post as a guest

Related