VBA Function range parameter to array

Question

Looking to grab several ranges and then manipulate them within a function by storing answers to mathematical manipulation in an array... However I think my probelm lies in the fact that I can't directly input values from a range into an array... Have tried to google the problem but to no avail. Would appreciate any light on the topic...

Function Monthsinhand(TotalDemand As Double, CusDemand As Range, Index As Integer, CusStock As Range, PoolStock As Double)

Dim DI As Integer

Dim DS As Integer

Dim IMIH As Double

Dim CusDemand1 As Variant

Dim CusStock1 As Variant

Dim Months As Variant

Dim i As Integer

DI = CusDemand.Cells.count

CusDemand1 = CusDemand

CusStock1 = CusStock

IMIH = (CusStock(Index) / CusDemand(Index)) * 12

If CusDemand(Index) = 0 Then

Monthsinhand = "No Demand"

Else

For i = 1 To DI

Months(i) = CusStock1(i) / CusDemand1(i)

Next

Monthsinhand = Months(1)

End If

End Function

Thanks

Answer 1

A function can only return one value to the calling variable -MonthInHand in this case.

You also has to store CusStock in an array to manipulate the data. I have changed the function, so the last argument is the customer number for which to return the result.

If further testing is needed, it would be good with some data and expected results.

Function MonthsInHand(p_CusDemand As Range, p_CusStock As Range, p_PoolStock As Double, l_OutputIndex As Long)

   Dim a_NumCusts As Integer 'Integer to hold number of customers being considered

   Dim a_MIH As Variant 'declaring an array to recieve the result of a "months in hand" calculation (Stock divided by yearly demand)

   Dim a_I As Integer

   Dim a_X As Integer

   Dim a_P As Integer 'Pool Stock counter

   Dim a_MinIndex As Integer 'Minimum index counter

   Dim a_CusStock As Variant 'Declare CusStock array

   a_NumCusts = p_CusDemand.Cells.Count 'Storing the number of customers in the a_CusDemand range

   ReDim a_MIH(a_NumCusts)

   a_CusStock = p_CusStock.Value 'Store CusStock in the array

   a_MinIndex = 1 'set minimum index counter to 1

   a_P = p_PoolStock 'set the Pool stock counter to the initail value of Pool Stock

   For a_X = 1 To p_PoolStock 'allocates the pool stock one item at a time to the customer stock with the lowest months in hand

         For a_I = 1 To a_NumCusts 'calculats the current customer month in hand within the array a_MIH

            If p_CusDemand(a_I) = 0 Then  'checks if the customer demand is 0, if so puts the 0 in the customer position in the array a_MIH

               a_MIH(a_I) = 0

            Else  'demand is greater than 0 it calculates the months in hand for that customer and puts in into the array a_MIH

               a_MIH(a_I) = (a_CusStock(1, a_I) / p_CusDemand(a_I)) * 12

                  If a_MIH(a_I) < a_MIH(a_MinIndex) Then 'calculates which customer has the lowest months in hand and stores the array index value in a_MinIndex

                     a_MinIndex = a_I

                  End If

            End If

          Next

      a_CusStock(1, a_MinIndex) = a_CusStock(1, a_MinIndex) + 1 'added 1 to the stock of the customer with the lowest months in hand

      a_P = a_P - 1 'take 1 from the pool stock counter

   Next

      'p_Output = WorksheetFunction.Transpose(a_MIH) 'output array a_MIH in a range of cells passed through to the function

    MonthsInHand = a_MIH(l_OutputIndex)

   End Function

Answer 2

With horizontal data input, we will have a two dimensional array.  See it as a worksheet with rows and columns. In your example 1 row with 6 columns. So to refer to an element in the array we need to use both row and columns: array(1,column)

Hint: Add a watch to the array variables, and insert a breakpoint after the arrays has been loaded, to see how data are organized in the array.

Function Monthsinhand(TotalDemand As Double, CusDemand As Range, Index As Integer, CusStock As Range, PoolStock As Double) As Variant

Dim DI As Integer

Dim DS As Integer

