Using a macro in place of formulae?

[ad9051]

Hello,

Firstly many thanks for taking a look at my post, my current problem is i deal with huge huge datsets, consisting of more than 8million cells. I have come to find that putting equations containing alot of if/or/and/isnumber functions, makes excel grind to a virtual hault. Just clicking on a cell taks 30 seconds! A friend has told me i should start making macros to perform the calculations as oppose to having thousands of formulae. I was wondering if someone could provide me with a macro to perform the functions of the following equation. Hopefully this will provide me with the ability/template, to create more macros like it in order to perform the functions that I desire.

Again many thanks for looking,

All the best

Alan

[MarvinP]

Hi Alan,
It is my understanding that Excel's native formulas are optimized to be as fast as possible. If you create User Defined Functions (UDF's) and make them Volitile, that will slow things down even more.
DonkeyOte has a link to why things slow down at http://www.decisionmodels.com/calcsecretsi.htm

My suggestion is to make Calculation Manual and enter all your numbers and only calculate when you press the F9 Key.

I deal with some large data sets and find Pivot Tables are the fastest way to get results, but your data is formatted nicely for Pivots.

[ad9051]

This is very interesting, and am now using the following macro to stop automatic recalculation

Please Login or Register  to view this content.

However i still have the need to have macros performing calculations for other reasons, including recogntion of user defined cell formatting. The information you supplied was however useful, ill boost your reputation as such!

Many thanks

Alan

[MarvinP]

If you don't want/need to recalculate the entire workbook and are only interested in a certain row or column you can use code like:

Turn calculation off with your xlManual above and then
Range("A1:A30").Calculate for a set range
or
Rows(10).Calculate for a single row
or
Columns("F").Calculate

The above range calculate examples may save lots of time if you can use them.
see http://msdn.microsoft.com/en-us/libr...ffice.11).aspx
or Range.Calculate on http://msdn.microsoft.com/en-us/library/bb687891.aspx

[ad9051]

Again this is very interesting. But i do still need a way of performing very simple calculations via a macro as oppose to in the sheet. User defined conditional formatting is one reason. But also for when i alow other members of staff to access without knowing the equation that defines the cell. (For personal security reasons)

Any help on this would be greatly appreciated.

All the best

Alan

[MarvinP]

Hi Alan,

Find the attached with 3 simple User Defined Functions (UDF). The way each one works is this.
1. In a cell , type an equal sign and then the name of the UDF name. ie "=SumByName"
2. These UDF need an argument of the name you want so you need to add a name ie "=SumByName("mary")
3. You can also put a cell that points to a name ie "=SumByName(A18)"

Using Application.Volitile will make these UDF recalculate whenever there is a change to your worksheet. If you thought things were slow before, see how much slower after using a bunch of these volitile functions.

I have set the range of where the sum, count and average are performed using a varialbe of MaxColumn and set it to 11 as per your example. You will need to change this (and also the 2) as your data moves.

The last part is, every function needs to return their calculations to the spreadsheet. You do this by using the UDF name in the code.

Here is what you wanted, but I'm not sure it really is.

[jaslake]

Hi Alan
I might be mistaken but, from reading your posts, I believe you're looking for something more like this

Please Login or Register  to view this content.

If not, throw it away. The code is based on your sample file.

[ad9051]

reputation boosted! This will give me the template i require!

Many thanks!

Alan

[ad9051]

Again thanks for the help just to prove this template has helped me form a template, and improve my understanding, i used it to achieve the results of colomn "O" i.e. the average, without needing the countif and sum listed on the sheet.

Many thanks again

Alan

Please Login or Register  to view this content.

[jaslake]

Nice Alan.

[snb]

Excel will be faster if

- you do the calculations in memory (arrays)
- if you reduce the number of writing operations:

Please Login or Register  to view this content.

[ad9051]

Hello SNB i like the idea of making it faster, however i cannot get this code to work, any chance of sticking it in the document in a format that works? Or breaking it down so i understand the code and can edit it a bit better.

Many thanks

Alan

[snb]

I improved the code to:

Please Login or Register  to view this content.

and implemented it in the attachment.