Excel DNA 64 bits

[Carlos]

Hi all,

I have recently migrated to Windows 7 64 bits, and I'm facing some
trouble porting my excel-dna add-ins.

My old add-ins seem to register ok, but some UDF's that work on 32bit
fail for some inputs. I haven't been able to narrow down to what
combination of inputs cause the failures (which are either of type
#NUM! or #VALUE!).

I tried using the 64 bit version of Excel DNA that's on the 0.29
preview, but I can't seem to register it as an add-in. I get
"exceldna64.xll is not a valid add-in" message if I try to register
it, and a "the file you are trying to open is in a different format
than specified by the file extension" message when I try to open it
directly.

I should mention that I'm using Excel 2007 (I saw a couple of messages
where ExcelDna64.xll is used with Excel 2010 64 bits, I'm not sure if
this should be a problem).

Best regards,
Carlos Jourdan

[Govert van Drimmelen]

Hi Carlos,

Under a 64-bit version of Windows, you can install either a 32-bit
version of Office or a 64-bit version of Office. The default, and what
Microsoft recommends, is to install the 32-bit version of Office.
That's also what you have done.

So Excel is running as a 32-bit process on your 64-bit operating
system. Then the libraries that Excel loads must also be 32-bit, so
you'll use the regular Excel-DNA .xll. The 64-bit version -
ExcelDna64.xll - is only for the 64-bit version of Excel 2010. When
you try to open the 64-bit .xll under your 32-bit Excel, it gives the
"...different format than specified..." error. Since no code in the
library ever runs, Excel-DNA can't give a nicer message.

So the add-in that loads but gives some problems is indeed the right
one to use.


Now, there should be no difference in how Excel-DNA and .NET behaves
whether you are running on 64-bit Windows or 32-bit Windows. Also,
there have been no issues reported in this configuration that I
recall. Could you be seeing some problem with some external library,
or with some interaction with the operating system. Under Windows 7
some of the paths are different or are redirected in surprising ways.
Permissions could also be an issue.

Typically, #VALUE means that an exception was thrown in your method.
You can try to wrap the code in a try-catch block, and return details
on the exception, or register a general UnhandledExceptionHandler with
the Excel-DNA runtime (see http://groups.google.com/group/exceldna/browse_thread/thread/973ca2e280a3c89f).
Could it be that you are referencing a 64-bit version of a mixed
(native and managed) assembly?

In summary - the behaviour you see with ExcelDna64.xll is what we
expect under the 32-bit version of Excel. The #NUM and #VALUE problems
in your add-in are not expected and are likely to be particular to
your configuration and functions.


Please post back if you find out anything more.

Regards,
Govert

[Carlos]

Hi Govert.

Thanks for the clarification. I'm going to try to debug my code to see
what might be causing the errors, but I can tell you in advance that
they don't depend on any external components: all they do is provide
some convenient UDFs to spare us some typing.

I'll post back once I have more info.

Carlos

> the Excel-DNA runtime (seehttp://groups.google.com/group/exceldna/browse_thread/thread/973ca2e2...).

> > Carlos Jourdan- Ocultar texto das mensagens anteriores -
>
> - Mostrar texto das mensagens anteriores -

[Carlos]

And here's another quirk: XlCall.Excel(XlCall.xlfAddress, 1, 1) fails
during the calculation cycle (after I press F9) but works when I'm
entering the formula (press F2 on the cell and then enter).

Has anything like this ever happen before?

> > - Mostrar texto das mensagens anteriores -- Ocultar texto das mensagens anteriores -

[Carlos]

So here's one error that I've pinned down so far: formulas stop
working with parameters with more than 255 characters long. For
instance, I have created a simple wrapper for the VLOOKUP function,
that looks like this:

[ExcelFunction(
Category = "Lookup & Reference",
Name = "VLookup2",
IsThreadSafe = true)]
public static object VLookup2(
object lookupValue,
[ExcelArgument(AllowReference = true)] object tableArray,
int columnNumber,
bool rangeLookup)
{
return XlCall.Excel(XlCall.xlfVlookup, lookupValue, tableArray,
columnNumber, rangeLookup);
}

And this function fails with a #NUM! (it returns null) if the return
value is longer than 255.

Any thoughts?

On 14 abr, 08:46, Carlos <jourdan.gade...@gmail.com> wrote:

> > - Mostrar texto das mensagens anteriores -- Ocultar texto das mensagens anteriores -

[Govert van Drimmelen]

Hi Carlos,

This looks like a known Excel limitation with VLOOKUP:
http://superuser.com/questions/247427/255-character-limit-on-vlookup
and Google for "Excel VLOOKUP 255" - there are many hits.

I seem to remember some of the 255 character string length limitations
in Excel 2007 being fixed in one of the service packs, but can't find
a reference now. What Excel service packs have you got loaded?

-Govert

[Carlos]

I see. The strange thing though is that if I enter the VLOOKUP formula
directly in Excel, it works even if the result is longer than 255. Its
as if the function being called by the Excel application and the one
called by XlCall are not the same.

You might be on to something with the service packs thing, since I
just noticed that I don't have any installed. I'll upgrade and post
back if anything changes. Thanks.

[Carlos]

Hi Govert.

After the update, everything seems to be working as expected. Thanks
for the tip.


Best regards,
Carlos