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


--
HTH

Bob Phillips

"Doug Glancy" <nobodyhere@replytogroup.com> wrote in message
news:ePS9%236XhFHA.1460@tk2msftngp13.phx.gbl...
> 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
>
>

[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

"Bob Phillips" <phillips@tiscali.co.uk> wrote in message
news:OsxROGYhFHA.1372@TK2MSFTNGP10.phx.gbl...
> 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
>
>
> --
> HTH
>
> Bob Phillips
>
> "Doug Glancy" <nobodyhere@replytogroup.com> wrote in message
> news:ePS9%236XhFHA.1460@tk2msftngp13.phx.gbl...
>> 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
>>
>>
>
>

[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" <nobodyhere@replytogroup.com> wrote in message
news:eWlAkQYhFHA.3656@TK2MSFTNGP09.phx.gbl...
> 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
>
> "Bob Phillips" <phillips@tiscali.co.uk> wrote in message
> news:OsxROGYhFHA.1372@TK2MSFTNGP10.phx.gbl...
>> 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
>>
>>
>> --
>> HTH
>>
>> Bob Phillips
>>
>> "Doug Glancy" <nobodyhere@replytogroup.com> wrote in message
>> news:ePS9%236XhFHA.1460@tk2msftngp13.phx.gbl...
>>> 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
>>>
>>>
>>
>>
>
>

[Doug Glancy]

Chip,

Thanks for setting my mind at ease <g>

Doug

"Chip Pearson" <chip@cpearson.com> wrote in message
news:u16o3VYhFHA.2156@TK2MSFTNGP14.phx.gbl...
> 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" <nobodyhere@replytogroup.com> wrote in message
> news:eWlAkQYhFHA.3656@TK2MSFTNGP09.phx.gbl...
>> 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
>>
>> "Bob Phillips" <phillips@tiscali.co.uk> wrote in message
>> news:OsxROGYhFHA.1372@TK2MSFTNGP10.phx.gbl...
>>> 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
>>>
>>>
>>> --
>>> HTH
>>>
>>> Bob Phillips
>>>
>>> "Doug Glancy" <nobodyhere@replytogroup.com> wrote in message
>>> news:ePS9%236XhFHA.1460@tk2msftngp13.phx.gbl...
>>>> 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
>>>>
>>>>
>>>
>>>
>>
>>
>
>