We help IT Professionals succeed at work.
Get Started

Create a UDF with array argument

VBAnewbie21777
on
211 Views
Last Modified: 2012-05-08
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

Open in new window

Comment
Watch Question
Grand Poobah
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011
Commented:
This problem has been solved!
Unlock 1 Answer and 4 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE