I have 2 subs. The first sub reads the data from a worksheet and after calculations makes an array. In the second one, I want to access that array (or data) modify further, however, I am not successful so far. One solution is to introduce a global variable but I would avoid it. So my question is how do I run Sub MyCalculation from Sub Modify_MyCalculation and get the data into Get_DataArray array. The code is shown below.
Option Explicit
Option Base 1
Sub MyCalculation()
' This code is giving output in MyCalcualtion
Dim CycleCounts As Variant
Dim StatusIndicator As Variant
Dim Calc() As Double
Dim NumberAtRisk() As Integer
Dim InverseRank() As Integer
Dim pj() As Double
Dim Rank() As Integer
Dim i As Integer
Dim DataArray As Variant
'Reads data from Table "TempData" in worksheet
CycleCounts = Range("TempData").Columns(1).Value
StatusIndicator = Range("TempData").Columns(2).Value
ReDim InverseRank(UBound(CycleCounts))
ReDim pj(UBound(CycleCounts))
ReDim Rank(UBound(CycleCounts))
ReDim Calc(UBound(CycleCounts))
For i = LBound(CycleCounts) To UBound(CycleCounts)
InverseRank(i) = (UBound(CycleCounts) + 1) - i
Rank(i) = i
Next i
For i = LBound(CycleCounts) To UBound(CycleCounts)
If StatusIndicator(i, 1) = 1 Then
pj(i) = Round(InverseRank(i + 1) / InverseRank(i), 3)
Else: pj(i) = 1
End If
Next i
Calc(1) = pj(1)
For i = 2 To UBound(CycleCounts)
Calc(i) = Round(pj(i) * Calc(i - 1), 3)
Next i
ReDim DataArray(LBound(CycleCounts) To UBound(CycleCounts), 1 To 3)
For i = 1 To 16
DataArray(i, 1) = CycleCounts(i, 1)
DataArray(i, 2) = StatusIndicator(i, 1)
DataArray(i, 3) = Calc(i)
Debug.Print DataArray(i, 1), DataArray(i, 2), DataArray(i, 3)
Next i
ReDim MyCalculation(LBound(CycleCounts) To UBound(CycleCounts), 1 To 3)
MyCalculation = DataArray
End Sub
Sub Modify_Mycalculation()
Dim Get_DataArray(1 To 16, 1 To 3) As Variant
Get_DataArray = MyCalculation
End Sub