I am a raw beginner at VBA and programming in general.
It need some help understanding where to place variables and how to
declare
them.

Please help me with this.
I have this code that changes a cell's text color, when I click a
transparent
label that is placed over the cell. There is a timer sub that delays
the code
and then allows another sub call *The_Sub()* to change the cell color
back.

There are 30 cells like this and enventually the code will be swapping
ranges

The code runs fine except for having *The_Sub()* recognize a variable
that is created in the main sub that is called *ManualSwap*
__________________________________________________ _______________
Public RunWhen As Double
Public FirstButtonPress As Integer
Public Const cRunIntervalSeconds = 4 '4 seconds
Dim FirstTeamNameCell As Range
Public Const cRunWhat = "The_Sub"
__________________________________________________ _______________

Public Sub ManualSwap(x As Integer)
Dim CrosstableCorner As Range
Set CrosstableCorner = Range("Crosstable_Corner")
If FirstButtonPress = 0 Then
FirstButtonPress = x
'Dim CrosstableCorner As Range
'Set CrosstableCorner = Range("Crosstable_Corner")
x = x - 1

Dim FirstTeamNameCell As Range
Set FirstTeamNameCell = Range(CrosstableCorner.Offset(x * 1 + 1, 0),
CrosstableCorner.Offset(x * 1 + 4, 0))
Dim FirstTeamRange As Range
Set FirstTeamRange = Range(CrosstableCorner.Offset(x * 1 + 1, 0),
CrosstableCorner.Offset(x * 1 + 4, 60))
*FirstTeamNameCell.Font.Color = RGB(255, 0, 0)*



'give the user 4 seconds to choose the second range in the swap
StartTime


Else

x = x - 1
Dim SecondTeamNameCell As Range
Set SecondTeamNameCell = Range(CrosstableCorner.Offset(x * 1 + 1, 0),
CrosstableCorner.Offset(x * 1 + 4, 0))
Dim SecondTeamRange As Range
Set SecondTeamRange = Range(CrosstableCorner.Offset(x * 1 + 1, 0),
CrosstableCorner.Offset(x * 1 + 4, 60))
'There are now 2 variables for the 2 ranges
'run some code here to swap the ranges
'set the to FirstButtonPress = 0 ready for the next swap
FirstButtonPress = 0


StopTimer
SecondTeamNameCell.Font.Color = RGB(255, 0, 0)
End If
'run some code with a short delay here to change both cells fonts back
to original color

End Sub
__________________________________________________ ______________
Sub The_Sub()
'Dim FirstButtonPress As Integer


FIRSTTEAMNAMECELL.FONT.COLOR = RGB(255, 204, 0)

_'Range(\"D6:D7\").Font.Color_=_RGB(255,_204,_0).. ._works in changing
the font color back, so the timer executes as intended but when I try
this using the variable _FirstTeamNameCell_ from the _ManualSwap_ I
can't get this sub to recognize it


End Sub
__________________________________________________ _______________
Sub StartTimer()
RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
Application.OnTime earliesttime:=RunWhen, procedu=cRunWhat, _
schedule:=True
End Sub
__________________________________________________ _______________
Sub StopTimer()
On Error Resume Next
Application.OnTime earliesttime:=RunWhen, _
procedu=cRunWhat, schedule:=False
End Sub
__________________________________________________ _______________


*I appreciate any help in understanding how to correctly use
variables so they will be recognized in other subs.*


--
SuitedAces
------------------------------------------------------------------------
SuitedAces's Profile: http://www.excelforum.com/member.php...o&userid=35840
View this thread: http://www.excelforum.com/showthread...hreadid=557356

Make it Public, not Dim perhaps.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"SuitedAces" wrote
in message ...

I am a raw beginner at VBA and programming in general.
It need some help understanding where to place variables and how to
declare
them.

Please help me with this.
I have this code that changes a cell's text color, when I click a
transparent
label that is placed over the cell. There is a timer sub that delays
the code
and then allows another sub call *The_Sub()* to change the cell color
back.

There are 30 cells like this and enventually the code will be swapping
ranges

The code runs fine except for having *The_Sub()* recognize a variable
that is created in the main sub that is called *ManualSwap*
__________________________________________________ _______________
Public RunWhen As Double
Public FirstButtonPress As Integer
Public Const cRunIntervalSeconds = 4 '4 seconds
Dim FirstTeamNameCell As Range
Public Const cRunWhat = "The_Sub"
__________________________________________________ _______________

Public Sub ManualSwap(x As Integer)
Dim CrosstableCorner As Range
Set CrosstableCorner = Range("Crosstable_Corner")
If FirstButtonPress = 0 Then
FirstButtonPress = x
'Dim CrosstableCorner As Range
'Set CrosstableCorner = Range("Crosstable_Corner")
x = x - 1

