Results 1 to 12 of 12
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    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
    Regards,
    Rudi

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MultiCalc Function (Excel 2000 >)

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

    =SUM(Calc_Range)

    in the worksheet.
    Legare Coleman

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    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!
    Regards,
    Rudi

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    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>

    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: MultiCalc Function (Excel 2000 >)

    OK, as usual, your arguments make good sense.
    Regards,
    Rudi

  7. #7
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    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!
    Regards,
    Rudi

  8. #8
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    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>)
    Jerry

  9. #9
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    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>!!!
    Regards,
    Rudi

  10. #10
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    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.
    Jerry

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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. #12
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    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
    Jerry

Posting Permissions

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