可以在工作表上输入VBA UDF结合单元格引用和局部变量(锯齿数组)?

Question

我创建了一个公共函数，用来创建一个锯齿数组。

Public Type CpnData
'coupon info
cpn_no_prime As Integer
fc_cpn_nbr As Integer
'departure info
dep_airpt As String
dep_date As Date
dep_time As String
'arrival info
arr_airpt As String
arr_date As Date
'carrier info
mkt_flt_carr As String
mkt_flt_nbr As Integer
op_flt_carr As String
op_flt_nbr As Integer
End Type
'Coupon numbers
Public Type cpnNo
cpn_nbr() As CpnData
End Type

Public Function FetchCpnData(ByRef tkt As cpnNo) As Variant
'Declare varbs
Dim wsCpn As Worksheet
Dim cpnCnt As Integer
Dim cnt As Integer
Dim i As Integer
Dim lRow As Integer

'Assign varbs
Set wsCpn = ThisWorkbook.Worksheets("tCpn")
cnt = 1
With wsCpn
cpnCnt = WorksheetFunction.CountIf(.Range(Range("tCpn_Fc_Ind").Value2), "Y")
lRow = .Range("F1") + 2
End With
ReDim tkt.cpn_nbr(cpnCnt)

With wsCpn
For i = 3 To lRow
If .Cells(i, Range("tCpn_Fc_Ind").Column).Value2 = "Y" Then
'coupon infot
tkt.cpn_nbr(cnt).cpn_no_prime = .Cells(i, Range("tCpn_Nbr_Prime").Column).Value2
tkt.cpn_nbr(cnt).fc_cpn_nbr = cnt
'depart info
tkt.cpn_nbr(cnt).dep_airpt = .Cells(i, Range("tCpn_Dep_Airpt").Column).Value2
tkt.cpn_nbr(cnt).dep_date = .Cells(i, Range("tCpn_Dep_Date").Column).Value2
tkt.cpn_nbr(cnt).dep_time = .Cells(i, Range("tCpn_dep_time").Column).Value2
'arriv info
tkt.cpn_nbr(cnt).arr_airpt = .Cells(i, Range("tCpn_Arr_Airpt").Column).Value2
'carrier info
tkt.cpn_nbr(cnt).mkt_flt_carr = .Cells(i, Range("tCpn_Mkt_Flt_Carr").Column).Value2
tkt.cpn_nbr(cnt).mkt_flt_nbr = .Cells(i, Range("tCpn_Mkt_Flt_Nbr").Column).Value2
tkt.cpn_nbr(cnt).op_flt_carr = .Cells(i, Range("tCpn_Op_Carr").Column).Value2
tkt.cpn_nbr(cnt).op_flt_nbr = .Cells(i, Range("tCpn_Op_Flt_Nbr").Column).Value2
cnt = cnt + 1
End If
Next i
End With

End Function

然后在单元格中输入4个其他函数，这些函数使用上述函数，通过调用函数 访问锯齿数组。例如:

Public Function DateRange(geo_cpn As String, geo_str As String, eval_cpn As String, fr_yy As String, fr_mm As String, fr_dd As String, to_yy As String, to_mm As String, to_dd As String, tvl_prt As String) As String
'Declare variables
Dim tkt As cpnNo

'Assign variables
Call FetchCpnData(tkt)

因为我在其他4个函数中使用第一个函数中创建的数组，所以在4个不同的场合调用它似乎是多余的。因此，是否有可能通过引用工作表函数来传递它?我试过了，但是在工作表上，输入要求tkt作为输入。

感谢任何建议或输入

Answer 1

您可以通过为数据使用公共变量来执行如下操作。在每次使用UDF时，它检查数据是否已经加载，如果没有则加载。因此，数据只在UDF的第一次加载，其他所有时间它都使用已经加载的公共变量的数据。

注意，不考虑数据的变化。因此，如果你改变了一些加载的数据，它将不会使用它，直到你再次运行LoadDataInArray。

每个Excel会话只加载一次数据(当您打开文件并且第一次计算工作表时)。

Option Explicit
' create a public variable
Public myArray() As Variant
' create a sub that loads data into that public array
Public Sub LoadDataInArray()
myArray = Array("test1", "test2", "test3")
End Sub

' create your udf that you use like a formula in Excel cells
Public Function udf_UseCreatedArray() As String

' check if th array was initialized (has data already)
If Not Not myArray Then
' do nothing
Else
' if it has no data load data
LoadDataInArray
End If

' use your array data
udf_UseCreatedArray = Join(myArray)  ' as example just return joined array data in cell
End Function