Create a UDF with array argument

I am trying to create a UDF that has arrays as arguments and I'm not sure how to write this.
Also - is there any reason you can't call fomulas using arrays within a udf (like index and choose).

I'm attaching code.  The arguments followed by parentheses are intended to be arrays. I'm new to VBA so if the code is awful - sorry!

'this function should be identical to NetWorkDays in Excel
Public Function TotalWorkdays(FirstDay As Date, LastDay As Date, Holidays() As Date) As Integer
    Dim totaldays As Integer
    totaldays = 0
    Dim i As Date
    i = FirstDay
 
    For i = FirstDay To LastDay
        If Application.WorksheetFunction.Weekday(i, 2) < 6 Then
        totaldays = totaldays + 1 - Application.WorksheetFunction.SumProduct((Holidays = i) * 1)
    Next i
 
    TotalWorkdays = totaldays
   
 
End Function
 
'This function will calculate trade price of a cargo.
'It can calculate for deals priced off a front month deal, up to two contracts.
'Excludes Deal Diff
Public Function TradePrice(Today As Date, FirstPriceDay As Date, _
    LastPriceDay As Date, AllPriceDays As Integer, FirstContract As Variant, _
    FirstContractExpire As Date, FirstDays As Integer, NextContract As Variant, _
    Dates() As Double, Contracts() As Double, _
    PriceIndex() As Double, Market As Integer, Holiday1() As Double, _
    Holiday2() As Double, Holiday3() As Double) As Double
    
    'choose holidays for correct market
    Dim Holidays(27) As Double
    Holidays = Application.WorksheetFunction.Choose(Market, Holiday1, Holiday2, Holiday3)
    
    'variables for price lookup
    Dim PickRow As Integer
    Dim PickColumn As Integer
    Dim PickColumn2 As Integer
    Dim SelectedPrices(1 To 356) As Double
    Dim SelectedPrices2(1 To 356) As Double
    
    'price components for 1st contract and 2nd contract
    Dim Price1 As Double
    Dim Price2 As Double
 
    PickRow = Application.WorksheetFunction.Match(Today, Dates, 0)
    PickColumn = Application.WorksheetFunction.Match(FirstContract, Contracts, 0)
    PickColumn2 = Application.WorksheetFunction.Match(NextContract, Contracts, 0)
    SelectedPrices = Application.WorksheetFunction.Index(PriceIndex, , PickColumn)
    SelectedPrices2 = Application.WorksheetFunction.Index(PriceIndex, , PickColumn2)
    
'One contract
 
    'pricing off one contract prior to pricing days
    If (AllPriceDays = FirstDays And Today <= FirstPriceDay) Then
    TradePrice = Application.WorksheetFunction.Index(PriceIndex, PickRow, PickColumn)
    End If
    
    'pricing off one contract during pricing days
    If (AllPriceDays = FirstDays And Today > FirstPriceDay And Today < LastPriceDay) Then
    TradePrice = (Application.WorksheetFunction.SumProduct((Dates >= FirstPriceDay) * 1, _
    (Dates < Today) * 1, SelectedPrices) + Application.WorksheetFunction.Index(PriceIndex, _
    PickRow, PickColumn) * TotalWorkdays(Today, LastPriceDay, _
    Holidays)) / AllPriceDays
    End If
    
    'pricing off one contract after pricing days are complete
    If (AllPriceDays = FirstDays And Today >= LastPriceDay) Then
    TradePrice = (Application.WorksheetFunction.SumProduct((Dates >= FirstPriceDay) * 1, _
    (Dates <= LastPriceDay) * 1, SelectedPrices)) / AllPriceDays
    End If
    
 
'Two contracts
 
    'pricing off two contracts and before first pricing day
    If (AllPriceDays <> FirstDays And Today < FirstPriceDay) Then
    Price1 = Application.WorksheetFunction.Index(PriceIndex, PickRow, PickColumn)
    Price2 = Application.WorksheetFunction.Index(PriceIndex, PickRow, PickColumn2)
    TradePrice = (Price1 * FirstDays + Price2 * (AllPriceDays - FirstDays)) / AllPriceDays
    End If
    
    'pricing off two contracts and during pricing period, prior to front month expiration
    If (AllPriceDays <> FirstDays And Today >= FirstPriceDay And Today < FirstContractExpire) Then
    Price1 = (Application.WorksheetFunction.SumProduct((Dates >= FirstPriceDay) * 1, _
    (Dates < Today) * 1, SelectedPrices) + Application.WorksheetFunction.Index(PriceIndex, _
    PickRow, PickColumn) * TotalWorkdays(Today, FirstContractExpire, _
    Holidays)) / FirstDays
    Price2 = Application.WorksheetFunction.Index(PriceIndex, PickRow, PickColumn2)
    TradePrice = (Price1 * FirstDays + Price2 * (AllPriceDays - FirstDays)) / AllPriceDays
    End If
    
    'pricing off two contracts and during pricing period, after front month expiration
    If (AllPriceDays <> FirstDays And Today >= FirstContractExpire And Today < LastPriceDay) Then
    Price1 = (Application.WorksheetFunction.SumProduct((Dates >= FirstPriceDay) * 1, _
    (Dates <= FirstContractExpire) * 1, SelectedPrices)) / FirstDays
    Price2 = (Application.WorksheetFunction.SumProduct((Dates > FirstContractExpire) * 1, _
    (Dates < Today) * 1, SelectedPrices2) + Application.WorksheetFunction.Index(PriceIndex, _
    PickRow, PickColumn2) * TotalWorkdays(Today, LastPriceDay, _
    Holidays)) / (AllPriceDays - FirstDays)
    TradePrice = (Price1 * FirstDays + Price2 * (AllPriceDays - FirstDays)) / AllPriceDays
    End If
    
    'pricing off two contracts and after pricing period
    If (AllPriceDays <> FirstDays And Today >= LastPriceDay) Then
     Price1 = (Application.WorksheetFunction.SumProduct((Dates >= FirstPriceDay) * 1, _
    (Dates <= FirstContractExpire) * 1, SelectedPrices)) / FirstDays
    Price2 = (Application.WorksheetFunction.SumProduct((Dates > FirstContractExpire) * 1, _
    (Dates <= LastPriceDay) * 1, SelectedPrices2)) / (AllPriceDays - FirstDays)
    TradePrice = (Price1 * FirstDays + Price2 * (AllPriceDays - FirstDays)) / AllPriceDays
    End If
    
End Function

Select all Open in new window