Code Hanging - Part II

[NZ VBA Developer]

Recently I posted a question about code hanging after displaying a built-in
dialog that got no response. I now have some more information that might help.

It seems that the code only hangs if the built-in dialog allows the entry of
text for insertion into the document and only if text is actually entered
manually. The dialog in question in my previous post was the 'Caption'
dialog. I've been working with the 'Envelopes and Labels' dialog recently and
ran into the same problem - but only intermittently. So I had a look at when
the code hung and when it didn't and discovered that if I fed the address
information to the 'Envelopes and Labels' dialog programmatically (e.g.
populated it with values from a UserForm), everything was fine. However, if I
added or modified the address information manually - even after populating it
originally from the UserForm - the code hung.

I reckon this is some sort of bug in Word, and I don't for a minute imagine
it's anything under my control. If I manually enter text into a dialog that's
displayed through code, the code is going to hang. I'm just wondering: has
anyone encounterd this problem previously and if you have, have you been able
to find a workaround - or at least a way to 'unhang' the code?

The problem is easy enough to recreate. Just create a UserForm with a single
TextBox and a CommandButton, then add the following code to the click event
for the button:

Private Sub CommandButton1_Click()
UserForm1.Hide
Dialogs(wdDialogToolsCreateEnvelope).AddrText = TextBox1.Value
Unload UserForm1
End Sub

This should display the built-in 'Envelopes and Labels' dialog box with the
value from the TextBox on the UserForm displayed in the 'Delivery address'
field. If you just add the envelope to the document, the code should continue
to run, but if you change the address info manually, it will hang. The code
can't be reset or even modified until you close and restart Word.
--
Cheers!
The Kiwi Koder

Please note: Uninvited email contact will be marked as SPAM and ignored -
unless you want to hire me. ;-)

 

[Doug Robbins - Word MVP]

I can replicate the problem, but cannot explain it.


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[Jean-Guy Marcil]

Recently I posted a question about code hanging after displaying a built-in
dialog that got no response. I now have some more information that might help.

It seems that the code only hangs if the built-in dialog allows the entry of
text for insertion into the document and only if text is actually entered
manually. The dialog in question in my previous post was the 'Caption'
dialog. I've been working with the 'Envelopes and Labels' dialog recently and
ran into the same problem - but only intermittently. So I had a look at when
the code hung and when it didn't and discovered that if I fed the address
information to the 'Envelopes and Labels' dialog programmatically (e.g.
populated it with values from a UserForm), everything was fine. However, if I
added or modified the address information manually - even after populating it
originally from the UserForm - the code hung.

I reckon this is some sort of bug in Word, and I don't for a minute imagine
it's anything under my control. If I manually enter text into a dialog that's
displayed through code, the code is going to hang. I'm just wondering: has
anyone encounterd this problem previously and if you have, have you been able
to find a workaround - or at least a way to 'unhang' the code?

The problem is easy enough to recreate. Just create a UserForm with a single
TextBox and a CommandButton, then add the following code to the click event
for the button:

Private Sub CommandButton1_Click()
UserForm1.Hide
Dialogs(wdDialogToolsCreateEnvelope).AddrText = TextBox1.Value
Unload UserForm1
End Sub

Am I missing something or a ".Show" statement of some sort is needed here?

This should display the built-in 'Envelopes and Labels' dialog box with the
value from the TextBox on the UserForm displayed in the 'Delivery address'
field. If you just add the envelope to the document, the code should continue
to run, but if you change the address info manually, it will hang. The code
can't be reset or even modified until you close and restart Word.

I believe it has to do with the fact that your userform is calling into
existence a built-in userform. There migth be some memory allocation or
internal collisions of some sort... Word is not releasing the VBA project
from its control. As you can see, I am totally guessing.
I am writing this becasue if you take the user userform out of the way, you
do not see this problem:

Option Explicit

Sub test()

Dim frmTest As UserForm1
Dim strText As String

Set frmTest = New UserForm1

With frmTest
.Show
strText = .TextBox1.Value
End With

MsgBox "Unloading"

Unload frmTest

Set frmTest = Nothing

With Dialogs(wdDialogToolsCreateEnvelope)
.AddrText = strText
.Show
End With

End Sub


With this code, you can modify anything you want in the address field of the
Envelope dialog, and then you can go back to your code to modify it... it is
not hung.

By the way, this is one example that further reinforces my conviction that
all code that has noting to do with the actual displaying of the userform
(resizing, hiding...)or user input validation does not belong in the userform
class module. In fact, I always strive to keep the code in the userform
module to a bare minimum. For example, I always handle all interaction with
the document itself in a regular module, I always unload the userform from
the calling module (Never from within the form itself), etc.

 

[NZ VBA Developer]

Thanks Jean-Guy. You may have given me the lead I need to find a solution.

I understand and appreciate your 'rule' around keeping the code in a
UserForm to a minimum; obviously it has its benefits and is generally just
good practice. (Unfortunately I haven't been following this practice, so I've
got a bit of work to do - none of it paid either. ~sigh~)

I'll try this approach with my current project and let you know how I get
on. Hopefully it will allow me to get around the problem with using the
'Caption' dialog as well.

And yes, I did forget the .Show in my sample code. I accidentally deleted it
when I was 'sanitising' the code for posting.
--
Cheers!
The Kiwi Koder

Please note: Uninvited email contact will be marked as SPAM and ignored -
unless you want to hire me. ;-)


:

[ Several line deleted for ease of readability]

 

[NZ VBA Developer]

Good to know it's not just because I'm holding my mouth wrong or something.
Was worried maybe it was the pink socks. ;-P

 

[NZ VBA Developer]

That's got it, Jean-Guy! Thanks so much for the help.

It was a little tricky trying to figure out how to pass the values from the
UserForm back to the calling module so I could use them to update the
document, but I managed to do it. And determining how the UserForm was closed
also presented a bit of a challenge, as I only wanted the 'Envelopes and
Labels' dialog to be displayed if the user clicked the 'OK' button. In the
end I used the following:

Private Sub AddEnvelope()
Dim frmMyForm As frmEnvelopeDetails
bAddEnvelope = False
Set frmMyForm = New frmEnvelopeDetails
frmMyForm.Show
Unload frmMyForm
Set frmMyForm = Nothing
If bAddEnvelope = True Then CreateEnvelope
Tools.SetToolbar
End Sub

Sub CollectUserFormValues()
bAddEnvelope = True
RecipName = frmEnvelopeDetails.txtRecipientName
RecipAddressL1 = frmEnvelopeDetails.txtRecipientAddressL1
RecipAddressL2 = frmEnvelopeDetails.txtRecipientAddressL2
RecipSuburb = frmEnvelopeDetails.txtRecipientSuburb
RecipCity = frmEnvelopeDetails.txtRecipientCity
RecipPostCode = frmEnvelopeDetails.txtRecipientPostCode
RecipAttention = frmEnvelopeDetails.txtRecipientAttention
End Sub

To explain:

bAddEnvelope is a Boolean variable that gets initialised to 'False' in the
'AddEnvelope' module. The 'CollectUserFormValues' module is called in the
Click event for the 'OK' button. This module not only collects the values
from the UserForm for use in the 'Envelopes and Labels' dialog, but also sets
the bAddEnvelope flag to 'True'. (Clicking the 'Cancel' button just hides the
form and leaves the flag set to 'False'.) This flag is then evaluated to see
if the built-in dialog should be displayed, which is done in the
'CreateEnvelope' module. (This module also does a few other things: unlocks
the document if it's protected, updates a toolbar button, etc.)

Now I just need to clean up the code that's used to create the document in
the first place to follow the 'minimal code in the UserForm' rule and I'll be
away laughing. Thanks again!

 

[Jean-Guy Marcil]

That's got it, Jean-Guy! Thanks so much for the help.

It was a little tricky trying to figure out how to pass the values from the
UserForm back to the calling module so I could use them to update the
document, but I managed to do it. And determining how the UserForm was closed
also presented a bit of a challenge, as I only wanted the 'Envelopes and
Labels' dialog to be displayed if the user clicked the 'OK' button. In the
end I used the following:

Private Sub AddEnvelope()
Dim frmMyForm As frmEnvelopeDetails
bAddEnvelope = False
Set frmMyForm = New frmEnvelopeDetails
frmMyForm.Show
Unload frmMyForm
Set frmMyForm = Nothing
If bAddEnvelope = True Then CreateEnvelope
Tools.SetToolbar
End Sub

Sub CollectUserFormValues()
bAddEnvelope = True
RecipName = frmEnvelopeDetails.txtRecipientName
RecipAddressL1 = frmEnvelopeDetails.txtRecipientAddressL1
RecipAddressL2 = frmEnvelopeDetails.txtRecipientAddressL2
RecipSuburb = frmEnvelopeDetails.txtRecipientSuburb
RecipCity = frmEnvelopeDetails.txtRecipientCity
RecipPostCode = frmEnvelopeDetails.txtRecipientPostCode
RecipAttention = frmEnvelopeDetails.txtRecipientAttention
End Sub

To explain:

bAddEnvelope is a Boolean variable that gets initialised to 'False' in the
'AddEnvelope' module. The 'CollectUserFormValues' module is called in the
Click event for the 'OK' button. This module not only collects the values
from the UserForm for use in the 'Envelopes and Labels' dialog, but also sets
the bAddEnvelope flag to 'True'. (Clicking the 'Cancel' button just hides the
form and leaves the flag set to 'False'.) This flag is then evaluated to see
if the built-in dialog should be displayed, which is done in the
'CreateEnvelope' module. (This module also does a few other things: unlocks
the document if it's protected, updates a toolbar button, etc.)

Now I just need to clean up the code that's used to create the document in
the first place to follow the 'minimal code in the UserForm' rule and I'll be
away laughing. Thanks again!

Glad to see that you sorted it out...
Just a quick comment on your code.. You do not need a separate module to
collect the info...

For example:

Private Sub AddEnvelope()

Dim frmMyForm As frmEnvelopeDetails
Dim boolGoAhead As Boolean

'This could be a public global variable in the Userform module _
set it to false in the Initialize event, then if OK is clicked, set it
to true
'bAddEnvelope = False

boolGoAhead = False

Set frmMyForm = New frmEnvelopeDetails

With frmMyForm
.Show
boolGoAhead = .bAddEnvelope
If boolGoAhead Then
RecipName = .txtRecipientName
RecipAddressL1 = .txtRecipientAddressL1
RecipAddressL2 = .txtRecipientAddressL2
RecipSuburb = .txtRecipientSuburb
RecipCity = .txtRecipientCity
RecipPostCode = .txtRecipientPostCode
RecipAttention = .txtRecipientAttention
End If
End With

Unload frmMyForm
Set frmMyForm = Nothing

If boolGoAhead Then CreateEnvelope
Tools.SetToolbar

End Sub

 

[NZ VBA Developer]

Thanks for the feedback, Jean-Guy. I think I see what you're driving at, but
it's really a case of 'six of one/half dozen of the other', isn't it? Either
the 'OK' button sets a flag itself or it calls the data collection module
that sets a flag. Unless I'm missing something, which is entirely possible, I
don't see much difference between the two. However, I do see a few small
advantages with calling a separate procedure (forgive me for calling it a
'module' in my previous post - just a slip of the keyboard):

First, I find it easier to keep track of what's being collected from the
UserForm if the collection process is in its own separate space rather than
embedded in the code that is primarily devoted to the loading and display of
the UserForm. If I miss a value or something goes wonky, this method makes it
simple to find where to look; just insert a break point at the start of the
procedure.

In addition, I see the collected data as be more document-related and
secondary to the UserForm itself. Therefore, the separate procedure is in
keeping with my rule that says, 'Don't mix your drinks!' ;-D Procedures
should be based around the objects and processes they're intended to support
- rather like your rule of keeping the code in the UserForm to only what is
required to support the form, altho at a bit finer granularity.

Finally, I don't have to worry about 'transporting variables across state
lines'. The bAddEnvelope flag is declared in the main module, initialised and
set in the main module, and evaluated and acted on in the main module. No
public global variables to try and track between modules; the only thing that
crosses between them are the values from the controls on the UserForm (which
are much more 'visible' than variables).

However, there may be advantages to your method that I'm just not seeing. If
so, please let me know as I'm always eager to learn. And I may give your
suggestion a go just for the experience of working with passing variables
between modules.

Thanks again for all your help. I really appreciate it.
--
Cheers!
The Kiwi Koder


:

[Several lines deleted for ease of readability]

Glad to see that you sorted it out...
Just a quick comment on your code.. You do not need a separate module to
collect the info...

*** KK: Sorry, should have said 'procedure', not 'module'. ***

 

[Jean-Guy Marcil]

Thanks for the feedback, Jean-Guy. I think I see what you're driving at, but
it's really a case of 'six of one/half dozen of the other', isn't it? Either
the 'OK' button sets a flag itself or it calls the data collection module
that sets a flag. Unless I'm missing something, which is entirely possible, I
don't see much difference between the two. However, I do see a few small
advantages with calling a separate procedure (forgive me for calling it a
'module' in my previous post - just a slip of the keyboard):

First, I find it easier to keep track of what's being collected from the
UserForm if the collection process is in its own separate space rather than
embedded in the code that is primarily devoted to the loading and display of
the UserForm. If I miss a value or something goes wonky, this method makes it
simple to find where to look; just insert a break point at the start of the
procedure.

In addition, I see the collected data as be more document-related and
secondary to the UserForm itself. Therefore, the separate procedure is in
keeping with my rule that says, 'Don't mix your drinks!' ;-D Procedures
should be based around the objects and processes they're intended to support
- rather like your rule of keeping the code in the UserForm to only what is
required to support the form, altho at a bit finer granularity.

Finally, I don't have to worry about 'transporting variables across state
lines'. The bAddEnvelope flag is declared in the main module, initialised and
set in the main module, and evaluated and acted on in the main module. No
public global variables to try and track between modules; the only thing that
crosses between them are the values from the controls on the UserForm (which
are much more 'visible' than variables).

However, there may be advantages to your method that I'm just not seeing. If
so, please let me know as I'm always eager to learn. And I may give your
suggestion a go just for the experience of working with passing variables
between modules.

I guess, especially since you seem to be dealing with a simple case of a
single userform, you summed it up nicely with "it's really a case of 'six of
one/half dozen of the other', isn't it?"

The only issue I migth have with your code is the second sub:

Sub CollectUserFormValues()
bAddEnvelope = True
RecipName = frmEnvelopeDetails.txtRecipientName
RecipAddressL1 = frmEnvelopeDetails.txtRecipientAddressL1
RecipAddressL2 = frmEnvelopeDetails.txtRecipientAddressL2
RecipSuburb = frmEnvelopeDetails.txtRecipientSuburb
RecipCity = frmEnvelopeDetails.txtRecipientCity
RecipPostCode = frmEnvelopeDetails.txtRecipientPostCode
RecipAttention = frmEnvelopeDetails.txtRecipientAttention
End Sub

Here, "frmEnvelopeDetails" is not the name of the userform object (the name
is actually "frmMyForm"), but the name of the class module that contains the
blue print of the userfrom. If you had two userforms for that class module in
memory, this code would not know which one to address, and might address the
wrong one.

I know that in your case it is very unlikely that it would happen, but
personally, I always like to be in total control and use the actual instance
of an object I create, which your code does not do. It works with the class
name, but it just so happens that there is only one instance of an object
created by that class in memory when that sub is called; so it works, not
because it is well coded, but by default...

But I guess I am splitting hair since your code seem simple enough so that
there won't be a case where you have two instances of the form in memory at
the same time...
I guess you could still use the second sub, but pass the userform object and
use that instead of the class name...

Finally, you stated :
"The bAddEnvelope flag is declared in the main module, initialised and
set in the main module, and evaluated and acted on in the main module. No
public global variables to try and track between modules".

Actually, you do have a "cross the state line" case. In your previous post,
you stated:
"The 'CollectUserFormValues' module is called in the
Click event for the 'OK' button. This module not only collects the values
from the UserForm for use in the 'Envelopes and Labels' dialog, but also sets
the bAddEnvelope flag to 'True'. (Clicking the 'Cancel' button just hides the
form and leaves the flag set to 'False'.)"
So, your flag *is* a global variable used in a few subs in the main module
and in the userform module, which is a separate class module. With my
approach, it is not a global variable, but a property of the userform object.
But again, we are back at "Tomayto" "Tomahto"! I prefer my approach because
I do not like having global variables if I can avoid it, for the exact
reasons you stated.

 

[NZ VBA Developer]

Jean-Guy,

In re using the class module rather than the UserForm object in my data
collection procedure: I agree absolutely with your point, and the final
version of my code does indeed make use of the UserForm object - e.g.
frmMyForm.[Control].Value. Please forgive the sloppy coding; it was a rookie
mistake. (Note that, in the end, I also specified the '.Value' property of
the controls explicitly instead of just relying on the VBA default - which is
what I usually do but just missed in this instance because I was in a rush to
work out the proof-of-concept.)

As for the bAddEnvelope flag, I suppose is it sort of a global variable, but
it's not used in the UserForm class module anywhere. The procedure that sets
it to True is located in the main module (EnvelopeOps), although this
procedure is called from an event in the UserForm module:

Private Sub btnOK_Click()
Me.Hide
EnvelopeOps.CollectUserFormValues
End Sub

It's probably just as easy use your method and have the 'OK' button Click
event set a property of the UserForm and then 'collect' this property at the
same time as I collect the values. However, I'd still rather make an explicit
call to the data collection procedure instead of letting the 'flow' of the
code control this. I still don't like the idea of having the data collection
code embedded in the UserForm display code.

Hmmm... However, that does give me an idea. Maybe the only data point I need
to collect in the flow of my UserForm display code is the value of the
property that's set when the OK or Cancel button is clicked. Then I can
evaluate this 'collected' value to decide whether to run the rest of the data
collection procedure. This has the added advantage of being able to
explicitly set the property when the Cancel button is clicked whilst still
keeping all of the data collection functions centralised in one procedure,
the execution of which is under direct control... I like it!

Thanks for the inspiration, Jean-Guy. Now I just need to work out how to
apply this concept to some of my other projects - especially those that use
arrays behind the UserForm to allow the collection of multiple sets of
similar data (e.g. names and addresses of multiple parties to a contract).

 

[NZ VBA Developer]

FYI - Here’s what the final result looks like:

***In the frmEnvelopeDetails UserForm module***
-eclarations:-
Public bAddEnvelope As Boolean
[Dim statements for a bunch of flags used to indicate the existence of some
document variables for error handling purposes]

-:The Click event for the OK button:-
Private Sub btnOK_Click()
bAddEnvelope = True
Me.Hide
End Sub

-:The Click event for the Cancel button:-
Private Sub btnCancel_Click()
bAddEnvelope = False
Me.Hide
End Sub

***In the EnvelopeOps module***
-eclarations:-
Dim frmMyForm As frmEnvelopeDetails
Dim RecipName As String
Dim RecipAddressL1 As String
Dim RecipAddressL2 As String
Dim RecipSuburb As String
Dim RecipCity As String
Dim RecipPostCode As String
Dim RecipAttention As String
Dim bAddEnvelope As Boolean
[Dim statement for a flag used to indicate the existence of a document
variable for error handling purposes]

-:The procedure for displaying the UserForm:-
Private Sub AddEnvelope()
Set frmMyForm = New frmEnvelopeDetails
frmMyForm.Show
bAddEnvelope = frmMyForm.bAddEnvelope
If bAddEnvelope = True Then CollectUserFormValues
Unload frmMyForm
Set frmMyForm = Nothing
[Conditional call to procedure for actually creating the envelope]
End Sub

-:The procedure for collecting the data from the UserForm; called only if
the 'OK' button has been clicked:-
Sub CollectUserFormValues()
With frmMyForm
RecipName = .txtRecipientName.Value
RecipAddressL1 = .txtRecipientAddressL1.Value
RecipAddressL2 = .txtRecipientAddressL2.Value
RecipSuburb = .txtRecipientSuburb.Value
RecipCity = .txtRecipientCity.Value
RecipPostCode = .txtRecipientPostCode.Value
RecipAttention = .txtRecipientAttention.Value
End With
End Sub

I think this covers everything quite nicely. Now if I could only figure out
how to pass arrays between modules…