Excel VBA 模块在运行时未更新 [英] Excel VBA Module Not Updating While Running

问题描述

我有 2 个模块，主模块在运行时更新另一个模块，并在每次更新时运行该模块.

I have 2 modules, the main module updates the other module while running, and runs that module every time it updates.

问题是另一个模块在运行时似乎没有更新(它运行第一个模块，因为输出都是根据第一个输入).但是运行完成后，我检查了另一个模块并进行了更新.但是输出不是根据更新后的模块.

The problem is that the other module seems to not being updated while running (it runs the very first module, since the outputs are all according to the first input). But after the run is completed, I checked the other module and it is updated. But the output is not according to that updated module.

我已经问过这个问题，但没有得到答案.VBA 函数模块未计算所有输出值

I already asked the question, but did not get an answer. VBA Function Module Not Calculating All Output Values

我发现了一个类似的问题，但该解决方案在我的情况下不起作用.excel vba 代码模块在运行期间未更新

I found a similar question but the solution did not work in my case. excel vba code module not updated during run

Option Explicit

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)
    CodeString = CodeStringGenerator(KWATT)

    ''Update the module code
    With ActiveWorkbook.VBProject.VBComponents("MyNewTest").CodeModule
        .DeleteLines 1, .CountOfLines
    End With

    With ActiveWorkbook.VBProject.VBComponents("MyNewTest").CodeModule
        nextline = .CountOfLines + 1
        .InsertLines nextline, CodeString
    End With

CallOtherModule x
''Calling the function in the second module (where the code was copied).
'''Cannot call the function directly from this sub, since excel will 
''''crash:Call MyNewTest.SortedArray(x)

Next x


End Sub

''''''''''''''''''''''''''''''''''''''''''''''''''''''
Public Sub CallOtherModule(ItemsCounter As Long)
    Call MyNewTest.SortedArray(ItemsCounter)
End Sub

''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''The function that writes the code of the second module as String
Function CodeStringGenerator(KWATT As Double) As String

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

End Function

在工作表 4 中，(input,output) (First Column,Second Column) 是:18, 23；20、23；10、23；9、23；9,23;10,23.

In sheet 4, the (input,output) (First Column,Second Column) is: 18, 23; 20, 23; 10, 23; 9, 23; 9,23; 10,23.

不过应该​​是18、23；20、25；10、15；9、14；9,14;10,15.

However, it should be 18, 23; 20, 25; 10, 15; 9, 14; 9,14; 10,15.

这些示例只是为了说明问题.

These are examples just to show the problem.

推荐答案

虽然对动态编写代码的危险给出了 +1，但更改方法名称似乎会强制重新编译:

While giving a +1 to the perils of dynamically writing code, changing the method name seems to force a recompile:

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

这篇关于Excel VBA 模块在运行时未更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！