Can this VBA code be written better?

[kalim]

Hi excel users.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I was wondering if anyone could look at this VBA code to see if it can be done more efficiently (not good with VBA) Thanks. In case you are wondering "b2" has a value that needs to be cleared first.

Sub list_1()
 
Sheet1.Activate
 
    Range("B2").ClearContents
    Range("f2:f41").Select
    Selection.Copy
    Range("A2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Range("A42:A53").Select
    Selection.ClearContents
    Sheet3.Select
    Range("B1").Select
 
End Sub

 

[Boller]

What intellisense do you get using the Range property that you do not get using Evaluate[] ?

Ok, I just checked and see that there is no intellisense immediately after the bracket.
I hadn't noticed before, so will be continuing to use [].

 

[GTO]

I disagree - I prefer to use Evaluate[].

What intellisense do you get using the Range prpoerty that you do not get using Evaluate[] ?

I know all the theory about it being more efficient but have never a seen a measurable demonstration of how much more efficient.
Can you suggest one?...

Greetings Boller,

I see that you already caught the intellisense part. As to testing - just my observations...

Excel Workbook
	A	B	C	D	E
1	With wb on flashdrive:		With WB on C:		On C: and tack in disable screen repaints and calculation
2	Eval method averaged: 2.4374 Seconds		Eval method averaged: 2.2126 Seconds		Eval method averaged: 1.5843 Seconds
3	Range averaged: 2.022 Seconds		Range averaged: 1.9297 Seconds		Range averaged: 1.25 Seconds
4	Speed increase: 17.0428%		Speed increase: 12.7859%		Speed increase: 21.1008%
5
6	Eval method averaged: 2.389 Seconds		Eval method averaged: 2.0173 Seconds		Eval method averaged: 1.564 Seconds
7	Range averaged: 2.0452 Seconds		Range averaged: 1.6749 Seconds		Range averaged: 1.2422 Seconds
8	Speed increase: 14.391%		Speed increase: 16.9732%		Speed increase: 20.5754%
9
10	Eval method averaged: 2.3187 Seconds		Eval method averaged: 2.1217 Seconds		Eval method averaged: 1.5686 Seconds
11	Range averaged: 1.9641 Seconds		Range averaged: 1.7705 Seconds		Range averaged: 1.2469 Seconds
12	Speed increase: 15.2931%		Speed increase: 16.5528%		Speed increase: 20.5087%
13
14	Eval method averaged: 2.2375 Seconds		Eval method averaged: 2.0531 Seconds		Eval method averaged: 1.5547 Seconds
15	Range averaged: 1.9015 Seconds		Range averaged: 1.6655 Seconds		Range averaged: 1.2312 Seconds
16	Speed increase: 15.0168%		Speed increase: 18.8788%		Speed increase: 20.8079%
17
18	Eval method averaged: 2.4483 Seconds		Eval method averaged: 2.0562 Seconds		Eval method averaged: 1.5577 Seconds
19	Range averaged: 2.1017 Seconds		Range averaged: 1.7018 Seconds		Range averaged: 1.2345 Seconds
20	Speed increase: 14.1568%		Speed increase: 17.2357%		Speed increase: 20.7485%
Sheet2

Excel 2003

Option Explicit
    
Private Declare Function GetTickCount Lib "kernel32" () As Long
    
Sub exam3()
Dim _
i                   As Long, ii                 As Long, _
StartEvalTime       As Double, EndEvalTime      As Double, _
StartRangeTime      As Double, EndRangeTime     As Double, _
TotalEvalTime       As Double, TotalRangeTime   As Double, _
PerformanceIncrease As Double
    
    Application.ScreenUpdating = False: Application.Calculation = xlCalculationManual
    For ii = 1 To 10
        
        Sheet1.Range("A:B").Clear
        
        StartEvalTime = GetTickCount()
        For i = 1 To 50000
            Sheet1.[A1].Offset(i).Value = i
        Next
        DoEvents
        EndEvalTime = GetTickCount()
        
        
        StartRangeTime = GetTickCount
        For i = 1 To 50000
            Sheet1.Range("B1").Offset(i).Value = i
        Next
        DoEvents
        EndRangeTime = GetTickCount
        
        
        TotalEvalTime = TotalEvalTime + (EndEvalTime - StartEvalTime)
        TotalRangeTime = TotalRangeTime + (EndRangeTime - StartRangeTime)
    Next
        Application.ScreenUpdating = True:  Application.Calculation = xlCalculationAutomatic
        
        TotalEvalTime = TotalEvalTime / 10000
        TotalRangeTime = TotalRangeTime / 10000
        PerformanceIncrease = FormatNumber(((TotalEvalTime - TotalRangeTime) / TotalEvalTime) * 100, 4)
        
        Debug.Print "Eval method averaged: " & TotalEvalTime & " Seconds"
        Debug.Print "Range averaged: " & TotalRangeTime & " Seconds"
        Debug.Print "Speed increase: " & PerformanceIncrease & "%"
End Sub

Hope that helps,

Mark

 

[Colin Legg]

I disagree - I prefer to use Evaluate[].

That's fine, I can live with that.

 

[Boller]

GTO

Thanks for posting the run-time comparisons.

Your results indicate that the processing of 50,000 range objects using [] takes about one third of a second longer than using the range property.

I can live with that - for me, the advantages of using [] are worth a lot more than 1/3 of a second per 50,000 objects.

 

[snb_]

I get a feeling as if we are still in the Intel 8086 age.
In my opinion indiscernable differences (in non-extraterrestial code) are no differences.
A proper analysis of the tasks to be accomplished and a sophisticated structure of tasks, data etc. is far more important than any preference for coding style.

 

[VoG]

I get a feeling as if we are still in the Intel 8086 age.
In my opinion indiscernable differences (in non-extraterrestial code) are no differences.
A proper analysis of the tasks to be accomplished and a sophisticated structure of tasks, data etc. is far more important than any preference for coding style.

Nah, sorry but the Evaluate method is plain inefficient. Use what you like but Range(...) is faster and, for me, more transparent. It is up ton you.

 

[Boller]

Nah, sorry but the Evaluate method is plain inefficient. Use what you like but Range(...) is faster and, for me, more transparent. It is up ton you.

Rather than "plain inefficient", don't you think that "immaterial difference" would be a more appropriate description ?

 

[Colin Legg]

Hi Boller,

If you wanted to get a reference to a control on a sheet, let's say an ActiveX control called TextBox1, would you use [TextBox1]? Yes? No? If not, then what would you use and why wouldn't you use [Textbox1]? After all, the concept is no different to using [A1] to get a Range. How about a shape, or perhaps a forms control? Any different with them?

Despite what you say, the very fact that evaluate can be used to return objects (and literals) other than a Range means that using like that in code is unclear. It is ambiguous. Because it is late bound it is slower and loses a very important advantage when coding which is intellisense. These are all significant factors which vastly outweigh the fact that you save a few keystrokes.

 

[Boller]

Hi Boller,

If you wanted to get a reference to a control on a sheet, let's say an ActiveX control called TextBox1, would you use [TextBox1]? Yes? No? If not, then what would you use and why wouldn't you use [Textbox1]? After all, the concept is no different to using [A1] to get a Range. How about a shape, or perhaps a forms control? Any different with them?

Despite what you say, the very fact that evaluate can be used to return objects (and literals) other than a Range means that using like that in code is unclear. It is ambiguous. Because it is late bound it is slower and loses a very important advantage when coding which is intellisense. These are all significant factors which vastly outweigh the fact that you save a few keystrokes.

I do not accept or agree with any of your nit-picking points/objections - but enough already.

I'll do it my way, you do it your way.

I think most readers will recognize that code clarity and run-time speed are not compromised (at least, not materially) and that your points are opinionated exaggeration ("code is unclear", "ambiguous", "very important advantage", "significant factors", "vastly outweigh") rather than being objective.

 

[Colin Legg]

Hello again Boller,

I do not accept or agree with any of your nit-picking points/objections - but enough already.

I'll do it my way, you do it your way.

I think most readers will recognize that code clarity and run-time speed are not compromised (at least, not materially) and that your points are opinionated exaggeration ("code is unclear", "ambiguous", "very important advantage", "significant factors", "vastly outweigh") rather than being objective.

It's not enough and it's not nit-picking.

In addition to the points already made, where it has been clearly demonstrated that the code clarity is compromised and [ ] is ambiguous, I can quite easily produce examples where [ ] will simply return the wrong results. I can also easily produce examples where using [ ] will take up more keystrokes than using the Range property.

It's obvious you're not being objective about this and that nothing will change your mind. Hopefully other readers of the thread will see sense.