How to pass array from C # (VSTO) project to VBA Macro
I am having performance issues with my VSTO solution, I believe the reason is mainly how cellColor is set cell by cell.
It depends on the data from the recordset and is thus different each time. (I cannot use copyFormats from another row / column)
it is similar to filling a range of values, only there are several methods for it.
I was thinking about creating everything in C # in memory first (array XlColorIndex [,]), which I move to a VBA method like the one below:
Sub fillInterior(ByRef rg As Range, a As Variant)
//a is a double array that represents the colors for the spreadsheet
Dim r As Long, c As Long
Dim tmpRg As Range
r = 1
c = 1
For Each Row In a
For Each colorIdx In Row
Set tmpRg = rg(r, c)
With tmpRg.Interior
.ColorIndex = colorIdx
.PatternColorIndex = xlAutomatic
.PatternColor = xlSolid
End With
c = c + 1
Next
c = 1
r = r + 1
Next
End Sub
I'm trying to call this macro as follows, but hasn't been successful yet, any pointers are welcome:
Excel.Range rg = this.Range[this.Cells[5, 3], this.Cells[6, 4]];
object[,] test2 = new object[2, 2];
test2[0, 0] = 15;
test2[0, 1] = 15;
test2[1, 0] = 15;
test2[1, 1] = 15;
this.Application.Run("Sheet1.fillInterior", rg, test2,
System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing,
System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing,
System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing,
System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing);
I tried int [,] -
I got another error when I tried Nullable int or Double: double? [,] (Array of Nullable Double):
The parameter is incorrect. (Exception from HRESULT: 0x80070057 (E_INVALIDARG))
If I'm not trying to use nullable types, I got the following HRESULT error (type missmatch?)
Exception from HRESULT: 0x800A000D
source to share
Okay, I'm better off reading this better: options should be avoided, so if I have a choice to write my VBA, I'd be better off doing it without an option, but with an appropriate array.
Second, I misused VBA arrays, I have to do the following for multidimensional arrays ( source ):
My VBA code now looks like this:
Sub fillInteriorMulti(rg As Range, Arr() As Long)
Dim N As Long, Ndx1 As Long, Ndx2 As Long
Dim icol As Long
Dim irow As Long
Dim NumDims As Long
// Custom Function: Get the number of array dimensions.
// NumberOfArrayDimensions will return 0
// if the array is not allocated.
NumDims = NumberOfArrayDimensions(Arr:=Arr)
Select Case NumDims
Case 0
// unallocated array
Exit Sub
Case 1
// single dimensional array
For N = LBound(Arr) To UBound(Arr)
With rg(N, 1).Interior
.ColorIndex = Arr(N)
.PatternColorIndex = xlAutomatic
.PatternColor = xlSolid
End With
Next N
Case 2
// 2 dimensional array
For Ndx1 = LBound(Arr, 1) To UBound(Arr, 1)
For Ndx2 = LBound(Arr, 2) To UBound(Arr, 2)
With rg(Ndx1, Ndx2).Interior
.ColorIndex = Arr(Ndx1, Ndx2)
.PatternColorIndex = xlAutomatic
.PatternColor = xlSolid
End With
Next Ndx2
Next Ndx1
Case Else
// Too many dimensions - Do Nothing
End Select
End Sub
Public Function NumberOfArrayDimensions(Arr As Variant) As Integer
// NumberOfArrayDimensions
// This function returns the number of dimensions of an array. An unallocated dynamic array
// has 0 dimensions. This condition can also be tested with IsArrayEmpty.
Dim Ndx As Integer
Dim Res As Integer
On Error Resume Next
// Loop, increasing the dimension index Ndx, until an error occurs.
// An error will occur when Ndx exceeds the number of dimension
// in the array. Return Ndx - 1.
Do
Ndx = Ndx + 1
Res = UBound(Arr, Ndx)
Loop Until Err.Number <> 0
NumberOfArrayDimensions = Ndx - 1
End Function
Finally, the C # code to test this:
int[] test3 = new int[3];
test3[0] = 15;
test3[1] = 15;
test3[2] = 48;
int[,] test4 = new int[2, 2];
test4[0, 0] = 15;
test4[0, 1] = 15;
test4[1, 0] = 15;
test4[1, 1] = 15;
this.Application.Run("Sheet1.fillInteriorMulti", rg, test4,
missing, missing, missing, missing, missing, missing, missing, missing, missing, missing,
missing, missing, missing, missing, missing, missing, missing, missing, missing, missing,
missing, missing, missing, missing, missing, missing, missing, missing);
source to share
Ugly hack or proof of concept, converting a double array to a string. Pass a string.
Sub fillInteriorString(rg As Range, str As String)
Dim i As Long, j As Long
i = 1
j = 1
a = Split(str, "@")
For Each part In a
b = Split(part, ",")
For Each colorIdx In b
With rg(i, j).Interior
.ColorIndex = colorIdx
.PatternColorIndex = xlAutomatic
.PatternColor = xlSolid
End With
j = j + 1
Next
j = 1
i = i + 1
Next
End Sub
C # code:
string testString = "15,15,48@48,48,15";
this.Application.Run("Sheet1.fillInteriorString", rg, testString,
System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing,
System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing,
System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing,
System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing);
Anyway, I know it works - I now go through SafeArrays in the hope that I can pass an array instead of a string.
source to share