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

问题描述

我有，我想用户窗体的内容存储在一个阵列时，用户窗体被关闭的问题。我原本以为语法正确的，但它似乎并没有在用户窗体初始化重新填充。我试图把阵列自身的模块中，这也不能工作。有人在乎赐教？

样code：

 公用Sub DPArrayStuff（） 暗淡的DP（2）
 DP（0）=块1
 DP（1）=块2
 DP（2）=块3 结束小组 私人小组userform_terminate（） 如果Block1.Value＆LT;＆GT;然后vbNullString呼叫DPArrayStuff 结束小组 私人小组userform_Initialize（） 如果DP（0）下;＆GT; vbNUllString然后
     块1 = DP（0）
     块2 = DP（1）
     块3 = DP（2）
 万一 结束小组
 

解决方案

特别是对于用户窗体

正如@Ralph指出的那样，而不是逼抢用户窗体，可以只是使用户窗体不可见。这将保留表的所有信息，而无需你需要传送数据到其它变量，并且很可能是最简单的选项presuming闭合形式是没有必要的。

如果您的变量只需要生存而工作簿是开放的：

子运行后，通过声明变量作为上市公司，在模块级，声明子之前，您可以维护变量。例如，下面的模块code持有的i整个工作簿的持续时间的值，并且被点击每任一时间按钮，i被操纵并保留用于进一步使用。

 公共我作为整数子的button1_Click（）
    I = I + 1
    范围（A1）。值=我
结束小组子Button2_Click（）
    I = I + 1
    范围（A1）。值=我
结束小组
 

请注意，这通常被认为是很好的做法，只要有可能限制使用公共变量;与在子程序级声明的变量粘着允许用户更容易地识别这实际上是所需要的变量code键中运行，特别是因为它是常见的（尽管实践差）使用在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屋！