Is application.worksheet function faster than inputting formula directly into a cell??

Good morning all!! Just had a quick question for you guys. Which method will run faster??

1. Inputting a formula directly into a cell via vba. (i.e. Range("A1").Value = "INDEX(blah blah)")

or

2. Using Application.WorksheetFunction.Index(blah blah)

I am trying to speed up some automation for my company, and just wondering which method would help it go faster as there are many lookups that I need to run in order to get all the information and am trying to keep it as quick and efficient as possible. Thank you!!

Dome

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however the thread title does not really convey what your request is about.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

The second option will put a value in the cell and will never be recalculated. The first puts a formula in the cell which may be recalculated. If the value is static and the formula does not need to be recalculated, then go for option 2.

Oops, sorry Alan. Typing while you were moderating

If it's a one off, say as part of a Worksheet Change Event handler, I’d go with the second option.

If it's necessary to fill down, I’d drop the formula into the entire range. If it may be recalculated, I’d leave the formulae there, otherwise I’d convert it to values.

I can't really comment on which is faster as a one off but I’d guess it would be option 2.

How do you determine the last value for i?

Some ideas to play with:

Please Login or Register  to view this content.