Solved

VBA - Cells vs Range

Posted on 2003-11-26
4
19,697 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
countXY challenge 28 146
sum28 challenge 31 106
canBalance challenge 34 70
Problem to start Neon 20 87
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 …
This article will show, step by step, how to integrate R code into a R Sweave document
The viewer will learn how to use the return statement in functions in C++. The video will also teach the user how to pass data to a function and have the function return data back for further processing.
This video will show you how to get GIT to work in Eclipse.   It will walk you through how to install the EGit plugin in eclipse and how to checkout an existing repository.

895 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

15 Experts available now in Live!

Get 1:1 Help Now