Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Which VBA code is better
Hi,
I have the following codes and would like to know which one is more effective and the difference. Sub Test1() Dim myRow As Single Range("A1") = Range("ItemTitle").Offset(myRow) Range("B1") = Range("QtyTitle").Offset(myRow) End Sub Sub Test2() Dim myRow As Single Range("A1") = Cells(myRow, Range("ItemTitle").Column) Range("B1") = Cells(myRow, Range("QtyTitle").Column) End Sub I like Test1 more as it is easier to write and seems to me that Test2 may need a worksheet qualifier if it's referencing another worksheet. Thanks Andy, HK |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Which VBA code is better
I prefer to use Cells as to Range, its easier when you work with looping
procedures i.e. Dim y as lnteger Dim y as Integer For x = 1 to 10 y =y +1 cells(x,y) = x Next x Pretty pointless results from this but this wouldn't be as simple to achieve using range -- Message posted via http://www.officekb.com |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Which VBA code is better
I don't see that either is 'better', one may suit one circumstance, one may
suit another. main thing, ALWAYS use worksheet qualifiers, even if you are expecting the activesheet, it is far clearer, and less difficult to debug. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Andy" wrote in message ... Hi, I have the following codes and would like to know which one is more effective and the difference. Sub Test1() Dim myRow As Single Range("A1") = Range("ItemTitle").Offset(myRow) Range("B1") = Range("QtyTitle").Offset(myRow) End Sub Sub Test2() Dim myRow As Single Range("A1") = Cells(myRow, Range("ItemTitle").Column) Range("B1") = Cells(myRow, Range("QtyTitle").Column) End Sub I like Test1 more as it is easier to write and seems to me that Test2 may need a worksheet qualifier if it's referencing another worksheet. Thanks Andy, HK |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Which VBA code is better
Thanks to both Bob and Crowbar
Andy "Bob Phillips" wrote in message ... I don't see that either is 'better', one may suit one circumstance, one may suit another. main thing, ALWAYS use worksheet qualifiers, even if you are expecting the activesheet, it is far clearer, and less difficult to debug. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Andy" wrote in message ... Hi, I have the following codes and would like to know which one is more effective and the difference. Sub Test1() Dim myRow As Single Range("A1") = Range("ItemTitle").Offset(myRow) Range("B1") = Range("QtyTitle").Offset(myRow) End Sub Sub Test2() Dim myRow As Single Range("A1") = Cells(myRow, Range("ItemTitle").Column) Range("B1") = Cells(myRow, Range("QtyTitle").Column) End Sub I like Test1 more as it is easier to write and seems to me that Test2 may need a worksheet qualifier if it's referencing another worksheet. Thanks Andy, HK |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Which VBA code is better
I trust the respective methods are not intended to refer to the same pair of
cells ! Regards, Peter T "Andy" wrote in message ... Hi, I have the following codes and would like to know which one is more effective and the difference. Sub Test1() Dim myRow As Single Range("A1") = Range("ItemTitle").Offset(myRow) Range("B1") = Range("QtyTitle").Offset(myRow) End Sub Sub Test2() Dim myRow As Single Range("A1") = Cells(myRow, Range("ItemTitle").Column) Range("B1") = Cells(myRow, Range("QtyTitle").Column) End Sub I like Test1 more as it is easier to write and seems to me that Test2 may need a worksheet qualifier if it's referencing another worksheet. Thanks Andy, HK |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Which VBA code is better
I'd either version, but I'd modify them slightly.
Option Explicit Sub Test1() Dim myRow As Long myrow = somenumberhere with activesheet .Range("A1").value = .Range("ItemTitle").Offset(myRow).value .Range("B1").value = .Range("QtyTitle").Offset(myRow).value end with End Sub I wouldn't use "As Integer". "As long" is faster for the computer to work with. And I'd qualify the ranges (like Bob wrote) and I'd specify the property that I wanted. And I'd add "option explicit" to the top of the module--I want to be forced to declare my variables. Andy wrote: Hi, I have the following codes and would like to know which one is more effective and the difference. Sub Test1() Dim myRow As Single Range("A1") = Range("ItemTitle").Offset(myRow) Range("B1") = Range("QtyTitle").Offset(myRow) End Sub Sub Test2() Dim myRow As Single Range("A1") = Cells(myRow, Range("ItemTitle").Column) Range("B1") = Cells(myRow, Range("QtyTitle").Column) End Sub I like Test1 more as it is easier to write and seems to me that Test2 may need a worksheet qualifier if it's referencing another worksheet. Thanks Andy, HK -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Which VBA code is better
I'd either version, but I'd modify them slightly.
should be: I'd USE either version, but I'd modify them slightly. Dave Peterson wrote: I'd either version, but I'd modify them slightly. Option Explicit Sub Test1() Dim myRow As Long myrow = somenumberhere with activesheet .Range("A1").value = .Range("ItemTitle").Offset(myRow).value .Range("B1").value = .Range("QtyTitle").Offset(myRow).value end with End Sub I wouldn't use "As Integer". "As long" is faster for the computer to work with. And I'd qualify the ranges (like Bob wrote) and I'd specify the property that I wanted. And I'd add "option explicit" to the top of the module--I want to be forced to declare my variables. Andy wrote: Hi, I have the following codes and would like to know which one is more effective and the difference. Sub Test1() Dim myRow As Single Range("A1") = Range("ItemTitle").Offset(myRow) Range("B1") = Range("QtyTitle").Offset(myRow) End Sub Sub Test2() Dim myRow As Single Range("A1") = Cells(myRow, Range("ItemTitle").Column) Range("B1") = Cells(myRow, Range("QtyTitle").Column) End Sub I like Test1 more as it is easier to write and seems to me that Test2 may need a worksheet qualifier if it's referencing another worksheet. Thanks Andy, HK -- Dave Peterson -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Which VBA code is better
Thanks Dave for the detailed explanation.
"Dave Peterson" wrote in message ... I'd either version, but I'd modify them slightly. Option Explicit Sub Test1() Dim myRow As Long myrow = somenumberhere with activesheet .Range("A1").value = .Range("ItemTitle").Offset(myRow).value .Range("B1").value = .Range("QtyTitle").Offset(myRow).value end with End Sub I wouldn't use "As Integer". "As long" is faster for the computer to work with. And I'd qualify the ranges (like Bob wrote) and I'd specify the property that I wanted. And I'd add "option explicit" to the top of the module--I want to be forced to declare my variables. Andy wrote: Hi, I have the following codes and would like to know which one is more effective and the difference. Sub Test1() Dim myRow As Single Range("A1") = Range("ItemTitle").Offset(myRow) Range("B1") = Range("QtyTitle").Offset(myRow) End Sub Sub Test2() Dim myRow As Single Range("A1") = Cells(myRow, Range("ItemTitle").Column) Range("B1") = Cells(myRow, Range("QtyTitle").Column) End Sub I like Test1 more as it is easier to write and seems to me that Test2 may need a worksheet qualifier if it's referencing another worksheet. Thanks Andy, HK -- Dave Peterson |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Which VBA code is better
Andy wrote:
I have the following codes and would like to know which one is more effective and the difference. Sub Test1() Dim myRow As Single Range("A1") = Range("ItemTitle").Offset(myRow) Range("B1") = Range("QtyTitle").Offset(myRow) End Sub Sub Test2() Dim myRow As Single Range("A1") = Cells(myRow, Range("ItemTitle").Column) Range("B1") = Cells(myRow, Range("QtyTitle").Column) End Sub I like Test1 more as it is easier to write and seems to me that Test2 may need a worksheet qualifier if it's referencing another worksheet. Thanks Andy, HK Notwithstanding advice about ALWAYS using worksheet qualifiers, another tool you might want to consider, if using the code in a General Module, is the following, which doesn't require such qualification, without regard to which sheet is active: Set rngA = Sheets(2).range("A1") rngA.Value = rngA(myRow, range("ItemTitle").Column) Alan Beban |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Which VBA code is better
"Alan Beban" wrote in message ... Andy wrote: I have the following codes and would like to know which one is more effective and the difference. Sub Test1() Dim myRow As Single Range("A1") = Range("ItemTitle").Offset(myRow) Range("B1") = Range("QtyTitle").Offset(myRow) End Sub Sub Test2() Dim myRow As Single Range("A1") = Cells(myRow, Range("ItemTitle").Column) Range("B1") = Cells(myRow, Range("QtyTitle").Column) End Sub I like Test1 more as it is easier to write and seems to me that Test2 may need a worksheet qualifier if it's referencing another worksheet. Thanks Andy, HK Notwithstanding advice about ALWAYS using worksheet qualifiers, another tool you might want to consider, if using the code in a General Module, is the following, which doesn't require such qualification, without regard to which sheet is active: Set rngA = Sheets(2).range("A1") rngA.Value = rngA(myRow, range("ItemTitle").Column) Alan Beban Thanks Alan, I'll certainly consider this approach. Learned few things today, how lucky I am, thank you guys. Andy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
split post code (zip code) out of cell that includes full address | Excel Discussion (Misc queries) | |||
Drop Down/List w/Code and Definition, only code entered when selec | Excel Worksheet Functions | |||
Shorten code to apply to all sheets except a few, instead of individually naming them, and later adding to code. | Excel Programming | |||
Protect Sheet with code, but then code will not Paste error. How do i get around this. Please read for explainations.... | Excel Programming | |||
Excel code convert to Access code - Concat & eliminate duplicates | Excel Programming |