Custom Functions

[smpatty08]

I have some custom functions that I would like to be used in every excel file I open (if it isn't already in there). I know I can use my Personal Workbook to do this, but I frequently send/share spreadsheets with other people I work with and it is not feasible to copy the functions to everybody's personal workbook. So, my thought is that is I could have a code in my personal workbook that when I open any excel file it inputs the function codes into the ThisWorkbook module. I have tried the following code that works but it puts the macro into the ThisWorkbook module in my Personal Workbook.

Sub Add_Macro_To_ThisWorkbook()


Dim VBP As VBProject
Dim VBM As VBComponent
Dim VBModule As CodeModule
Dim VBProc As VBComponent


Set VBP = ThisWorkbook.VBProject


Set VBModule = VBP.VBComponents.Item("ThisWorkbook").CodeModule


VBModule.AddFromString ("Sub Sample_Macro" & vbCrLf & "ret = msgbox (""Hello VBADUD"") " & vbCrLf & "End Sub")


End Sub

Does anybody have any ideas how this can be done?

 

[Rick Rothstein]

I guess you could open a new workbook, add the code wherever you want it, save the workbook as a Template and then when you open a new workbook, open the Template instead and any code you added will automatically be there.