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页中,(输入,输出)(第一列,第二列)是: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屋!