How to I bind EXCEL range in VBA macro from a C# Library that returns object[,]
I have a C# library which has 2 methods. One returns an Integer and another returns a object[,]
public int ReturnInt()
{
//Something that return INT
}
public object[,] Return2DArray()
{
//returns twoDimensionalArrayResponse;
}
Now I am very new to VBA and due to some legacy reasons I need to write a VBA macro rather than VSTO excel Addin to call this Library.
Getting the result from the first meathod was easy. I made the assembly com visible and registered it for Com Interop and then added it as a reference in my VBA project and called it using the code below
Sub GXSData()
Dim InteropClass As New ExcelInterOpWrapper
Dim result As Integer
result = InteropClass.ReturnInt()
MsgBox "Rows Returned =" & CStr(result)
End Sub
So far , so good. Now I want to call the second method get the开发者_运维百科 data and bind it to excel.
Sounds simple but
--> What type should I use to hold this data. I tried Dynamic array and it did not work
--> How do I bind it to a dynamic range. Let's say the 2d array is 100*10 I want to bind it to a range something like Range.value = Array
I know how to do this in excel addin application in C# but VBA is giving me real problems
Any help on this will be great
I have found a way to do this . It is not the perfect approach but works for me for now. Due to my C# skills I wrote the function that will write in excel in C# and simply called it in VBA macro. I am sure there are pure macro ways of doing this , which I would like. This is because I am writing this as an API and MACRO clients will be calling my API. So I would like to have presentation layer in macro. But anyway the C# is here in case it is useful to someone The C# method
public static void BindDataToExcel( Excel.Range range, object[,] response)
{
int rows = response.GetLength(0);
int cols = response.GetLength(1);
int n = 0;
Excel.Range newRange = range.get_Offset(n, 0).get_Resize(rows - n, cols);
newRange.Value = response;
}
My Macro
Sub GxsData()
Dim InteropClass As New ExcelInterOpWrapper
Dim o As Object
Set o = InteropClass.BindDataToExcel(ActiveCell)
End Sub
精彩评论