Dim IMIH As Double

Dim CusDemand1 As Variant

Dim CusStock1 As Variant

Dim Months As Variant

Dim i As Integer

Application.Volatile

DI = CusDemand.Cells.Count

ReDim Months(1) 'Can not use ReDim Preserve later unless the variable is initialized as an array

CusDemand1 = CusDemand.Value

CusStock1 = CusStock.Value

'IMIH = (CusStock1(Index) / CusDemand1(Index)) * 12

If CusDemand1(1, Index) = 0 Then

    Monthsinhand = "No Demand"

Else

    For i = 1 To DI

        ReDim Preserve Months(1 To i)

        If CusStock1(1, i) > 0 Then 'Avoid division by zero error

            Months(i) = CusStock1(1, i) / CusDemand1(1, i)

        End If

    Next

    Monthsinhand = Months(1)

End If

End Function

Answer 3

Thank you, Thank you, Thank you. Works like a dream! Wow I have ben pondering how to acomplish this probelm for a long long time and now finally this UDF has given me that ability.

I need to put this function in 6 cells then in order to take out the contents of all six cells in the output array. But this means that the whole calculation needs to be done 6 times very time inefficient... Can I be cheeky and ask one final question... do you have any suggestions as to the best way to out put the other answers from the function?

Answer 4

This is now the completed function... Ive been trying to debug it but to no avail... any suggestions?

' MonthsInHand - this function will look to divide the pool stock proportinally amongst the customers with the least number of months in hand when consideration has been taken of there current stock levels in relation to demand.

'   p_CusDemand - a horizontal range containing the individual customer yearly demands (currently 6 customers but would like allow for future expansion)

'   p_CusStock - a horizontal range containing the individual costomers stock levels (indexes align with p_CusDemand)

'   p_PoolStock - current pool stock level

Function MonthsInHand(p_CusDemand As Range, p_CusStock As Range, p_PoolStock As Double, p_Output As Range)

   Dim a_NumCusts As Integer 'Integer to hold number of customers being considered

   Dim a_MIH As Variant 'declaring an array to recieve the result of a "months in hand" calculation (Stock divided by yearly demand)

   Dim a_I As Integer

   Dim a_X As Integer

   Dim a_P As Integer 'Pool Stock counter

   Dim a_MinIndex As Integer 'Minimum index counter

   a_NumCusts = p_CusDemand.Cells.count 'Storing the number of customers in the a_CusDemand range

   ReDim a_MIH(a_NumCusts)

   a_MinIndex = 1 'set minimum index counter to 1

   a_P = p_PoolStock 'set the Pool stock counter to the initail value of Pool Stock

   For a_X = 1 To p_PoolStock 'allocates the pool stock one item at a time to the customer stock with the lowest months in hand

         For a_I = 1 To a_NumCusts 'calculats the current customer month in hand within the array a_MIH

            If p_CusDemand(a_I) = 0 Then 'checks if the customer demand is 0, if so puts the 0 in the customer position in the array a_MIH

               a_MIH(a_I) = 0

            End If

            If p_CusStock(a_I) > 0 Then 'If the demand is greater than 0 it calculates the months in hand for that customer and puts in into the array a_MIH

               a_MIH(a_I) = (p_CusStock(a_I) / p_CusDemand(a_I)) * 12

                  If a_MIH(a_I) < a_MIH(a_MinIndex) Then 'calculates which customer has the lowest months in hand and stores the array index value in a_MinIndex

                     a_MinIndex = a_I

                  End If

            End If

          Next

      p_CusStock(a_MinIndex) = p_CusStock(a_MinIndex) + 1 'added 1 to the stock of the customer with the lowest months in hand

      a_P = a_P - 1 'take 1 from the pool stock counter

   Next

      p_Output = WorksheetFunction.Transpose(a_MIH) 'output array a_MIH in a range of cells passed through to the function

   End Function

Answer 5

That works... not entirly sure why as I thought I had tried most of the changes you have made... except the

Application.Volatile

Perhaps it was just I didn't try them all in the right order...

Thank you for solving my confusion!