ExcelDNA cannot recognise macro name

[Scotbert]

I am having a very odd error which I hope someone can help with.

I recently converted a vba addin with macro names to ExcelDNA,
implementing the names of the addin macros in a c# assembly referenced
from the ExcelDNA file.

When I load a new spreadsheet I can call the names through ExcelDNA
without problem

Whe I load an old spreadsheet which previously referenced the addin, I
am getting #NAME? in cells that reference the functions.
I have removed all references to the add-in, but still nothign is
called via ExcelDNA.

Does anyone know what the issue is?

Thanks

Scotbert

[Matthew Rose]

Hi Scot

I have the same symptoms when moving from a COM adding to an DLL along the lines of DNA. If you examine the XML produced by saving the sheet in pre 2007 XML you can see some namespacing along the lines of the progid. The function references in the sheet also get qualified with the same when the sheet is opened with the new addin.

I couldn't find a way around it so am going to have to provide users with an update macro to reenter all the affected formulae.

I don't know whether vba exhibits the same problem but it certainly sounds similar.

Matt

> --
> You received this message because you are subscribed to the Google Groups "Excel-Dna" group.
> To post to this group, send email to exce...@googlegroups.com.
> To unsubscribe from this group, send email to exceldna+u...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/exceldna?hl=en.
>

[Govert van Drimmelen]

Hi,

I also think you need to update the formulae if you move from
functions in a .xla or an automation add-in to having the functions in
an .xll (like Excel-Dna).

The easiest way to 're-enter' all the formulae is to 'search-and-
replace' for the "=" character.

Here is a somewhat relevant thread on Wilmott:
http://www.wilmott.com/messageview.cfm?catid=10&threadid=79763.

Govert

On Nov 8, 10:03 am, Matthew Rose <m...@rose.org.uk> wrote:
> Hi Scot
>
> I have the same symptoms when moving from a COM adding to an DLL along the
> lines of DNA. If you examine the XML produced by saving the sheet in pre
> 2007 XML you can see some namespacing along the lines of the progid. The
> function references in the sheet also get qualified with the same when the
> sheet is opened with the new addin.
>
> I couldn't find a way around it so am going to have to provide users with an
> update macro to reenter all the affected formulae.
>
> I don't know whether vba exhibits the same problem but it certainly sounds
> similar.
>
> Matt

> On 8 Nov 2010 07:33, "Scotbert" <scotbert...@googlemail.com> wrote:
>
>
>
>
>
>
>
> > I am having a very odd error which I hope someone can help with.
>
> > I recently converted a vba addin with macro names to ExcelDNA,
> > implementing the names of the addin macros in a c# assembly referenced
> > from the ExcelDNA file.
>
> > When I load a new spreadsheet I can call the names through ExcelDNA
> > without problem
>
> > Whe I load an old spreadsheet which previously referenced the addin, I
> > am getting #NAME? in cells that reference the functions.
> > I have removed all references to the add-in, but still nothign is
> > called via ExcelDNA.
>
> > Does anyone know what the issue is?
>
> > Thanks
>
> > Scotbert
>
> > --
> > You received this message because you are subscribed to the Google Groups
> "Excel-Dna" group.
> > To post to this group, send email to exce...@googlegroups.com.
> > To unsubscribe from this group, send email to
>

> exceldna+u...@googlegroups.com<exceldna%2Bunsubscribe@googlegroups.c om>
> .> For more options, visit this group at
>
> http://groups.google.com/group/exceldna?hl=en.
>
>
>
>
>
>
>
>

[Scotbert]

Thank you both for your replies.

Unfortunately, re-entering the formula in the same sheet does not
work.
My situation is complicated by the fact that the VBA addin was a "udf
bridge" to an Excel2003 VSTO addin.
The vba function names in the old spreadsheets are not qualified by
anything, but do not resolve themselves to the new ExcelDNA addin.

If I add a dummy xl vba function in the old spreadsheets with the old
name, it gets called.
I'm wondering if there is some internal namespacing or signature
difference which is stopping the sheet looking outside of the vba
project scope

Thanks again

> > exceldna+u...@googlegroups.com<exceldna%2Bunsubscr...@googlegroups.c om>

> > .> For more options, visit this group at
>

> >http://groups.google.com/group/exceldna?hl=en.- Hide quoted text -
>
> - Show quoted text -

[Govert van Drimmelen]

Mmm....

You might have to experiment a bit.

Open the sheet without having the .xla add-in open.
Type in a new formula on the sheet, which now give #NAME.
Open the Excel-Dna .xll.
Re-enter the formula you entered before - does it work now?

Or

Open the sheet without having the .xla add-in open.
Change one of the formulae to a dummy name - say =MyFunction_TEMP(...)
Save and close.
Open the Excel-Dna .xll and your sheet.
Change the formula back to =MuFunction(...) - does it work now?

At some point, one presumes, Excel will forget that the old formula
was inside an .xla.

If you're on Excel 2007 or later, you can also explore inside
the .xlsx to see how the formulae are stored.

Let us know what you find.

Govert

> > >http://groups.google.com/group/exceldna?hl=en.-Hide quoted text -

[Scotbert]

I'm completely flummoxed.

I got the sheet saved in 200 format and examined the cell contents by
unpackaging it with System.IO.Packaging.
The cell contents are just a simple call to the macro name, no
qualifiers.

Interestingly, if
- add a dummy macro inside the spreadsheet it gets called
- add a vba xla with the same dummy macro (not the vsto bridge) it
does not get called
- add with Excel-DNA: it does not get called

I suspect it must be some sort of namespace or signature scoping

> > > >http://groups.google.com/group/exceldna?hl=en.-Hidequoted text -
>
> > > - Show quoted text -- Hide quoted text -

[Scotbert]

I forgot to mention,

If I open the function wizard on the #Name? cell the function wizard
is properly populated with the ExcelDNA signature but no function
evaluation.
After the wozard is closed, the cell remains as #Name?

Opening the wizard on a new sheet yields proper function evaluation.

> > > >http://groups.google.com/group/exceldna?hl=en.-Hidequoted text -
>
> > > - Show quoted text -- Hide quoted text -

[Scotbert]

All, just to let you know I found a fix.

The original spreadsheets are xl2003. If I open in xl2007 with macros
enabled, save, close, reopen with macros enabled and save as xl2003
the functions are referenced.

Thanks,

Joe

> > > > >http://groups.google.com/group/exceldna?hl=en.-Hidequotedtext -