accessing variant after macro has ended

[RAYLWARD102]

I have a public variable declared in a Module, that's triggered by worksheet change event; My public variable ceases to exist after the Module subroutine completes. How do I preserve a Public variable in memory, for use later on? Is there a way? Basically want to be able to retrieve that variable without after an extended amount of time has passed, where the user is not running any macros (just using the workbook sheets) until the change event occurs once again.


EDIT:
basically want a non-expiring variable; one that remains active until workbook project is closed.
I did read about ActiveWorkbook.CustomDocumentProperties, but these properties can't seem to store an array (variant)

 

[Richard U]

In it's own module, declare the variable as "public" and set it to some value during the open event.

Option Explicit

Public foo As String

You need to declare it as a global variable, this will do that.

 

[RAYLWARD102]

Isn't that what I said?? I said I have a public declared variant in a module; I store an array in this public variant upon worksheet change event; after macros have all completed, I cannot retrieve previously store data in the public variant variable I set earlier from a macro that runs later on.

 

[Greg Truby]

I have a public variable declared in a Module, that's triggered by worksheet change event; My public variable ceases to exist after the Module subroutine completes. How do I preserve a Public variable in memory, for use later on? Is there a way? Basically want to be able to retrieve that variable without after an extended amount of time has passed, where the user is not running any macros (just using the workbook sheets) until the change event occurs once again.


EDIT:
basically want a non-expiring variable; one that remains active until workbook project is closed.
I did read about ActiveWorkbook.CustomDocumentProperties, but these properties can't seem to store an array (variant)

Are you needing the variable's value to persist between workbook sessions? I.e. if the user closes the workbook and re-opens it, the variable still has the value it had when the user closed the workbook? If that's the case then we can look at properties or other methods to solve the problem. But before we tickle that bear let's see if we have to...

Or the variable does not need to persist between workbook sessions, but it does need to persist between calls to the event handler?

If that's the case then the main question is: do I need to use this variable in other procedures?

If NO then just declare the variable inside the event handler using the STATIC keyword:

Private Sub Worksheet_Change(ByVal Target As Range)

    Static vntMyArray As Variant
    
    '// ...some amazing bit of code goes here...


End Sub

Or do you need multiple routines but all of the routines are within the same module to be able to access this variable? If so then

Private vntMyArray As Variant


Private Sub Worksheet_Change(ByVal Target As Range)


    '// ...some amazing bit of code goes here...


End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)


    '// ...more stunning code here...


End Sub

Or do you need multiple routines in multiple modules to be able to access this variable? If so then

Public vntMyArray As Variant


Private Sub Worksheet_Change(ByVal Target As Range)


    '// ...some amazing bit of code goes here...


End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)


    '// ...more stunning code here...


End Sub

 

[RAYLWARD102]

Hi Greg,

No to keeping variable persistent between workbook sessions; Ideally, set the variable first time user triggers one of my routines, and remember it until workbook closed (even if no macros are events are running)
I've got Userforms, modules (many subroutines and functions), class (with private event subs) and worksheet subs; all of them can jump around each each other and maintain a Public variable that was defined in my Module.
But!! my public variable cannot keep info when all events, macros are not running (because the user hasn't triggered them yet and is just using the excel sheet). I would like my public variable to maintain it's value, when ever I call it (in between macros running and not running)

 

[Greg Truby]

Public or private variables should be persisting... for example these persist, each in their own modules, even though the names are the same:

'// Code in Sheet1's worksheet module

Private strLastAddress As String


Private Sub Worksheet_Deactivate()
    Application.StatusBar = False
    strLastAddress = " on another worksheet."
End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)


    If Len(strLastAddress) Then
        Application.StatusBar = "You last selected cell(s) " & strLastAddress
    End If
    
    Let strLastAddress = Target.Address(0, 0)


End Sub

and

'// Code in Sheet2's worksheet module


Private strLastAddress As String


Private Sub Worksheet_Deactivate()
    Application.StatusBar = False
    strLastAddress = "cheating on me another worksheet you fickle fiend."
End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)


    If Len(strLastAddress) Then
        Application.StatusBar = "Your last know whereabouts were " & strLastAddress
    End If
    
    Let strLastAddress = Target.Address(1, 1, xlR1C1, True)


End Sub

I'm wondering if what you need to do is initialize the variable(s) when you open the workbook or when a particular worksheet is activated.

 

[RAYLWARD102]

Everyone seems to be missing the point. I have a module with variables declared like: Public v as Variant and Public iRow1 as long
Then, the user is using the worksheet, and the selection change is triggered, driving below event (see code)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If iRow1 <> Target.Row Then
        If IsArray(v) = False Then
            'I need to load Public v as variant with an array from access database
        End If
        'check sheet values against array for changes and update databse accordingly
        
        iRow1 = Target.Row 'mark current row so we know for next SelectionChange event
    End If
End Sub

After the above completes, when the user triggers the SelectionChange event again, I wanted v to be preserved from the first go around, but it isn't and has to be reloaded every time. For some reason, iRow1 is preserved; not v. v is the important one. How do we keep v loaded without any macros running. v is an array (copy of a huge database recordset, that I don't want to load everytime a person moves between cells on the worksheet.

 

[jkpieterse]

A variable declared the way you did should retain its value for as long as your workbook is open. But there are a couple of actions which may reset your VBA project and hence zap your module-level variables:
- Executing the End statement
- User presses "End" after a runtime error
- You click the End toolbar button during debugging
- An ActiveX control is added to any sheet in the workbook containing the VBAProject (!)

 

[Jaafar Tribak]

Everyone seems to be missing the point. I have a module with variables declared like: Public v as Variant and Public iRow1 as long
Then, the user is using the worksheet, and the selection change is triggered, driving below event (see code)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If iRow1 <> Target.Row Then
        If IsArray(v) = False Then
            'I need to load Public v as variant with an array from access database
        End If
        'check sheet values against array for changes and update databse accordingly
        
        iRow1 = Target.Row 'mark current row so we know for next SelectionChange event
    End If
End Sub

After the above completes, when the user triggers the SelectionChange event again, I wanted v to be preserved from the first go around, but it isn't and has to be reloaded every time. For some reason, iRow1 is preserved; not v. v is the important one. How do we keep v loaded without any macros running. v is an array (copy of a huge database recordset, that I don't want to load everytime a person moves between cells on the worksheet.

Based on your description, the Variant v variable shouldn't be reset and If IsArray(v) should always return TRUE after the first go around unless the project is somehow reset as kindly pointed out by jkpieterse.

Do you have another variable also called v somewhere else in your vbaproject ?

Alternatively, you could declare v as a Static varaible inside the selection event handler ... something like this :

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   [COLOR=#0000ff][B] Static v As Variant[/B][/COLOR]
    If iRow1 <> Target.Row Then
        If IsArray(v) = False Then
            'I need to load Public v as variant with an array from access database
        End If
        'check sheet values against array for changes and update databse accordingly
        
        iRow1 = Target.Row 'mark current row so we know for next SelectionChange event
    End If
End Sub

Late edit
Or Static v as variant where n is an integer/Long
If you are not populating the array via the Array function.

 

[Greg Truby]

- An ActiveX control is added to any sheet in the workbook containing the VBAProject

I would not have thought of that. Thanks, JKP!

... I have a module with variables declared like: Public v as Variant ...

(snip)
        If IsArray(v) = False Then
...
        'check sheet values against array for changes and update databse accordingly
(snip)

(a) We've set a watch on IsArray(v) (context set to all procedure in all modules) and a breakpoint on the IF and stepped code and we're sure that at some point IsArray(v) does return true?
(b) "...check...update" again - stepping code with a watch on IsArray(v) and we're sure that it's still an array when we're done checking and updating?

Oh, and "HI!" to Jaafar. Nice to see you again, my old friend.