Changing a "Counter" Value from a Sub

Hi,

I want to know if it is possible to change a "counter" amount, (or call address
number?) from a sub which the main sub calls. For example, suppose I have a
setup something like this:

Sub MySub()
Count = 1
Coot = 2
For i = 1 to 50
Count = Count + 1
Coot = Coot + 1
If Sheet1.Cells(Count,1) < Then Fred ()
....
Next i
End Sub

Fred()
(some code)
End Sub

The data I am looking at is such that if something happens in Fred(), the
counter value for MySub() must be altered. Usually it has to be increased.
For example, if the next Count number in MySub() shuuld be, say, 8 (according
to the For...Next system), a value I discover in Fred may make it necessary
for the counter in MySub() to be, say 9. This allows the program to look at a
cell in a different order that it would automatically do following the
instructions in MySub(). i.e. Cell (9,1) instead of Cell (8,1) Right now I do
not seem to be able to have that happen. I have looked at the counter value in
Fred() and I can make it change to any number I want by adding X to it (Count
= Count + X). However, when I look at "Couint' in MySub() it does not
change to the value I give it in Fred(). (At least I do not think it does and
I do not get the end result I need.) . I have set the variable "Count" to
Public but even if I change it it Fred(), it MySub() does not seem change
"Count." The program I am writing works OK except for the counter problem.
Any suggestions as to how I can do what I need to do?

Thanks

Hi Jimfor,

By way of example:

Sub MySub()
Dim iCtr As Long

iCtr = 0
For iCtr = 1 To 1000
MsgBox "iCtr Pre-Fred = " & vbTab & iCtr
Fred iCtr
MsgBox "iCtr Post-Fred = " & vbTab & iCtr
iCtr = iCtr + 1
Next iCtr
End Sub

Sub Fred(ByRef mycount As Long)
If Range("A1") > 10 Then
mycount = 10 * mycount
Else
mycount = mycount * 5
End If
End Sub

See ByRef in VBA help.

---
Regards,
Norman



"JimFor" <[email protected]> wrote in message
news:[email protected]...
> Hi,
>
> I want to know if it is possible to change a "counter" amount, (or call
> address
> number?) from a sub which the main sub calls. For example, suppose I
> have a
> setup something like this:
>
> Sub MySub()
> Count = 1
> Coot = 2
> For i = 1 to 50
> Count = Count + 1
> Coot = Coot + 1
> If Sheet1.Cells(Count,1) < Then Fred ()
> ...
> Next i
> End Sub
>
> Fred()
> (some code)
> End Sub
>
> The data I am looking at is such that if something happens in Fred(), the
> counter value for MySub() must be altered. Usually it has to be
> increased.
> For example, if the next Count number in MySub() shuuld be, say, 8
> (according
> to the For...Next system), a value I discover in Fred may make it
> necessary
> for the counter in MySub() to be, say 9. This allows the program to look
> at a
> cell in a different order that it would automatically do following the
> instructions in MySub(). i.e. Cell (9,1) instead of Cell (8,1) Right now
> I do
> not seem to be able to have that happen. I have looked at the counter
> value in
> Fred() and I can make it change to any number I want by adding X to it
> (Count
> = Count + X). However, when I look at "Couint' in MySub() it does not
> change to the value I give it in Fred(). (At least I do not think it does
> and
> I do not get the end result I need.) . I have set the variable "Count"
> to
> Public but even if I change it it Fred(), it MySub() does not seem change
> "Count." The program I am writing works OK except for the counter
> problem.
> Any suggestions as to how I can do what I need to do?
>
> Thanks

An alternative is to use a function, e.g.

Sub MySub()
Count = 1
Coot = 2
For i = 1 to 50
Count = Count + 1
Coot = Coot + 1
If Sheet1.Cells(Count,1) < Then Count = Fred(Count)
....
Next i
End Sub

Function Fred(cntr)
'some code
Fred = cntr+1
End Function

