0

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
smttsp
  • 4,011
  • 3
  • 33
  • 62
  • Is this what you are looking for: http://stackoverflow.com/questions/28326429/vba-pass-array-by-reference-and-modify-contents? – Ralph Mar 12 '17 at 13:48
  • Hi, I have tried that but I am getting a compile error. – Hammad Awan Mar 12 '17 at 14:37
  • 2
    What is wrong with `Sub Modify_Mycalculation(Get_DataArray as variant) and removing `Dim Get_DataArray` in `Modify_Mycalculation` ? –  Mar 12 '17 at 14:40
  • Hi! This has worked. I have spent a lot of time on it. Can you please explain why I was getting an error. – Hammad Awan Mar 12 '17 at 14:48
  • 1
    When you are tempted to use global variables with subs, it is an indication that some of those subs might be better refactored so as to be functions. – John Coleman Mar 12 '17 at 15:10

0 Answers0