CODEWITHSUNDEEP
excelvbacrashlabeled-statements
Dhay
Dhay

Reputation: 621

Excel crashes while compiling in a line contains label and DoEvents keyword

I have below 3 lines which are part of a procedure which makes the Excel crash. I have given them as four procedures with different names to be clear.

Lines which make the Excel crash:

Sub CrashVersion1()
163:                    Do
164:                    Loop Until Timer - st >= 0.5
165:                    DoEvents
End Sub

Sub CrashVersion2()
163:                    Do
164:                    DoEvents
165:                    Loop Until Timer - st >= 0.5

End Sub

Lines which don't create problem:

Sub FineVersion()
163:                    Do
164:                    Loop Until Timer - st >= 0.5
'DoEvents without a Label
                    DoEvents
End Sub

Sub FineVersion2()
163:                    Do
164:                    Loop Until Timer - st >= 0.5
Application.StatusBar = "PL"
166:                    DoEvents
End Sub
  1. The macro runs if DoEvents is above Do or after (but not immediately after) Do and Loop or when there is no label for it.
  2. The Excel crashes if DoEvents is in between Do and Loop or immediately after Do and Loop ONLY when there is a label.

I can put DoEvents without a label but just curious to know what is happening and a solution or workaround.

Upvotes: 1

Views: 89

Answers (2)

MT1
MT1

Reputation: 982

The Timer function returns the number of seconds since midnight as a Single datatype.

Microsoft documentation for the Timer function

The st variable is never initialised

FineVersion2() runs OK on my computer.

FineVersion1() runs OK on my computer.

CrashVersion2() runs OK on my computer.

CrashVersion1() appears to loop continuously on my computer. (blue circle) Excel has not crashed and can be closed via the red X in the top right hand corner.

The following code runs OK on my computer.

Option Explicit

Sub CrashVersionNoColons()
Dim st As Single
163                    Do
164                    Loop Until Timer - st >= 0.5
165                    DoEvents
End Sub

There are no colons and this runs OK

As I see it there are two ways the compiler could see the code

163: Do

pseudo code

line number 163 / blank statement / colon statement divider / valid Do statement

=> should run OK but it does not

or

pseudo code

illegal label 163: (does not start with a letter) / ignore the Do statement

=> should produce an error message but it does not

Microsoft® Excel® for Microsoft 365 MSO (Version 2311 Build 16.0.17029.20028) 64-bit

But read this Microsoft documentation Label statements in Visual BASIC this gives an example with the 163: syntax as a valid label.

From the Microsoft Visual Basic for Applications documentation

Used to identify a single line of code, a line label can be any combination of characters that starts with a letter and ends with a colon (:). Line labels are not case sensitive and must begin in the first column.

I realise this is an inconclusive answer but it may help in understanding what is happeniing and what the workaround could be.

Upvotes: 1

Black cat
Black cat

Reputation: 6281

This is because of the label using in the code.

If you use line numbers for labelling then don't add : colon to the end, then Excel will not crashes.

Use only : colon when line labels are used. (Start with a letter and end with a colon)

Upvotes: 1

Related Questions