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

[NateO]

Hello,

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

See the following:

http://msdn.microsoft.com/library/en-us/veendf98/html/defstatement.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. =/

 

[macleanb]

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.

 

[NateO]

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!

 

[macleanb]

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.

 

[NateO]

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.databases.ms-access/msg/88c1db39de3e9f23

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?

 

[ExcelChampion]

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

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.

 

[ExcelChampion]

This took 30 seconds:

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

 

[NateO]

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

Take the following:

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.

 

[ExcelChampion]

"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?"

 

[macleanb]

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