Getting started example Excel code 3406DMSO

Post your VB and VBA discussions here
davewilliams
Newbie
Posts: 0
Joined: Wed Feb 10, 2016 9:28 pm

Re: Getting started example Excel code 3406DMSO

Post by davewilliams »

Thanks Hitesh, it would be great if you could take a look. I expect there is not a solution, but I hope you can help.

Attached is the spreadsheet/program.
Clicking 'Communicate with scope' should correctly open the connection to the scope and get the unit's information. It then reads the two values in cells C10 (labelled a) and C11 (labelled b). Divides C10 by C11 and puts the result in C12 (labelled c=a/b). It then closes the connection to the scope, reporting the status value.

Cell C11 contains the value 1, so everything is fine. To cause an error, change cell C11 to 0.

The program will then crash with a division by zero while the connection to the scope is still open.

On my system (Windows 10, Office 365 running 16 bit), there appears to be no way to exit the debugger without excel crashing. I have tried unplugging the 'scope.

The issue is just an annoyance while debugging. When my program is properly debugged there is no problem - it's just getting to that point!

Yours,

Dave
Attachments
08-Mar-16_CrashTesting_A.xlsm
File does not crash until you change the value in cell C11 to zero.
(52.54 KiB) Downloaded 372 times

Hitesh

Re: Getting started example Excel code 3406DMSO

Post by Hitesh »

Hi Dave,

The lock-up is possibly due to the fact that the connection to the device is still in place via the driver.

I think the best way to proceed is either to check for a divide by zero or if possible use a try-catch code block in order to call your exit_ScopeControl_subroutine code.

Hope this helps,

davewilliams
Newbie
Posts: 0
Joined: Wed Feb 10, 2016 9:28 pm

Re: Getting started example Excel code 3406DMSO

Post by davewilliams »

Cheers Hitesh,

The division by zero was just to simulate an unknown error in coding, made while debugging.

I have written an error trapping routine using ON ERROR (see attached file). This solves the problem of excel crashing, and it reports the description of the error, but it doesn't tell you where the error occurs, which makes it pretty much useless. Excel/VBA does have the ability to provide the line number via an Erl() function, but this always is returns as a zero, because we no longer use line numbers in BASIC programming. The Visual Basic editor shows a line number and column number for the cursor, so it strikes me that it would have been sensible for VBA to report this number using Erl() - but it doesn't, instead it (rather unhelpfully) returns zero.

There are some clumsy goto-s to jump over the error handler but I don't think there's a way to avoid that.

I think the problem would be completely solved if the error handler could report where the error occurs (or could jump back into the debugger at that point).

If you know how, do let me know!

Thanks,

Dave
Attachments
16-Mar-16_CrashTesting_A.xlsm
Error trapping "On Error" used to prevent crashing - but you then don't know where the error occurred.
(52.17 KiB) Downloaded 320 times

Post Reply