Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing range to subprocedure - maybe?
I'm using a macro that calls a subprocedure (the subprocedure colors
the passed range yellow). When I do a test, the subprocedure DOES work fine. But in my full macro, the subprocedure DOESN'T work. The full macro DOES work if I use the variable.interior.colorindex=6 command, which is essentially all the subprocedure does anyway. (Eventually, the color subprocedure will get more complicated, so I do need to do it through its own procedure.) Somehow, passing the ranges to the subprocedure doesn't work, even though the subprocedure works on its own. So I'm stumped. Here's the relevant code: Thanks in advance, Mike The subprocedu Sub color(RangeToColor As Range) RangeToColor.Interior.ColorIndex = 6 End Sub The test: Sub testcolor() Dim testrng As Range Set testrng = Range("c1:c10") color testrng End Sub The problem: Sub NewMacro() Application.ScreenUpdating = False Application.Calculation = xlCalculationManual ' Dim str As String Dim rng As Range Dim nrng As Range Dim currentrow As Range ' Dim cono, jobno, famlvl, famcode As Range ' actually many more variables here ' MaxRow = Cells.SpecialCells(xlLastCell).Row MaxCol = Cells.SpecialCells(xlLastCell).Column ' 'Delete old range names (to avoid duplication) For Each nm In ThisWorkbook.Names nm.Delete Next 'now rename each column as a named range with the name being the title _ in the first row concatenated with "range" (i.e. conorange) j = 1 Do While j <= MaxCol Set rng = Cells(1, j) Set nrng = rng.Resize(MaxRow, 1) str = Cells(1, j).Value & "range" Names.Add str, nrng j = j + 1 Loop ' i = 2 Do While i <= MaxRow Set currentrow = Cells(i, 1).EntireRow 'define each cell to be checked as the intersect of the variable column and the current row Set cono = Intersect(currentrow, Range("conorange")) Set jobno = Intersect(currentrow, Range("jobnorange")) Set famlvl = Intersect(currentrow, Range("famlvlrange")) Set famcode = Intersect(currentrow, Range("famcoderange")) ' 'many more ' 'now specific checks 'works this way If (IsEmpty(cono) Or Not (Application.IsNumber(cono.Value))) Then _ cono.Interior.ColorIndex = 6 If (IsEmpty(jobno) Or Not (Application.IsNumber(jobno.Value))) Then _ jobno.Interior.ColorIndex = 6 If (IsEmpty(famlvl) Or Not (Application.IsNumber(famlvl.Value))) Then _ famlvl.Interior.ColorIndex = 6 If (IsEmpty(famcode) Or Not (Application.IsNumber(famcode.Value))) Then _ famcode.Interior.ColorIndex = 6 'doesn't work this way If (IsEmpty(cono) Or Not (Application.IsNumber(cono.Value))) Then _ color cono If (IsEmpty(jobno) Or Not (Application.IsNumber(jobno.Value))) Then _ color jobno If (IsEmpty(famlvl) Or Not (Application.IsNumber(famlvl.Value))) Then _ color famlvl If (IsEmpty(famcode) Or Not (Application.IsNumber(famcode.Value))) Then _ color famcode ' 'etc ' i = i + 1 Loop Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic 'XL97 & later End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing range to subprocedure - maybe?
Mike,
Well, perhaps predictably, the tester works for me. I don't really want to try to implement the longer example. Have you stepped through the code from NewMacro into color and back? What is the value of RangeToColor in color? Can you address RangeToColor while the code is stopped in color (e.g., in Immediate window, issue 'Application.Goto RangeToColor' or RangeToColor = "X")? Note that by using 'Dim cono, jobno, famlvl, famcode As Range' only famcode will be a true Range data type. The others will be Variants. Try declaring the "range" variables explicitly as Ranges. -- Bob Kilmer "Mike Gerbracht" wrote in message m... I'm using a macro that calls a subprocedure (the subprocedure colors the passed range yellow). When I do a test, the subprocedure DOES work fine. But in my full macro, the subprocedure DOESN'T work. The full macro DOES work if I use the variable.interior.colorindex=6 command, which is essentially all the subprocedure does anyway. (Eventually, the color subprocedure will get more complicated, so I do need to do it through its own procedure.) Somehow, passing the ranges to the subprocedure doesn't work, even though the subprocedure works on its own. So I'm stumped. Here's the relevant code: Thanks in advance, Mike The subprocedu Sub color(RangeToColor As Range) RangeToColor.Interior.ColorIndex = 6 End Sub The test: Sub testcolor() Dim testrng As Range Set testrng = Range("c1:c10") color testrng End Sub The problem: Sub NewMacro() Application.ScreenUpdating = False Application.Calculation = xlCalculationManual ' Dim str As String Dim rng As Range Dim nrng As Range Dim currentrow As Range ' Dim cono, jobno, famlvl, famcode As Range ' actually many more variables here ' MaxRow = Cells.SpecialCells(xlLastCell).Row MaxCol = Cells.SpecialCells(xlLastCell).Column ' 'Delete old range names (to avoid duplication) For Each nm In ThisWorkbook.Names nm.Delete Next 'now rename each column as a named range with the name being the title _ in the first row concatenated with "range" (i.e. conorange) j = 1 Do While j <= MaxCol Set rng = Cells(1, j) Set nrng = rng.Resize(MaxRow, 1) str = Cells(1, j).Value & "range" Names.Add str, nrng j = j + 1 Loop ' i = 2 Do While i <= MaxRow Set currentrow = Cells(i, 1).EntireRow 'define each cell to be checked as the intersect of the variable column and the current row Set cono = Intersect(currentrow, Range("conorange")) Set jobno = Intersect(currentrow, Range("jobnorange")) Set famlvl = Intersect(currentrow, Range("famlvlrange")) Set famcode = Intersect(currentrow, Range("famcoderange")) ' 'many more ' 'now specific checks 'works this way If (IsEmpty(cono) Or Not (Application.IsNumber(cono.Value))) Then _ cono.Interior.ColorIndex = 6 If (IsEmpty(jobno) Or Not (Application.IsNumber(jobno.Value))) Then _ jobno.Interior.ColorIndex = 6 If (IsEmpty(famlvl) Or Not (Application.IsNumber(famlvl.Value))) Then _ famlvl.Interior.ColorIndex = 6 If (IsEmpty(famcode) Or Not (Application.IsNumber(famcode.Value))) Then _ famcode.Interior.ColorIndex = 6 'doesn't work this way If (IsEmpty(cono) Or Not (Application.IsNumber(cono.Value))) Then _ color cono If (IsEmpty(jobno) Or Not (Application.IsNumber(jobno.Value))) Then _ color jobno If (IsEmpty(famlvl) Or Not (Application.IsNumber(famlvl.Value))) Then _ color famlvl If (IsEmpty(famcode) Or Not (Application.IsNumber(famcode.Value))) Then _ color famcode ' 'etc ' i = i + 1 Loop Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic 'XL97 & later End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing range to subprocedure - maybe?
Another observation. You appear to be implicitly using the ActiveSheet, in
as much as I do not see that you specify a particular worksheet for your ranges. As a rule, I tend to specify the workbook/worksheet if I think there may be a hint of ambiguity. This may not be contributing to your problem in this case, but it is worth a mention. -- Bob Kilmer "Mike Gerbracht" wrote in message m... I'm using a macro that calls a subprocedure (the subprocedure colors the passed range yellow). When I do a test, the subprocedure DOES work fine. But in my full macro, the subprocedure DOESN'T work. The full macro DOES work if I use the variable.interior.colorindex=6 command, which is essentially all the subprocedure does anyway. (Eventually, the color subprocedure will get more complicated, so I do need to do it through its own procedure.) Somehow, passing the ranges to the subprocedure doesn't work, even though the subprocedure works on its own. So I'm stumped. Here's the relevant code: Thanks in advance, Mike The subprocedu Sub color(RangeToColor As Range) RangeToColor.Interior.ColorIndex = 6 End Sub The test: Sub testcolor() Dim testrng As Range Set testrng = Range("c1:c10") color testrng End Sub The problem: Sub NewMacro() Application.ScreenUpdating = False Application.Calculation = xlCalculationManual ' Dim str As String Dim rng As Range Dim nrng As Range Dim currentrow As Range ' Dim cono, jobno, famlvl, famcode As Range ' actually many more variables here ' MaxRow = Cells.SpecialCells(xlLastCell).Row MaxCol = Cells.SpecialCells(xlLastCell).Column ' 'Delete old range names (to avoid duplication) For Each nm In ThisWorkbook.Names nm.Delete Next 'now rename each column as a named range with the name being the title _ in the first row concatenated with "range" (i.e. conorange) j = 1 Do While j <= MaxCol Set rng = Cells(1, j) Set nrng = rng.Resize(MaxRow, 1) str = Cells(1, j).Value & "range" Names.Add str, nrng j = j + 1 Loop ' i = 2 Do While i <= MaxRow Set currentrow = Cells(i, 1).EntireRow 'define each cell to be checked as the intersect of the variable column and the current row Set cono = Intersect(currentrow, Range("conorange")) Set jobno = Intersect(currentrow, Range("jobnorange")) Set famlvl = Intersect(currentrow, Range("famlvlrange")) Set famcode = Intersect(currentrow, Range("famcoderange")) ' 'many more ' 'now specific checks 'works this way If (IsEmpty(cono) Or Not (Application.IsNumber(cono.Value))) Then _ cono.Interior.ColorIndex = 6 If (IsEmpty(jobno) Or Not (Application.IsNumber(jobno.Value))) Then _ jobno.Interior.ColorIndex = 6 If (IsEmpty(famlvl) Or Not (Application.IsNumber(famlvl.Value))) Then _ famlvl.Interior.ColorIndex = 6 If (IsEmpty(famcode) Or Not (Application.IsNumber(famcode.Value))) Then _ famcode.Interior.ColorIndex = 6 'doesn't work this way If (IsEmpty(cono) Or Not (Application.IsNumber(cono.Value))) Then _ color cono If (IsEmpty(jobno) Or Not (Application.IsNumber(jobno.Value))) Then _ color jobno If (IsEmpty(famlvl) Or Not (Application.IsNumber(famlvl.Value))) Then _ color famlvl If (IsEmpty(famcode) Or Not (Application.IsNumber(famcode.Value))) Then _ color famcode ' 'etc ' i = i + 1 Loop Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic 'XL97 & later End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Passing a range name as an argument to the Index Function | Excel Discussion (Misc queries) | |||
multiplying rows and passing formula throughout the col range | Excel Discussion (Misc queries) | |||
Passing a range to a user defined function | Excel Discussion (Misc queries) | |||
Passing Excel NAMED Range to VBA | Excel Worksheet Functions | |||
Passing a variable to a range statement | Excel Programming |