Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

In add_in.xlam I have:

Type Quantity
    i As Integer
end type

Sub show_quant(q as Quantiy)
    MsgBox(q.i)
end sub

In another workbook, I do:

Sub load_addin()
    Dim AI As Excel.AddIn
    Set AI = Application.AddIns.Add(Filename:="add_in.xlam")
    AI.Installed = True

    Dim q As Quantity
    show_quant(q)
End Sub

This doesn't work. Excel complains that the User defined type hasn't been defined yet.

So how do you declare variables of a type defined in an add in?

share|improve this question

The only way I've ever been able to get something like that to work is to follow the instructions on this page: http://fontstuff.com/vba/vbatut08.htm Specifically, you have to give the vba project name a new name, and you need to add it as a reference, using that name. The website explains it better.

Another page discussing the same thing: calling-add-in-functions-from-vba

share|improve this answer

This worked for me without manually adding the reference:

Private Sub Workbook_Open()
    Dim success As Boolean
    Dim myAddIn As AddIn

    ' Load and install new XLAM
    Set myAddIn = AddIns.Add(Filename:=ActiveWorkbook.Path & "\path\to\myAddin.xlam")
    myAddIn.Installed = True

    ' Load known XLAM
    For Each myAddIn In AddIns
        If myAddIn.Name = "myAddin.xlam" Then
            myAddIn.Installed = False
            myAddIn.Installed = True
        End If
    Next
    Call AddReference(ActiveWorkbook.Path & "\path\to\myAddin.xlam")
End Sub

I struggled because AddIns.Add needs an absolute path... But it seems to work!


Edit : I also needed to use and call:

Sub AddReference(AddInPath As String)
    ThisWorkbook.VBProject.References.AddFromFile (AddInPath)
End Sub

Source: https://blogs.msdn.microsoft.com/accelerating_things/2010/09/16/loading-excel-add-ins-at-runtime/

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .