scottdk
11-27-2011, 08:57 PM
When I use Unload Userform, my VBA Project appears to be reset and any global variables lose thier values, whereas if I call Me.Hide they remain intact. Is there a reason for this? (There are no unhandled errors)
I'm developing an Excel addin with a right click menu to bring up a form and process the value in the cell which was right clicked in any open workbook, so I initialise the variable APP on workbook open (and also reinitialize it whenever my forms close but my APP_SheetBeforeRightClick event still only fires every second time!)
Public WithEvents APP As Application
Public g_RightClickValue As String
Public g_StartupValue As String
Private Sub Workbook_Open()
Set APP = Application
g_StartupValue Value = Now()
End Sub
Private Sub APP_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
g_RightClickValue = Target.value
'Set up right click menu
'......
end sub I discovered the reason while I was preparing an example to post here, and as such my workaround is to 1) Cancel "UserForm_QueryClose" and 2) always use "Me.Hide" rather than "Unload Me". Wouldn't it be valid to want to unload a form and clear it from Memory without resetting the VBA project? Am I doing something wrong? I have attached an example spreadsheet to demonstrate this.
Excel Help implies the memory used by the form is reclaimed, but not the workbook's variables:
When an object is unloaded, it's removed from memory and all memory associated with the object is reclaimed. Until it is placed in memory again using the Load statement, a user can't interact with an object, and the object can't be manipulated programmatically. I can live with using Me.Hide, but I hope my question will help somebody else who is stumped by it, and an enlightened person will explain why...
I'm developing an Excel addin with a right click menu to bring up a form and process the value in the cell which was right clicked in any open workbook, so I initialise the variable APP on workbook open (and also reinitialize it whenever my forms close but my APP_SheetBeforeRightClick event still only fires every second time!)
Public WithEvents APP As Application
Public g_RightClickValue As String
Public g_StartupValue As String
Private Sub Workbook_Open()
Set APP = Application
g_StartupValue Value = Now()
End Sub
Private Sub APP_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
g_RightClickValue = Target.value
'Set up right click menu
'......
end sub I discovered the reason while I was preparing an example to post here, and as such my workaround is to 1) Cancel "UserForm_QueryClose" and 2) always use "Me.Hide" rather than "Unload Me". Wouldn't it be valid to want to unload a form and clear it from Memory without resetting the VBA project? Am I doing something wrong? I have attached an example spreadsheet to demonstrate this.
Excel Help implies the memory used by the form is reclaimed, but not the workbook's variables:
When an object is unloaded, it's removed from memory and all memory associated with the object is reclaimed. Until it is placed in memory again using the Load statement, a user can't interact with an object, and the object can't be manipulated programmatically. I can live with using Me.Hide, but I hope my question will help somebody else who is stumped by it, and an enlightened person will explain why...