# Thread: MultiCalc Function (Excel 2000 >)

1. ## MultiCalc Function (Excel 2000 >)

Following the thread starting at <post#=517,888>post 517,888</post: >, we could set up a little function to do a lot of calculations. Specify a range and specify an operation, and the function provides the necessary answers where ever it was used.

Function MULTICALC(Calc_Range As Range, TypeCalc As String)
Select Case UCase(TypeCalc)
Case "SUM"
MULTICALC = Application.Sum(Calc_Range)
Case "AVERAGE"
MULTICALC = Application.Average(Calc_Range)
Case "COUNT"
MULTICALC = Application.Count(Calc_Range)
Case "MAX"
MULTICALC = Application.Max(Calc_Range)
Case "MIN"
MULTICALC = Application.Min(Calc_Range)
Case "PRODUCT"
MULTICALC = Application.Product(Calc_Range)
Case "MEDIAN"
MULTICALC = Application.Median(Calc_Range)
Case "MODE"
MULTICALC = Application.Mode(Calc_Range)
Case "VAR"
MULTICALC = Application.Var(Calc_Range)
Case "STDEV"
MULTICALC = Application.StDev(Calc_Range)
Case Else
MULTICALC = Evaluate("NA()")
End Select
End Function

2. ## Re: MultiCalc Function (Excel 2000 >)

Yes, but is sure is a lot less efficient than just saying:

=SUM(Calc_Range)

in the worksheet.

3. ## Re: MultiCalc Function (Excel 2000 >)

>> but is sure is a lot less efficient ???

Are you stating that it's less efficient! I don't see why! Think of 30 calculations. By changing the operation value in one cell, I can get 30 new calcs in a blink of an eye where you will need to change SUM to another operation manually!

4. ## Re: MultiCalc Function (Excel 2000 >)

It depends on the workbook and on what you want to accomplish. VBA functions are always less efficient than the built-in ones, so if you have a worksheet with lots of calculations, using MultiCalc may have a noticeable negative impact on performance. Also, the AutoCalc feature in the status bar (see screenshot) already provides part of the functionality - the default is Sum, but you can select several other functions by right-clicking it.

But if performance is not an issue, and if you want to be able to switch quickly between functions for several formulas at once, MultiCalc can be handy.

5. ## Re: MultiCalc Function (Excel 2000 >)

Yes, but:
1. How often do you actually need to do that? <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
2. Most of those can be done using SUBTOTAL with the relevant function number. However, if you wanted a slightly more generic version:
<pre>Function DoFormula(strAction As String, rngInput As Range) As Variant
Dim strFormula As String
strFormula = strAction & "(" & rngInput.Address & ")"
If IsError(Evaluate(strFormula)) Then
DoFormula = "Error - check formula!"
Else
DoFormula = Evaluate(strFormula)
End If
End Function
</pre>

6. ## Re: MultiCalc Function (Excel 2000 >)

OK, as usual, your arguments make good sense.

7. ## Re: MultiCalc Function (Excel 2000 >)

Well, you just shortened the code by 300 lines. <img src=/S/grin.gif border=0 alt=grin width=15 height=15> Congratulations!

Cheers Rory, appreciate the input!

8. ## Re: MultiCalc Function (Excel 2000 >)

Hi Rudi

Stepped in here at the tail end as usual. I have attached a simple excel wb for you to lookup which does not require code but just the subtle use of the Vloopup.

The blue section is the list of numbers I want to calculate from named as cal_range. The list in green is a list of the various calculations I would like i.e. average,count and product.

Cell A2 contains a validated drop down box which lists all the calculations in range I5:I7. B2 contains =VLOOKUP(A2,I5:J7,2,TRUE).

Now by clicking the dropdown in A2 I can select the calculation I would like. Columns I anf J can be hidden or placed in a hidden worksheet but have been left here to show you how easy it is to produce the result above just using cell formatting ( and easier than rectifying code when it goes pear shaped <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>)

9. ## Re: MultiCalc Function (Excel 2000 >)

Thanx for this interesting angle on the Q at hand Jezza! Its amazing what can be done with some inginuity and an open mind!!
Cheers

PS: but just the subtle use of the Vloopup.... I thought you said the W/B does not have any code. But you explicitly mention a loop in your reply!!! Are you trying to deceive me... <img src=/S/cranky.gif border=0 alt=cranky width=18 height=25>!!!

10. ## Re: MultiCalc Function (Excel 2000 >)

Whoops

It's amazing how a typo can change the meaning. If at all possible I always try my best to keep away from the using functions in code if it is just a matter of changing the format of my spreadsheet. Of cause you will have to have it in there at some stage for more complex features.

I wrote a little application for cell automata and it went really slowly until a colleague did it with functions within the spreadsheet and his worked 10 times faster. I think this touches on what Hans said nearer the top of this thread.

11. ## Re: MultiCalc Function (Excel 2000 >)

Yes builtin formulas, even complex ones are generally faster than user functions and macros.

A notable exception is the use the many array functions: if you have a large list and many array functions it can slow down dramatically, since each array function could have thousands of operations in it and those same operations are being repeated 1000s of times. In those cases A VB routine could be better.

The same logic goes for built-in routines (filtering, sorting, etc) instead of creating your own. Most times it is much faster to do a few extra steps, creating a new sheet, copying the appropriate data,having the excel routines work on them, then "clean up" the intermediate details, than to create you own code to do it directy. (The other advantage is that it generally is easier "logic" to code: you let excel do the "heavy lifting")

Steve

12. ## Re: MultiCalc Function (Excel 2000 >)

Yes, I totally agree with your point.

I suppose my <post#=518,368>post 518,368</post: > was a thinly veiled point to Rudi that we may sometimes go for a sledge hammer to crack a nut. Too many times users (in general) think through a problem and decide that the only way to solve it is to use VBA. Just a little innovation and a second opinion (Of course Woodys is good for objectivity) can bring out new ideas that in fact the problem could be solved in another way that does not require VBA. I suppose my example in that post proved it. Although I do not disagree with Rudi's approach (I don't know the full reason he has gone down that route, he knows the bigger picture), alternatives, in my camp, are always welcome.

Having said that though, I like mathematical proofing using Excel and coding is the only way sometimes for number hacking and cracking. I use a combination of worksheet functions to calculate "a number" and then the code to manipulate it. A mixture of both arguments but try to make a judgement.My <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15> worth

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•