InteropReference Example
ManfredU
I just discovered ExcelDNA and it seems to be a great tool! Creation
of UDFs is very easy and works fine for me.
I've got an issue with the InteropReference example though, which does
not seem to work for me. The ' "Hello from Excel version " &
app.Version' part does work, but ' "Cell A1 contains: " & r.Value'
seems to abend. The sub stops and I can't see an error message.
Another strange symptom:
'ExcelDnaUtil.Application.ActiveCell = "Hello!" ' does work, but
'app.ActiveCell = "Hello!" 'causes an compile time error ("error
BC30526: Property 'ActiveCell' is 'ReadOnly'.").
My environment: Win XP SP3, Excel 2007, ExcelDNA 0.28
My test script:
-------------------------------------------------
<DnaLibrary Language="VB">
<Reference Name="Microsoft.Office.Interop.Excel" />
<Reference Name="System.Windows.Forms" />
<!-- <Reference Name="MyMathLib" /> -->
<!-- <Reference Path="OtherLibs\MyOtherLib.dll" /> -->
<![CDATA[
Imports System.Windows.Forms
Imports Excel = Microsoft.Office.Interop.Excel
Public Module TestInterop
<ExcelCommand(MenuText:="Say Hello")> _
Public Sub ShowMessageBox()
Dim app as Excel.Application
dim r as Excel.Range
Dim strNow as String
app = ExcelDnaUtil.Application
r = app.Cells(1,1)
strNow = Format(Now(), "yyyy-MM-dd-hh.mm.ss")
'does not work:
'MessageBox.Show("Hello from Excel version " & app.Version & ".
Cell A1 contains: " & r.Value)
'does work:
MessageBox.Show("Hello from Excel version " & app.Version )
'does work:
MessageBox.Show("Cell A1 row: " & r.Row)
'does NOT work (sub abends at runtime, can't see an error
message):
'MessageBox.Show("Cell A1 contains: " &
ExcelDnaUtil.Application.Cells(1,1))
'does NOT work (sub abends at runtime, can't see an error
message):
'MessageBox.Show("Cell A1 contains: " & r.Value)
'does work:
ExcelDnaUtil.Application.ActiveCell = strNow
'does NOT work (compile time error):
'There were errors when compiling project: error BC30526:
Property 'ActiveCell' is 'ReadOnly'.
'app.ActiveCell = strNow
End Sub
End Module
]]>
</DnaLibrary>
-------------------------------------------------
Thanks,
Manfred
Govert van Drimmelen
You're diving into the subtle differences between VBA and VB.NET, with
the .NET/COM interaction in the middle...
You can catch the exceptions that would otherwise terminate the macro
by wrapping your code with
Try
... your code
Catch ex As Exception
MessageBox.Show("Exception caught: " & e.ToString()
End Try
Here's what I see when running the code: (Excel 2007, VS 2010)
* the first place you use r.Value and say 'does not work' works fine
for me.
* your "..." & ...Cells(1,1) fails with an exception because the
compiler does not use the default property when trying to figure out
how to concatenate with the & operator. If you said ".."
& ....Cells(1,1).Value, it works fine.
* The "... contains: " & r.Value call works fine for me.
* The compiler error is a VB.NET / VBA story. In VBA you have to say
Set xxx = MyObject
when assigning an object, but you say
xxx = "Hello"
and that could refer to the default property of xxx, equivalent to
xxx.Value = "Hello"
In VB.NET you never need to use 'Set', but in this case you need to
state the property explicitly, so this works:
app.ActiveCell.Value = strNow
* There is a difference between ExcelDnaUtil.Application.ActiveCell
and app.ActiveCell: in the first case your call is resolved late-bound
at runtime, in the second case the type is known at compile-time, so
the compiler sees the uncertainty in how to interpret the assignment
and gives the error. Late-bound, at runtime, it seems to resolve the
default property correctly.
So this does not explain all the differences between what you see and
what I see - there are two places where r.Value worked fine for me but
not for you. I can only guess that you has some funny value in cell
A1, or that you have some older version of the Primary Interop
Assemblies on you machine. You might like to try r.Value2 - that used
to be needed from C# since Value is actually a "parameterized
property".
Please write again if we need to explore further.
Regards,
Govert
Manfred Usselmann
thanks a lot for the fast and detailed response and taking the time to
analyse my issue.
> Here's what I see when running the code: (Excel 2007, VS 2010)
> * the first place you use r.Value and say 'does not work' works fine
> for me.
The exception I get for 'r.Value' is:
System.Runtime.InteropServices.COMException (0x80028018): Old format or invalid type library. (Exception from HRESULT: 0x80028018 (TYPE_E_INVDATAREAD))
at System.RuntimeType.ForwardCallToInvokeMember(String memberName, BindingFlags flags, Object target, Int32[] aWrapperTypes, MessageData& msgData)
at Microsoft.Office.Interop.Excel.Range.get_Value(Object RangeValueDataType)
at TestInterop.ShowMessageBox()
I'm not sure what causes this.
> * your "..." & ...Cells(1,1) fails with an exception because the
> compiler does not use the default property when trying to figure out
> how to concatenate with the & operator. If you said ".."
> & ....Cells(1,1).Value, it works fine.
Without .Value:
System.InvalidCastException: Der Operator & ist für Zeichenfolge Cell A1 contains: und Typ Range nicht definiert.
at Microsoft.VisualBasic.CompilerServices.Operators.InvokeUserDefinedOperator(UserDefinedOperator Op, Object[] Arguments)
at Microsoft.VisualBasic.CompilerServices.Operators.ConcatenateObject(Object Left, Object Right)
at TestInterop.ShowMessageBox()
This is what you explained. Don't know why a part of the error message
is in German, Excel and WinXP are English versions, but I've got a
German version of VS 2010 installed.
But with .Value I get another error (the same as for r.Value):
System.Runtime.InteropServices.COMException (0x80028018): Old format or invalid type library. (Exception from HRESULT: 0x80028018 (TYPE_E_INVDATAREAD))
at Microsoft.VisualBasic.CompilerServices.LateBinding.LateGet(Object o, Type objType, String name, Object[] args, String[] paramnames, Boolean[] CopyBack)
at Microsoft.VisualBasic.CompilerServices.NewLateBinding.LateGet(Object Instance, Type Type, String MemberName, Object[] Arguments, String[] ArgumentNames, Type[] TypeArguments, Boolean[] CopyBack)
at TestInterop.ShowMessageBox()
Same error with .Value2
> In VB.NET you never need to use 'Set', but in this case you need to
> state the property explicitly, so this works:
>
> app.ActiveCell.Value = strNow
This removes the compiler error, but causes the following runtime error:
System.Runtime.InteropServices.COMException (0x800A03EC): Exception from HRESULT: 0x800A03EC
at System.RuntimeType.ForwardCallToInvokeMember(String memberName, BindingFlags flags, Object target, Int32[] aWrapperTypes, MessageData& msgData)
at Microsoft.Office.Interop.Excel.Range.set_Value(Object RangeValueDataType, Object )
at TestInterop.ShowMessageBox()
> * There is a difference between ExcelDnaUtil.Application.ActiveCell
> and app.ActiveCell: in the first case your call is resolved late-bound
> at runtime, in the second case the type is known at compile-time, so
> the compiler sees the uncertainty in how to interpret the assignment
> and gives the error. Late-bound, at runtime, it seems to resolve the
> default property correctly.
Thanks for the explanation! Does this difference also explain why
ExcelDnaUtil.Application.ActiveCell = strNow
is working fine, but
app.ActiveCell.Value = strNow
does not?
Anyway, I found a solution: I just changed my regional settings from
German to English, so that it fits to Windows and Excel (got the idea
from Google) and guess what, the problem went away. Now how weird is
this!
Since I want to keep my regional settings I just change them
temporarily inside the sub:
------------------------------------------
<ExcelCommand(MenuText:="Say Hello")> _
Public Sub ShowMessageBox()
Dim app as Excel.Application
dim r as Excel.Range
Dim originalCulture As CultureInfo
Try
originalCulture = Thread.CurrentThread.CurrentCulture
Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US")
app = ExcelDnaUtil.Application
r = app.Cells(1,1)
'now it works: :-)
MessageBox.Show("Hello from Excel version " & app.Version & ". Cell A1 contains: " & r.Value)
Catch ex As Exception
MessageBox.Show("Exception caught: " & ex.ToString())
Finally
Thread.CurrentThread.CurrentCulture = originalCulture
End Try
End Sub
------------------------------------------
Unfortunately this is not a generic solution, just a workaround on my
system. The Add-In will be used across different countries, but since
it's the same company and all sites should have the default English
software installed I still may be lucky...
Thanks,
Manfred