VBA/Word: Passing form name as argument works but results in error

Question

I have several forms, and there are several lines of code used to call and place each one. Instead of replicating these lines of code for each form, I want to use a subroutine simply to load and place a form. This code works:

Sub LoadForm_BulletBeginningEmphasis()
Load formBulletBeginningEmphasis
formBulletBeginningEmphasis.Show
formBulletBeginningEmphasis.StartUpPosition = 0
formBulletBeginningEmphasis.Left = Application.Left + (0.5 * Application.Width) - (0.5 * formBulletBeginningEmphasis.Width)
formBulletBeginningEmphasis.Top = Application.Top + (0.5 * Application.Height) - (0.5 * formBulletBeginningEmphasis.Height)
End Sub

What I want, though, is for this code to work, instead:

Public Sub LoadAndShowForms(ByVal formName As Object)
Load formName
formName.Show
formName.StartUpPosition = 0
formName.Left = Application.Left + (0.5 * Application.Width) - (0.5 * formName.Width)
formName.Top = Application.Top + (0.5 * Application.Height) - (0.5 * formName.Height)
End Sub

Sub LoadForm_BulletBeginningEmphasis()
Call LoadAndShowForms(formBulletBeginningEmphasis)
End Sub

The problem is that when I execute the second set of code lines, they work and the form works, but when the form disappears, VBA throws an error:

What's going on here? Why is the second version working but throwing an error?

[This answer might be worth a read...](https://stackoverflow.com/a/47291028/4088852) — Comintern, Sep 07 '18 at 19:10
Have you tried putting the `Show` method *after* the positioning commands? — Cindy Meister, Sep 08 '18 at 06:55
@CindyMeister, that solved it. Thanks! Can you explain why the Show method can precede all of the code when it is done the first way, but it has to follow all of the code when done the second way? — JoshG, Sep 09 '18 at 05:03

score 1 · Accepted Answer · answered Sep 10 '18 at 18:02

1

Put the Show method after the positioning commands.

If you think about it, this makes sense as code execution pauses when a modal UserForm is shown. Code that executes after Show logically shouldn't have any effect.

Depending on how the UserForm "disappears", the error will show up. If there's code "behind" the UserForm that unloads it then the "callee" (the UserForm) is no longer available. If the code only hides the UserForm, then an error may not occur (because the object is still loaded in memory).

It also makes a difference when calling the from another procedure: In the question's first code sample it's likely that the procedure is simply ending silently, even though it doesn't execute the positioning of the form. In the second code sample, execution has to return control to the calling procedure, but it can't because the procedure it called can't finish correctly.

answered Sep 10 '18 at 18:02

Cindy Meister

25,071
21
34
43

thank you for the solution and for the information. The information is, unfortunately, currently above my head. Can you point to an article that might give me some background information so that I can better understand your answer? – JoshG Sep 10 '18 at 19:36
1

Sorry, I don't know of any - my answer bases on logic and 25 years experience, not any kind of documentation. VBA code can end "silently" (no error is shown) within a "Sub", as is probably happening in the first example. But in the second example, `LoadForm_BulletBeginningEmphasis` is expecting code execution to return. If `LoadAndShowForms` isn't able to finish and code execution must return, then this forces the error to show instead of ending silently. – Cindy Meister Sep 10 '18 at 19:45
Thanks. I thought that LoadAndShowForms was causing the error, because the debugger immediately highlights formName.Show in that procedure. – JoshG Sep 10 '18 at 19:53
@JoshG That one *is* causing the error. It's just that it's not being shown if it's not being called from another procedure. – Cindy Meister Sep 10 '18 at 20:40

score 0 · Answer 2 · answered Sep 09 '18 at 01:13

I believe your problem is that you are passing the form ByVal and it should be ByRef. Public Sub LoadAndShowForms(ByRef formName as Object). I would also change Object to UserForm. Your sub's signature would become Public Sub LoadAndShowForms(ByRef formName as UserForm)

VBA/Word: Passing form name as argument works but results in error

2 Answers2