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

Just realised that my question if far to vauge and I needed to add lots more explination... hopefully this will explain my problem properly...

' 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_TotalDemand - combined customer yearly demand

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

'   p_Index - The position of the currently consider customer within the two ranges

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

'   p_PoolStock - current pool stock level

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

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

   Dim a_CusDemand1 As Variant 'declaring an arry to recieve CusDemand Range passed into the function

   Dim a_CusStock1 As Variant 'declaring an arry to recieve CusStock Range passed into the function

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

   Dim i As Integer

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

   a_CusDemand1 = CusDemand 'attempt to copy contents of CusDemand range into a_CusDemand1 array

   a_CusStock1 = CusStock 'attempt to copy contents of CusStock range into a_CusStock1 array

   If CusDemand(Index) = 0 Then 'If customer has no demand for this particular part then output to cell will be "No Demand"

     Monthsinhand = "No Demand"

   Else

   'If customer has demand for part this is the first stage of pool stock distrubution, that will eventually look to check which customer has the least months in hand in order to allocate a part from pool stock before rechecking which customer now has the least months in had etc. Current state of this function is just an attempt to put the customer months in hand into the array a_Months

   For i = 1 To a_D

ReDim Preserve a_Months(1 To i)

If a_CusStock1(i) > 0 Then

      a_Months(i) = (a_CusStock1(i, 1) / a_CusDemand1(i, 1)) * 12

   End If

   Next

   End If

Monthsinhand = a_Months(1) 'quick check to see if the if statement is doing what I want it to do by outputing to the current cell the first value in the array a_Months. This output is currently giving me #value

End Function

Answer 4

Thanks for your help... Tried the code but it is still not working... I think I failed to mention that the two ranges that I am looking to pass in are horizontal

eg  = monthsinhand(C2,D2:I2,1,L2:Q2,R2)

Answer 5

To put your values (from a column) into an one-dimensional array, you have to transpose the data. Also make sure that you are using the array variables later in the code and avoid 'Division  by zero' errors.

 Not sure if you get the desired result, but the function does work:

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

DI = CusDemand.Cells.Count

ReDim Months(0)

CusDemand1 = WorksheetFunction.Transpose(CusDemand.Value)

CusStock1 = WorksheetFunction.Transpose(CusStock)

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

If CusDemand1(Index) = 0 Then

    Monthsinhand = "No Demand"

Else

    For i = 1 To DI

        ReDim Preserve Months(i)

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

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

        End If

    Next

    Monthsinhand = Months(1)

End If

End Function