Missing VBA Classes in Excel.Interop

[Ricardo Gerbaudo]

Hello,

I'm working on a migration of a VBA project to Excel-DNA, and it makes use of some (apparently) unsupported new Excel features like the CommentsThreaded class. It seems that the Excel.Interop won't be updated anymore, so I would like to ask you all what have you been doing to overcome those limitations in Excel-DNA add-ins? Is there any complimentary library that provides access to the entire VBA Object Model?

[Govert van Drimmelen]

Hi Ricardo,

 

One option is to use late-binding with the ‘dynamic’ type to make these calls.

 

        [ExcelCommand(ShortCut = "%+C")] // Alt + Shift + C

        public static void SetCommentsThreadedDynamic()

        {

            dynamic app = ExcelDnaUtil.Application;

            dynamic ws = app.Worksheets[1];

            ws.Range["B4"].AddCommentThreaded("Sales");

        }

 

Another is to directly reference the Excel COM Interop library from your project, instead of the ExcelDna.Interop package.

This will build your project with the version of the object model that is running on your machine:

 

  <ItemGroup>

    <COMReference Include="Microsoft.Office.Interop.Excel">

      <WrapperTool>tlbimp</WrapperTool>

      <VersionMinor>9</VersionMinor>

      <VersionMajor>1</VersionMajor>

      <Guid>00020813-0000-0000-c000-000000000046</Guid>

      <Lcid>0</Lcid>

      <Isolated>false</Isolated>

      <EmbedInteropTypes>true</EmbedInteropTypes>

    </COMReference>

  </ItemGroup>

 

 

        [ExcelCommand(ShortCut = "^+C")] // Ctrl + Shift + C

        public static void SetCommentsThreaded()

        {

            Application app = ExcelDnaUtil.Application as Application;

            Worksheet ws = app.Worksheets[1];

            ws.Range["D5"].AddCommentThreaded("Income");

            ws.Range["D6"].AddCommentThreaded("Expenditure");

            CommentsThreaded cts = ws.CommentsThreaded;

            ws.Range["D7"].Value = cts.Count;

        }

 

-Govert

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/exceldna/0763df4d-84f5-410c-8a83-942c618fdd03n%40googlegroups.com.

[Ricardo Gerbaudo]

OMG Govert, you are amazing! I was about to give up migrating from VBA to Excel-DNA. Thanks a lot. 

I joined your GitHub sponsorship program last month and will gladly continue to be part of it.