How do I pass an array of a user defined class type ByRef from excel vba to vb.net? I have a similar post that deals with passing double arrays here and I tried using the same method with my UDT but it doesn't work. Any ideas?
-
As a quick solution, you could always write it to a text / excel file and make .Net open it... Otherwise, you need to explain more about how you want to pass it.... Are you writing this in VBA and calling a .Net program or writing it in .Net and using Excel as an intermediate step?John Bustos– John Bustos2013-08-07 17:06:54 +00:00Commented Aug 7, 2013 at 17:06
-
I have a .net class library that I made COM visible to access in excel. I create an instance of a class and try to call a Sub routine that takes as a parameter an array of another UDT class, performs operations on the elements of the array, and returns the array.Taylor Tvrdy– Taylor Tvrdy2013-08-07 18:02:54 +00:00Commented Aug 7, 2013 at 18:02
-
I am not sure you can as far as I am aware. A UDT is not an object. You could try refactoring your VBA code and making the UDT a class and then pass to a VB Variant then any method calls would be late-bound. However, I am not sure if that would work. The problem is that yur VBA code has no way of exporting the information that defines a class (i.e. where to call such and such a method). In comparison VB/C# can deal with arrays of doubles/Collections because they follow a standard/can reference the type library respectively. Why do you need any VBA code if you are using VB.NET?Cor_Blimey– Cor_Blimey2013-08-07 18:26:04 +00:00Commented Aug 7, 2013 at 18:26
-
I am updating some old code that used to be all in excel vba. The program takes in various information from excel cells, writes to excel cells, and has visual displays on graphs as the program is running.Taylor Tvrdy– Taylor Tvrdy2013-08-07 18:51:25 +00:00Commented Aug 7, 2013 at 18:51
Add a comment
|
1 Answer
Sorry to answer my own question (again) that is very similar to the other question I linked in this question, but I was able to accomplish it in nearly the same way as the answer to the other question. I just had to change some of the refraction parameters around.
In the vb.net code I take in the parameter as an object, get that object's type (which it recognizes as an object() array) and then direct cast that object array into the desired class array using the following function in vb.net
Friend Function ComObjectArrayToPointArray(ByVal comObject As Object) As Point()
Dim thisType As Type = comObject.GetType
Dim fibType As Type = Type.GetType("System.Object[]")
Dim fibArray(0) As Point
If thisType Is fibType Then
Dim args(0) As Object
Dim numEntries As Integer = CInt(thisType.InvokeMember("Length", BindingFlags.GetProperty, _
Nothing, comObject, Nothing))
ReDim fibArray(numEntries - 1)
For j As Integer = 0 To numEntries - 1
args(0) = j
fibArray(j) = DirectCast((thisType.InvokeMember("GetValue", BindingFlags.InvokeMethod, _
Nothing, comObject, args)), Point)
Next
End If
Return fibArray
End Function