ByRef Cancel in event module

[Doug Glancy]

Hello,

I've gotten in the habit of calling regular procedures from my workbook
event procedures and putting all the working code in the regular subs.
Working on a BeforePrint Procedure, to get it to work I had to set declare
the cancel_print argument ByRef in order to get it to actually cancel the
print job - if I pass it ByVal, Cancel in the event procedure never gets set
to True. Here's a simplified version:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Call wb_bf(Cancel)
End Sub

Sub wb_bf(ByRef cancel_print As Boolean)
If MsgBox("cancel?", vbOKCancel) = vbCancel Then
cancel_print = True
End If
End Sub

I want to know if declaring cancel_print to ByRef is good practice here.
I've gotten the impression that ByRefs are "dangerous." If so, is there a
safer way to do this, like calling a boolean function? Also, the actual
application has3 layers of modules: it starts at the application level class
event, Cancel is passed to an addin, and then to the wb_bf sub.

Thanks,

Doug

 

[Bob Phillips]

Why not use a function to return a value. This works for me

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Cancel = wb_bf
End Sub

Function wb_bf() As Boolean
wb_bf = False
If MsgBox("cancel?", vbOKCancel) = vbCancel Then
wb_bf = True
End If
End Function

I must say though that asking a question of Cancel? and then having
OK/Cancel buttons would lead me to press OK to Cancel, not Cancel. You might
be better with Yes/No, less ambiguous (like NOT ambiguous)

Function wb_bf() As Boolean
wb_bf = False
If MsgBox("cancel?", vbYesNo) = vbYes Then
wb_bf = True
End If
End Function

 

[Doug Glancy]

Bob,

Thanks. I know what you mean about canceling a cancel. Even though it's
just for my use now, I was confusing myself!

With my main question, I would still like to know, would a function be
better programing? I prefer the ByRef Cancel, it's just easier for me to
track down through the 3 levels of procedures, so my question is there a
"danger" with using ByRef here?

It's kind of a bigger picture question, but I've picked up a lot of good
practices from this group and am interested in guidance on whether this is
an appropriate use of ByRef.

Doug

 

[Chip Pearson]

Passing the Cancel variable ByRef is just fine. There is no sort
of 'danger' to it.


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

 

[Doug Glancy]

Chip,

Thanks for setting my mind at ease <g>

Doug