我有两个模块,主模块在运行时更新另一个模块,并在每次更新时运行该模块。
问题是,另一个模块在运行时似乎没有更新(它运行第一个模块,因为输出都是根据第一个输入)。但是在运行完成后,我检查了另一个模块,它被更新了。但输出不是根据更新后的模块。
我已经问过这个问题了,但没有得到答案。VBA Function Module Not Calculating All Output Values
我发现了一个类似的问题,但解决方案在我的情况下不起作用。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中,(输入,输出)(第一列,第二列)是: 18,23;20,23;10, 23;9,23;9,23;10,23。
但是,它应该是18,23;20,25;10, 15;9,14;9,14;10,15。
这些都是用来说明问题的例子。
https://stackoverflow.com/questions/56532672
复制相似问题