Yesterday I was in a discussion (buried deep in another string) with
Jean-Guy Marcil about the proper conventions for developing userform
code.  In particular, where the bulk of the code should reside.   Here
is a very simple example where a userform provides one text input to
supply a bookmark in the document.  I am using this to illustrate at
least four different approaches for processing the form.  Jean-Guy
advocates method 1.  I personally like the concept of method 3, but it
carries the baggage (at least I think it is baggage from my limited
understanding of the topic) of having to use public declarations (or
worse Get and Let property statements which brings on nightmares).

Was hoping to expand the discussion and get opinions from other on best
methods/practices.

Thanks.

1. Process form in the calling macro:

Project code:

Sub CallUF()
Dim myFrm As UserForm1
Set myFrm = New UserForm1
myFrm.Show
ActiveDocument.Bookmarks("bkName").Range.Text = myFrm.txtName
Unload myFrm
Set myFrm = Nothing
End Sub

UserForm code:

Private Sub CmdBtnOK_Click()
Me.Hide
End Sub

2. Process form with a control in the form (e.g., a command button
event)

Project code:

Sub CallUF()
Dim myFrm As UserForm1
Set myFrm = New UserForm1
myFrm.Show
Unload myFrm
Set myFrm = Nothing
End Sub

UserForm Code

Private Sub CmdBtnOK_Click()
Me.Hide
ActiveDocument.Bookmarks("bkName").Range.Text = Me.txtName
End Sub

3.  Process form using a separate routine called from the calling
macro.  Note for simplicity I just made a public declaration of the
variable pName in the project module:

Project code:

Option Explicit
Public pName As String
Sub CallUF()
Dim myFrm As UserForm1
Set myFrm = New UserForm1
myFrm.Show
ProcessForm
Unload myFrm
Set myFrm = Nothing
End Sub

Sub ProcessForm()
ActiveDocument.Bookmarks("bkName").Range.Text = pName
End Sub

UserForm code:

Private Sub CmdBtnOK_Click()
Me.Hide
pName = Me.txtName
End Sub

4.  Process form using a separate routine contained in the user form

Project code:

Sub CallUF()
Dim myFrm As UserForm1
Set myFrm = New UserForm1
myFrm.Show
Unload myFrm
Set myFrm = Nothing
End Sub

UserForm code:

Private Sub CmdBtnOK_Click()
Me.Hide
ProcessForm
End Sub

Sub ProcessForm()
ActiveDocument.Bookmarks("bkName").Range.Text = Me.txtName
End Sub

I tend to look for using as little code as possible. On that basis, I use 
the following method

Project code:

Sub CallUF()
Dim myFrm As UserForm1
  Set myFrm = New UserForm1
  myFrm.Show
End Sub

UserForm code:

Private Sub CmdBtnOK_Click()
  ActiveDocument.Bookmarks("bkName").Range.Text = Me.txtName
  Unload Me
End Sub

However, if there is more processing code, then you might not want to 
include it all in the CmdBtnOK_Click event. If some or all of the code might 
need to be called from other occasions, then you need to get that code out 
to separate routines.

I normally use Unload me rather than Me.Hide in an event routine to close 
the form, so that when you move on to the next line of the calling routine 
the form has not only been hidden but also unloaded. That way, you don't 
need explicit code in the calling routine to unload the form and set the 
object variable to Nothing.

-- 
Regards
Jonathan West - Word MVP
www.intelligentdocuments.co.uk
Please reply to the newsgroup
Keep your VBA code safe, sign the ClassicVB petition www.classicvb.org



