This is Probalbly as Lesson I will Learn Again

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Greg Maxey - 20 Dec 2007 02:17 GMT

I have a faint recollection of being schooled on this issue once before but
I can't find my notes and I can't remember the lesson.

If I have a procedure in a standard module and I want to get several items
of data from a UserForm is there anything wrong with an approach as shown in
this simple example:

Code in the Standard Module:

Sub CallUF()
UserForm1.Show
End Sub

Sub DoSomeThingWithUFData(ByRef oForm As UserForm1)
MsgBox oForm.TextBox1
MsgBox oForm.TextBox2
MsgBox oForm.CheckBox1.Value
MsgBox oForm.CheckBox2.Value
End Sub

Code in the UserForm:

Private Sub CommandButton1_Click()
DoSomeThingWithUFData Me
Unload Me
End Sub

Would it be a better practice to move the DoSomethingWith procedure into the
UserForm module?

If I do it this way is ByRef the way to go or ByVal?

Thanks.

Signature

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

Reply to this Message

Tony Strazzeri - 02 Jan 2008 13:19 GMT

Hi Greg,

> Sub CallUF()
> UserForm1.Show
[quoted text clipped - 16 lines]
> Would it be a better practice to move the DoSomethingWith procedure into the
> UserForm module?

My personal preference is to keep any code to do with the operation of
the form in the form module. If there is extensive operations to be
done once the code (usually data entry and validation) is the form is
complete, I hide the form and then do the rest in the calling
procedure.

But is all you are doing is validation and such like I would keep that
in the form. In your example (I appreciate it is a trivial example) I
would keep it in the form. Additionally if the procedure is in the
form module then you don't have to explicitly reference the controls
as you would is the code was outside.

eg your above example could be
Sub DoSomeThingWithUFData
MsgBox TextBox1
MsgBox TextBox2
MsgBox CheckBox1.Value
MsgBox CheckBox2.Value
End Sub

> If I do it this way is ByRef the way to go or ByVal?

Someone else may be able to advise with regard to memory usage or
speed on these two options. The default in VBA (if not specified) is
Byef. When using ByRef you should take care if you modify the
referenced variable. Using ByVal would avoid the need for that.

Hope this helps.
Cheers
TonyS.

Reply to this Message

Greg Maxey - 02 Jan 2008 15:00 GMT

Thanks Tony.

Signature

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

> Hi Greg,
>
[quoted text clipped - 48 lines]
> Cheers
> TonyS.

Reply to this Message