Solved

VBA - Cells vs Range

Posted on 2003-11-26
4
19,858 Views
Last Modified: 2008-02-01
Between two of the methods of addressing a cell in VBA

xlsheet.Range("A1").Value
and
xlsheet.Cells(1, 1).Value


Is either method more efficient than the other at runtime?
0
Comment
Question by:krinpit
4 Comments
 
LVL 42

Expert Comment

by:frodoman
ID: 9824880
I believe that .Cells actually returns a .Range value so technically speaking using the range version is probably slightly more efficient.  Reasonably speaking though we're talking about a difference of picoseconds so unless you're doing this billions of times there probably won't be a perceptable difference.
0
 

Author Comment

by:krinpit
ID: 9824988
Thanks for you input frodoman,

I'll leave this post open for a couple of days to see if I get any further responses before giving you the points.

0
 
LVL 27

Expert Comment

by:Dabas
ID: 9829264
Hi krinpit,
I concur with frodoman

Dabas
0
 
LVL 1

Accepted Solution

by:
victoresq earned 50 total points
ID: 10015829

My students often ask these type of questions.

It is all a question of overhead...

The "Range" function selects the starting or active cell or cells in Excel, the "Cell" function is a specific location.

So to use Range to return a specific Cell is a bit of an overkill.

At run time this may not be an issue for your single line execution as you have outlined.  Now make multiply this by 100, 1000, 10000, 1000000 evaluations and watch the time increase for each successive execution.

Something similar happened to an application that I fixed for Enron (before the debaucle), I was called in since an XLS VBA application was taking quite a while to execute a simple 10,000 x 10,000 evaluation matrix.  The previous programmer used the Range instead of the Cell as you describe.

The time was cut down considerably once I converted the sequence as I have described.

have fun!

Vic
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
static class 3 80
NotAlone Challenge 20 88
withoutTen challenge 14 137
word0 challenge 3 112
Having just graduated from college and entered the workforce, I don’t find myself always using the tools and programs I grew accustomed to over the past four years. However, there is one program I continually find myself reverting back to…R.   So …
Whether you’re a college noob or a soon-to-be pro, these tips are sure to help you in your journey to becoming a programming ninja and stand out from the crowd.
The viewer will learn how to implement Singleton Design Pattern in Java.
The viewer will be introduced to the member functions push_back and pop_back of the vector class. The video will teach the difference between the two as well as how to use each one along with its functionality.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question