"Greg Maxey"  wrote in message 
news:1147440647.497512.220470@j73g2000cwa.googlegroups.com...
> Yesterday I was in a discussion (buried deep in another string) with
> Jean-Guy Marcil about the proper conventions for developing userform
> code.  In particular, where the bulk of the code should reside.   Here
> is a very simple example where a userform provides one text input to
> supply a bookmark in the document.  I am using this to illustrate at
> least four different approaches for processing the form.  Jean-Guy
> advocates method 1.  I personally like the concept of method 3, but it
> carries the baggage (at least I think it is baggage from my limited
> understanding of the topic) of having to use public declarations (or
> worse Get and Let property statements which brings on nightmares).
>
> Was hoping to expand the discussion and get opinions from other on best
> methods/practices.
>
> Thanks.
>
> 1. Process form in the calling macro:
>
> Project code:
>
> Sub CallUF()
> Dim myFrm As UserForm1
> Set myFrm = New UserForm1
> myFrm.Show
> ActiveDocument.Bookmarks("bkName").Range.Text = myFrm.txtName
> Unload myFrm
> Set myFrm = Nothing
> End Sub
>
> UserForm code:
>
> Private Sub CmdBtnOK_Click()
> Me.Hide
> End Sub
>
> 2. Process form with a control in the form (e.g., a command button
> event)
>
> Project code:
>
> Sub CallUF()
> Dim myFrm As UserForm1
> Set myFrm = New UserForm1
> myFrm.Show
> Unload myFrm
> Set myFrm = Nothing
> End Sub
>
> UserForm Code
>
> Private Sub CmdBtnOK_Click()
> Me.Hide
> ActiveDocument.Bookmarks("bkName").Range.Text = Me.txtName
> End Sub
>
> 3.  Process form using a separate routine called from the calling
> macro.  Note for simplicity I just made a public declaration of the
> variable pName in the project module:
>
> Project code:
>
> Option Explicit
> Public pName As String
> Sub CallUF()
> Dim myFrm As UserForm1
> Set myFrm = New UserForm1
> myFrm.Show
> ProcessForm
> Unload myFrm
> Set myFrm = Nothing
> End Sub
>
> Sub ProcessForm()
> ActiveDocument.Bookmarks("bkName").Range.Text = pName
> End Sub
>
> UserForm code:
>
> Private Sub CmdBtnOK_Click()
> Me.Hide
> pName = Me.txtName
> End Sub
>
> 4.  Process form using a separate routine contained in the user form
>
> Project code:
>
> Sub CallUF()
> Dim myFrm As UserForm1
> Set myFrm = New UserForm1
> myFrm.Show
> Unload myFrm
> Set myFrm = Nothing
> End Sub
>
> UserForm code:
>
> Private Sub CmdBtnOK_Click()
> Me.Hide
> ProcessForm
> End Sub
>
> Sub ProcessForm()
> ActiveDocument.Bookmarks("bkName").Range.Text = Me.txtName
> End Sub
>

Thanks Jonathan,

I have read lots of discussions where you support economy in code ;-)

<If some or all of the code might need to be called from other
occasions, then you need to get that code out to separate routines.

Where would you put this code?

"Greg Maxey"  wrote in message 
news:1147443387.588370.241750@i39g2000cwa.googlegroups.com...
> Thanks Jonathan,
>
> I have read lots of discussions where you support economy in code ;-)
>
> <If some or all of the code might need to be called from other
> occasions, then you need to get that code out to separate routines.
>
> Where would you put this code?
>

It depends.

If the code will only need to be called by other events within the same 
form, you might as well keep it in the form. That way, you can keep the form 
as self-contained as possible if you decide to transfer it to another 
template. In addition, if you use the Me keyword in the code to refer to the 
current instance of the form, then the code won't need to be modified if you 
bring it out to a different routine.

If the code may need to be called from other forms or other macros, then you 
would probably want to put it into a separate module. If the code refers to 
controls in the form, you will need to pass the instance of the form as a 
parameter to the routine, because the Me keyword won't work in code in 
routines in ordinary modules, even if called from a form or class module.


-- 
Regards
Jonathan West - Word MVP
www.intelligentdocuments.co.uk
Please reply to the newsgroup
Keep your VBA code safe, sign the ClassicVB petition www.classicvb.org

Jonathan,

>If the code may need to be called from other forms or other macros, then you
>would probably want to put it into a separate module. If the code refers to
>controls in the form, you will need to pass the instance of the form as a
>parameter to the routine, because the Me keyword won't work in code in
>routines in ordinary modules, even if called from a form or class module.

There is an example of why I consider you a master and me a novice.
"...you will need to pass the instance of the form as a parameter to
the routine..."

You lost me.  Could you provide an example of this method using the
simple processing required in previous examples.

Thanks.

"Greg Maxey"  wrote in message 
news:1147446834.776707.91250@j73g2000cwa.googlegroups.com...
> Jonathan,
>
>>If the code may need to be called from other forms or other macros, then 
>>you
>>would probably want to put it into a separate module. If the code refers 
>>to
>>controls in the form, you will need to pass the instance of the form as a
>>parameter to the routine, because the Me keyword won't work in code in
>>routines in ordinary modules, even if called from a form or class module.
>
> There is an example of why I consider you a master and me a novice.
> "...you will need to pass the instance of the form as a parameter to
> the routine..."
>
> You lost me.  Could you provide an example of this method using the
> simple processing required in previous examples.

Try this.

Project code:

Sub CallUF()
Dim myFrm As UserForm1
  Set myFrm = New UserForm1
  myFrm.Show
End Sub

UserForm code:

Private Sub CmdBtnOK_Click()
  ProcessForm Me
  Unload Me
End Sub

Code in separate module:

Public Sub ProcessForm(oForm as Object)
  ActiveDocument.Bookmarks("bkName").Range.Text = oForm.txtName
End Sub


-- 
Regards
Jonathan West - Word MVP
www.intelligentdocuments.co.uk
Please reply to the newsgroup
Keep your VBA code safe, sign the ClassicVB petition www.classicvb.org

Yes I see.  So if I add a second textbox (txtAge) I could:

Public Sub ProcessForm(oForm As Object)
ActiveDocument.Bookmarks("bkName").Range.Text = oForm.txtName
ActiveDocument.Bookmarks("bkAge").Range.Text = oForm.txtAge
End Sub

Question:  As I type the code for the third line above why doesn't
intellisense give me the dropdown list of oForm properties?

Greg Maxey was telling us:
Greg Maxey nous racontait que :

> Yes I see.  So if I add a second textbox (txtAge) I could:
>
> Public Sub ProcessForm(oForm As Object)
> ActiveDocument.Bookmarks("bkName").Range.Text = oForm.txtName
> ActiveDocument.Bookmarks("bkAge").Range.Text = oForm.txtAge
> End Sub
>
> Question:  As I type the code for the third line above why doesn't
> intellisense give me the dropdown list of oForm properties?

I might be wrong, but I believe is because oForm is declared as an Object, 
which is a generic container. So, in this case, the properties and methods 
specific to oForm are not available to the "intellisense" engine. 
Nevertheless, the code will run because it will compile correctly once the 
connection is established between the object container and the passed object 
at runtime.

So, if you used
    ActiveDocument.Bookmarks("bkAge").Range.Text = 
oForm.SuperDuperControl.Text
you would get an error at runtime because SuperDuperControl is not a 
property or method of oForm, which is a userform in this case.
Even Option Explicit would not catch that because it cannot know ahead of 
time what type of object oForm will be.

I am not sure if all this makes sense...


-- 
Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
jmarcilREMOVE@CAPSsympatico.caTHISTOO
Word MVP site: http://www.word.mvps.org

"Greg Maxey"  wrote in message 
news:1147453771.178393.200380@i39g2000cwa.googlegroups.com...
> Yes I see.  So if I add a second textbox (txtAge) I could:
>
> Public Sub ProcessForm(oForm As Object)
> ActiveDocument.Bookmarks("bkName").Range.Text = oForm.txtName
> ActiveDocument.Bookmarks("bkAge").Range.Text = oForm.txtAge
> End Sub
>
> Question:  As I type the code for the third line above why doesn't
> intellisense give me the dropdown list of oForm properties?
>

That is because I declared it as Object rather than as UserForm1 (i.e. using 
late binding). At editing time, the VBA editor don't know what kind of 
object might be passed to the routine, and so cannot make any sensible 
suggestions using Intellisense. I did that because the purpose of the 
demonstration was to look at the possibility of calling the routine from 
different forms, which meant that you would have to use Object.

For a case where such a routine was practical, suppose that you had a number 
of different forms, each of which was supposed to be associated with a 
different document with different bookmarks. You could simplify matters a 
good deal by proceeding as follows

- for each TextBox within each form, place the name of the associated 
bookmark in the Tag property.

- Call ProcessForm from each form as I already indicated.

- Use the following code in ProcessForm

Public Sub ProcessForm(oForm as Object)
  Dim oControl as Control
  For Each oControl In oForm.Controls
    If TypeOf oControl Is MSForms.TextBox Then
      ActiveDocument.Bookmarks(oControl.Tag).Range.Text = oControl.Text
    End If
  Next oControl
End Sub


-- 
Regards
Jonathan West - Word MVP
www.intelligentdocuments.co.uk
Please reply to the newsgroup
Keep your VBA code safe, sign the ClassicVB petition www.classicvb.org

Well it seems to make sense.  Thanks.

Jonathan,

<late binding

The fog of confusion is moving in.  I will need to do some more study
before delving deeper into this realm.

Thanks for your time and effort.

"Greg Maxey"  wrote in message 
news:1147457427.294810.177540@i39g2000cwa.googlegroups.com...
> Jonathan,
>
> <late binding
>
> The fog of confusion is moving in.  I will need to do some more study
> before delving deeper into this realm.
>
> Thanks for your time and effort.

Late binding is where you use object variables but you don't know in advance 
what kind of object your variable will be assigned to. Early binding is 
where you do.

Dim x as Object    'late binding
Dim x as UserForm1 'early binding

Think of late binding as being the object equivalent of using a Variant 
instead of a specific datatype such as a String.

-- 
Regards
Jonathan West - Word MVP
www.intelligentdocuments.co.uk
Please reply to the newsgroup
Keep your VBA code safe, sign the ClassicVB petition www.classicvb.org

Ok, I see sort of.

So by using:

Sub ProcessForm(oForm as UserForm1)

I could then process one or a hundred control values in a procedure
outside the UserForm.  This would be sort of like my original method 3,
but I wouldn't need to declare Public variables to pass the data from
the form back to the module.  Personally I like the code separated out
this way.

However, it sounds like using a method like this would make me odd man
out wrt coding conventions.

Still it is good to know and good to see your other examples. 

Thanks.

"Greg Maxey"  wrote in message 
news:1147458607.804170.129110@y43g2000cwc.googlegroups.com...
> Ok, I see sort of.
>
> So by using:
>
> Sub ProcessForm(oForm as UserForm1)
>
> I could then process one or a hundred control values in a procedure
> outside the UserForm.  This would be sort of like my original method 3,
> but I wouldn't need to declare Public variables to pass the data from
> the form back to the module.  Personally I like the code separated out
> this way.

I would prefer to avoid using Public variables where there is no particular 
need. Therefore, to my mind, this option is better than your option 3. 
However, I see no great benefit in putting the routine in a separate module 
if it will only ever be called for a single Userform. In addition to 
minimising the amount of code, I like to simplify things as much as possible 
by keeping closely related code together in a single module. Therefore, 
unless the external routine is going to be called from multiple forms (hence 
passing the userform As Object in my example), your option 4 is preferable 
in my view.

In essence, Option 1 or Option 4 are both perfectly fine for code that is 
only used by a single UserForm, and the choice between them is largely one 
of convenience - with more processing you may want to split the code up into 
separate routines.

>
> However, it sounds like using a method like this would make me odd man
> out wrt coding conventions.

I've seen *much* worse! Including from Microsoft. If you want to see a truly 
dire example of spaghetti code, open up the VBA project for the Batch 
Conversion Wizard that is included within Microsoft Office.

Ultimately, the main thing is to remember that source code serves two quite 
separate purposes.

1. It is a set of instruction to be compiled and executed by the computer.

2. It is a message to yourself (or to another programmer who views your 
code) about what you are trying to achieve, so that code can more easily be 
debugged, maintained and extended in future.

All coding conventions have this second point as their ultimate aim. 
Provided that you have a coding convention that works for you, you needn't 
worry much if your code doesn't conform to other people's conventions. Just 
concentrate on making it clear by ensuring that whatever convention you 
choose, you apply it as consistently as reasonably possible.


-- 
Regards
Jonathan West - Word MVP
www.intelligentdocuments.co.uk
Please reply to the newsgroup
Keep your VBA code safe, sign the ClassicVB petition www.classicvb.org

Thanks for the detailed reply and explanations.


-- 
Greg Maxey/Word MVP
See:
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.


Jonathan West wrote:
> "Greg Maxey"  wrote in message
> news:1147458607.804170.129110@y43g2000cwc.googlegroups.com...
>> Ok, I see sort of.
>>
>> So by using:
>>
>> Sub ProcessForm(oForm as UserForm1)
>>
>> I could then process one or a hundred control values in a procedure
>> outside the UserForm.  This would be sort of like my original method
>> 3, but I wouldn't need to declare Public variables to pass the data
>> from the form back to the module.  Personally I like the code
>> separated out this way.
>
> I would prefer to avoid using Public variables where there is no
> particular need. Therefore, to my mind, this option is better than
> your option 3. However, I see no great benefit in putting the routine
> in a separate module if it will only ever be called for a single
> Userform. In addition to minimising the amount of code, I like to
> simplify things as much as possible by keeping closely related code
> together in a single module. Therefore, unless the external routine
> is going to be called from multiple forms (hence passing the userform
> As Object in my example), your option 4 is preferable in my view.
>
> In essence, Option 1 or Option 4 are both perfectly fine for code
> that is only used by a single UserForm, and the choice between them
> is largely one of convenience - with more processing you may want to
> split the code up into separate routines.
>
>>
>> However, it sounds like using a method like this would make me odd
>> man out wrt coding conventions.
>
> I've seen *much* worse! Including from Microsoft. If you want to see
> a truly dire example of spaghetti code, open up the VBA project for
> the Batch Conversion Wizard that is included within Microsoft Office.
>
> Ultimately, the main thing is to remember that source code serves two
> quite separate purposes.
>
> 1. It is a set of instruction to be compiled and executed by the
> computer.
> 2. It is a message to yourself (or to another programmer who views
> your code) about what you are trying to achieve, so that code can
> more easily be debugged, maintained and extended in future.
>
> All coding conventions have this second point as their ultimate aim.
> Provided that you have a coding convention that works for you, you
> needn't worry much if your code doesn't conform to other people's
> conventions. Just concentrate on making it clear by ensuring that
> whatever convention you choose, you apply it as consistently as
> reasonably possible.

Jonathan,

One more thing.

<I normally use Unload me rather than Me.Hide in an event routine to close
<the form, so that when you move on to the next line of the calling routine
<the form has not only been hidden but also unloaded. That way, you don't
<need explicit code in the calling routine to unload the form and set the
<object variable to Nothing.

So if Unload myFrm *is* used in the calling routine vice Unload Me in the 
userform as you propose,  is Set myFrm = Nothing then redundant in the 
calling routine?



