Register VBA Macro from MyModule.dna

Nov 8, 2012 at 6:51 PM

Hello,

I am packaging some C# modules into an Excel-DNA Addin, and would also like to add some top-level VBA macros for user convenience.  So MyModule.dna would be a mixed language library.  Is this possible?  For example, in the following file, I can see MyVbaAdd, MyCSharpAdd and MyCSharpHello are registered as functions, but I do not see the MyVbaHello available as a Macro.  Is there a way to register a VBA macro much like the bas files?

Thanks, Bishr

<!-- Excel DNA Document for specifing UDF and XLL Addin. -->

<DnaLibrary Name="MDEA" RuntimeVersion="v4.0">
  <ExternalLibrary Path="MyCommon.dll"/>
  <ExternalLibrary Path="MyRuntime.dll"/>
  
  <!-- add other XLL libraries HERE -->

  <Project Language="CS">
    <Reference Path="System.Windows.Forms.dll"/>
    <![CDATA[
    using System;
    using ExcelDna.Integration;
    using System.Windows.Forms;
    
    public class MyCSharpSampleScriptFunctions
    {
      [ExcelFunction(Description="MyCSharpAdd", Category="ExcelDnaTest", IsVolatile=false)]
      public static double MyCSharpAdd(double x, double y)
      {
        return x + y;
      }
      
      [ExcelFunction(Description="MyCSharpHello", Category="ExcelDnaTest", IsVolatile=false)]
      public static string MyCSharpHello()
      {
        return "Hello World!";
      }            
    }
    ]]>
  </Project>

  <Project Language="VB">
  <![CDATA[
    Public Module MDEAModule
      <ExcelFunction(IsMacroType:=True)> _
      Function MyVbaAdd(x, y)
        MyVbaAdd = x + y
      End Function

      Sub MyVbaHello
        MsgBox( "Hello world!",,"ExcelDna - Macro")
      End Sub
    End Module
  ]]>
  </Project>  
  
  <!-- IMAGES -->
  <Image Name="RefreshAll" Path="Mde.Analytics.Excel.AddIn.Resources.RefreshAll.png" Pack="false"/>
  <Image Name="Refresh" Path="Mde.Analytics.Excel.AddIn.Resources.Refresh.png" Pack="false"/>
  <Image Name="RefreshSelected" Path="Mde.Analytics.Excel.AddIn.Resources.RefreshSelected.png" Pack="false"/>
  <Image Name="Help" Path="Mde.Analytics.Excel.AddIn.Resources.Help.png" Pack="false"/>
  <Image Name="Info" Path="Mde.Analytics.Excel.AddIn.Resources.Info.png" Pack="false"/>

  <!-- RIBBON BAR -->
  <!-- This can also be loaded by Excel 2007 because of the namespace -->
  <CustomUI>
    <customUI xmlns='http://schemas.microsoft.com/office/2006/01/customui' loadImage='LoadImage'>
      <ribbon>
        <tabs>
          <tab id='tabMdea' label='MDE Analytics'>
            <group id='grpMdeaRefresh' label='Refresh'>
              <button id='btnRefreshSelected' label='Refresh Selected' size='large' image='RefreshSelected' onAction='OnRefreshSelectedEvent'/>
              <button id='btnRefreshWorksheet' label='Refresh Worksheet' size='large' image='Refresh' onAction='OnRefreshWorksheetEvent'/>
              <button id='btnRefreshWorkbook' label='Refresh Workbook' size='large' image='RefreshAll' onAction='OnRefreshWorkbookEvent'/>
            </group >
            <group id='grpMdeaAbout' label='Help'>
              <button id='btnAbout' label='About' size='large' image='Info' onAction='OnAboutEvent'/>
            </group>
          </tab>
        </tabs>
      </ribbon>
    </customUI>
  </CustomUI>
  
</DnaLibrary>
Nov 8, 2012 at 7:21 PM

Although I am curious why MyVbaHello macro is not showing in the available list of Excel sheet macros, a simple approach that may work is a 4-liner bas I pulled from a discussion on the sibling Google Group for Excel-DNA:

Attribute VB_Name = "MyModule"

Sub MyRefresh()
    Application.Run ("MyRefreshWorkbookMacroMasqueradingAsExcelDnaFunction")
End Sub
Coordinator
Nov 8, 2012 at 8:02 PM

Hi,

Macros defined in your Excel-DNA will not appear in the Alt+F8 Macro dialog. This is the case for all macros defined in .xll add-ins, and has to do with different ways in which names can be defined in Excel. However, if you type in the name of the macro into the Macro Name text box, the 'Run' button should activate and allow you to run the code.

You can also register a shortcut for the macro or run it via a menu or ribbon, or call it from VBA as you show.

-Govert