Excel VBA 中的“包括”？

"Include" in Excel VBA?

提问by user3508196

I have a user-form which is made up of many subs, this is assigned as a macro to a button on the worksheet. When the user is finished with this user-form they can press a button on it which causes its visibility to become false and when entered again everything appears how it was left resulting in a save like feature.

我有一个由许多子程序组成的用户表单，它作为宏分配给工作表上的一个按钮。当用户完成此用户表单时，他们可以按下其上的一个按钮，这会导致其可见性变为假，当再次输入时，所有内容都会显示其原样，从而产生类似保存的功能。

I now need to apply this to multiple buttons on the worksheet and each user form needs to have the exact same code and same buttons but be a separate form as each individual button requires it's own save like feature. The way I was planning on doing this was to copy the existing user form and paste it many times with different names however, if a modification is required it will take a long time to carry out therefore, is there a method such as "include" which could use a base module from which all the code is accessed so that if I ever need to change anything I just do it on that one module and everything else updates via the include?

我现在需要将其应用于工作表上的多个按钮，并且每个用户表单都需要具有完全相同的代码和相同的按钮，但要成为一个单独的表单，因为每个单独的按钮都需要它自己的类似保存功能。我计划这样做的方法是复制现有的用户表单并使用不同的名称多次粘贴，但是，如果需要修改，则需要很长时间才能执行，是否有诸如“include”之类的方法它可以使用一个基本模块，从中访问所有代码，这样如果我需要更改任何内容，我只需在该模块上进行更改，其他所有内容都通过包含更新？

EDIT:

编辑：

I now have a public function called costing() and am getting an error when I used:

我现在有一个名为 costing() 的公共函数，当我使用时出现错误：

Private Sub material_Change()
Call costing
End Sub

采纳答案by chris neilsen

You can have multiple instances of the same form. You can use this to retain multiple sets of form values

您可以拥有同一表单的多个实例。您可以使用它来保留多组表单值

Try this:

尝试这个：

Create your form, as usual. Lets call it MyForm

像往常一样创建表单。让我们称之为MyForm

Create several buttons on you sheet. My example uses ActiveX buttons, but Form Control buttons can be used too. Lets call them CommandButton1and CommandButton2

在工作表上创建几个按钮。我的示例使用 ActiveX 按钮，但也可以使用表单控件按钮。让我们给他们打电话CommandButton1和CommandButton2

In your form module, include a TerminateSub, which includes this code

在您的表单模块中，包含一个TerminateSub，其中包含此代码

Private Sub UserForm_Terminate()
    '  any other code you may need...
    Unload Me
End Sub

The Form buton to save/Hide the form needs to be

用于保存/隐藏表单的表单按钮需要为

Private Sub btnSaveAndHide_Click()
    Me.Hide
End Sub

The Sheet Button code is as follows

Sheet Button代码如下

• The code is identical for each button (and calls a common Sub), and each button has its own Staticform variable.)
• The Error handler is needed to deal with the case a form is properly closed. In this case the instance no longer exists, but the local Static variable is also not Nothing

• Example shows form shown as Modeless, you can change this to Modal if you want.

  Private Sub CommandButton1_Click()
      Static frm As MyForm
      ShowMyForm frm
  End Sub
  
  Private Sub CommandButton2_Click()
      Static frm As MyForm
      ShowMyForm frm
  End Sub
  
  
  Private Sub ShowMyForm(frm As MyForm)
      If frm Is Nothing Then Set frm = New MyForm
  
      On Error GoTo EH
      frm.Show vbModeless
  Exit Sub
  EH:
      If Err.Number = -2147418105 Then
          On Error GoTo 0
          Set frm = Nothing
          Set frm = New MyForm
          frm.Show
      End If
      On Error GoTo 0
  End Sub
  

• 每个按钮的代码都是相同的（并调用一个公共 Sub），并且每个按钮都有自己的Static表单变量。）
• 需要错误处理程序来处理表单正确关闭的情况。在这种情况下，实例不再存在，但局部静态变量也不是 Nothing

• 示例显示窗体显示为无模式，如果需要，您可以将其更改为模式。

  Private Sub CommandButton1_Click()
      Static frm As MyForm
      ShowMyForm frm
  End Sub
  
  Private Sub CommandButton2_Click()
      Static frm As MyForm
      ShowMyForm frm
  End Sub
  
  
  Private Sub ShowMyForm(frm As MyForm)
      If frm Is Nothing Then Set frm = New MyForm
  
      On Error GoTo EH
      frm.Show vbModeless
  Exit Sub
  EH:
      If Err.Number = -2147418105 Then
          On Error GoTo 0
          Set frm = Nothing
          Set frm = New MyForm
          frm.Show
      End If
      On Error GoTo 0
  End Sub
  

End result: multiple copies of the same form, each with their own values

最终结果：同一表单的多个副本，每个副本都有自己的值

---

---

UPDATE

更新

In responce to comment How would I access the variables inside of each user form externally

作为对评论的回应，我将如何从外部访问每个用户表单中的变量

In the example ubove the Forminstances are only accessable in the Command Button Click Handler routines, of within the From module itself. If you can write your code in the form module, then no change is needed.

在上面的示例中，Form实例只能在 From 模块本身的命令按钮单击处理程序例程中访问。如果您可以在表单模块中编写代码，则无需更改。

To make the Form instances available elsewhere, consider moving their declaration to Module Scope of a standard Module. You could declare declare them as, eg individual variables, an array (either static or dynamic), a Collection, a Dictionary. Which structure is best will depend on how you want to manage and access your form instances.

要使 Form 实例在其他地方可用，请考虑将它们的声明移动到标准模块的模块范围。您可以将它们声明为例如单个变量、数组（静态或动态）、集合、字典。哪种结构最好取决于您希望如何管理和访问表单实例。

For eaxample, a Static Array: Code in a standard Module

例如，静态数组：标准模块中的代码

Option Explicit
Global MyForms(1 To 2) As MyForm

Update the CommandButtoncode to

将CommandButton代码更新为

Private Sub CommandButton1_Click()
    ShowMyForm Module1.MyForms(1)
End Sub

Private Sub CommandButton2_Click()
    ShowMyForm Module1.MyForms(2)
End Sub

Private Sub ShowMyForm(frm As MyForm)no change, same as before

Private Sub ShowMyForm(frm As MyForm)没有变化，和以前一样

The code works the same as before, but you can now access the Global variable in a standard Module

代码的工作方式与以前相同，但您现在可以访问标准模块中的全局变量

Sub Demo()
    Dim i As Long

    For i = LBound(MyForms) To UBound(MyForms)
        If Not MyForms(i) Is Nothing Then
            MsgBox "For " & i & " Value = " & MyForms(i).TextBox1.Value
        End If
    Next
End Sub

回答by rory.ap

You don't need an "Include" (none exists in VBA); all you need to do is create a module and make the common methods public.

您不需要“包含”（VBA 中不存在）；您需要做的就是创建一个模块并公开公共方法。

For example, if you create a module and have a function like this:

例如，如果您创建一个模块并具有如下功能：

Public Function Add(first As Integer, second As Integer) As Integer
    Add = first + second
End Function

Then you can access it like this from another module/form/class module:

然后你可以从另一个模块/表单/类模块像这样访问它：

Sub test()
    MsgBox Add(3, 6)
End Sub