返回变量矩阵下标超出范围（错误9） [英] returning a variant matrix Subscript out of range (Error 9)

问题描述

我正在尝试从函数返回一个矩阵并在代码的最后一行获取下标错误"GetData = vMatrix"。
我不知道我在这里做错了什么是代码：

私人colEntries As New Collection
Sub start（）

Dim s As String
Init ActiveSheet，s
End Sub
Public Sub Init（作为工作表，ByRef err as String）

Dim je As JournalEntries
Set je = New JournalEntries

Dim var As Variant
var = GetData（ActiveSheet）

设置colEntries = je.GetEntries（var）

结束子
公共函数GetData（）As变型

Dim vMatrix As Variant
Dim rngLastRow As Range
用于我们正在使用的范围的手柄
使用ActiveSheet.Range（"）答：C"）

'得到LastRow
设置rngLastRow = .Find（what：=" *"，searchorder：= xlByRows，searchdirection：= xlPrevious）

'如果范围包含数据，则填充我们的变体arra y
如果不是rngLastRow什么都没有那么就是
vMatrix = .Resize（rngLastRow.Row - .Row + 1，.Columns.Count）
如果结束
GetData = vMatrix
结束功能

---

McC

解决方案

Hello McC，

您需要修改最后一行，如下所示。

• GetData = vMatrix - > 在GetData（）函数中设置GetData = vMatrix 。

年份"""""""""""""""""""""""""""""""""""""""""""""""""" '''''''''''''''''''''''''''公共功能GetData（）作为变体

作为变体的暗淡vMatrix
Dim rngLastRow作为范围

'我们正在处理的范围的句柄
使用ActiveSheet.Range（"A：C"）

得到LastRow
设置rngLastRow = .Find（what：=" *"，searchorder：= xlByRows，searchdirection：= xlPrevious）

'如果范围包含数据，则填充我们的变量数组
如果不是rngLastRow什么都没有那么左边是vMatrix = .Resize（rngLastRow.Row - .Row + 1，.Columns.Count）
如果结束的话/>'''''''''''''''''''''''' '''''''''''设置GetData = vMatrix
'''''''''''''' ''''''''''''''''''''>>>>>>>>>>>>>>>

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

---

McC

解决方案

Hello McC,

You need to do modify last line as below.

• GetData = vMatrix --> Set GetData = vMatrix in your GetData() Function.

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
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
''''''''''''''''''''''''''''''''''''''''''''''''
Set GetData = vMatrix
''''''''''''''''''''''''''''''''''''''''''''''''
End Function


Best Wishes.

这篇关于返回变量矩阵下标超出范围（错误9）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！