or even use a Public variable (not my personal preference)

Public Count

Sub MySub()
Count = 1
Coot = 2
For i = 1 to 50
Count = Count + 1
Coot = Coot + 1
If Sheet1.Cells(Count,1) < Then Fred ()
....
Next i
End Sub

Fred()
Count=Count+1
End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Norman Jones" <[email protected]> wrote in message
news:%[email protected]...
> Hi Jimfor,
>
> By way of example:
>
> Sub MySub()
> Dim iCtr As Long
>
> iCtr = 0
> For iCtr = 1 To 1000
> MsgBox "iCtr Pre-Fred = " & vbTab & iCtr
> Fred iCtr
> MsgBox "iCtr Post-Fred = " & vbTab & iCtr
> iCtr = iCtr + 1
> Next iCtr
> End Sub
>
> Sub Fred(ByRef mycount As Long)
> If Range("A1") > 10 Then
> mycount = 10 * mycount
> Else
> mycount = mycount * 5
> End If
> End Sub
>
> See ByRef in VBA help.
>
> ---
> Regards,
> Norman
>
>
>
> "JimFor" <[email protected]> wrote in message
> news:[email protected]...
> > Hi,
> >
> > I want to know if it is possible to change a "counter" amount, (or call
> > address
> > number?) from a sub which the main sub calls. For example, suppose I
> > have a
> > setup something like this:
> >
> > Sub MySub()
> > Count = 1
> > Coot = 2
> > For i = 1 to 50
> > Count = Count + 1
> > Coot = Coot + 1
> > If Sheet1.Cells(Count,1) < Then Fred ()
> > ...
> > Next i
> > End Sub
> >
> > Fred()
> > (some code)
> > End Sub
> >
> > The data I am looking at is such that if something happens in Fred(),
the
> > counter value for MySub() must be altered. Usually it has to be
> > increased.
> > For example, if the next Count number in MySub() shuuld be, say, 8
> > (according
> > to the For...Next system), a value I discover in Fred may make it
> > necessary
> > for the counter in MySub() to be, say 9. This allows the program to
look
> > at a
> > cell in a different order that it would automatically do following the
> > instructions in MySub(). i.e. Cell (9,1) instead of Cell (8,1) Right
now
> > I do
> > not seem to be able to have that happen. I have looked at the counter
> > value in
> > Fred() and I can make it change to any number I want by adding X to it
> > (Count
> > = Count + X). However, when I look at "Couint' in MySub() it does
not
> > change to the value I give it in Fred(). (At least I do not think it
does
> > and
> > I do not get the end result I need.) . I have set the variable "Count"
> > to
> > Public but even if I change it it Fred(), it MySub() does not seem
change
> > "Count." The program I am writing works OK except for the counter
> > problem.
> > Any suggestions as to how I can do what I need to do?
> >
> > Thanks
>
>

Thanks. I'll try these. If I make a variable a Public variable and it appears
in Fred, would MySub automatically use the value I assign it in Fred no matter
what it would have been in MySub without what happens in Fred? Not sure this
is happening with. me.

If you create a public variable, and don't (very important) define the same
name in the subs, there is only one vraiable, and wherever that is changed
it will be picked up in the other module. For instance, try this

Public Count

Sub MySub()
Count = 1
msgbox "MySub: " & Count
Fred
msgbox "MySub: " & Count
End Sub

Sub Fred()
Count=Count+1
msgbox "Fred: " & Count
End Sub

You should see
MySub: 1
Fred: 2
MySub: 2
--

HTH

RP
(remove nothere from the email address if mailing direct)


"JimFor" <[email protected]> wrote in message
news:[email protected]...
> Thanks. I'll try these. If I make a variable a Public variable and it
appears
> in Fred, would MySub automatically use the value I assign it in Fred no
matter
> what it would have been in MySub without what happens in Fred? Not sure
this
> is happening with. me.