CODEWITHSUNDEEP
arraysexcelvbauserform
Doug Coats
Doug Coats

Reputation: 7117

Calling an Array Upon User Form Terminate/Close VBA

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

Upvotes: 3

Views: 760

Answers (2)

Grade &#39;Eh&#39; Bacon
Grade &#39;Eh&#39; Bacon

Reputation: 3833

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.

Upvotes: 3

Sorcefyre
Sorcefyre

Reputation: 204

Unfortunately, VBA doesn't support persistent memory storage when code goes out of scope (e.g., subroutine finishes executing). There are some very archaic ways to write values directly to memory, but they are cumbersome and dangerous for those not familiar with the methodology.

Possible solutions I can think of would be to take the data from the form, either directly or via an array, and write it to a hidden worksheet in Excel. You could also store the data in a database or write it to a text file.

A hidden worksheet is easier to code since you're already working in the Excel reference library. Writing to a database or a text file means setting or creating references to the applicable reference libraries and knowing the syntax of how to do those things.

Upvotes: 1

Related Questions