Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
functions
from the little bit of programming i have taken, i know you can combine
similar codes using functions. can someone help me with these sets of code i have? Sub SortProjectName() ActiveSheet.Cells.ClearOutline Range("A6:T1112").Sort Key1:=Range("Q7"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal End Sub Sub SortDateRec() ActiveSheet.Cells.ClearOutline Range("A6:T1112").Sort Key1:=Range("T7"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
functions
You can actually combine the code as a new sub - but what I would need to
know is how the user will interact with the sheet to determine which variation of the code is to run - how do you intend for them to choose which column to sort by? Do you want to use the selected cell to determine the sort column? Or prompt them in some way and use their response to choose? Or activate from a control, such as a button or listbox? That makes a big difference in how the code will be written. -- - K Dales "steve" wrote: from the little bit of programming i have taken, i know you can combine similar codes using functions. can someone help me with these sets of code i have? Sub SortProjectName() ActiveSheet.Cells.ClearOutline Range("A6:T1112").Sort Key1:=Range("Q7"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal End Sub Sub SortDateRec() ActiveSheet.Cells.ClearOutline Range("A6:T1112").Sort Key1:=Range("T7"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
functions
i'm sorry, they are parts of buttons. the code for each button is longer
than this, but this is one portion that is similar in both. if you could explain this part to me, i could apply it to the rest. but to answer your question, the user will pick a button to use, "sort by date" or "sort by name". also, while i have your attention, what exactly does option explicit do to the code? thank you "K Dales" wrote: You can actually combine the code as a new sub - but what I would need to know is how the user will interact with the sheet to determine which variation of the code is to run - how do you intend for them to choose which column to sort by? Do you want to use the selected cell to determine the sort column? Or prompt them in some way and use their response to choose? Or activate from a control, such as a button or listbox? That makes a big difference in how the code will be written. -- - K Dales "steve" wrote: from the little bit of programming i have taken, i know you can combine similar codes using functions. can someone help me with these sets of code i have? Sub SortProjectName() ActiveSheet.Cells.ClearOutline Range("A6:T1112").Sort Key1:=Range("Q7"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal End Sub Sub SortDateRec() ActiveSheet.Cells.ClearOutline Range("A6:T1112").Sort Key1:=Range("T7"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
functions
I'll start with the second question: Option Explicit comes at the top of a
module and it forces you to declare (Dim) all your variables in a module. Without it I could do this: Sub Test() For MyInt = 1 to 5 debug.print MyInt Next MyInt End Sub With Option Explicit I would need to do this: Sub Test() Dim MyInt as Integer For MyInt = 1 to 5 debug.print MyInt Next MyInt End Sub Basically, it forces you to follow good programming practice since you really should declare all variables anyway. One reason: in the first instance, MyInt is treated as a Variant data type, which uses more memory than in the second, where it is explicitly defined to be an integer. Now for the code - If what you want to do is replace these code fragments in your two button procedures by a single procedure, here is what you could do. As an explanation note that I am using a variable, SortCol, as a parameter in the sub to take the place of the range used for the sort key; you specify the actual range when you call the Sub ChooseSort: Sub ChooseSort(SortCol as Range) ActiveSheet.Cells.ClearOutline Range("A6:T1112").Sort Key1:=SortCol, Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal End Sub Sub Button1_Click() ' Your other code is here... ' Where you have the code you called SortProjectName instead do this: Call ChooseSort(Range"Q7") ' Finish your code... End Sub Sub Button2_Click() ' Your other code is here... ' Where you have the code you called SortDateRec instead do this: Call ChooseSort(Range"T7") ' Finish your code... End Sub If there is other "overlapping" code between the two button procedures you could incorporate that into the new sub as well. In fact, you could make a general version of the sub that sorts any specified range by any specified column: Sub FlexSort(SortRange as Range, SortCol as Range) ActiveSheet.Cells.ClearOutline SortRange.Sort Key1:=SortCol, Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal End Sub -- - K Dales "steve" wrote: i'm sorry, they are parts of buttons. the code for each button is longer than this, but this is one portion that is similar in both. if you could explain this part to me, i could apply it to the rest. but to answer your question, the user will pick a button to use, "sort by date" or "sort by name". also, while i have your attention, what exactly does option explicit do to the code? thank you "K Dales" wrote: You can actually combine the code as a new sub - but what I would need to know is how the user will interact with the sheet to determine which variation of the code is to run - how do you intend for them to choose which column to sort by? Do you want to use the selected cell to determine the sort column? Or prompt them in some way and use their response to choose? Or activate from a control, such as a button or listbox? That makes a big difference in how the code will be written. -- - K Dales "steve" wrote: from the little bit of programming i have taken, i know you can combine similar codes using functions. can someone help me with these sets of code i have? Sub SortProjectName() ActiveSheet.Cells.ClearOutline Range("A6:T1112").Sort Key1:=Range("Q7"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal End Sub Sub SortDateRec() ActiveSheet.Cells.ClearOutline Range("A6:T1112").Sort Key1:=Range("T7"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to convert cell formula functions to code functions | Excel Discussion (Misc queries) | |||
efficiency: database functions vs. math functions vs. array formula | Excel Discussion (Misc queries) | |||
Looking for a site with functions that substitute the ATP functions | Excel Worksheet Functions | |||
Public Functions As Worksheet Available Functions | Excel Programming | |||
excel functions and User defined functions | Excel Programming |