A VBA code to list all add-ins in Excel

Microsoft Excel can receive additional commands and capabilities through the use of add-ins. Because Excel does not make its add-ins immediately accessible by default, you will need to first install and then (in certain situations) activate the add-ins in question before you can make use of them. A group of AddIn objects that collectively represents all of the add-ins that can be used with Microsoft Excel, regardless of whether or not the add-ins have been installed.

We can list all add-ins in an Excel sheet through a VBA code. So let?s see the VBA code and how it works in this tutorial.

Step 1 

Open an Excel sheet and press Alt and F11 key (Alt+F11) to open Microsoft Visual Basic for Application windows. See the below given image.

Step 2 

After that, select Insert > Module from the menu bar to bring up the popup Module window. See the following image.

Step 3 

After opening the Module window, type the following VBA code in it.

<span class="typ">Public</span><span class="pln"> </span><span class="typ">Sub</span><span class="pln"> </span><span class="typ">ListAddins</span><span class="pun">()</span><span class="pln">
</span><span class="typ">Dim</span><span class="pln"> xWSh </span><span class="typ">As</span><span class="pln"> </span><span class="typ">Worksheet</span><span class="pln">
</span><span class="typ">Dim</span><span class="pln"> xWB </span><span class="typ">As</span><span class="pln"> </span><span class="typ">Workbook</span><span class="pln">
</span><span class="typ">Dim</span><span class="pln"> xAddin </span><span class="typ">As</span><span class="pln"> </span><span class="typ">AddIn</span><span class="pln">
</span><span class="typ">Dim</span><span class="pln"> xCOMAddin </span><span class="typ">As</span><span class="pln"> </span><span class="typ">COMAddIn</span><span class="pln">
</span><span class="typ">Dim</span><span class="pln"> xFA</span><span class="pun">,</span><span class="pln"> xFCA </span><span class="typ">As</span><span class="pln"> </span><span class="typ">Integer</span><span class="pln">
</span><span class="typ">Dim</span><span class="pln"> xI </span><span class="typ">As</span><span class="pln"> </span><span class="typ">Integer</span><span class="pln">
</span><span class="typ">Dim</span><span class="pln"> xStr </span><span class="typ">As</span><span class="pln"> </span><span class="typ">String</span><span class="pln"> 

</span><span class="typ">On</span><span class="pln"> </span><span class="typ">Error</span><span class="pln"> </span><span class="typ">Resume</span><span class="pln"> </span><span class="typ">Next</span><span class="pln"> 
</span><span class="typ">Application</span><span class="pun">.</span><span class="typ">DisplayAlerts</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="kwd">False</span><span class="pln">
xStr </span><span class="pun">=</span><span class="pln"> </span><span class="str">"Addins List"</span><span class="pln">
</span><span class="typ">Set</span><span class="pln"> xWB </span><span class="pun">=</span><span class="pln"> </span><span class="typ">Application</span><span class="pun">.</span><span class="typ">ActiveWorkbook</span><span class="pln">
</span><span class="typ">Set</span><span class="pln"> xWSh </span><span class="pun">=</span><span class="pln"> xWB</span><span class="pun">.</span><span class="typ">Worksheets</span><span class="pun">.</span><span class="typ">Item</span><span class="pun">(</span><span class="pln">xStr</span><span class="pun">)</span><span class="pln">
</span><span class="typ">If</span><span class="pln"> </span><span class="typ">Not</span><span class="pln"> xWSh </span><span class="typ">Is</span><span class="pln"> </span><span class="typ">Nothing</span><span class="pln"> </span><span class="typ">Then</span><span class="pln">
   xWSh</span><span class="pun">.</span><span class="typ">Delete</span><span class="pln">
