Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing a "Counter" Value from a Sub
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing a "Counter" Value from a Sub
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" wrote in message ... 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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing a "Counter" Value from a Sub
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing a "Counter" Value from a Sub
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" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I stop "global" hyperlinks changing to "local" links? | Excel Worksheet Functions | |||
How do I set up a "roll over" counter in excel 2003? | Excel Worksheet Functions | |||
multiple file uploading - runtime error'13': type mismatch "While Counter <= UBound(FName)" | Excel Discussion (Misc queries) | |||
Excel: Changing "numeric $" to "text $" in a different cell. | Excel Worksheet Functions | |||
Changing "returned" values from "0" to "blank" | Excel Worksheet Functions |