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

NateO

Legend
Joined
Feb 17, 2002
Messages
9,700
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. :LOL:

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

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

macleanb

Well-known Member
Joined
Dec 10, 2004
Messages
715
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

Legend
Joined
Feb 17, 2002
Messages
9,700
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! :LOL:
 

macleanb

Well-known Member
Joined
Dec 10, 2004
Messages
715
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

Legend
Joined
Feb 17, 2002
Messages
9,700
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... :confused:

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

Well-known Member
Joined
Aug 12, 2005
Messages
976
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.
 

ExcelChampion

Well-known Member
Joined
Aug 12, 2005
Messages
976
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
 

NateO

Legend
Joined
Feb 17, 2002
Messages
9,700
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. :LOL:
 

ExcelChampion

Well-known Member
Joined
Aug 12, 2005
Messages
976
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?"
 

macleanb

Well-known Member
Joined
Dec 10, 2004
Messages
715
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 :)
 

Watch MrExcel Video

Forum statistics

Threads
1,101,909
Messages
5,483,663
Members
407,399
Latest member
Rakeforms

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top