在运行时将userform添加到其他工作簿

I have an addin and a workbook open. The addin is a .xlam file and in the workbook I've added a reference to it. The addin is password protected.

我有一个插件和一本工作簿。插件是.xlam文件，在工作簿中我添加了对它的引用。插件受密码保护。

It is possible to run public methods of the addin from my workbook. However one method in the addin makes use of VBA.UserForms.Add to open a userform that was created at runtime like this

可以从我的工作簿中运行addin的公共方法。但是，addin中的一个方法使用VBA.UserForms.Add来打开在运行时创建的用户表单，就像这样

Let's say the workbook which holds a reference to myAddin has this:

假设持有myAddin引用的工作簿有这样的：

Private Sub callAddin()
    myAddin.ShowForm ThisWorkbook
End Sub

Ordinarily, the code in my addin looks like this:

通常，我的插件中的代码如下所示：

Public Sub ShowForm(CallerWorkbook As Workbook)
    Const vbext_ct_MSForm As Long = 3

    'This is to stop screen flashing while creating form
    Application.VBE.MainWindow.Visible = False

    'Add to ThisWorkbook, not supplied workbook or VBE will crash - ignore CallerWorkbook
    Dim myForm As Object
    Set myForm = ThisWorkbook.VBProject.VBComponents.Add(vbext_ct_MSForm)

    'Create the User Form
    With myForm
        .Properties("Caption") = "Select"
        .Properties("Width") = 300
        .Properties("Height") = 270
    End With

    'Show the form
    Dim finalForm As Object
    Set finalForm = VBA.UserForms.Add(myForm.Name)
    finalForm.Show

    'Remove form
    ThisWorkbook.VBProject.VBComponents.Remove myForm

End Sub

Which works fine. However when my addin is password protected, trying to add a temporary userform to it is not allowed. No problem, I just add the temporary userform to the workbook that called the code instead, as this will not be password protected

哪个工作正常。但是，当我的插件受密码保护时，不允许尝试向其添加临时用户表单。没问题，我只是将临时用户表单添加到调用代码的工作簿中，因为这不受密码保护

Sub ShowForm(CallerWorkbook As Workbook)
    Const vbext_ct_MSForm As Long = 3

    'This is to stop screen flashing while creating form
    Application.VBE.MainWindow.Visible = False

    'Add to CallerWorkbook instead
    Dim myForm As Object
    Set myForm = CallerWorkbook.VBProject.VBComponents.Add(vbext_ct_MSForm)

    'Create the User Form
    With myForm
        .Properties("Caption") = "Select"
        .Properties("Width") = 300
        .Properties("Height") = 270
    End With

    'Show the form
    Dim finalForm As Object
    'Now myForm cannot be found and added
    Set finalForm = VBA.UserForms.Add(myForm.Name)
    finalForm.Show

    'Remove form
    CallerWorkbook.VBProject.VBComponents.Remove myForm

End Sub

However VBA can't seem to see where myForm.Name points to now, so the Add method fails with "Run time error 424: Object required"

但是，VBA似乎无法看到myForm.Name指向的位置，因此Add方法失败并显示“运行时错误424：需要对象”

Is there any way to display a form created at runtime in another workbook?

有没有办法在另一个工作簿中显示在运行时创建的表单？

1 个解决方案

#1

The problem that you're encountering is that UserForms are Privately instanced by default. That means that a project cannot refer to a UserForm in another project, and if you can't refer to the form, you can't call it's Show method.

您遇到的问题是默认情况下UserForms是私有实例。这意味着项目不能引用另一个项目中的UserForm，如果您不能引用该表单，则无法调用它的Show方法。

Your Set myForm = CallerWorkbook.VBProject.VBComponents.Add(vbext_ct_MSForm) statement returns a VbComponent, not a UserForm, so that's why you can't then use VBA.UserForms.Add(myForm.Name)

你的Set myForm = CallerWorkbook.VBProject.VBComponents.Add（vbext_ct_MSForm）语句返回一个VbComponent，而不是UserForm，这就是为什么你不能再使用VBA.UserForms.Add（myForm.Name）

There are 2 ways around this:

有两种方法：

1 - Create a PublicNotCreatable template UserForm in your add-in

