Cells(1,1) vs Range("A1")
Page 3 of 7 FirstFirst 12345 ... LastLast
Results 21 to 30 of 63

Thread: Cells(1,1) vs Range("A1")
Thanks Thanks: 0 Likes Likes: 0

  1. #21
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hello,

    I don't think a brief interruption will hurt...

    See the following:

    http://msdn.microsoft.com/library/en...fstatement.asp

    I'll quote, to be sure:

    statement
    A syntactically complete unit that expresses one kind of action, declaration, or definition. A statement generally occupies a single line, although you can use a colon ( : ) to include more than one statement on a line. You can also use a line-continuation character (_) to continue a single logical line onto a second physical line.
    It's a technique for combining two lines of code into one. You've probably seen this in action more than you may think; it's commonly used with the Else condition of an If Statement, and following Do in a Do/Loop Loop.

    There's the madness to the method.

    • Edited by NPO: Added a space, the colon coerced to a smiley. =/

  2. #22
    Board Regular macleanb's Avatar
    Join Date
    Dec 2004
    Location
    London
    Posts
    715
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I understood what it was doing - I was just trying to understand why you chose to combine two of the three statements, rather than all three or none.
    ttfn benm
    Ben Maclean - London
    Win2k / Excel 2002 SP3

    Why do people say "It goes without saying" or "With the greatest of respect"

  3. #23
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Oh...

    It's simply a matter of preference, i.e., I have my own style. I prefer to space my code in a certain manner, etc...

    I can generally spot my own code from a mile away!

  4. #24
    Board Regular macleanb's Avatar
    Join Date
    Dec 2004
    Location
    London
    Posts
    715
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    cheers...

    And as you seem such a knowledgable chap:

    I am not in the habit of destroying objects (local objects that is, and those without quit/close methods) do you do this to "keep yourself honest", or are there actual run time implications of allowing VB to take them out when they fall out of scope.
    ttfn benm
    Ben Maclean - London
    Win2k / Excel 2002 SP3

    Why do people say "It goes without saying" or "With the greatest of respect"

  5. #25
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks. We aim to please.

    I got in the habit some time ago... I consider it 'best-in-class' practice, to be honest.

    It's a subject of debate, and it's not terribly fashionable to do this in Excel... I was actually reprimanded for doing this at one point... But, as you may note, it didn't stick...

    Keep in mind that we're binding with Excel from Access in the aforementioned example.

    There are certain Object Variables that I don't explicitly terminate, e.g., a Range Object in a For Each/Next Loop. Having taken a peek at them following the Loop, they're not initialized, so in this case, you're beating the dead horse, or memory assignment, or whatever you will.

    What opened my eyes to explicitly terminating initialized Object Variables was my research and experimentation with DAO and ADO. They have had some buggy-bugs over the years. For example, see Michael Kaplan's post, here:

    http://groups.google.com/group/comp....c1db39de3e9f23

    Michael's a former Access MVP and Microsoft employee. Is he right? Not sure... If you want, feel free to argue with him.

    ADO's not perfect, either, e.g.,

    http://support.microsoft.com/kb/319998

    Unfortunately, trying to terminate that specific object by setting it to Nothing doesn't help, it's hung memory in spite of what you attempt, aside from terminating the instance of Excel (it seems to come around at this point).

    So, I terminate my Object Variables, I don't worry about intrinsic data types. And be very careful about the order in which you build and terminate your Objects, build from the ground-up and terminate from the top-down. I could [seriously] answer 10 posts a day about an Excel instance being hung in memory from a error in judgment regarding an automation attempt from Access...

    Helpful?

  6. #26
    Board Regular ExcelChampion's Avatar
    Join Date
    Aug 2005
    Location
    Detroit, MI
    Posts
    976
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Given the two examples below, paste special runs faster on my machine (20 seconds for Pastespecial, 28 seconds for Value.)

    Code:
    Sub PasteSpecial()
        Dim strt As Date, endtime As Date
        Dim i As Long
        strt = Time
        For i = 1 To 100
            Range("A2:A50000").Formula = "=SUM(RC2)"
            Range("A2:A50000").Copy
            Range("A2:A50000").PasteSpecial xlPasteValues
        Next i
        endtime = Time
        MsgBox Format(endtime - strt, "hh:mm:ss")
    End Sub
    
    Sub Value()
        Dim strt As Date, endtime As Date
        Dim i As Long
        strt = Time
        For i = 1 To 100
            Range("A2:A50000").Formula = "=SUM(RC2)"
            Range("A2:A50000").Value = Range("A2:A50000").Value
        Next i
        endtime = Time
        MsgBox Format(endtime - strt, "hh:mm:ss")
    End Sub
    Would I do this normally, no, because there are faster ways. But in terms, at least in regards to what I was talking about, Pastespecial always runs faster.
    Todd

  7. #27
    Board Regular ExcelChampion's Avatar
    Join Date
    Aug 2005
    Location
    Detroit, MI
    Posts
    976
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    This took 30 seconds:

    Code:
    Sub vTmp()
        Dim vTmp As Variant
        Dim strt As Date, endtime As Date
        Dim i As Long
        Application.ScreenUpdating = False
        strt = Time
        For i = 1 To 100
            Range("A2:A50000").Formula = "=SUM(RC2)"
            vTmp = ActiveSheet.UsedRange
            ActiveSheet.UsedRange.Value = vTmp
        Next i
        endtime = Time
        Application.ScreenUpdating = True
        MsgBox Format(endtime - strt, "hh:mm:ss")
    End Sub
    Todd

  8. #28
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Okay, please allow me to qualify my post with some tests and see if we can agree.

    Take the following:

    Code:
    Public Declare Function QueryPerformanceFrequency _
        Lib "kernel32.dll" ( _
        lpFrequency As Currency) As Long
    
    Public Declare Function QueryPerformanceCounter _
        Lib "kernel32.dll" ( _
        lpPerformanceCount As Currency) As Long
    
    Sub TimerTime()
    Dim Ctr1 As Currency, Ctr2 As Currency, Freq As Currency
    Dim Overhead As Currency, a As String, i As Long
    QueryPerformanceFrequency Freq
    QueryPerformanceCounter Ctr1
    QueryPerformanceCounter Ctr2
    Overhead = Ctr2 - Ctr1 ' determine API overhead
    QueryPerformanceCounter Ctr1 ' time loop
    
    For i = 1 To 100
        Call foo
        'Call bar
    Next i
    
    QueryPerformanceCounter Ctr2
    Debug.Print (Ctr2 - Ctr1 - Overhead) / Freq
    End Sub
    
    Sub Foo()
    With Range("A1:B10000")
        .Formula = "=2+2"
        .Copy
        .PasteSpecial xlPasteValues
    End With
    End Sub
    
    Sub Bar()
    With Range("A1:B10000")
        .Formula = "=2+2"
        .Value = .Value
    End With
    End Sub
    Foo() is clocking in around 3.9 seconds, on average, on my machine, while Bar() is clocking in around 5.5 seconds. The PasteSpecial Method is throttling the marshalling of Values approach in this case, over a fairly large Range.

    Now change B10000, in Foo() and Bar() to B10 and note the rather big difference: Foo() is clocking around .53 seconds while Bar() is annihilating it, clocking in around .19 seconds.

    So, the real answer may be more of the usual... What are you attempting, again? This may vary with the actual size of the Range you're working with. Shame on me for trying to post a hasty generalization... When in doubt, use a timer.

  9. #29
    Board Regular ExcelChampion's Avatar
    Join Date
    Aug 2005
    Location
    Detroit, MI
    Posts
    976
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Code:
    "Now change B10000, in Foo() and Bar() to B10. Not the rather big difference: Foo() is clocking around .53 seconds while Bar() is annhilating it, clocking in around .19 seconds. "
    Huh? Wow, that is a big difference. But, on the other hand, humanly speaking, there is no difference.

    But, I guess my question is, "why?"
    Todd

  10. #30
    Board Regular macleanb's Avatar
    Join Date
    Dec 2004
    Location
    London
    Posts
    715
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Have you tried foo-ing & bar-ing then bar-ing and foo-ing? (its a serious question but I am not expecting an answer)

    On the destroying variables - I am not sure what version of my post you saw as I edited it. I am always explicit with automation variables, I guess I use a limited number of automation/complex objects and know which ones I have to be careful with. I guess if I was doing more varied work, I might develop the habit/ be more thorough. Thanks for all the input - I'm going to try and be more "best practice" - as in a large corparate - we might get Excel with .NET in a just 4 or five years from now
    ttfn benm
    Ben Maclean - London
    Win2k / Excel 2002 SP3

    Why do people say "It goes without saying" or "With the greatest of respect"

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •