• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 198
  • Last Modified:

How do I convert a function into a macro

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?

Thanks,
John
GetSeatCodes.xls
0
gabrielPennyback
Asked:
gabrielPennyback
  • 2
  • 2
1 Solution
 
DarkMainCommented:
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 ! ! !
0
 
dlmilleCommented:
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:

Enjoy!

Dave
GetSeatCodes-r1.xls
0
 
gabrielPennybackAuthor Commented:
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.

Thanks,
John
0
 
gabrielPennybackAuthor Commented:
0
 
dlmilleCommented:
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".

Dave
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now