Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2003

    Unanswered: returning a variant matrix Subscript out of range (Error 9)

    I am trying to return a matrix from a function and get the Subscript error on the last line of the code "GetData = vMatrix".

    Im not sure what im doing wrong here is the code:

    Private colEntries As New Collection
    Sub start()

    Dim s As String
    Init ActiveSheet, s

    End Sub
    Public Sub Init(wks As Worksheet, ByRef err As String)

    Dim je As JournalEntries
    Set je = New JournalEntries

    Dim var As Variant
    var = GetData(ActiveSheet)

    Set colEntries = je.GetEntries(var)

    End Sub
    Public Function GetData() As Variant

    Dim vMatrix As Variant
    Dim rngLastRow As Range

    'a handle on the range we are working with
    With ActiveSheet.Range("A:C")

    'get the LastRow
    Set rngLastRow = .Find(what:="*", searchorder:=xlByRows, searchdirection:=xlPrevious)

    'if the range contains data then populate our variant array
    If Not rngLastRow Is Nothing Then
    vMatrix = .Resize(rngLastRow.Row - .Row + 1, .Columns.Count)
    End If

    End With
    GetData = vMatrix
    End Function

  2. #2
    Join Date
    Sep 2008
    London, UK
    Hi Mike,

    You are passing a reference to the activesheet into your GetData() function, but the function doesn't have a parameter list?

    Also, I mentioned on the other thread that auto-instantiation is not a good thing in VBA:
    Private colEntries As New Collection
    Hope that helps...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts