运行时Excel VBA模块未更新

内容来源于 Stack Overflow,并遵循CC BY-SA 3.0许可协议进行翻译与使用

  • 回答 (1)
  • 关注 (0)
  • 查看 (26)

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

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

我已经问了这个问题,但没有得到答案。 VBA功能模块不计算所有输出值

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

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中,(输入,输出)(第一列,第二列)是:18,23; 20,23; 10,23; 9,23; 9,23; 10,23。

但是,它应该是18,23; 20,25; 10,15; 9,14; 9,14; 10,15。

这些只是为了说明问题的例子。

提问于
用户回答回答于

当添加a+1给动态编写代码的,改变方法的名称可能会迫使重新编译:

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

扫码关注云+社区

领取腾讯云代金券

年度创作总结 领取年终奖励