</span><span class="typ">End</span><span class="pln"> </span><span class="typ">If</span><span class="pln">
</span><span class="typ">Set</span><span class="pln"> xWSh </span><span class="pun">=</span><span class="pln"> xWB</span><span class="pun">.</span><span class="typ">Worksheets</span><span class="pun">.</span><span class="typ">Add</span><span class="pln">
xWSh</span><span class="pun">.</span><span class="typ">Name</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> xStr
xWSh</span><span class="pun">.</span><span class="typ">Range</span><span class="pun">(</span><span class="str">"A1"</span><span class="pun">).</span><span class="typ">Value</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="str">"Name"</span><span class="pln">
xWSh</span><span class="pun">.</span><span class="typ">Range</span><span class="pun">(</span><span class="str">"B1"</span><span class="pun">).</span><span class="typ">Value</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="str">"FullName"</span><span class="pln">
xWSh</span><span class="pun">.</span><span class="typ">Range</span><span class="pun">(</span><span class="str">"C1"</span><span class="pun">).</span><span class="typ">Value</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="str">"Installed"</span><span class="pln">
</span><span class="typ">For</span><span class="pln"> xFA </span><span class="pun">=</span><span class="pln"> </span><span class="lit">1</span><span class="pln"> </span><span class="typ">To</span><span class="pln"> </span><span class="typ">Application</span><span class="pun">.</span><span class="typ">AddIns</span><span class="pun">.</span><span class="typ">Count</span><span class="pln">
   </span><span class="typ">Set</span><span class="pln"> xAddin </span><span class="pun">=</span><span class="pln"> </span><span class="typ">Application</span><span class="pun">.</span><span class="typ">AddIns</span><span class="pun">(</span><span class="pln">xFA</span><span class="pun">)</span><span class="pln">
   xI </span><span class="pun">=</span><span class="pln"> xFA </span><span class="pun">+</span><span class="pln"> </span><span class="lit">1</span><span class="pln">
   </span><span class="typ">Range</span><span class="pun">(</span><span class="str">"A"</span><span class="pln"> </span><span class="pun">&</span><span class="pln"> xI</span><span class="pun">).</span><span class="typ">Value</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> xAddin</span><span class="pun">.</span><span class="typ">Name</span><span class="pln">
   </span><span class="typ">Range</span><span class="pun">(</span><span class="str">"B"</span><span class="pln"> </span><span class="pun">&</span><span class="pln"> xI</span><span class="pun">).</span><span class="typ">Value</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> xAddin</span><span class="pun">.</span><span class="typ">FullName</span><span class="pln">
   </span><span class="typ">Range</span><span class="pun">(</span><span class="str">"C"</span><span class="pln"> </span><span class="pun">&</span><span class="pln"> xI</span><span class="pun">).</span><span class="typ">Value</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> xAddin</span><span class="pun">.</span><span class="typ">Installed</span><span class="pln">
</span><span class="typ">Next</span><span class="pln"> xFA
xFA </span><span class="pun">=</span><span class="pln"> </span><span class="pun">(</span><span class="pln">xFA </span><span class="pun">+</span><span class="pln"> </span><span class="lit">2</span><span class="pun">)</span><span class="pln">
xWSh</span><span class="pun">.</span><span class="typ">Range</span><span class="pun">(</span><span class="str">"A"</span><span class="pln"> </span><span class="pun">&</span><span class="pln"> xFA</span><span class="pun">).</span><span class="typ">Value</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="str">"Description"</span><span class="pln">
xWSh</span><span class="pun">.</span><span class="typ">Range</span><span class="pun">(</span><span class="str">"B"</span><span class="pln"> </span><span class="pun">&</span><span class="pln"> xFA</span><span class="pun">).</span><span class="typ">Value</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="str">"progID"</span><span class="pln">
xWSh</span><span class="pun">.</span><span class="typ">Range</span><span class="pun">(</span><span class="str">"C"</span><span class="pln"> </span><span class="pun">&</span><span class="pln"> xFA</span><span class="pun">).</span><span class="typ">Value</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="str">"Connect"</span><span class="pln">
</span><span class="typ">For</span><span class="pln"> xFCA </span><span class="pun">=</span><span class="pln"> </span><span class="lit">1</span><span class="pln"> </span><span class="typ">To</span><span class="pln"> </span><span class="typ">Application</span><span class="pun">.</span><span class="typ">COMAddIns</span><span class="pun">.</span><span class="typ">Count</span><span class="pln">
   xI </span><span class="pun">=</span><span class="pln"> xFCA </span><span class="pun">+</span><span class="pln"> xFA 
   </span><span class="typ">Set</span><span class="pln"> xCOMAddin </span><span class="pun">=</span><span class="pln"> </span><span class="typ">Application</span><span class="pun">.</span><span class="typ">COMAddIns</span><span class="pun">(</span><span class="pln">xFCA</span><span class="pun">)</span><span class="pln">
   </span><span class="typ">Range</span><span class="pun">(</span><span class="str">"A"</span><span class="pln"> </span><span class="pun">&</span><span class="pln"> xI</span><span class="pun">).</span><span class="typ">Value</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> xCOMAddin</span><span class="pun">.</span><span class="typ">Description</span><span class="pln">
   </span><span class="typ">Range</span><span class="pun">(</span><span class="str">"B"</span><span class="pln"> </span><span class="pun">&</span><span class="pln"> xI</span><span class="pun">).</span><span class="typ">Value</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> xCOMAddin</span><span class="pun">.</span><span class="pln">progID
   </span><span class="typ">Range</span><span class="pun">(</span><span class="str">"C"</span><span class="pln"> </span><span class="pun">&</span><span class="pln"> xI</span><span class="pun">).</span><span class="typ">Value</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> xCOMAddin</span><span class="pun">.</span><span class="typ">Connect</span><span class="pln">
</span><span class="typ">Next</span><span class="pln"> xFCA
</span><span class="typ">Application</span><span class="pun">.</span><span class="typ">DisplayAlerts</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="kwd">True</span><span class="pln">
</span><span class="typ">End</span><span class="pln"> </span><span class="typ">Sub</span>

See the following image to understand how it's done.

Step 4 

After adding the VBA code run the code by pressing F5 or click Run. See the following image.

You can save the above VBA code. To save the VBA code, go to "File > Save".

Step 5 

Now go to the excel sheet which was created. We can see here the list of all add-ins installed. See the following image.