• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 20303
  • Last Modified:

VBA - Cells vs Range

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
krinpit
Asked:
krinpit
1 Solution
 
frodomanCommented:
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
 
krinpitAuthor Commented:
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
 
DabasCommented:
Hi krinpit,
I concur with frodoman

Dabas
0
 
victoresqCommented:

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!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now