Can VBA UDF entered on worksheet combine cell references and local variables (jagged array)?

Question

I have created a public function whereby I create a jagged array.

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

I then have 4 other functions entered into cells which use the above function, which access the jagged array by calling the function

For example:

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)

Because I am using the array created in the first function across 4 other ones, it seems redundant to call it on 4 separate occassions. Therefore, is it possible to pass it by reference to the worksheet functions? I tried this, however on the worksheet, the input was asking for tkt as an input.

Appreciate any advice or input

Answer 1

You can do something like below by using a public variabe for you data. In each use of the UDF it checks if the data was already loaded, if not it loads it. So the data is only loaded at the first time of the UDF and all the other times it uses the already loaded data of the public variable.

Note that does not take into account changed data. So if you change some of the loaded data it will not use that until you run LoadDataInArray again.

The data is only loaded once per Excel session (when you open your file and the sheet is calculated the first time).

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