A UserForm is like a class, so it can have its Instancing property set, just like a class. However, the VBE doesn't expose the Instancing property in the Properties Window for UserForms, so to set the instancing, you need to export the form, and then edit the Attribute VB_Exposed attribute in the FRM file in a text editor, before importing the form again. Here are the steps:

UserForm就像一个类，因此它可以像设置类一样设置Instancing属性。但是，VBE不会在UserForms的“属性”窗口中显示Instancing属性，因此要设置实例化，需要导出表单，然后在导入之前在文本编辑器中编辑FRM文件中的“属性VB_Exposed”属性。再次形成。以下是步骤：

• Create a UserForm named TemplateForm in your add-in project
• 在加载项项目中创建名为TemplateForm的UserForm
• Remove TemplateForm and choose to Export the form before removing it
• 删除TemplateForm并选择在删除之前导出表单
• Open the TemplateForm.frm file in a text editor
• 在文本编辑器中打开TemplateForm.frm文件
• Edit the line Attribute VB_Exposed = False so that is reads Attribute VB_Exposed = True
• 编辑行属性VB_Exposed = False，以便读取属性VB_Exposed = True
• Save the changes to TemplateForm.frm
• 将更改保存到TemplateForm.frm
• Import TemplateForm.frm into your add-in
• 将TemplateForm.frm导入您的加载项

• Add a public function that returns a new instance of TemplateForm to your add-in. I've made this function accept a workbook reference so that the add-in can configure any workbook specific properties on the form:

  添加一个公共函数，它将一个新的TemplateForm实例返回到您的加载项。我已使此函数接受工作簿引用，以便加载项可以在窗体上配置任何工作簿特定的属性：

  Public Function GetTemplateForm(CallerWorkbook As Workbook) As TemplateForm
    Dim frm As TemplateForm
    Set frm = New TemplateForm
    'Set early-bound properties with intellisense
    frm.Caption = "Select"
    frm.Width = 300
    frm.Height = 270
  
    'Configure CallerWorkbook specific form properties here
    '...
    Set GetTemplateForm = frm
  End Function
  

• In your user's workbook, you can then show an instance of the TemplateForm, without ever having to dynamically add a form, or deal with screen-flickering, or hard-to-debug code:

  在用户的工作簿中，您可以显示TemplateForm的实例，而无需动态添加表单，或处理屏幕闪烁或难以调试的代码：

  Sub ShowAddinForm()
      With MyAddin.GetTemplateForm(ThisWorkbook)
          'Do more workbook specific propery setting here...
          '...
          .Show
      End With
  End Sub
  

** Note - The Rubberduck VBA add-in will soon have the ability to add a PublicNotCreatable UserForm.

**注意 - Rubberduck VBA加载项很快就可以添加PublicNotCreatable UserForm。

2 - Have the add-in create the UserForm component, but have the user's workbook manage it

This approach isn't nearly as elegant. There's a lot more code for the user to manage, and there's screen flickering, and hard to debug code. Here are the steps:

这种方法并不那么优雅。用户需要管理更多代码，屏幕闪烁，代码调试困难。以下是步骤：

• Add this code to the add-in:

  将此代码添加到加载项：

  Public Function GetTempFormName(CallerWorkbook As Workbook) As String
      Const vbext_ct_MSForm As Long = 3
  
      'This is to stop screen flashing while creating form
      Application.VBE.MainWindow.Visible = False
  
      'Add to CallerWorkbook instead
      With CallerWorkbook.VBProject.VBComponents.Add(vbext_ct_MSForm)
          .Properties("Caption") = "Select"
          .Properties("Width") = 300
          .Properties("Height") = 270
          GetTempFormName = .Name
      End With
  End Function
  
  Public Sub RemoveTempForm(CallerWorkbook As Workbook, FormName As String)
      With CallerWorkbook.VBProject.VBComponents
          Dim comp As Object
          Set comp = .Item(FormName)
          .Remove .Item(FormName)
      End With
  End Sub
  

• Then, in the user's workbook, add this code:

  然后，在用户的工作簿中，添加以下代码：

  Sub GetAddinToCreateForm()
      Dim FormName As String
      FormName = MyAddin.GetTempFormName(ThisWorkbook)
      With VBA.UserForms.Add(FormName)
          .Show
      End With
      MyAddin.RemoveTempForm ThisWorkbook, FormName
  End Sub