Invert the area on excel

13 views
Skip to first unread message

Phạm Minh Tùng

unread,
Oct 19, 2021, 12:11:09 PMOct 19
to Excel-DNA
I have built Sheet1 object as global variable, I write function in vb.net like below, to return 1 array in vba, I got the arr array from sheet 1, but when initializing the rs array, the VBA error "type mismatch (13)"
------------------------------VB.NET------------------------------------------------------
Public Function getRange() As Object(,)
        Dim arr As Object(,), tong As Double = 0, lr As Long, lc As Integer, rs As Object(,), row As Long, col As Long
        With Sheet1
            lr = .Range("A100000").End(XlDirection.xlUp).Row
            lc = .Range("XDF1").End(XlDirection.xlToLeft).Column
            arr = .Range(.Cells(1, 1), .Cells(lr, lc)).Value
        End With
        kq = New Object(arr.GetLength(1), arr.GetLength(2)) {}  ''error
        For i As Integer = UBound(arr) To 1 Step -1
            row += 1
            col = 0
            For j As Integer = UBound(arr, 2) To 1 Step -1
                col += 1
                rs(row, col) = arr(i, j)
            Next
        Next
        Return rs
    End Function
---------------------------------------------------------------------------
    Public Book As Workbook
    Public Sheet1 As Worksheet
    Public App As Application
    Public Sub CreateWB()
        Dim ten As String = MinhTung.TenApp & ".xlsm"
        Book = MinhTung.Excel.Workbooks(ten)
        Sheet1 = Book.Sheets(1)
        'Sheet1.Range("A1").Value = "Heloo"
        App = clsMyFunction.Excel
    End Sub
-----------------------------------------VBA code ---------------------------
Sub testFunction()
    Dim arr As Variant
    arr = Application.Run("getRange")
    MsgBox arr(1, 1)
End Sub
--------------------------------------------------------------------------------------

Reply all
Reply to author
Forward
0 new messages