public variables

[johnny]

Hi,

When I use ActiveSheet.Copy to copy the current sheet to another
sheet, all the public variables or global variables are reset to 0,

anyway to avoid this or keep the public variables values when coping ?

Thanks.

Johnny.

 

[Dave Peterson]

I've never seen this happen.

Can you post the snippet of code that is affected?

Any chance that you have a misbehaving event macro that's really doing the dirty
work of clearing those variables.

 

[johnny]

I've never seen this happen.

Can you post the snippet of code that is affected?

Any chance that you have a misbehaving event macro that's really doing the dirty
work of clearing those variables.

I just found out from other site, said if the vb code got edited, all
the global variables will be reset.
As there are procedures on the current sheet, if I copy to other
sheet, all the code behind it will be copied too.
I think this cause all the global variables reset to default. I need
to copy the variable to a temp cells and retrieve back the value after
the sheet copy.

Is anyway just copy only the content of the current sheet to the other
sheet ?


Thanks.

Johnny.

 

[Dave Peterson]

What you've described isn't editing the code.

And that isn't really true. If you execute an "End" line (not "End if", not
"End Sub", not "End Function", but a simple "End"), then the public variables
will be reset. And if you hit the Reset button (or Run|Reset in the VBE), the
same thing will happen.

So I'm not sure what you saw is applicable to your situation.

Is there a chance that you're looking at variables in the new workbook--not the
variables in project that contained the macro that was running?

 

[johnny]

What you've described isn't editing the code.

And that isn't really true. If you execute an "End" line (not "End if", not
"End Sub", not "End Function", but a simple "End"), then the public variables
will be reset. And if you hit the Reset button (or Run|Reset in the VBE), the
same thing will happen.

So I'm not sure what you saw is applicable to your situation.

Is there a chance that you're looking at variables in the new workbook--not the
variables in project that contained the macro that was running?

I inserted a new module and put all public variables there.
I put a copy button on one sheet.
on the click event :

ActiveSheet.Copy After:=Sheets("Temp1")
ActiveSheet.Name = "Temp"
If ActiveSheet.Name = "Temp" Then
ActiveSheet.Unprotect Password:=c_Password
ActiveSheet.OLEObjects("b_Copy").Delete


after click on the copy button, and new sheet created, also all the
variables are reset.

All happens in one workbook.

thanks.

Johnny.

 

[Dave Peterson]

And if you do this a second time, the line that tries to rename the newly copied
sheet as Temp will fail. If you hit the End option on the Debug dialog, you'll
lose the contents of the variables.

And it looks like you're not showing all the code. It looks to me that you have
an "On error resume next" line that avoids the rename error. Maybe something in
the code you're not sharing is the problem.

 

[johnny]

And if you do this a second time, the line that tries to rename the newly copied
sheet as Temp will fail. If you hit the End option on the Debug dialog, you'll
lose the contents of the variables.

And it looks like you're not showing all the code. It looks to me that you have
an "On error resume next" line that avoids the rename error. Maybe something in
the code you're not sharing is the problem.

Sorry, I did not post all the code:

Here is the complete one:

Private Sub b_Copy_Click()
Dim i_Sheet As Integer
Dim i_ExistSheet As Integer


i_Sheet = 1
i_ExistSheet = 0

If ActiveSheet.Name = "Data Entry" Then
Do While i_Sheet <= Sheets.Count
If Sheets(i_Sheet).Name = "Temp" Then
i_ExistSheet = i_Sheet
End If
i_Sheet = i_Sheet + 1
Loop
If i_ExistSheet > 0 Then
If MsgBox("The existing Temp sheet will be deleted, before the
copy.", vbOKCancel) = vbOK Then
Application.DisplayAlerts = False
Sheets(i_ExistSheet).Delete
i_ExistSheet = 0
Application.DisplayAlerts = True
End If
End If
If i_ExistSheet = 0 Then
ActiveSheet.Copy After:=Sheets("Legend")
ActiveSheet.Name = "Temp"
If ActiveSheet.Name = "Temp" Then
ActiveSheet.Unprotect Password:=c_Password
ActiveSheet.OLEObjects("b_Copy").Delete
ActiveSheet.OLEObjects("Copy_Prev_Row").Delete
MsgBox ("Delete this temporary sheet after finishing working
with it.")
End If
End If
End If

End Sub

 

[Dave Peterson]

I put this in a General module:

Option Explicit
Public aaaa As String
Sub aa()
aaaa = "this is a test"
End Sub

And I ran it to initialize that public aaaa variable before I clicked the button
that ran your code.

I added a couple of lines to your code (debug.print's):

Option Explicit

Private Sub b_Copy_Click()
Dim i_Sheet As Integer
Dim i_ExistSheet As Integer
Dim c_Password As String

Debug.Print "Before: " & aaaa

c_Password = "xx"

i_Sheet = 1
i_ExistSheet = 0

If ActiveSheet.Name = "Data Entry" Then
Do While i_Sheet <= Sheets.Count
If Sheets(i_Sheet).Name = "Temp" Then
i_ExistSheet = i_Sheet
End If
i_Sheet = i_Sheet + 1
Loop
If i_ExistSheet > 0 Then
If MsgBox("The existing Temp sheet will be deleted, before the copy.", _
vbOKCancel) = vbOK Then
Application.DisplayAlerts = False
Sheets(i_ExistSheet).Delete
i_ExistSheet = 0
Application.DisplayAlerts = True
End If
End If
If i_ExistSheet = 0 Then
ActiveSheet.Copy After:=Sheets("Legend")
ActiveSheet.Name = "Temp"
If ActiveSheet.Name = "Temp" Then
ActiveSheet.Unprotect Password:=c_Password
ActiveSheet.OLEObjects("b_Copy").Delete
ActiveSheet.OLEObjects("Copy_Prev_Row").Delete
MsgBox ("Delete this temporary sheet after finishing working with it.")
End If
End If
End If
Debug.Print "after: " & aaaa
End Sub

I got this back:
Before: this is a test
after: this is a test

But if you're having trouble keeping the variables, then you could define a
boolean in that same general module.

Public VarsAreInitialized as boolean

Then check that each time you need to rely on them

if varsareinitialized = false then
call routinethatinitializesvariables
end if

Sub routinethatinitializesvariables()
varsareinitialized = true
'rest of vars here
end sub

If you're depending on variables that may change after initialization, then
maybe putting them on a worksheet would be the way to go.