ByRef Cancel in event module

  • Thread starter Thread starter Doug Glancy
  • Start date Start date
D

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
 
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
 
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
 
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
 
Back
Top