Solved

VBA - Cells vs Range

Posted on 2003-11-26
4
19,648 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
Comment Utility
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
Comment Utility
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
Comment Utility
Hi krinpit,
I concur with frodoman

Dabas
0
 
LVL 1

Accepted Solution

by:
victoresq earned 50 total points
Comment Utility

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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
socket programming 8 32
countEvens challenge 2 56
lucky13 challenge 11 111
WPF - External data binding to controls inside a UserControl 2 43
Navigation is an important part of web design from a usability perspective. But it is often a pain when it comes to a developer’s perspective. By navigation, it often means menuing. This is less theory and more practical of how to get a specific gro…
This article will show, step by step, how to integrate R code into a R Sweave document
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …

771 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now