use of VBA vs excel formulas in cell

[moonfish]

So far I've only been using VBA if Excel's functions couldn't solve a problem. When I do use VBA, I try to limit myself to writing functions.

What I'm wondering is if I'm selling myself short by working like this. I'm looking for examples of situations where using Excel is the inferior solving method when it comes to reliability and flexibility of the resulting data.

 

[Jonmo1]

My general rule of thumb is
If it can be done with a formula, then it probably should be done with a formula.
VBA doesn't calculate any faster than a formula. The same math must be done, right?

But all rules have exceptions..
And that is based on your own judgement.

Other than the fact that vba can do things that actually can't be done with formula..
In my opinion, the only real advantage VBA has over a formula is..
You can control when, why and how frequently your calculations are done.


So I would say continue with the logic you described..
If you can do it with a formula, then VBA won't really be any faster.

Until you get to a point where your formula calculations make the sheet's performance annoyingly poor.
And when that happens, even before considering VBA,
Consider why your formulas are so slow?
Is it really just because there are so many, or can some be made more efficient?

Feel free to post formulas here to see if they can be made more efficient.


Hope that helps.

 

[Ruddles]

Functions, like formulae, can only affect the cell they're actually in. They can't move stuff around in a worksheet, they can't create and delete columns, rows, sheets, workbooks, files and folders or perform looping operations.

So if your requirement involves doing any of those, formulae and functions just aren't up to the job.

 

[moonfish]

I think I can describe the size of the ordeal I'm in without having to show you anything.

I have a spreadsheet that I've been building on for a few months now. It's recently reached 100MB in file size. The file consists of 21 sheets, of which 6 are source data ranging from currency history to a copy of the entire product database.
The purpose of it all is to get more insight in actual transportation costs and expected transportation costs, and use this information to create more accurate methods of calculating said expected costs.

In a nutshell this means 747848 instances of index(match) and a somewhat less frightening amount of calculations and statistical tests, with no VBA whatsoever.

Where possible I've tried to replace formulas with values but I've already had that backfiring on me several times while trying to trace my steps. For this reason I've decided not to worry so much about calculation time (shift+F9 has become one of my closest friends and if all else fails I can read the newspaper I keep bringing).

 

[Jonmo1]

Here's one GREAT improvement in performance you can make...
I'm guessing you have some formulas like...

=INDEX(Sheet1!B:B,MATCH(A1,Sheet1!$A:$A,0))
And that is filled right for several columns..
so it changes to
=INDEX(Sheet1!C:C,MATCH(A1,Sheet1!$A:$A,0))
=INDEX(Sheet1!D:D,MATCH(A1,Sheet1!$A:$A,0))
etc...


If this is the case, I recommend putting the match in it's own column, say column Z
Z1: =MATCH(A1,Sheet1!A:A,0)
And fill down as far as needed
Then use
=INDEX(Sheet1!A:A,$Z1)
And fill right.

This reduces the amount of calculations tremendously, by doing the Match only once per row.
Instead of 1 for every column and row.

Hope that helps.

 

[moonfish]

Thanks for that! There's a few instances where I can use that.

Assuming that all rows below 50000 are completely empty, is there a noticeable difference between referring to A:A and referring to, say, $A1:$A50000?

 

[Jonmo1]

Yes, you'll almost always be better off using a finite range, instead of an entire column.

It shouldn't make much difference in INDEX..
but in the MATCH, it would..

You might also consider using Dynamic Named Ranges
These will automatically adjust in size accordingly as you add/remove rows of data.
http://www.cpearson.com/Excel/excelF.htm#DynamicRanges

 

[moonfish]

I've looked into dynamic ranges before but the major problem here is that my source data contains too many blanks, even in places where these shouldn't occur.

 

[Jonmo1]

Do you have 1 column that can be used to determine the end of the data?
Say "I know column A always has data in the last row of my sheet" ?

Then you can define some names like this

lastrow: =LOOKUP(2,1/(Sheet1!$A$1:$A$65535<>""),ROW(Sheet1!$A$1:$A$65535))
Notice the row 65535 (1 short of the last useable row 65536 in xl2003)

Then use that in subsequent dynamic ranges to define the last row

Acol: =Sheet1!$A$1:INDEX(Sheet1!$A:$A,lastrow)


Hope that helps.