Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
syntax question
just wondering if these are functionally the same:
i = Range(ws3.Cells(2, x), ws3.Cells(lRow, x)) i = ws3.Range(Cells(2, x), Cells(lRow, x)) -- Gary |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
syntax question
Gary, If the ActiveSheet is ws3 both will work. If the ActiveSheet is some other sheet both will blow up. I guess in that regard they both function the same. <g The preferred method is to prefix Range and Cells with the worksheet... ws3.Range(ws3.Cells(2, x), ws3.Cells(lRow, x)) -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "Gary Keramidas" <GKeramidasATmsn.com wrote in message just wondering if these are functionally the same: i = Range(ws3.Cells(2, x), ws3.Cells(lRow, x)) i = ws3.Range(Cells(2, x), Cells(lRow, x)) -- Gary |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
syntax question
ok, thanks jim, i thought they would do the same thing, but wasn't sure.
i knew they would only work if the sheet was active. thanks for the reply. -- Gary "Jim Cone" wrote in message ... Gary, If the ActiveSheet is ws3 both will work. If the ActiveSheet is some other sheet both will blow up. I guess in that regard they both function the same. <g The preferred method is to prefix Range and Cells with the worksheet... ws3.Range(ws3.Cells(2, x), ws3.Cells(lRow, x)) -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "Gary Keramidas" <GKeramidasATmsn.com wrote in message just wondering if these are functionally the same: i = Range(ws3.Cells(2, x), ws3.Cells(lRow, x)) i = ws3.Range(Cells(2, x), Cells(lRow, x)) -- Gary |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
syntax question
jim:
why does this work Application.Sum(Range(ws4.Cells(2, x), ws4.Cells(lRow, x))) and this doesn't Application.Sum(ws4.Range(Cells(2, x),Cells(lRow, x))) -- Gary "Jim Cone" wrote in message ... Gary, If the ActiveSheet is ws3 both will work. If the ActiveSheet is some other sheet both will blow up. I guess in that regard they both function the same. <g The preferred method is to prefix Range and Cells with the worksheet... ws3.Range(ws3.Cells(2, x), ws3.Cells(lRow, x)) -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "Gary Keramidas" <GKeramidasATmsn.com wrote in message just wondering if these are functionally the same: i = Range(ws3.Cells(2, x), ws3.Cells(lRow, x)) i = ws3.Range(Cells(2, x), Cells(lRow, x)) -- Gary |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
syntax question
i guess to be more exact:
this works If Application.Sum(Range(ws4.Cells(2, x), ws4.Cells(lRow, x))) 0 Then this doesn't If Application.Sum(ws4.Range(Cells(2, x), Cells(lRow, x))) 0 Then -- Gary "Jim Cone" wrote in message ... Gary, If the ActiveSheet is ws3 both will work. If the ActiveSheet is some other sheet both will blow up. I guess in that regard they both function the same. <g The preferred method is to prefix Range and Cells with the worksheet... ws3.Range(ws3.Cells(2, x), ws3.Cells(lRow, x)) -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "Gary Keramidas" <GKeramidasATmsn.com wrote in message just wondering if these are functionally the same: i = Range(ws3.Cells(2, x), ws3.Cells(lRow, x)) i = ws3.Range(Cells(2, x), Cells(lRow, x)) -- Gary |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
syntax question
I agree with Jim, Gary. They are functionally the same when ws3 is the
activesheet. And, I guess you could technically consider them fully equivalent under that condition. Here's why: If you check the online help for the range property and the cells property, you'll find the statements (paraphrased here): Range Property: When used without an object qualifier, this property is a shortcut for the phrase ActiveSheet.Range (it returns a range from the active sheet), and Cells Property: Using this property without an object qualifier returns a Range object that represents all the cells on the active worksheet. Essentially the Cells property is a shortcut for the phrase ActiveSheet.Cells. Therefore, when ws3 is the activesheet, substitute "activesheet" for ws3 in both of your statements and convert the shorthand versions of Range and Cells to their longhand version (by prefixing them with 'activesheet.'): i = Range(ws3.Cells(2, x), ws3.Cells(lRow, x)) is equivalent to i = activesheet.range(activesheet.cells(2, x), activesheet.Cells(lRow, x)) And, i = ws3.Range(Cells(2, x), Cells(lRow, x)) is also equivalent to i = activesheet.range(activesheet.cells(2, x), activesheet.cells(lRow, x)) You can see here that your original statements are equivalent in every way when ws3 is the active sheet. Now, when ws3 is not the active sheet, they are no longer equivalent, functionally or otherwise. Here's why: Statement 1 does not produce a runtime error and in fact sets a valid object in variable i while Statement 2 simply fails, which makes them different. The arguments of the range property in Statement 1 are simply explicit enough to unambiguously define a group of cells (a range) on a worksheet other than the activesheet. Statement 2 however, is instructing VB to set a range in ws3 (with ws3.Range) using cell arguments from a different sheet, the activesheet (activesheet.cells(2, x), activesheet.cells(lRow, x)). This conflict produces the runtime error. --------------------------------- So, functionally equivalent ? Yes and No. Equivalent ? Yes and No. --- Jay -- Jay "Gary Keramidas" wrote: just wondering if these are functionally the same: i = Range(ws3.Cells(2, x), ws3.Cells(lRow, x)) i = ws3.Range(Cells(2, x), Cells(lRow, x)) -- Gary |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
syntax question
ok, i think it's because ws4 is not the active sheet, so the 2nd one fails.
thanks jim and jay -- Gary "Gary Keramidas" <GKeramidasATmsn.com wrote in message ... i guess to be more exact: this works If Application.Sum(Range(ws4.Cells(2, x), ws4.Cells(lRow, x))) 0 Then this doesn't If Application.Sum(ws4.Range(Cells(2, x), Cells(lRow, x))) 0 Then -- Gary "Jim Cone" wrote in message ... Gary, If the ActiveSheet is ws3 both will work. If the ActiveSheet is some other sheet both will blow up. I guess in that regard they both function the same. <g The preferred method is to prefix Range and Cells with the worksheet... ws3.Range(ws3.Cells(2, x), ws3.Cells(lRow, x)) -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "Gary Keramidas" <GKeramidasATmsn.com wrote in message just wondering if these are functionally the same: i = Range(ws3.Cells(2, x), ws3.Cells(lRow, x)) i = ws3.Range(Cells(2, x), Cells(lRow, x)) -- Gary |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
syntax question
It also depends on where the code is.
An unqualified range (without the ws. stuff) in a general module, will refer to the activesheet. An unqualified range in a worksheet module will refer to the sheet that owns the code. dim i as range set ws = worksheets("someothersheet") 'this will always work set i = ws.Range(ws3.Cells(2, x), ws3.Cells(lRow, x)) 'this will cause an error if you're in a worksheet module (not someothersheet, though): set i = ws.Range(Cells(2, x), Cells(lRow, x)) ===== To save typing (and easier to understand (for me at least)), I'd use: dim ws as worksheet dim i as range set ws = worksheets("someothersheet") with ws set i = .Range(.Cells(2, x), .Cells(lRow, x)) end with Note the dot's in front of .range and both .cells. Gary Keramidas wrote: just wondering if these are functionally the same: i = Range(ws3.Cells(2, x), ws3.Cells(lRow, x)) i = ws3.Range(Cells(2, x), Cells(lRow, x)) -- Gary -- Dave Peterson |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
syntax question
Hi Dave - could you check your syntax ? I might be misunderstanding your
post, but both statements you posted produce an error (winXP/XL03) regardless of the location (general module or worksheet module). My thought is that this type of statement can never ask for the range object from one worksheet based on the cells object from another; they're incompatibility produces the error. Let us know what you think... -- Respectfully, Jay "Dave Peterson" wrote: It also depends on where the code is. An unqualified range (without the ws. stuff) in a general module, will refer to the activesheet. An unqualified range in a worksheet module will refer to the sheet that owns the code. dim i as range set ws = worksheets("someothersheet") 'this will always work set i = ws.Range(ws3.Cells(2, x), ws3.Cells(lRow, x)) 'this will cause an error if you're in a worksheet module (not someothersheet, though): set i = ws.Range(Cells(2, x), Cells(lRow, x)) ===== To save typing (and easier to understand (for me at least)), I'd use: dim ws as worksheet dim i as range set ws = worksheets("someothersheet") with ws set i = .Range(.Cells(2, x), .Cells(lRow, x)) end with Note the dot's in front of .range and both .cells. Gary Keramidas wrote: just wondering if these are functionally the same: i = Range(ws3.Cells(2, x), ws3.Cells(lRow, x)) i = ws3.Range(Cells(2, x), Cells(lRow, x)) -- Gary -- Dave Peterson |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
syntax question
If you're writing about this line:
set i = ws.Range(ws3.Cells(2, x), ws3.Cells(lRow, x)) then you're right. But it was more of a typo (copy|paste) error than anything. set i = ws.Range(ws.Cells(2, x), ws.Cells(lRow, x)) (I copied from a combination of previous posts and should have just typed the line in the message.) But I'll stand by the one I actually typed! dim ws as worksheet dim i as range set ws = worksheets("someothersheet") with ws set i = .Range(.Cells(2, x), .Cells(lRow, x)) end with Jay wrote: Hi Dave - could you check your syntax ? I might be misunderstanding your post, but both statements you posted produce an error (winXP/XL03) regardless of the location (general module or worksheet module). My thought is that this type of statement can never ask for the range object from one worksheet based on the cells object from another; they're incompatibility produces the error. Let us know what you think... -- Respectfully, Jay "Dave Peterson" wrote: It also depends on where the code is. An unqualified range (without the ws. stuff) in a general module, will refer to the activesheet. An unqualified range in a worksheet module will refer to the sheet that owns the code. dim i as range set ws = worksheets("someothersheet") 'this will always work set i = ws.Range(ws3.Cells(2, x), ws3.Cells(lRow, x)) 'this will cause an error if you're in a worksheet module (not someothersheet, though): set i = ws.Range(Cells(2, x), Cells(lRow, x)) ===== To save typing (and easier to understand (for me at least)), I'd use: dim ws as worksheet dim i as range set ws = worksheets("someothersheet") with ws set i = .Range(.Cells(2, x), .Cells(lRow, x)) end with Note the dot's in front of .range and both .cells. Gary Keramidas wrote: just wondering if these are functionally the same: i = Range(ws3.Cells(2, x), ws3.Cells(lRow, x)) i = ws3.Range(Cells(2, x), Cells(lRow, x)) -- Gary -- Dave Peterson -- Dave Peterson |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
syntax question
Got it. I figured it might be a typo, but I wasn't sure if you had some
other points in mind. -- Thanks for the feedback, Jay "Dave Peterson" wrote: If you're writing about this line: set i = ws.Range(ws3.Cells(2, x), ws3.Cells(lRow, x)) then you're right. But it was more of a typo (copy|paste) error than anything. set i = ws.Range(ws.Cells(2, x), ws.Cells(lRow, x)) (I copied from a combination of previous posts and should have just typed the line in the message.) But I'll stand by the one I actually typed! dim ws as worksheet dim i as range set ws = worksheets("someothersheet") with ws set i = .Range(.Cells(2, x), .Cells(lRow, x)) end with Jay wrote: Hi Dave - could you check your syntax ? I might be misunderstanding your post, but both statements you posted produce an error (winXP/XL03) regardless of the location (general module or worksheet module). My thought is that this type of statement can never ask for the range object from one worksheet based on the cells object from another; they're incompatibility produces the error. Let us know what you think... -- Respectfully, Jay "Dave Peterson" wrote: It also depends on where the code is. An unqualified range (without the ws. stuff) in a general module, will refer to the activesheet. An unqualified range in a worksheet module will refer to the sheet that owns the code. dim i as range set ws = worksheets("someothersheet") 'this will always work set i = ws.Range(ws3.Cells(2, x), ws3.Cells(lRow, x)) 'this will cause an error if you're in a worksheet module (not someothersheet, though): set i = ws.Range(Cells(2, x), Cells(lRow, x)) ===== To save typing (and easier to understand (for me at least)), I'd use: dim ws as worksheet dim i as range set ws = worksheets("someothersheet") with ws set i = .Range(.Cells(2, x), .Cells(lRow, x)) end with Note the dot's in front of .range and both .cells. Gary Keramidas wrote: just wondering if these are functionally the same: i = Range(ws3.Cells(2, x), ws3.Cells(lRow, x)) i = ws3.Range(Cells(2, x), Cells(lRow, x)) -- Gary -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Syntax question | Excel Worksheet Functions | |||
VBA syntax question | Excel Discussion (Misc queries) | |||
syntax question | Excel Worksheet Functions | |||
Syntax question | Excel Programming | |||
VBA Syntax Question? | Excel Programming |