General debugging question

[Gerard]

I've got a general debugging question. Basically the
question/problem is this. I've got a function that I can
call from code (say a from sub) or directly from Excel
passing range objects etc. When I run the function from
code by directly running a sub that calls the function and
something goes wrong, the debugger gives me an error
message and points me to the problem. When I run the
function by evaluating a formula in excel it just stops on
errors with no help what so ever. The only way to figure
out where the problem occurs is to step through and
remember the step just before the code stops running.
Also, no message box is given. Is there a way to get the
debugger to be more helpful for code called from excel?
Also, setting the option for stop on unhandled errors does
not seem to affect code called from an excel formula.

Basically, I'm just looking for some sort of explaination
of what the difference is.

Thanks in advance,
Gerard

 

[Chip Pearson]

Gerard,

VBA procedures that are called directly (or indirectly, for that
matter) from worksheet cells cannot change the Excel environment
in any way, including changing the values of other cells. If a
procedure attempts such an operation, VBA quits immediately, no
error trapping, no messages, nothing.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Alan Beban]

I've got a general debugging question. Basically the
question/problem is this. I've got a function that I can
call from code (say a from sub) or directly from Excel
passing range objects etc. When I run the function from
code by directly running a sub that calls the function and
something goes wrong, the debugger gives me an error
message and points me to the problem. When I run the
function by evaluating a formula in excel it just stops on
errors with no help what so ever. The only way to figure
out where the problem occurs is to step through and
remember the step just before the code stops running.
Also, no message box is given. Is there a way to get the
debugger to be more helpful for code called from excel?
Also, setting the option for stop on unhandled errors does
not seem to affect code called from an excel formula.

Basically, I'm just looking for some sort of explaination
of what the difference is.

Thanks in advance,
Gerard

Two things occur to me:

1) If you're developing a program, get something basic that works and
then make small changes one at a time; that way you'll know that the
error was caused by the last change.

2) If you're running a developed program, change the code to anticipate
the errors, trap them, and have the function return error messages that
indicate the problem.

Alan Beban

 

[Alan Beban]

Gerard,

VBA procedures that are called directly (or indirectly, for that
matter) from worksheet cells cannot change the Excel environment
in any way, including changing the values of other cells. If a
procedure attempts such an operation, VBA quits immediately, no
error trapping, no messages, nothing.

xl2000

Not so; enter =Test60000() in Cell A20 and the error message will be
returned to that cell:

Function test60000()
On Error GoTo e
Range("A21").Value = 3
e: test60000 = "Can't change environment"
End Function

Alan Beban

 

[Chip Pearson]

Interesting. I never tried that before. Thanks for the info.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com