Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
common functions:where to put them?
I have written an Excel macro which is subdivided into 6 modules.
Although the functions in each are largely distinct, there are three functions which 5 of the modules use in common. These are used for reading data from/to names in a workbook (see bottom). Since all are Private, I have had to make copies of them in each of the 5 modules that use them. Other than making them Public, how would the group suggest I get away from this duplication? The idea of a code module holding common functions appeals, but how then can I call the functions from other modules? Your guidance and suggestions invited ... Thanks MGD Private Function addNameAndData(wkb As Workbook, wshIndex As Variant, name As Variant, refersto As Variant, visible As Boolean) 'adds name to specified wkb and sheet, and stores data init wkb.Sheets(wshIndex).Names.Add name:=name, refersto:=refersto, visible:=visible End Function Private Function getNameData(wkb As Workbook, wshIndex As Variant, name As Variant) As Variant 'recovers data from name specified getNameData = wkb.Worksheets(wshIndex).Evaluate(name) End Function Private Function setNameData(wkb As Workbook, wshIndex As Variant, name As Variant, refersto As Variant, visible As Boolean) 'identical to addNameAndData, but conceptually distinct in that is used to update info in an extant name wkb.Sheets(wshIndex).Names.Add name:=name, refersto:=refersto, visible:=visible End Function |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
common functions:where to put them?
instead of making your functions Private, put them in a common module
and put Option Private at the top. That way, they're unreachable to other projects (and XL's macro dialog won't list them), but any Sub in the project can utilize them. In article .com, wrote: Since all are Private, I have had to make copies of them in each of the 5 modules that use them. Other than making them Public, how would the group suggest I get away from this duplication? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
common functions:where to put them?
Thanks for the suggestion.
After posting y'day I came across a v similar solution from a posting from Chip Pearson back in Jun 2000 and have been re-rranging my macro ever since. MGD |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Second Most Common Value | Excel Discussion (Misc queries) | |||
MODE function where there are no common functions. | Excel Discussion (Misc queries) | |||
Combining two ranges based on common a common value | Excel Discussion (Misc queries) | |||
Common footer but not common margins please -(Page 1 of 2) etc | Excel Discussion (Misc queries) | |||
Common Functions and Subs | Excel Discussion (Misc queries) |