VBA References

bobtherr · February 17th, 2005, 11:29 AM

When I add a reference in VBA by checking the appropriate box under Tools/References, is there a way to tell Excel to "remember" that this reference is checked for all new worksheets the next time I open Excel?

Thanks

gcianfanelli · February 17th, 2005, 11:38 AM

Don't think you can do this out-of-the-box.

You could however create an Excel addin (.XLA) which exposes the Excel Application's NewWorkbook event, ie. create a class in your addin that reads something like:

Code:

public WithEvents ExcelApp As Excel.Application
Private Sub ExcelApp_NewWorkbook(ByVal Wb As Workbook)
  Wb.VBProject.References.AddFromFile "C:\myComponent.XLA"
End Sub

Then in the addin's Workbook_Open event, or the AddinInstall event, whatever is appropriate, initialise the ExcelApp variable with the current instance of the application, ie.

Code:

Private mAppSinkClass As clsAppSink
Private Sub Workbook_Open()
  set mAppSinkClass = New clsAppSink
  et mAppSinkClass.ExcelApp = Excel.Application
End Sub

mjppaba · February 18th, 2005, 09:33 AM

Open the VB Editor. Select the Personal.xls object. (If it does not exists then go back to excel and record a macro stating to save it in the personal macro workbook)

In the VB Editor, open a page of code in a module sitting in the personnal workbook. In the references add the reference you want. Still in the VBEditor, click the save icon to save the personal macro book. Close Excel and reopen. The Reference should still be there. Because you are associating the reference to the Personnal Macro book the reference is opened each time you open Excel.

Cheers

Matthew