Cells(1,1) vs Range("A1")

[Oorang]

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.

 

[hatman]

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:

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.

 

[Oorang]

One behaviour to be aware of... This:

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.

 

[ExcelChampion]

One behaviour to be aware of... This:

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.

 

[macleanb]

it is? Seems paste special is slower in my code

 

[macleanb]

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?

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

 

[NateO]

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.

 

[macleanb]

CSE functions ????

 

[NateO]

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/viewtopic.php?p=1230105#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.

 

[macleanb]

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:

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