View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
[email protected] matthewgdodds@hotmail.com is offline
external usenet poster
 
Posts: 1
Default 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