My variable always says False when I set it to True?

C

CRayF

I’m stumped… It may just be because this newbie has not mastered variables…

In this worksheet module code I am expecting the “ImportRequested†variable
to be set to “True†when the routine triggered by selecting the B1 cell is
completed successfully.

If I test the “ImportRequested†variable before if leaves the B1 routine End
If, it indeed holds the value of True. However when I immediately select the
N1 cell (only for debugging, “ImportRequested†variable = False. I expect it
to still be True and don’t see how it’s getting changed back to False’

Key references:

'<------- 1 I created the “ImportRequested†variable As Boolean
'<------- 2 Code for reference only for debug so when N1 is selected it
displays the value of the “ImportRequested†variable. It always says false, I
would expect it to say True after running the B1 cell routine… ;-(

'<----- 3 The B1 routine has run so set the “ImportRequested†to True

'<------ 4 Test BEFORE leaving the routine “ImportRequested†is TRUE as
expected.

(once I can figure out haw to have this “ImportRequested†variable hold it’s
value, I intend to test for it to bypass the “"Are you sure you want to CLEAR
this Worksheet?†prompt if the import option was just taken…)

Can you please shed some light as to what’s happinging?

-------------------------------------------------
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim srcProgramDataInputWs As Worksheet
Dim srcProgramSummaryTemplateWs As Worksheet
Dim srcProgramSummaryWs As Worksheet
Dim srcBettingTemplateWs As Worksheet
Dim racePark As Variant
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim wb As Workbook
Dim MyPath As String
Dim SaveDriveDir As String
Dim ImportRequested As Boolean '<------- 1

Set srcProgramSummaryTemplateWs = Sheets("@TemplateProgramSummary")
Set srcProgramSummaryWs = Sheets("ProgramSummary")
Set srcBettingTemplateWs = Sheets("@TempleteBetting")
Set srcProgramDataInputWs = Sheets("ProgramDataInput")

racePark = Left(srcProgramDataInputWs.Range("H3").Value, 3)

If Target.Address = "$A$1" Then
Dim exists As Boolean
Dim ExistingBettingWsName As Worksheet
Dim NewBettingWsName As Variant

Range("N3").Select

NewBettingWsName = Format(srcProgramDataInputWs. _
Range("F3").Value, "mm-dd ") & _
Left(srcProgramDataInputWs.Range("H3").Value, 3)

exists = False
For Each ExistingBettingWsName In ThisWorkbook.Sheets
If ExistingBettingWsName.Name = NewBettingWsName Then
exists = True
Exit For
End If
Next
If exists Then
MsgBox "Betting Worksheet for [ " & NewBettingWsName & _
" ] already exists. [RENAME] or [DELETE] that Worksheet and try
again."

Else
Dim NewBettingWs As Worksheet
Dim NewBettingWsTabColor As Variant
Dim raceParkList As Variant
Dim src As Variant

i = 6
raceParkList = srcProgramDataInputWs.Range("N" & i).Value
Do Until raceParkList = ""
raceParkList = srcProgramDataInputWs.Range("N" & i).Value
If racePark = raceParkList Then NewBettingWsTabColor =
srcProgramDataInputWs.Range("O" & i).Value
i = i + 1
Loop
Range("N3").Select

srcBettingTemplateWs.Copy before:=ActiveSheet
Set NewBettingWs = ActiveSheet
With NewBettingWs
.Name = NewBettingWsName
.Unprotect
.Tab.ColorIndex = NewBettingWsTabColor 'or replace with
index number

src = srcProgramDataInputWs.Range("B3").Value
i = 3
j = 0
Do Until src = ""
srcBettingTemplateWs.Rows("11:22").Copy .Cells((j * 12)
+ 11, 1)
i = i + 12
j = j + 1
src = srcProgramDataInputWs.Cells(i, 2).Value
Loop

.Protect
End With
End If
End If


If Target.Address = "$N$1" Then '<------ 2 Reference only for debug
MsgBox "End of B1 loop " & ImportRequested
End If


If Target.Address = "$K$1" Then
Dim ResetWorksheet As Boolean
If ImportRequested = True Then
ResetWorksheet = True
Else
If MsgBox("Are you sure you want to CLEAR this Worksheet?", _
vbYesNo) = vbYes Then
ResetWorksheet = True
End If
End If

If ResetWorksheet = True Then
src = ""
ActiveSheet.Unprotect
ActiveSheet.Range("A3:AZ300").Clear
With ActiveSheet
src = srcProgramDataInputWs.Range("B3").Value
i = 3
j = 0
Do Until src = ""
srcProgramSummaryTemplateWs.Rows("3:14").Copy .Cells((j
* 12) + 3, 1)
i = i + 12
j = j + 1
src = srcProgramDataInputWs.Cells(i, 2).Value
Loop
ActiveSheet.Protect
Range("K1").Value = "default"
Range("N3").Select
End With
End If
'ImportRequested = False
ResetWorksheet = False
End If


If Target.Address = "$B$1" Then
Dim SelectedTxtInputFile As Variant
SaveDriveDir = CurDir
MyPath = ThisWorkbook.Path & "/RaceData-XLS-Ready"
ChDrive MyPath
ChDir MyPath

SelectedTxtInputFile = Application.GetOpenFilename( _
"Race Program Input Files (*.txt),*.txt", , _
"Select which RACE Program to import", , False)

If SelectedTxtInputFile = "False" Then
Range("N3").Select
Else
ImportRequested = True '<----- 3

srcProgramDataInputWs.Unprotect
srcProgramDataInputWs.Range("A3:H242").ClearContents

With srcProgramDataInputWs.QueryTables.Add(Connection:= _
"TEXT;" & SelectedTxtInputFile _
, Destination:=srcProgramDataInputWs.Range("A3:H242"))
.Name = "ImportProgramData"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileOtherDelimiter = "|"
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
srcProgramDataInputWs.Protect
End If
'Range("K1").Select
ChDrive SaveDriveDir
ChDir SaveDriveDir
MsgBox "End of B1 loop " & ImportRequested '<------ 4
End If

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
'Range("K1").Value = "Clear"
ws_exit:
Application.EnableEvents = True
End Sub
 
J

Jim Thomlinson

The default value for a boolean when it is created is False. Your code
executes and changes it to true. When the code complete the varaible (which
exists on the stack memory) is destroyed. The next time that the code
executes and the varaible is created it is false again. In order to avoid
this there are a couple of things that you can do:
1. Declare the variable static. Doing this places the vairable on the heap
and not the stack. Variables on the heap are not destroyed until the program
ends. That is to say they persist even after the procedure ends.
2. Declare the variable outside of a procedure at the top of the code
(Global). Globals also presist as they are created on the heap and are immune
from destruction when a precedure ends.
--
HTH...

Jim Thomlinson


CRayF said:
I’m stumped… It may just be because this newbie has not mastered variables…

In this worksheet module code I am expecting the “ImportRequested†variable
to be set to “True†when the routine triggered by selecting the B1 cell is
completed successfully.

If I test the “ImportRequested†variable before if leaves the B1 routine End
If, it indeed holds the value of True. However when I immediately select the
N1 cell (only for debugging, “ImportRequested†variable = False. I expect it
to still be True and don’t see how it’s getting changed back to False’

Key references:

'<------- 1 I created the “ImportRequested†variable As Boolean
'<------- 2 Code for reference only for debug so when N1 is selected it
displays the value of the “ImportRequested†variable. It always says false, I
would expect it to say True after running the B1 cell routine… ;-(

'<----- 3 The B1 routine has run so set the “ImportRequested†to True

'<------ 4 Test BEFORE leaving the routine “ImportRequested†is TRUE as
expected.

(once I can figure out haw to have this “ImportRequested†variable hold it’s
value, I intend to test for it to bypass the “"Are you sure you want to CLEAR
this Worksheet?†prompt if the import option was just taken…)

Can you please shed some light as to what’s happinging?

-------------------------------------------------
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim srcProgramDataInputWs As Worksheet
Dim srcProgramSummaryTemplateWs As Worksheet
Dim srcProgramSummaryWs As Worksheet
Dim srcBettingTemplateWs As Worksheet
Dim racePark As Variant
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim wb As Workbook
Dim MyPath As String
Dim SaveDriveDir As String
Dim ImportRequested As Boolean '<------- 1

Set srcProgramSummaryTemplateWs = Sheets("@TemplateProgramSummary")
Set srcProgramSummaryWs = Sheets("ProgramSummary")
Set srcBettingTemplateWs = Sheets("@TempleteBetting")
Set srcProgramDataInputWs = Sheets("ProgramDataInput")

racePark = Left(srcProgramDataInputWs.Range("H3").Value, 3)

If Target.Address = "$A$1" Then
Dim exists As Boolean
Dim ExistingBettingWsName As Worksheet
Dim NewBettingWsName As Variant

Range("N3").Select

NewBettingWsName = Format(srcProgramDataInputWs. _
Range("F3").Value, "mm-dd ") & _
Left(srcProgramDataInputWs.Range("H3").Value, 3)

exists = False
For Each ExistingBettingWsName In ThisWorkbook.Sheets
If ExistingBettingWsName.Name = NewBettingWsName Then
exists = True
Exit For
End If
Next
If exists Then
MsgBox "Betting Worksheet for [ " & NewBettingWsName & _
" ] already exists. [RENAME] or [DELETE] that Worksheet and try
again."

Else
Dim NewBettingWs As Worksheet
Dim NewBettingWsTabColor As Variant
Dim raceParkList As Variant
Dim src As Variant

i = 6
raceParkList = srcProgramDataInputWs.Range("N" & i).Value
Do Until raceParkList = ""
raceParkList = srcProgramDataInputWs.Range("N" & i).Value
If racePark = raceParkList Then NewBettingWsTabColor =
srcProgramDataInputWs.Range("O" & i).Value
i = i + 1
Loop
Range("N3").Select

srcBettingTemplateWs.Copy before:=ActiveSheet
Set NewBettingWs = ActiveSheet
With NewBettingWs
.Name = NewBettingWsName
.Unprotect
.Tab.ColorIndex = NewBettingWsTabColor 'or replace with
index number

src = srcProgramDataInputWs.Range("B3").Value
i = 3
j = 0
Do Until src = ""
srcBettingTemplateWs.Rows("11:22").Copy .Cells((j * 12)
+ 11, 1)
i = i + 12
j = j + 1
src = srcProgramDataInputWs.Cells(i, 2).Value
Loop

.Protect
End With
End If
End If


If Target.Address = "$N$1" Then '<------ 2 Reference only for debug
MsgBox "End of B1 loop " & ImportRequested
End If


If Target.Address = "$K$1" Then
Dim ResetWorksheet As Boolean
If ImportRequested = True Then
ResetWorksheet = True
Else
If MsgBox("Are you sure you want to CLEAR this Worksheet?", _
vbYesNo) = vbYes Then
ResetWorksheet = True
End If
End If

If ResetWorksheet = True Then
src = ""
ActiveSheet.Unprotect
ActiveSheet.Range("A3:AZ300").Clear
With ActiveSheet
src = srcProgramDataInputWs.Range("B3").Value
i = 3
j = 0
Do Until src = ""
srcProgramSummaryTemplateWs.Rows("3:14").Copy .Cells((j
* 12) + 3, 1)
i = i + 12
j = j + 1
src = srcProgramDataInputWs.Cells(i, 2).Value
Loop
ActiveSheet.Protect
Range("K1").Value = "default"
Range("N3").Select
End With
End If
'ImportRequested = False
ResetWorksheet = False
End If


If Target.Address = "$B$1" Then
Dim SelectedTxtInputFile As Variant
SaveDriveDir = CurDir
MyPath = ThisWorkbook.Path & "/RaceData-XLS-Ready"
ChDrive MyPath
ChDir MyPath

SelectedTxtInputFile = Application.GetOpenFilename( _
"Race Program Input Files (*.txt),*.txt", , _
"Select which RACE Program to import", , False)

If SelectedTxtInputFile = "False" Then
Range("N3").Select
Else
ImportRequested = True '<----- 3

srcProgramDataInputWs.Unprotect
srcProgramDataInputWs.Range("A3:H242").ClearContents

With srcProgramDataInputWs.QueryTables.Add(Connection:= _
"TEXT;" & SelectedTxtInputFile _
, Destination:=srcProgramDataInputWs.Range("A3:H242"))
.Name = "ImportProgramData"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileOtherDelimiter = "|"
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
srcProgramDataInputWs.Protect
End If
'Range("K1").Select
ChDrive SaveDriveDir
ChDir SaveDriveDir
MsgBox "End of B1 loop " & ImportRequested '<------ 4
End If

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
'Range("K1").Value = "Clear"
ws_exit:
Application.EnableEvents = True
End Sub
Click to expand...
 
C

CRayF

Not sure if I completely understand where these are created…

Note that on my original post, I included the entire code. This is on the
“Right Click; View Code†open which I understand is to be the “worksheet
event codeâ€.

I tried moving the “Dim ImportRequested As Boolean '<------- 1†to the very
top of the code thinking this would make it “Globalâ€. Is this not where it
belongs?

How would I “Declare the variable static†or
“Declare the variable outside of a procedure at the top of the codeâ€
(differently than I did in the supplied codeâ€

Hopefully this will shed me some light on variable assignments properties…
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Detect when a Cell value has changed. Working too well :-( 4
How to test Worksheet exists before trying to create it? 7
Tried and true copy code lines fail me here 7
Ctrl-Click to change TRUE and FALSE in a cell 0
Some trouble assigning a string to a variable 22
Permutations andText to column problem 6
Development combinations with exclusion of columns 0
Development combinations with exclusion of columns 0

Top