How do I convert a function into a macro

Posted on 2011-05-09
Last Modified: 2012-05-11
Pls take a look at the attached workbook. I would like to achieve the results produced by the "GetSeats" function iwith a macro. How ould i write that?

Question by:gabrielPennyback

    Expert Comment

    I would suggest that you place the code into one of the ...

      Private Sub Workbook_Activate()
      Private Sub Worksheet_Activate()

    events to have it automatically trigger or add a button similar to "Function Group" to trigger the code manually.  Don't forget to add a loop for the range of cells that you need to read from (col B) and write to (col F).

    Good Luck ! ! !
    LVL 41

    Accepted Solution

    Add this to your suite of macros - note the change to AssignGroups() subroutine you have:

    Sub AssignGroups()
        Call getSeats_Macro(Range("B6:B15"))
    End Sub
    Sub getSeats_Macro(rng As Range)
    Dim myCell As Range
        For Each myCell In rng
            Cells(myCell.Row, "F").Value = GetSeats(myCell)
        Next myCell
    End Sub

    Open in new window

    See attached file:


    LVL 1

    Author Comment

    Thanks, Dave, this great. I love compact and simple and I noticed that I can condense it even further as shown below.  At the same time I have a feeling that using the intermediate macro might be necessary in certain cases.
    If you can educate me a little on why and when that would be true (if it is true), I'd appreciate it.

    Also please look at this related question that I just posted.

    LVL 1

    Author Comment

    LVL 41

    Expert Comment

    Macro vs function? Off the top of my head:

    - the function consumes resources when dependent variables change, or if its a volatile function, then anytime the worksheet calculates.  The macro can be run on demand and thus consume less resources.  You could also call the macro on the sheet activate event, so anytime you went to the tab where the macro was needed, it would update.  If there's really no need to update the cells until you're "ready" to review output, then the macro can be of good benefit over function.  

    Another thought - More intensive functions that may invoke optimization routines, etc., may not be desired as real-time functions...

    At the end of the day, your work process that you're building the support spreadsheet around may give you more clues as to whether you need to update in real time as opposed to "On Demand".


    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Suggested Solutions

    Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
    This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
    Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
    This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now