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

Hmm That is a good option... thank you for the work on this... I think though currently I have been tasked to try and keep the solution to this problem as simple as possible as others in the future will be taking on the running of the spreadsheet and if they need to expand the capacity the ease of copying the function down a few cells and wait a little while for the calculations to catch up (its not a volitile function so less of a problem) will probably trump the complication of needing to update a macro and function combination...

However maybe in the future this will change

Thanks for all your help its been a steep learning curve but a useful one.

Answer 4

Glad to hear that it is working as desired.

In my previous post I said that a function can only pass one value back. It is only true when the function is called from a worksheet. If we call the function from a macro the function can return an array.

First we need this statement at the very top of the module:

Option Base 1

Then we can use this macro to do the job. The only catch is that it will only update, when you run the macro. You can add a button on the sheet which can call the function.

Sub TestFunction()

Dim OutputArr As Variant

OutputArr = MonthsInHand(Range("B2:G2"), Range("I2:N2"), Range("P2"))

Range("B6").Resize(1, UBound(OutputArr)) = OutputArr

End Sub

If you want to control the input/output ranges you can use the macro below:

Sub TestFunction()

Dim OutputArr As Variant

Dim Title As String

Dim CusDemand As Range

Dim CusStock As Range

Dim OutputRng As Range

Dim PoolStock As Double

Title = "Month In Hand"

Set CusDemand = Application.InputBox("Select Customer Demand range", Title, "B2:G2", Type:=8)

Set CusStock = Application.InputBox("Select Customer Stock range", Title, "I2:N2", Type:=8)

PoolStock = Application.InputBox("Select Pool Stock cell", Title, "P2", Type:=8).Value

Set OutputRng = Application.InputBox("Select leftmost output cell", Title, "B6", Type:=8)

OutputArr = MonthsInHand(CusDemand, CusStock, PoolStock)

OutputRng.Resize(1, UBound(OutputArr)) = OutputArr

End Sub