3

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
Improve this question
3
  • There is no way to save it in memory? I was hoping to avoid input to worksheet and then output to userform. Commented Feb 17, 2016 at 13:47
  • 2
    I think one important thing to remember is that when there is no VBA code running all the information that you stored in memory is gone. So if you have no code running after the form is closed, you have lost your settings. You could choose to store that information in a textfile in the temp folder for instance. Makes it harder to retrieve the settings though. Commented Feb 17, 2016 at 13:48
  • 1
    @Tom Could you please delete your comment, or edit to reflect the fact that while a Dim'd variable is lost after code stops, Public'd variables are maintained as long as the workbook is open [and the module code is unedited]? See my answer below for details. Commented Feb 17, 2016 at 15:23

2 Answers 2

Reset to default
3

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

So referring back to my original post, if store the values of the array as a public variable(outside a sub) then this might actually work? Super cool. I will def try it and see if this works.
Maybe you want to (even more so) elaborate upon your answer and state at the top that Doug could preserve the content of the form by merely hiding it and NOT unloading it. Also, he would have to remove the "initialize" sub. But I believe this is essentially what he is looking for (as long as the excel file stays open, no error occurs, and the form does not get changed).
Thanks guys, I did do the research the in general details related to this issue (before asking) , and had played around with the hiding/unhiding option, but I was curious to this solution as well.
@DougCoats There may be reasons to close the userform, but if you can make it invisible instead, that will likely be easiest - keep in mind that even then, the userform will be lost when the file is closed.
1

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.

Improve this answer

1 Comment

Note that when a sub finishes executing, data remains available if variables have been declared public; however when a workbook is closed, even publicly declared variables are wiped out.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.