VBA更改代码的编译是否会导致循环中的错误结果? [英] Is VBA compilation of changing code is responsible for wrong results in a loop?

问题描述

在准备SO帖子的答案时

While preparing answer of SO post Macro to Analyze and Evaluate a String with respect to Data in different cell the below code find to be working for 1st loop only. The result of the 1st loop is getting carried forward till the last.To keep Question short details are avoided. May please refer post linked above.

尝试过DoEvents，Wait，Sleep甚至使用MsgBox和断点暂停代码，但所有尝试都是徒劳的.但是，已经达到了发布的解决方法. 运行时是否缺少编译?那么，为什么代码总是在单循环中正常工作?寻找对该主题的可能解释和/或理解.

Tried with DoEvents, Wait ,Sleep and even with halting the code with MsgBox and break points, but all are in vain. However an workaround had been reached as posted in the post. Is it lack of compilation in runtime? Then why code is working correctly always for single loop? Looking for a possible explanation and or understanding of the subject.

Option Explicit
Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Sub test()
Dim TestStr As String
Dim CondStr As String, xFormula As String, iFormula As String
Dim Arr As Variant, VBstr As String
Dim i As Integer, Srw As Long, Lrw As Long, Rw As Long
Dim Ws As Worksheet

Set Ws = ThisWorkbook.ActiveSheet
Srw = 1
Lrw = Ws.Cells(Rows.Count, 1).End(xlUp).Row

For Rw = Srw To Lrw
TestStr = "AAA BBB DDD EEE GGG HHH A11 B11 C11 1A1 1AB AA0"
TestStr = "AAA BBB EEE GGG HHH A11 B11 C11 1A1 1AB AA0"
TestStr = Ws.Cells(Rw, 1).Value

CondStr = "( AAA + BBB + ( CCC | DDD ) + ( EEE + ! FFF ) ) | ( GGG + HHH + DDD + EEE + FFF )"
CondStr = Ws.Cells(Rw, 2).Value
'Debug.Print CondStr

Arr = Split(CondStr, " ")
VBstr = ""
    For i = LBound(Arr) To UBound(Arr)
    xFormula = Trim(Arr(i))
    Select Case xFormula
    Case ""
    iFormula = ""
    Case "(", ")"
    iFormula = Arr(i)
    Case "+"
    iFormula = " And "
    Case "|"
    iFormula = " OR "
    Case "!"
    iFormula = " Not "
    Case Else
    iFormula = (InStr(1, TestStr, xFormula) > 0)
    End Select
    VBstr = VBstr & iFormula
    Next i
VBstr = "VersatileCode=" & VBstr
Debug.Print Rw & VBstr

Dim StrLine As Long, LineCnt As Long
ThisWorkbook.VBProject.VBComponents("Module5").Activate
With ThisWorkbook.VBProject.VBComponents("Module5").CodeModule
StrLine = .ProcBodyLine("VersatileCode", vbext_pk_Proc)
LineCnt = .ProcCountLines("VersatileCode", vbext_pk_Proc)
.DeleteLines StrLine + 1, LineCnt - 2
.InsertLines StrLine + 1, VBstr
End With
'Sleep 200
DoEvents
DoEvents
Debug.Print VersatileCode()

    If VersatileCode() = True Then
    Ws.Cells(Rw, 4).Value = Ws.Cells(Rw, 3).Value
    Else
    Ws.Cells(Rw, 4).Value = 0
    End If
'MsgBox Rw & VBstr & vbCrLf & VersatileCode()
Next Rw
End Sub

带有1-5行循环的调试日志.第3行的正确结果为False 其他人是真的

Debug log with 1-5 Row loop. Row 3 correct result would be False while others are True

1VersatileCode=(True And True And (False OR True) And (True And  Not False)) OR (True And True And True And True And False)
True
2VersatileCode=(False And True And (False OR True) And (True And  Not True)) OR (True And True And True And True And True)
True
3VersatileCode=(True And True And (False OR False) And (True And  Not False)) OR (True And True And False And True And False)
True
4VersatileCode=(True And True And (False OR True) And (True And  Not False)) OR (True And True And True And True And False)
True
5VersatileCode=(True And True And (False OR True) And (True And  Not False)) OR (True And True And True And True And False)
True

带有3-5行循环的调试日志.第3行的正确结果将为False，而 其他人是真的

