Invert the area on excel

Skip to first unread message

Phạm Minh Tùng

Oct 19, 2021, 12:11:09 PMOct 19
to Excel-DNA
I have built Sheet1 object as global variable, I write function in 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)"
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)
        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
0 new messages