Excel User Defined Function to Access Function

[Tom_R]

Hi All, this is my 1st time post....but have used this site many times for
help on Access projects.

I have a situation that I am converting from an Excel file to Access database.
I use an user defined function to calculate weeks of supply. I need to
covert the formula into Access and am not knowing where to start.

The function in Excel is:

Function WOS(inv, fdemand)
'Calculates True WOS with inventory and future ships as arguments.
'inv is a single cell representing the current week's ending inventory
'fdemand is the range of future demand listed in 1 row and two or
' more columns
'if the sum of future demand won't deplete the current week's ending
' inventory the future demand is used to calculate an average
' demand and a WOS is estimated
'Data should be structured like this:
' Wk1 Wk2 Wk3 Wk4
'Inventory 10 15 10 15
'Demand 0 5 5 5
'WOS 2.0 3.0 2.0 3.0

Dim tot, i As Integer, n As Integer, x As Integer
Dim tot1
Dim tot2
Dim tot3
Dim WOS1
tot = Application.Sum(fdemand)
n = fdemand.Columns.Count
x = Application.WorksheetFunction.CountIf(fdemand, ">0")
For i = 1 To n - 1
tot1 = Application.Sum(fdemand.Resize(1, i))
tot2 = Application.Sum(fdemand.Resize(1, i + 1))
tot3 = Application.Sum(fdemand.Resize(1, 1))

' If inv <= tot1 Then
' WOS1 = i - 1 + (inv / tot1)

If inv <= tot3 Then
WOS1 = inv / tot3

ElseIf inv > tot1 And inv <= tot2 Then
WOS1 = i + ((inv - tot1) / (tot2 - tot1))
Exit For
End If
Next
If WOS1 = 0 Then
' WOS = "Fcst Error"
WOS = (inv / (tot / x))
Else
WOS = WOS1
End If
End Function

I know I can't use the function as is for Access, but what do I need to
change to get it to work in a query? Please let me know if you need
additional information.

Thanks

Tom

 

[PieterLinden via AccessMonster.com]

Tom,

If the data is still in Excel, you can declare a reference to the Excel
library in your code and then use it as if you were in Excel.... Then you
can return the result to Access. If you are trying to mimic what Excel does
inside Access, that's a different matter entirely.

 

[Tom_R via AccessMonster.com]

Thanks for the response. I am trying mimic this Excel function in Access.
This is the only thing that is stopping me from finishing this database.

The data in Excel is set up as:
Customer Metric Wk1 Wk2 Wk3 Wk4
Customer A Inventory 10 15 10 15
Customer A Demand 0 5 5 5
Customer A WOS 2.0 3.0 2.0 3.0 <---- The function
in question calculates this
Customer B Inventory 10 15 10 15
Customer B Demand 0 5 5 5
Customer B WOS 2.0 3.0 2.0 3.0 <---- The function
in question calculates this

The data in Access is set up as:

Customer Week Inventory Demand WOS
Customer A Wk1 10 0 ??
Customer A Wk2 15 5 ??
Customer A Wk3 10 5 ??
Customer A Wk4 15 5 ??
Customer B Wk1 10 0 ??
Customer B Wk2 15 5 ??
Customer B Wk3 10 5 ??
Customer B Wk4 15 5 ??

?? = where the function needs to be

I am not sure how to write that loop in Access that is in the Excel function
that goes through each demand record until it meets the requirement of being
greater then Tot1 but less then or equal to Tot2 and also a new requirement
to reset after reach customer grouping.

I do appreciate any help. Thanks again!