VBA UDF 返回数组 [英] VBA UDF Return Array

问题描述

我有以下 UDF，它需要遍历名为 Classes 的工作表上的所有数据，如果学生姓名显示在工作表的列表中，则返回学生姓名和班级名称(A 列和 B 列)称为时间表(此列表在单元格 BM3 到 BM21 中)并且课程发生在 UDF 中输入的日期和时间.目前它返回一个#Value 错误.我做错了什么?

函数 TTDisplay(Day As String, Time As Variant) As VariantDim 结果(1 到 12)作为字符串将学生调暗为字符串将单元格调暗为整数将 LastRow 调暗至长将类变暗为工作表昏暗的时间表作为工作表Dim x 作为整数Dim TimeSpan 作为整数将 TTTime 调暗为整数Classes = Sheets("Classes")时间表 = Sheets("时间表")LastRow = Classes.Cells(Classes.Rows.count, "A").End(xlUp).RowTTTime = TMins(时间)对于单元格 = 3 到 21学生 = 学生 &Timetable.Cells(cell, 65).value &！"下一个单元格x = 1对于单元格 = 2 到 LastRow如果 InStr(Students, Classes.Cells(cell, 2)) 那么如果 Day = Classes.Cells(cell, 9) 那么如果时间 = Classes.Cells(cell, 12) 那么结果(x) = Classes.Cells(cell, 2) &Chr(10) &Classes.Cells(cell, 1)x = x + 1别的时间跨度 = TMins(Classes.Cells(cell, 12)) + 30做 While TimeSpan <TMins(Classes.Cells(cell, 11))如果 TimeSpan = TTTime 那么结果(x) = Classes.Cells(cell, 2) &Chr(10) &Classes.Cells(cell, 1)x = x + 1转到继续别的时间跨度 = 时间跨度 + 30万一环形继续前行:万一万一万一下一个单元格TTDisplay = 结果(1)结束函数

解决方案

如果你想返回一个数组，你可以将函数定义为 Variant，但最好将你的函数头改成这个(这样更容易直接看到 return-函数类型):

函数 TTDisplay(Day As String, Time As Variant) As String()

在最后一行 (TTDisplay = Result(1)) 你只返回一个值，所以改变它以返回整个数组:TTDisplay = Result

I have the following UDF which needs to loop through all the data on the sheet called Classes and return the names of the students and the class names (columns A and B) if the students name is displayed in a list on the sheet called Timetable (this list is in cells BM3 to BM21) and the class takes place on the day and time entered in the UDF. Currently it returns a #Value error. What have I done wrong?

Function TTDisplay(Day As String, Time As Variant) As Variant

Dim Result(1 To 12) As String
Dim Students As String
Dim cell As Integer
Dim LastRow As Long
Dim Classes As Worksheet
Dim Timetable As Worksheet
Dim x As Integer
Dim TimeSpan As Integer
Dim TTTime As Integer

Classes = Sheets("Classes")
Timetable = Sheets("Timetable")
LastRow = Classes.Cells(Classes.Rows.count, "A").End(xlUp).Row
TTTime = TMins(Time)

For cell = 3 To 21
Students = Students & Timetable.Cells(cell, 65).value & "!"
Next cell

x = 1
For cell = 2 To LastRow

        If InStr(Students, Classes.Cells(cell, 2)) Then
            If Day = Classes.Cells(cell, 9) Then
                If Time = Classes.Cells(cell, 12) Then
                Result(x) = Classes.Cells(cell, 2) & Chr(10) & Classes.Cells(cell, 1)
                x = x + 1
                Else
                TimeSpan = TMins(Classes.Cells(cell, 12)) + 30
                    Do While TimeSpan < TMins(Classes.Cells(cell, 11))
                        If TimeSpan = TTTime Then
                        Result(x) = Classes.Cells(cell, 2) & Chr(10) & Classes.Cells(cell, 1)
                        x = x + 1
                        GoTo MoveOn
                        Else
                        TimeSpan = TimeSpan + 30
                        End If
                    Loop
MoveOn:
                End If
            End If
        End If

Next cell
TTDisplay = Result(1)
End Function

解决方案

If you want to return an array, you can define the function as Variant, but preferably change your function head to this (makes it easier to directly see return-type of the function):

Function TTDisplay(Day As String, Time As Variant) As String()

On the last line (TTDisplay = Result(1)) you are only returning one value, so change it to return the whole array: TTDisplay = Result

这篇关于VBA UDF 返回数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！