Object Variable with Block Variable Not set

[SuitedAces]

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, procedure:=cRunWhat, _
schedule:=True
End Sub
_________________________________________________________________
Sub StopTimer()
On Error Resume Next
Application.OnTime earliesttime:=RunWhen, _
procedure:=cRunWhat, schedule:=False
End Sub
_________________________________________________________________


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

[Bob Phillips]

Make it Public, not Dim perhaps.

--

HTH

Bob Phillips

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

"SuitedAces" <SuitedAces.2a80iq_1151680206.3283@excelforum-nospam.com> wrote
in message news:SuitedAces.2a80iq_1151680206.3283@excelforum-nospam.com...
>
> 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, procedure:=cRunWhat, _
> schedule:=True
> End Sub
> _________________________________________________________________
> Sub StopTimer()
> On Error Resume Next
> Application.OnTime earliesttime:=RunWhen, _
> procedure:=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
>

[Jim Thomlinson]

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, procedure:=cRunWhat, _
> schedule:=True
> End Sub
> _________________________________________________________________
> Sub StopTimer()
> On Error Resume Next
> Application.OnTime earliesttime:=RunWhen, _
> procedure:=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
>
>

[SuitedAces]

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, procedure:=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]

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

Baffling to me PUBLIC seems like it should be PUBLIC but I guess 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.

[SuitedAces]

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