Calling Back into an Excel Macro or Function with Excel DNA

[Paul Wolff]

Hi Govert,

I'd like to use Excel macros and functions in vb.net with Excel DNA.  For example the vb.net code is called from Excel and I want to use an Excel Function in the vb.net code.  Is this possible with Excel DNA?  I tried it with the code below and application.run doesn't work.

Paul

'*************************************************************************************

Imports Microsoft.Office.Interop

Imports ExcelDna.Integration

'Sum all numeric values in a 2-D array, excluding numbers formatted as dates

Public Module MyFunctions

    Dim Application As Excel.Application = ExcelDnaUtil.Application

    <ExcelCommand(MenuName:="Useful Macros", MenuText:="App Run")>

    Public Sub AppRun()

        Dim wks As Excel.Worksheet

        Dim rng As Excel.Range

        wks = Application.ActiveSheet

        wks.Cells(1, 1) = 5

        wks.Cells(2, 1) = 10

        rng = Application.Range(wks.Cells(1, 1), wks.Cells(2, 1))

        Application.Run("sum", rng)

    End Sub

End Module

'**************************************************************

[Govert van Drimmelen]

Hi Paul,

You might try Application.WorksheetFunction.Sum(rng).

-Govert

---

From: exce...@googlegroups.com [exce...@googlegroups.com] on behalf of Paul Wolff [pauljos...@gmail.com]
Sent: 08 October 2014 10:37 PM
To: exce...@googlegroups.com
Subject: [ExcelDna] Calling Back into an Excel Macro or Function with Excel DNA

--
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 post to this group, send email to exce...@googlegroups.com.
Visit this group at http://groups.google.com/group/exceldna.
For more options, visit https://groups.google.com/d/optout.

[Paul Wolff]

Hi Govert,

Thank you for the quick reply again.

The example I attached may have been a bit misleading.  My goal is not just to access some of the excel built in functions but to access many functions and subroutines I've developed in VBA and call them from vb.net using Excel DNA.

Is it possible to call an excel vba subroutine using Excel DNA?  With COM I've used both application.run and by calling a macro in a worksheet module.

Paul

[Govert van Drimmelen]

Hi Paul,

VBA functions should work fine through Application.Run(...).

For macros (Subs in VBA) I'm a bit less sure.

Sometime it works when you say

    Application.Run( "mymacro")

Other times you might need to use the workbook!macro format:

    Application.Run( "test.xls!mymacro")

or if the file has a space, you need an extra set of single quotes:

    Application.Run("'text file.xls'!mymacro)

I'm not sure what you mean by:

"With COM I've used..."

When talking to Excel from your Excel-DNA add-in using the ExcelDnaUtil.Application object, you're directly talking to the Excel COM object model.

-Govert

---

From: exce...@googlegroups.com [exce...@googlegroups.com] on behalf of Paul Wolff [pauljos...@gmail.com]

Sent: 08 October 2014 11:02 PM
To: exce...@googlegroups.com
Subject: [ExcelDna] Re: Calling Back into an Excel Macro or Function with Excel DNA

--

[Paul Wolff]

Thank you Govert,

Application.run works with a vba macro.

Paul

On Wednesday, October 8, 2014 4:37:03 PM UTC-4, Paul Wolff wrote:

[Paul Wolff]

Thank You Govert,

Application.run works with my vba macros.

Paul

On Wednesday, October 8, 2014 4:37:03 PM UTC-4, Paul Wolff wrote:

[Paul Wolff]

Hi Govert,

I'm a bit confused.

I can develop COM components in visual studio with vb.net by using the COM class template.  I can develop components with similar functionality using Excel DNA.

On Wednesday, October 8, 2014 4:37:03 PM UTC-4, Paul Wolff wrote:

[Paul Wolff]

On Wednesday, October 8, 2014 5:48:57 PM UTC-4, Paul Wolff wrote:

Hi Govert,

 I'm a bit confused.

I can develop COM components in visual studio with vb.net by using the COM class template.  I can develop components with similar functionality using Excel DNA which also is based on COM.  What's the advantage of Excel DNA?

Paul 

[Govert van Drimmelen]

Hi Paul,

 

Excel-DNA is not based on COM. But for automating Excel it can use either the C API or the COM object model. Mostly the COM object model is more powerful and flexible.

 

Some advantages of using Excel-DNA:

·         You can make high-performance UDF worksheet functions in .NET.

·         Advanced features like multi-threaded calculation and native async UDF support is included.

·         You can create single-file add-ins (including Ribbon extensions, RTD servers) that require no installation or admin permissions to run.

 

Although it’s possible to make UDF functions with ‘Automation add-ins’ there are various limitations to this.

 

You might like to try different ways of creating your add-in, to better understand the advantages and disadvantages of each.

 

-Govert

 

 

From: exce...@googlegroups.com [mailto:exce...@googlegroups.com] On Behalf Of Paul Wolff
Sent: 8 October 2014 23:52
To: exce...@googlegroups.com
Subject: [ExcelDna] Re: Calling Back into an Excel Macro or Function with Excel DNA

 

--

[Paul Wolff]

Thank you Govert,

Paul

On Wednesday, October 8, 2014 4:37:03 PM UTC-4, Paul Wolff wrote: