在用户表单中调用数组终止/关闭VBA [英] Calling an Array Upon User Form Terminate/Close VBA

问题描述

我有一个问题，我想在用户窗体关闭时将用户窗体的内容存储在数组中。我以为我的语法正确，但似乎没有重新填充用户窗体初始化。我尝试将数组放在自己的模块中，这也没有。任何人都要关心我？

示例代码：

  DPArrayStuff（）
 
 Dim DP（2）
 DP（0）= Block1 
 DP（1）= Block2 
 DP（2）= Block3 
 
 End Sub 
 
 Private Sub userform_terminate（）
 
如果Block1.Value<> vbNullString然后调用DPArrayStuff 
 
 End Sub 
 
 Private Sub userform_Initialize（）
 
如果DP（0） vbNUllString然后
 Block1 = DP（0）
 Block2 = DP（1）
 Block3 = DP（2）
 End If 
 
 End Sub 
  

解决方案

对于特定用户形式 p>

如@Ralph所指出，而不是关闭userform，您只能使用户不可见。这将保留表单上的所有信息，而无需将数据传输到其他变量，并且可能最简单的选项是假设关闭表单是不必要的。

如果您的变量只需要在工作簿打开时生存：

您可以在子运行后维护变量，将该变量声明为公开在模块级别之前，声明一个Sub。例如，下面的模块代码在工作簿的整个持续时间内保存i的值，并且每次点击任一按钮时，我被操纵并保留以供进一步使用。

  Public i As Integer 
 
 Sub Button1_Click（）
i = i + 1 
范围（A1）Value = i 
 End Sub 
 
 Sub Button2_Click（）
i = i + 1 
 Range（A1）。Value = i 
 End Sub 
  

请注意，通常认为尽可能长时间限制使用公共变量的好习惯;使用子程序级别声明的变量，允许用户更容易地识别要运行的代码实际需要哪些变量，特别是因为在VBA中尚未声明的变量是常见的（尽管很差的做法）。不必要的使用公共变量可能会导致懒惰。

如果您的变量需要在工作簿关闭和开启后生存下来：

如果要在打开和关闭工作簿之间存储这样的数据，您有几个选项可以使数据对用户不可见[由于各种原因]：

- 您可以保留数组值中的空白表，然后将该表隐藏并保护。这是存储数据数组的最简单的方法之一，只要保密性不是一个问题[因为存储在工作表中的数据可以被承诺的用户平均访问，无论工作簿是否受密码保护）

- 您可以将数据存储在另一个文件中，然后Excel在启动时拉入数组。这具有保持原始文件更小的优点，并且如果保密性受到关注，您可以将附加文件存储在只有一些用户可访问的网络位置。

- 您可以创建不可见的形状并编辑这些形状的内部属性，如标题。该数据可能是可访问的，但对用户不显眼;可能不是数组数组的一个很好的选择，但可能是。

I have an issue where I'd like to store the contents of a userform in an array when the userform is closed. I thought I had the syntax correct but it seems not to repopulate upon userform initialize. I tried putting the array in its own module and this did not work either. Anyone care to enlighten me?

Sample code:

Public Sub DPArrayStuff()

Dim DP(2)
DP(0) = Block1
DP(1) = Block2
DP(2) = Block3

End Sub

Private Sub userform_terminate()

If Block1.Value <> vbNullString Then Call DPArrayStuff

End Sub

Private Sub userform_Initialize()

If DP(0) <> vbNUllString Then 
    Block1 = DP(0)
    Block2 = DP(1)
    Block3 = DP(2)
End If

End Sub 

解决方案

For userforms in particular

As pointed out by @Ralph, instead of closing down the userform, you can merely make the userform invisible. This will retain all information on the form without you needing to transfer the data to other variables, and is likely the simplest option presuming closing the form is not necessary.

If your variables only need to survive while the workbook is open:

You can maintain variables after a sub has run, by declaring that variable as Public, at the module level, before declaring a Sub. For example, the below module code holds the value of i throughout the duration of the workbook, and every time either button is clicked, i is manipulated and retained for further use.

Public i As Integer

Sub Button1_Click()
    i = i + 1
    Range("A1").Value = i
End Sub

Sub Button2_Click()
    i = i + 1
    Range("A1").Value = i
End Sub

Note that it is generally considered good practice to limit the use of public variables as long as possible; sticking with variables declared at the subroutine level allow a user to more easily identify which variables are actually required for code to be run, particularly because it is common (although poor practice) to use a variable in VBA that has not yet been declared. Unnecessary usage of public variables may encourage laziness.

If your variables need to survive the workbook closing and opening:

If you want to store data like this in between opening and closing a workbook, you have a few options to make it so the data is not visible to your users [for various reasons]:

-You can keep an empty sheet which drops in the array values, and then have that sheet hidden and protected. This is one of the simplest ways to store an array of data, as long as confidentiality is not a concern [because data stored in a worksheet can be trivially accessed by a committed user, whether the workbook is password protected or not]

-You can store the data in another file, which Excel then pulls into an array on starting. This has the advantage of keeping your original file smaller, and if confidentiality is a concern, you can have the additional file stored in a network location to which only some users have access.

-You can create invisible shapes and edit internal properties of those shapes, such as their titles. This data is potentially accessible but unobtrusive to the user; probably not a great option for an array of data, but it may be.

这篇关于在用户表单中调用数组终止/关闭VBA的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！