Page 2 of 7 FirstFirst 1234 ... LastLast
Results 11 to 20 of 63

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

  1. #11
    Board Regular Oorang's Avatar
    Join Date
    Mar 2005
    Posts
    2,071
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    It's just air code to show you can pull a range (any range) in to a variant array. Change to data in the array and then plug the data back into the range (thereby changing the data in the range). I find that approach useful if I am making making many changes particularly conditional changes and/or changes that depend on data being gleaned from other sources. It just give you a little more control. You can work with the data in memory without changing the worksheet an then if you opt to change the worksheet you can do it all at one time.
    It's not a "do everytime" trick, it's just one way that can be useful in certain circumstances.
    • Get better answers! Include your version of Office in your post.

  2. #12
    Board Regular hatman's Avatar
    Join Date
    Apr 2005
    Location
    Palmer, MA
    Posts
    2,664
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Th eother advantage to working in an array is that it contains ONLY the .Value property of the source range, hence it can be manipulated more quickly, potentially with less code. In the olden days before Object Oriented Programming, Arrays of various dimensions were the preferred method for storing and manipulating large amounts of data (especially non-numeric) while processing.

    The range object contains evrything that a corresponding array contains, in terms of raw data, but it also carries with it the full fidelity of the Methods and Properties that go with that object.

    Also remember that a Variant is simply a Miscellaneous, or undetermined, Variable type, which VBA coerces to a specific data type at runtime, based on the type of data that is being assigned to it. Variants require slightlly more memory than variable types that are declared at Design-Time, but variants are about the only way to jam the raw data from an object into an array in one step without the processor hit of a loop. In the same way, you can assign data from a Range Object to a ListBox or Combobox (which store data as an arrays) using the following syntax:

    Code:
    Private Sub UserForm_Initialize()
        
        Me.ListBox1.ColumnCount = 2
    
        Me.ListBox1.List = Range("A1:B10").Value
    
    End Sub("A1:B12")
    Although perhaps this isn't a good example, since the .List property is already pre-defined as an array and requires the specific assignment of the .Value property of the Range Object.
    (XL2010 on Windows 7 Professional SP 2)

  3. #13
    Board Regular Oorang's Avatar
    Join Date
    Mar 2005
    Posts
    2,071
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    One behaviour to be aware of... This:
    Code:
    Dim vTmp as Variant
    vTmp = ActiveSheet.UsedRange
    ActiveSheet.UsedRange.Value =  vTmp
    Will replace your formulas with values. I actually use this in preference to copy/pastespecial values as it prevents me from having to hijack the clipboard. But if you didn't WANT to replace your formulas you should know about it.
    • Get better answers! Include your version of Office in your post.

  4. #14
    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

    One behaviour to be aware of... This:
    Code:
    Dim vTmp as Variant
    vTmp = ActiveSheet.UsedRange
    ActiveSheet.UsedRange.Value =  vTmp
    Will replace your formulas with values. I actually use this in preference to copy/pastespecial values as it prevents me from having to hijack the clipboard. But if you didn't WANT to replace your formulas you should know about it.
    As well, though, its slower than using Copy/Paste Special Values.
    Todd

  5. #15
    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

    it is? Seems paste special is slower in my code
    ttfn benm
    Ben Maclean - London
    Win2k / Excel 2002 SP3

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

  6. #16
    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

    Interesting (or I'm being dumb) Take a look at the below code. If the variant block is first in the sub, then runtime is very fast (~1 second). HOwever if its second then it runs in a similar time to the pastespecial - any ideas?

    Code:
    Private Sub CommandButton1_Click()
    
    Dim i As Long
    Dim stime As Date
    Dim time1 As String
    Dim time2 As String
    Dim var****e As Variant
    
    stime = Now()
    var****e = Range("A1:Z40")
    For i = 1 To 500
        Range("A51:Z90") = var****e
    Next i
    time1 = Format(Now() - stime, "nn:ss")
    
    stime = Now()
    Range("A1:Z40").Copy
    For i = 1 To 500
        Range("A51:Z90").PasteSpecial xlPasteValues
    Next i
    time2 = Format(Now() - stime, "nn:ss")
    
    Debug.Print time1 & "/" & time2
    
    End Sub
    
    
    Private Sub CommandButton1_Click()
    
    Dim i As Long
    Dim stime As Date
    Dim time1 As String
    Dim time2 As String
    Dim var****e As Variant
    
    stime = Now()
    Range("A1:Z40").Copy
    For i = 1 To 500
        Range("A51:Z90").PasteSpecial xlPasteValues
    Next i
    time2 = Format(Now() - stime, "nn:ss")
    
    stime = Now()
    var****e = Range("A1:Z40")
    For i = 1 To 500
        Range("A51:Z90") = var****e
    Next i
    time1 = Format(Now() - stime, "nn:ss")
    
    
    
    Debug.Print time1 & "/" & time2
    
    End Sub
    ttfn benm
    Ben Maclean - London
    Win2k / Excel 2002 SP3

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

  7. #17
    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

    Code:
    var****e = Range("A1:Z40") 
    For i = 1 To 500 
        Range("A51:Z90") = var****e 
    Next i
    LOL!

    The PasteSpecial Method is generally slower than passing Values. Not only do you have to access the clipboard but look at what it's doing; I mean literally watch it. It's actually selecting ranges, and generating a fair amount of screen redraw, etc... These aren't especially fast operations or desirable.

    Why would the 2nd procedure be bogged down relative to the first? Let's revisit that clipboard... Did you clear it out, or are you chewing up memory? Memory is a pretty important factor when executing code...

    Original question? Yes, the Cells() property is relatively faster than Range("String"), Strings are interpreted at run-time and, as such, bind pretty late. [Anything] or [Evaluate], is a relatively sophisticated Method, even capable of parsing CSE functions and will be quite a bit slower... While they say short is sweet, and it is quite maintainable, in my opinion.

    I'm not sure one can say with any certainty that Range() or Cells() is superior; they're slightly different ways of referring to a Range Object. They both have differing benefits, pending your goals/intent.

  8. #18
    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

    CSE functions ????
    ttfn benm
    Ben Maclean - London
    Win2k / Excel 2002 SP3

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

  9. #19
    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

    Control-Shift-Enter, or Array-entered, functions, if you prefer.

    Here's an example of the Evaluate Method parsing a CSE function:

    http://www.mrexcel.com/board2/viewto...230105#1230105

    What appears to be a simple Range reference is actually a pretty sophisticated Method call which requires quite a bit of coercion; you will pay a price for this level of sophistication.

    Simple logic generally binds tighter than complex logic.

  10. #20
    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 Nosey

    Sorry for hijacking this thread, especially with an OT question - but....

    Nate, I notice at the end of the code in the example you gave me that you finish off thus:

    Code:
    Set xlWs = Nothing:             Set xlWb = Nothing 
    Set xlApp = Nothing 
    End Sub
    Now I'm just being nosey/curious here - why do you use the ":" as a seperator for one line and not the other. I only use ":" for labels (oh god did I admit that I sometimes use labels - error handlers only honest guv) I didnt even realise you could use colons as separators!

    I'm guessing its just a style thing but wanted to make sure.

    cheers...
    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
  •