Excel VBA no automatic break when divide by 0

[RBachman]

I have been using Excel VBA since 1994 and have written 100's of applications. I am currently Using Microsoft 365 and am on a preview subscription and update once a week. I am now developing array output UDF functions.

Over the last while (at least 6 months) I noticed that when my VBA code divides by zero I get no VBA error message dialog box like I used to have (with a Debug or End selection required). The program just returns to the Excel environment with an error in a single cell. This is extremely annoying as this usually happens deep within a For-Next loop.

I have no user forms or class modules or any 'On Error' statements. In the trust center Macro Setting tab I have 'Enable VBA macros' selected. No add-ins are enabled. There are no links in the workbook to other workbooks and I am using the *.xlsm file system. Setting breakpoints and navigating through the code works fine. When I hit run at some point it crashes (I know it is dividing by 0). I am then returned to the Excel enrionment.

My VBA options are the same as I always have used:
Auto Syntax Check = off
Variable Declaration = on

On the General tab
I have 'Break on unhandled Errors' , the default. I have tried 'Break on all Errors' to no avail.

I have run out of ideas.

Bob

 

[Joe4]

So, if you complete close out of Excel, then start it up again, create a brand new workbook, and place this simple VBA procedure into a new module and try to run it, do you get any error messages?

Sub MyTest()
    Dim x As Long
    x = 3 / 0
End Sub

I see this:

 

[RBachman]

So, if you complete close out of Excel, then start it up again, create a brand new workbook, and place this simple VBA procedure into a new module and try to run it, do you get any error messages?

Sub MyTest()
    Dim x As Long
    x = 3 / 0
End Sub

I see this:
View attachment 89640

I followed your instructions to the tee and replicated your behavior. I then tried a user defined function similar to your Sub:

Function MyTestFun()
Dim x As Long
x = 3 / 0
MyTestFun = x
End Function


Sheet1 Cell A1 :
=MytestFun()

No Dialog box and a return to the spreadsheet with #VALUE!. The dialog box is not present. The Function works fine when I use 1 in the denominator.

 

[Joe4]

I followed your instructions to the tee and replicated your behavior. I then tried a user defined function similar to your Sub:

Function MyTestFun()
Dim x As Long
x = 3 / 0
MyTestFun = x
End Function


Sheet1 Cell A1 :
=MytestFun()

No Dialog box and a return to the spreadsheet with #VALUE!. The dialog box is not present. The Function works fine when I use 1 in the denominator.

So, you are saying that you get the error message that I showed when it is a Sub, but when it is a Function, you only see ther #VALUE in the cell the formula is located in?
That is the expected behavior. To the best of my knowledge, you do not typically dialog boxes with Functions when used in cells on the Sheet.
However, you would if the function is called from a VBA procedure, i.e.

Sub MyTest()
    Dim y As Long
    y = MyTestFun
End Sub


Function MyTestFun()
    Dim x As Long
    x = 3 / 0
    MyTestFun = x
End Function

In a nutshell, I would only expect to see the error dialog box if a Sub Procedure is involved somehow.
If just using a Function and a Formula on the sheet, you would not get one, but would rather get that #VALUE error instead.

 

[RBachman]

Thanks, I duplicated your behavior. What you said makes sense.

I guess I am in a quandary. I can run Subs from code. Then I am back to writing code my old way with a button and a relatively rigid structure to the input and output data. I can see why MS disabled dialog boxes in Functions as you can get a near infinite loop of dialog messages if they are all over the place. However with the unstructured data I get I though the new dynamic array programmable functions gave me more flexibility. I typically then have very few UDF function calls (usually only 1) and the infinite dialog box problem would not be present.

Argh dynamic UDF's do have their limits, and if I continue to use them in time series analysis and crash out I am in for long debug times..

Bob