Debug log with 3-5 Row loop. Row 3 correct result would be False while others are True

3VersatileCode=(True And True And (False OR False) And (True And  Not False)) OR (True And True And False And True And False)
False
4VersatileCode=(True And True And (False OR True) And (True And  Not False)) OR (True And True And True And True And False)
False
5VersatileCode=(True And True And (False OR True) And (True And  Not False)) OR (True And True And True And True And False)
False

动态代码用于重写模块中的单个功能，否则为空.为了强制编译，尝试了重写整个函数并且只更改了程序主体行.但是此方法仅适用于循环的第一次迭代，并且在后续迭代中给出错误的结果.

The dynamic code is used to rewrite a single function in a module otherwise empty. for forcing compilation tried both rewriting entire function and only changing procedure body line. But this method is working for 1st iteration of loop only and giving incorrect result on subsequent iteration.

Function VersatileCode() As Boolean
VersatileCode = (True And True And (False Or True) And (True And Not False)) Or (True And True And True And True And False)
End Function

要使解决方法成功，我必须在新添加的工作簿和模块中将动态代码作为过程编写，并将结果放入添加的工作簿中的单元格中.

for workaround to succeed, I had to write the dynamic code as procedure in a new added workbook and module and put the result in a cell in the added workbook.

代码外循环

Set Wb = Workbooks.Add
Set vbc = Wb.VBProject.VBComponents.Add(vbext_ct_StdModule)

''' 循环内的代码

Dim StrLine As Long, LineCnt As Long
With vbc.CodeModule
On Error Resume Next
StrLine = .ProcBodyLine("VersatileCode", vbext_pk_Proc)
LineCnt = .ProcCountLines("VersatileCode", vbext_pk_Proc)
.DeleteLines StrLine, LineCnt
On Error GoTo 0
.InsertLines StrLine + 1, "Sub VersatileCode()"
.InsertLines StrLine + 2, VBstr
.InsertLines StrLine + 3, "ThisWorkbook.Sheets(1).cells(1,1).value = X"
.InsertLines StrLine + 4, "End Sub"
End With
DoEvents
Application.Run Wb.Name & "!VersatileCode"
DoEvents

Rslt = Wb.Sheets(1).Cells(1, 1).Value

仍在寻找仅在当前工作簿模块中将动态代码用作功能而不涉及任何传递结果的单元格的可能性.

推荐答案

这是一个有效的示例:

Sub test()

    Dim TestStr As String
    Dim CondStr As String, xFormula As String, iFormula As String
    Dim Arr As Variant, VBstr As String
    Dim i As Long

    TestStr = "AAA BBB DDD EEE GGG HHH A11 B11 C11 1A1 1AB AA0"

    CondStr = "( AAA + BBB + ( CCC | DDD ) + ( EEE + ! FFF ) ) | ( GGG + HHH + DDD + EEE + FFF )"

    Arr = Split(CondStr, " ")
    VBstr = ""
    For i = LBound(Arr) To UBound(Arr)
        xFormula = Trim(Arr(i))
        Select Case xFormula
        Case ""
            iFormula = ""
        Case "(", ")"
            iFormula = Arr(i)
        Case "+"
            iFormula = " And "
        Case "|"
            iFormula = " OR "
        Case "!"
            iFormula = " Not "
        Case Else
            iFormula = (InStr(1, TestStr, xFormula) > 0)
        End Select
        VBstr = VBstr & iFormula
    Next i

    Debug.Print EvaluateCode(VBstr)

End Sub

'evaluate VBA passed in as a string and return the result
Function EvaluateCode(VBstr As String)
    Const MOD_NAME As String = "Dynamic"
    Dim fn As String, theCode As String

    Randomize
    fn = "Temp_" & CLng(Rnd() * 1000)
    Debug.Print fn

    theCode = "Public Function " & fn & "()" & vbCrLf & _
              fn & " = " & VBstr & vbCrLf & _
              "End Function"

    With ThisWorkbook.VBProject.VBComponents(MOD_NAME).CodeModule
        If .CountOfLines > 0 Then .DeleteLines 1, .CountOfLines
        .InsertLines .CountOfLines + 1, theCode
    End With

    EvaluateCode = Application.Run(MOD_NAME & "." & fn)

End Function

这篇关于VBA更改代码的编译是否会导致循环中的错误结果?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！