excelvba

How to export Excel VBA script as .XLAM Add-in?

I want to distribute my VBA script as .XLAM add-in. My VBA-script contains one based on worksheet event:

 Private Sub Woorksheet_Change (ByVal Target as Range)
 ....
 End sub

Unfortunately, button "Compile VBA project" disabled, why?

Compilation disabled

And when I try to export my VBA script as Script.XLAM I receive just source code started from:

Version 1.0 Class
BEGIN
   MultiUser = -1 'True
END
ATTRIBUTE VB_NAME=....

If I try to import this Script.XLAM as Add-in to another Workbook I get error "Excel can not open the file script.xlam because the file format or file extension is not valid. Verify..."

Wrong XLAM

I can not understand what going wrong?

Solution

  • Final solution take Tim Williams Events on XLAM Add-in not connected to Workbook

     'Class module with name AppEvents
 Option Explicit
 Private AppEvt As AppEvents
 Private Sub Workbook_Open()
     Set AppEvt = New AppEvents
     Set AppEvt.App = Application
     MsgBox "Workbook_Open"
 End Sub

 'ThisWorkbook
 Option Explicit
 Public WithEvents App As Application
 Private Sub App_SheetChange(ByVal Sh As Object, ByVal Target As Range)
     MsgBox "App_WorksheetChange"
 End Sub

    XLAM