问Excel VBA模块在运行时未更新

EN

Public Sub AddNewWorkBookTEST()

    Dim nextline As Long, LastUsedRowList As Long
    Dim CodeString As String
    Dim x As Long
    Dim KWATT As Double


    Dim folderPath As String
    folderPath = Application.ActiveWorkbook.Path

    LastUsedRowList = sheet4.Cells(Rows.Count, 1).End(xlUp).Row

    For x = 1 To LastUsedRowList
        KWATT = sheet4.Cells(x, 1)
        Debug.Print KWATT
        CodeString = CodeStringGenerator(x, KWATT)
        ''Update the module code
        With ActiveWorkbook.VBProject.VBComponents("MyNewTest").CodeModule
            .DeleteLines 1, .CountOfLines
            nextline = .CountOfLines + 1
            .InsertLines nextline, CodeString
        End With
        Application.Run "MyNewTest.SortedArray_" & x, x
    Next x
End Sub


Function CodeStringGenerator(x As Long, KWATT As Double) As String
    CodeStringGenerator = "'Option Explicit" & vbCrLf & _
    "Public Function SortedArray_" & x & "(ItemsCounter As Long) As Variant()" & vbCrLf & vbCrLf _
    & "Dim TempSortedArray() As Variant" & vbCrLf _
    & "Sheet4.Cells(ItemsCounter, 2) = " & KWATT + 5 & vbCrLf _
    & "End Function" & vbCrLf
End Function

Option Explicit

Public Sub Main()
    Dim fixedElements As Variant
    fixedElements = Array(0.5, 0.75, 1#, 2#, 3#, 4#)

    Dim solutions As Variant
    solutions = SolveForLoad(totalLoad:=20, numberOfSteps:=3, _
                             fixedElements:=fixedElements)

    Dim solutionsRows As Long
    Dim solutionsCols As Long
    solutionsRows = UBound(solutions, 1) - LBound(solutions, 1) + 1
    solutionsCols = UBound(solutions, 2) - LBound(solutions, 2) + 1

    Sheet1.UsedRange.Clear

    Dim solutionArea As Range
    Set solutionArea = Sheet1.Range("A1").Resize(solutionsRows, solutionsCols)
    solutionArea = solutions

    '--- sort the solutions now, calulating std deviation and range from load
End Sub

Private Function SolveForLoad(ByVal totalLoad As Long, _
                              ByVal numberOfSteps As Long, _
                              ByRef fixedElements As Variant) As Variant
    Dim checkElements As Variant
    checkElements = Array(3, 6, 9, 12, 15)

    '--- two-dimensional array that will hold all possible results
    Dim results As Variant
    ReDim results(LBound(fixedElements) To UBound(fixedElements), _
                  LBound(checkElements) To UBound(checkElements))

    Dim i As Long
    Dim j As Long
    Dim checkResult As Double
    For i = LBound(fixedElements) To UBound(fixedElements)
        For j = LBound(checkElements) To UBound(checkElements)
            checkResult = numberOfSteps * (checkElements(j) * fixedElements(i))
            results(i, j) = checkResult
        Next j
    Next i
    SolveForLoad = results
End Function