Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Declaring Variables
Can anybody confirm how to declare variables so that they
are accessible outside the procedure in which they had been declared. If I use Dim, it only appears to work within its own procedure. I also tried Static and it doe'nt appear to work. A simplified version of the procedures are belo. The main procedure is Transfer_Prices which calls soln_check. Variables like soln_count,rnum,cnum declared in Transfer_Prices do not seem to be recognised in Soln_check. Do the variables also have to be included in the call ie procedure e.g. Soln_check(soln, soln_count) ? Sub Transfer_Prices() ' Static soln_rnum As Integer Static soln_cnum As Integer Static soln_count As Integer Static rnum As Integer Static cnum As Integer Static soln As String rnum = 11 cnum = 14 soln_rnum = 11 soln_cnum = 17 soln_count = 0 soln_rnum = soln_rnum + 1 If Cells(soln_rnum, soln_cnum + 2).Value < 0 Then Soln_check (soln) Cells(soln_rnum, soln_cnum - 1).Value = soln_count End If Cells(soln_rnum, soln_cnum).Select End Sub Sub Soln_check(soln) Do While rnum < 30 rnum = rnum + 1 If ActiveCell.FormulaR1C1 = soln Then soln_count = soln_count + 1 ElseIf ActiveCell.FormulaR1C1 < soln Then Cells(rnum, cnum).Select End If Loop Range("O39").Select End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Declaring Variables
try using Public
they would need to be declared in the General bit, outside your sub. eg public soln_rnum As Integer sub transfer_prices soln_rnum = 11 'etc end sub -----Original Message----- Can anybody confirm how to declare variables so that they are accessible outside the procedure in which they had been declared. If I use Dim, it only appears to work within its own procedure. I also tried Static and it doe'nt appear to work. A simplified version of the procedures are belo. The main procedure is Transfer_Prices which calls soln_check. Variables like soln_count,rnum,cnum declared in Transfer_Prices do not seem to be recognised in Soln_check. Do the variables also have to be included in the call ie procedure e.g. Soln_check(soln, soln_count) ? Sub Transfer_Prices() ' Static soln_rnum As Integer Static soln_cnum As Integer Static soln_count As Integer Static rnum As Integer Static cnum As Integer Static soln As String rnum = 11 cnum = 14 soln_rnum = 11 soln_cnum = 17 soln_count = 0 soln_rnum = soln_rnum + 1 If Cells(soln_rnum, soln_cnum + 2).Value < 0 Then Soln_check (soln) Cells(soln_rnum, soln_cnum - 1).Value = soln_count End If Cells(soln_rnum, soln_cnum).Select End Sub Sub Soln_check(soln) Do While rnum < 30 rnum = rnum + 1 If ActiveCell.FormulaR1C1 = soln Then soln_count = soln_count + 1 ElseIf ActiveCell.FormulaR1C1 < soln Then Cells(rnum, cnum).Select End If Loop Range("O39").Select End Sub . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Declaring Variables
Hi Robert
Short answer: On top of the module, before any Sub. Use "Public" instead of "Dim" and they are available outside the module as well. Do not use "static", that's for other things. Longer answer, you could, and I personally think should, pass as many variables as possible to where they are needed instead. Note that they should have the same datatype passed as dimmed : Sub Soln_check(ByRef soln As String, ByRef soln_count As Integer, ByRef rnum As Integer) -but it's a question of readability and style (and I broke this my own rule badly less than 40 minutes ago doing serious work :-) -- HTH. Best wishes Harald Followup to newsgroup only please. "Robert" wrote in message ... Can anybody confirm how to declare variables so that they are accessible outside the procedure in which they had been declared. If I use Dim, it only appears to work within its own procedure. I also tried Static and it doe'nt appear to work. A simplified version of the procedures are belo. The main procedure is Transfer_Prices which calls soln_check. Variables like soln_count,rnum,cnum declared in Transfer_Prices do not seem to be recognised in Soln_check. Do the variables also have to be included in the call ie procedure e.g. Soln_check(soln, soln_count) ? Sub Transfer_Prices() ' Static soln_rnum As Integer Static soln_cnum As Integer Static soln_count As Integer Static rnum As Integer Static cnum As Integer Static soln As String rnum = 11 cnum = 14 soln_rnum = 11 soln_cnum = 17 soln_count = 0 soln_rnum = soln_rnum + 1 If Cells(soln_rnum, soln_cnum + 2).Value < 0 Then Soln_check (soln) Cells(soln_rnum, soln_cnum - 1).Value = soln_count End If Cells(soln_rnum, soln_cnum).Select End Sub Sub Soln_check(soln) Do While rnum < 30 rnum = rnum + 1 If ActiveCell.FormulaR1C1 = soln Then soln_count = soln_count + 1 ElseIf ActiveCell.FormulaR1C1 < soln Then Cells(rnum, cnum).Select End If Loop Range("O39").Select End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Declaring variables | Excel Discussion (Misc queries) | |||
Declaring variables | Excel Discussion (Misc queries) | |||
DEclaring variables | Excel Programming | |||
Declaring variables | Excel Programming | |||
Declaring Variables | Excel Programming |