• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Persistent variable in a userform...

dan_l

dan_l

Active Member
Hi,

I'm working on a userform. It's actually there to configure some functionality. It's got some dynamic listboxes.


Anyway, so
step 1: uses a refedit to get a range. User selects a range, presses a button. The range is passed to a array. The headers of the selected range are put into a listbox.
step 2: user select item from the list box in 1. Hits a button. That's when all that derivative functionality takes place.

The trouble is that the array erases after step 1. So, if I try to call it in step 2, I get an error. I have a couple of options that come to mind:

-Just redefine the array.
-Maybe output the array in a worksheet somewhere

Neither seems all that efficient. Is there a better way? I can post code or a sample, but I'm thinking this question is broad enough that it might not be required.
 
Ahk, I appreciate the links. They helped.

So, as it turns out, there doesn't seem to be a way to save variables that will last through multiple 'stages' of what's happening in the userform. Redefining all of that stuff sort of sucked and made my embarrassingly bad userform code even worse. Here's something neat though:

You can add a array to a 'safe' location by just putting it in as a name with refers to as your array. Even after your array goes away, it stays in the names which seems great for having a seemingly ok place to store stuff.


I can get the stuff out of the name to put back into a drop down or a list box, but I still can't find a way to get the values back into a array in VBA.


Any thoughts?

ok, nm: you can just pull it into a variant and scope it with ubounds from there. Neat trick.
 
Last edited:
Ok, check this out: It's kind of a neat workaround since your variables evaporate as soon as the code is finished running.


Code: 
Sub MoveArrayToName()
Dim rData As Range
Dim avData As Variant

Set rData = Sheets("data").UsedRange
avData = rData

ThisWorkbook.Names.Add Name:="MyData", RefersTo:=avData

End Sub


Sub MoveNameToArray()
Dim avData As Variant
Dim rOP As Range

avData = [mydata]

Set rOP = Sheets("op").Range("a1")
rOP.Resize(UBound(avData, 1), UBound(avData, 2)) = avData

End Sub



Sub DeleteAName()
Dim nm As Name

For Each nm In ThisWorkbook.Names
  If nm.Name = "MyData" Then
  nm.Delete
  End If
Next nm

   
End Sub
 
You must log in or register to reply here.
Back
Top