calling .Net DLL

[Jurgen]

Hello,

I have some VBA code in Excel, and I have a .Net DLL with some
functions I designed in it. I can call those functions from within VBA
- no
problems so far.
But I don't succeed in passing an array (Dim A() as Double) as a
parameter to a function in the DLL. How do I do this?

e.g.
VBA:
C = MySum(A())


VB.NET DLL:
public function MySum(byval A() as double) as double


Any help is welcome!

Regards,

Jurgen

 

[RB Smissaert]

Don't know anything about .net, but you could try in the .net dll:

public function MySum(byref A as variant) as double

RBS

 

[Jonathan West]

Hi Jurgen,

Your .NET DLL would need to be set up as an ActiveX DLL. You then need to
ensure that it is registered, and then ensure that it is added to the list
of project references.

--
Regards
Jonathan West - Word MVP
www.intelligentdocuments.co.uk
Please reply to the newsgroup
Keep your VBA code safe, sign the ClassicVB petition www.classicvb.org

 

[Jurgen]

Hi Jurgen,

Your .NET DLL would need to be set up as an ActiveX DLL. You then
need to ensure that it is registered, and then ensure that it is
added to the list of project references.

Hi,
sorry fir the late reaction, but have been on vacation
I did it the way you described. I can use functions with paramters as
integer or string, the problem is passing an array. I'll try it passing
the array as a variant.

regards,

Jurgen

 

[Jonathan West]

Hi,
sorry fir the late reaction, but have been on vacation
I did it the way you described. I can use functions with paramters as
integer or string, the problem is passing an array. I'll try it passing
the array as a variant.

That might not work, since VB.NET does not have the Variant datatype. The
problem with arrays is that .NET only natively supports arrays with a zero
lower bound (these are termed vectors), and VBA arrays can have a variable
lower bound (i.e. you can declare them like this Dim a(3 To 6))

I'm not at all sure how you manage this problem, as I'm not all that
familiar with VB.NET, but I would guess that there is something on it in the
..NEt documentation somewhere. Alternatively, you might try one of the dotnet
newsgroups, where I'm sure this question has been raised before.


--
Regards
Jonathan West - Word MVP
www.intelligentdocuments.co.uk
Please reply to the newsgroup
Keep your VBA code safe, sign the ClassicVB petition www.classicvb.org

 

[Jurgen]

That might not work, since VB.NET does not have the Variant datatype.
The problem with arrays is that .NET only natively supports arrays
with a zero lower bound (these are termed vectors), and VBA arrays
can have a variable lower bound (i.e. you can declare them like this
Dim a(3 To 6))

Hello Jonathan,
I managed to get it to work! (Don't ask me why, it was more a
try-and-error thing ) The Variant type indeed doesn't exists in
VB.NET, but Object does, like RBS suggested. I'll keep in mind your
suggestion, and wont use a variable lower bound in my final code.

This is my VB.NET code:
Public Function MySum(ByVal aRange As Object) As Double

Dim I As Int32
For I = 1 To UBound(aRange)
MySum = MySum + aRange(I, 1)
Next

End Function

And my Excel-VBA:

Public Sub CallMySum()
Dim TestObject As TestDLL.MyClass
Dim TempArray() As Variant
Dim aRange As Range

Set TestObject = New TestDLL.MyClass

Set aRange = Sheets("Sheet1").Range("B4:B5")
TempArray = aRange
MsgBox TestObject.MySum(TempArray())
End Sub

both VBA and .Net seem to use 1 and 2 as LBound and UBound

Thanks for the helping me out.

regards,

 

[Michael J]

Jurgen:

I'm glad you were able to get it working! Your question is very similar to
one I've been researching (without success) for some time. Would it be
possible for you to share the full VB.NET source code that worked for you?
Did you manually register the .NET DLL, or use the "dynamic" registration
approach? Also, could you please let me know what you did to inform VBA about
where to find the VB.NET code? I just can't seem to get that to work! Thanks
for any information you can provide.

Michael Johnson

Also, could you briefly describe the process you went through to register
the .NET assembly (or DLL) before calling it from Excel VBA?

 

[Jurgen]

Jurgen:

I'm glad you were able to get it working! Your question is very
similar to one I've been researching (without success) for some time.
Would it be possible for you to share the full VB.NET source code
that worked for you? Did you manually register the .NET DLL, or use
the "dynamic" registration approach? Also, could you please let me
know what you did to inform VBA about where to find the VB.NET code?
I just can't seem to get that to work! Thanks for any information you
can provide.

Michael Johnson

Also, could you briefly describe the process you went through to
register the .NET assembly (or DLL) before calling it from Excel VBA?

Hi Michael,

No problem, I'll put everything i have together and post it here, I'll
do it tomorrow, as I'm quiet busy today.

greetings,

Jurgen

 

[Michael J]

Hi Jugen,

Many thanks! Whenever you get a chance to post the information that will be
great. I really appreciate your willingness to share info about your success!

Michael