-- 
Greg Maxey/Word MVP
See:
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.


Jonathan West wrote:
> "Greg Maxey"  wrote in message
> news:1147458607.804170.129110@y43g2000cwc.googlegroups.com...
>> Ok, I see sort of.
>>
>> So by using:
>>
>> Sub ProcessForm(oForm as UserForm1)
>>
>> I could then process one or a hundred control values in a procedure
>> outside the UserForm.  This would be sort of like my original method
>> 3, but I wouldn't need to declare Public variables to pass the data
>> from the form back to the module.  Personally I like the code
>> separated out this way.
>
> I would prefer to avoid using Public variables where there is no
> particular need. Therefore, to my mind, this option is better than
> your option 3. However, I see no great benefit in putting the routine
> in a separate module if it will only ever be called for a single
> Userform. In addition to minimising the amount of code, I like to
> simplify things as much as possible by keeping closely related code
> together in a single module. Therefore, unless the external routine
> is going to be called from multiple forms (hence passing the userform
> As Object in my example), your option 4 is preferable in my view.
>
> In essence, Option 1 or Option 4 are both perfectly fine for code
> that is only used by a single UserForm, and the choice between them
> is largely one of convenience - with more processing you may want to
> split the code up into separate routines.
>
>>
>> However, it sounds like using a method like this would make me odd
>> man out wrt coding conventions.
>
> I've seen *much* worse! Including from Microsoft. If you want to see
> a truly dire example of spaghetti code, open up the VBA project for
> the Batch Conversion Wizard that is included within Microsoft Office.
>
> Ultimately, the main thing is to remember that source code serves two
> quite separate purposes.
>
> 1. It is a set of instruction to be compiled and executed by the
> computer.
> 2. It is a message to yourself (or to another programmer who views
> your code) about what you are trying to achieve, so that code can
> more easily be debugged, maintained and extended in future.
>
> All coding conventions have this second point as their ultimate aim.
> Provided that you have a coding convention that works for you, you
> needn't worry much if your code doesn't conform to other people's
> conventions. Just concentrate on making it clear by ensuring that
> whatever convention you choose, you apply it as consistently as
> reasonably possible.

"Greg Maxey" <gmaxey@mvps.oSCARrOMEOgOLF> wrote in message 
news:%23zbJgWtdGHA.536@TK2MSFTNGP02.phx.gbl...
> Jonathan,
>
> One more thing.
>
> <I normally use Unload me rather than Me.Hide in an event routine to close
> <the form, so that when you move on to the next line of the calling 
> routine
> <the form has not only been hidden but also unloaded. That way, you don't
> <need explicit code in the calling routine to unload the form and set the
> <object variable to Nothing.
>
> So if Unload myFrm *is* used in the calling routine vice Unload Me in the 
> userform as you propose,  is Set myFrm = Nothing then redundant in the 
> calling routine?

Yes. In fact in most cases it is redundant anyway because the variable will 
go out of scope (i.e. cease to exist) when the calling routine is ended.

-- 
Regards
Jonathan West - Word MVP
www.intelligentdocuments.co.uk
Please reply to the newsgroup
Keep your VBA code safe, sign the ClassicVB petition www.classicvb.org

Jonathan,

The only time I see where is might matter is if you call the form using
the default instance and only hide it in the Userform (vice unload).  I
ran the following two procedures:

Sub Test1()
UserForm1.Show
Set UserForm1 = Nothing 'Without this line the UserForm terminate event
doesn't fire
End Sub

Sub Test2()
Dim myFrm As UserForm1
Set myFrm = New UserForm1
myFrm.Show
End Sub
'Terminate event fires after the procedrue ends

With the following code in a Userform:

Private Sub CommandButton1_Click()
Me.Hide
End Sub

Private Sub UserForm_Terminate()
Debug.Print "Terminate"
End Sub

Sub CmdButton_Click
Me.Hide
End Sub