Dim FirstTeamNameCell As Range
Set FirstTeamNameCell = Range(CrosstableCorner.Offset(x * 1 + 1, 0),
CrosstableCorner.Offset(x * 1 + 4, 0))
Dim FirstTeamRange As Range
Set FirstTeamRange = Range(CrosstableCorner.Offset(x * 1 + 1, 0),
CrosstableCorner.Offset(x * 1 + 4, 60))
*FirstTeamNameCell.Font.Color = RGB(255, 0, 0)*



'give the user 4 seconds to choose the second range in the swap
StartTime


Else

x = x - 1
Dim SecondTeamNameCell As Range
Set SecondTeamNameCell = Range(CrosstableCorner.Offset(x * 1 + 1, 0),
CrosstableCorner.Offset(x * 1 + 4, 0))
Dim SecondTeamRange As Range
Set SecondTeamRange = Range(CrosstableCorner.Offset(x * 1 + 1, 0),
CrosstableCorner.Offset(x * 1 + 4, 60))
'There are now 2 variables for the 2 ranges
'run some code here to swap the ranges
'set the to FirstButtonPress = 0 ready for the next swap
FirstButtonPress = 0


StopTimer
SecondTeamNameCell.Font.Color = RGB(255, 0, 0)
End If
'run some code with a short delay here to change both cells fonts back
to original color

End Sub
__________________________________________________ ______________
Sub The_Sub()
'Dim FirstButtonPress As Integer


FIRSTTEAMNAMECELL.FONT.COLOR = RGB(255, 204, 0)

_'Range(\"D6:D7\").Font.Color_=_RGB(255,_204,_0).. ._works in changing
the font color back, so the timer executes as intended but when I try
this using the variable _FirstTeamNameCell_ from the _ManualSwap_ I
can't get this sub to recognize it


End Sub
__________________________________________________ _______________
Sub StartTimer()
RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
Application.OnTime earliesttime:=RunWhen, procedu=cRunWhat, _
schedule:=True
End Sub
__________________________________________________ _______________
Sub StopTimer()
On Error Resume Next
Application.OnTime earliesttime:=RunWhen, _
procedu=cRunWhat, schedule:=False
End Sub
__________________________________________________ _______________


*I appreciate any help in understanding how to correctly use
variables so they will be recognized in other subs.*


--
SuitedAces
------------------------------------------------------------------------
SuitedAces's Profile:
http://www.excelforum.com/member.php...o&userid=35840
View this thread: http://www.excelforum.com/showthread...hreadid=557356

The proper use of variables is key to good programming. My general rule of
thumb is to keep my variables as private as possible and keep their scope to
a minimum.

The big key to this is to keep your global variables (declared outside of
porcedures at the top of the module) to a minimum. The issue with these
variables is that everything has access to them so everything could change
them. This becomes a problem when you try to debug something and it turns out
the the value of the global variable is not what you think it should be. In
this case you often will not know which procedure modified it last. Now where
do you start to try to fix it? You are much better off to pass variables from
one procedure to the next. This takes some practice to do but once you get
the hang of it, it is not too bad.

When passing varaibles there are 2 ways. ByVal and ByRef. Unless specified
otherwise you will be passing ByRef. Unless you have a good reason to do so
you should be passing ByVal. When you pass byval you are not passing the
actual variable but rather a copy of the variable. You can do anything to it
you want without changing the variable that you passed into the procedure.
Give this a try to see what I mean...

public sub test
dim this as integer
dim that as integer

this = 1
that = 2
msgbox this & " - " & that
call test2(this, that)
msgbox this & " - " & that
end sub

sub Test2(byval this as integer, byref that as integer)
this = this + 10
that = that + 20
msgbox this & " - " & that
end sub

Where should you declare variables. IMO you should declare all of your
variables at the top of your procedure so that they are all in one place. You
can decarle them wherever you want but I find it more difficult if they are
declared all over the place. to keep things simple I also initialize all (as
many as I reasonably can) of my variables at the top of the module. Doing
this means that when I am debugging my code I know what variables I have and
thier starting values right from the beginning.

These are just general rules and with all rules they are made to be broken.
That being said if you don't have a good reason to break them then don't.
--
HTH...

Jim Thomlinson


"SuitedAces" wrote:


I am a raw beginner at VBA and programming in general.
It need some help understanding where to place variables and how to
declare
them.

Please help me with this.
I have this code that changes a cell's text color, when I click a
transparent
label that is placed over the cell. There is a timer sub that delays
the code
and then allows another sub call *The_Sub()* to change the cell color
back.

There are 30 cells like this and enventually the code will be swapping
ranges

The code runs fine except for having *The_Sub()* recognize a variable
that is created in the main sub that is called *ManualSwap*
__________________________________________________ _______________
Public RunWhen As Double
Public FirstButtonPress As Integer
Public Const cRunIntervalSeconds = 4 '4 seconds
Dim FirstTeamNameCell As Range
Public Const cRunWhat = "The_Sub"
__________________________________________________ _______________

Public Sub ManualSwap(x As Integer)
Dim CrosstableCorner As Range
Set CrosstableCorner = Range("Crosstable_Corner")
If FirstButtonPress = 0 Then
FirstButtonPress = x
'Dim CrosstableCorner As Range
'Set CrosstableCorner = Range("Crosstable_Corner")
x = x - 1

Dim FirstTeamNameCell As Range
Set FirstTeamNameCell = Range(CrosstableCorner.Offset(x * 1 + 1, 0),
CrosstableCorner.Offset(x * 1 + 4, 0))
Dim FirstTeamRange As Range
Set FirstTeamRange = Range(CrosstableCorner.Offset(x * 1 + 1, 0),
CrosstableCorner.Offset(x * 1 + 4, 60))
*FirstTeamNameCell.Font.Color = RGB(255, 0, 0)*



'give the user 4 seconds to choose the second range in the swap
StartTime


Else

x = x - 1
Dim SecondTeamNameCell As Range
Set SecondTeamNameCell = Range(CrosstableCorner.Offset(x * 1 + 1, 0),
CrosstableCorner.Offset(x * 1 + 4, 0))
Dim SecondTeamRange As Range
Set SecondTeamRange = Range(CrosstableCorner.Offset(x * 1 + 1, 0),
CrosstableCorner.Offset(x * 1 + 4, 60))
'There are now 2 variables for the 2 ranges
'run some code here to swap the ranges
'set the to FirstButtonPress = 0 ready for the next swap
FirstButtonPress = 0


StopTimer
SecondTeamNameCell.Font.Color = RGB(255, 0, 0)
End If
'run some code with a short delay here to change both cells fonts back
to original color

End Sub
__________________________________________________ ______________
Sub The_Sub()
'Dim FirstButtonPress As Integer


FIRSTTEAMNAMECELL.FONT.COLOR = RGB(255, 204, 0)

_'Range(\"D6:D7\").Font.Color_=_RGB(255,_204,_0).. ._works in changing
the font color back, so the timer executes as intended but when I try
this using the variable _FirstTeamNameCell_ from the _ManualSwap_ I
can't get this sub to recognize it


End Sub
__________________________________________________ _______________
Sub StartTimer()
RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
Application.OnTime earliesttime:=RunWhen, procedu=cRunWhat, _
schedule:=True
End Sub
__________________________________________________ _______________
Sub StopTimer()
On Error Resume Next
Application.OnTime earliesttime:=RunWhen, _
procedu=cRunWhat, schedule:=False
End Sub
__________________________________________________ _______________


*I appreciate any help in understanding how to correctly use
variables so they will be recognized in other subs.*


--
SuitedAces
------------------------------------------------------------------------
SuitedAces's Profile: http://www.excelforum.com/member.php...o&userid=35840
View this thread: http://www.excelforum.com/showthread...hreadid=557356

Thank You both for your help.

But this raises some further questions.

In my main procedure I call StartTimer() which in turn calls The_Sub()
in a strange way that I do not fully understand.
I took this code associated with OnTime from a website by an author of
a book on VBA, so my guess is that it is used in the correct way .


From Public Sub ManualSwap(x As Integer) I call Sub StartTimer()

Sub StartTimer()
RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
Application.OnTime earliesttime:=RunWhen, procedu=cRunWhat, _
schedule:=True
End Sub

Then StartTimer() calls The_Sub()
through this statement *Public Const cRunWhat = "The_Sub"*

But how do I the pass the variables through ?

For reasons which I do not fully understand I cannot do this.......
APPLICATION.ONTIME NOW + TIMEVALUE(\"00:00:3\"), \"THE_SUB\"
which would allow me to pass the variable as you described in a
straight forward
way.

This is what EXCEL HELP says....
OnTime(EarliestTime, Procedure, LatestTime, Schedule)
Procedure Required String. The name of the procedure to be run.

The site I grabbed the code that I am using indicated that the Sub has
to be called this way because OnTime requires a string.

I cannot say that I understand the distinction here.


--
SuitedAces
------------------------------------------------------------------------
SuitedAces's Profile: http://www.excelforum.com/member.php...o&userid=35840
View this thread: http://www.excelforum.com/showthread...hreadid=557356

Bob I tried making it public and I get the same error.

Baffling to me *PUBLIC* seems like it should be *PUBLIC* but I gues
not.

The help says the variable might not be set but it is set and is
demonstated to be set in the ManualSwap procedure because
the cell text changes.

No attempt I have made to pass the variable will work either.

Very aggravating

--
SuitedAce
-----------------------------------------------------------------------
SuitedAces's Profile: http://www.excelforum.com/member.php...fo&userid=3584
View this thread: http://www.excelforum.com/showthread.php?threadid=55735

Bob

I neglected to remove this line

DIM FIRSTTEAMNAMECELL AS RANGE

From the SwapButtons sub, after declaring the range Public at the top
of the module.

The code run correctly now .

Thank You


--
SuitedAces
------------------------------------------------------------------------
SuitedAces's Profile: http://www.excelforum.com/member.php...o&userid=35840
View this thread: http://www.excelforum.com/showthread...hreadid=557356