Is VBA compilation of changing code is responsible for wrong results in a loop?

Question

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.

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

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

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.

Code outside loop

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

''' Code inside Loop

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

Still looking for possibility for using the dynamic code as a function only in the current workbook module without involving any cell for passing the result.

When building code on the fly it's best to insert your dynamic code to a single module containing no other code - are you doing that? See also stackoverflow.com/questions/56532672/… - seems like changing the method name will help trigger a recompile of your new code. — Tim Williams
– Tim Williams, Commented Aug 1, 2019 at 16:26
@Tim Williams Sir thanks for response. Since,at start, I tried in use the dynamic code as a function only in a single otherwise empty module but could not succeed. Finally as a workaround, I put the dynamic code as procedure in a new added workbook and module and put the result in a cell in the added workbook. I will carefully go through each links referred and try if it is possible to use it as a function in current workbook without involving any cell. Again thanks for pointing towards valuable references. — Ahmed AU
– Ahmed AU, Commented Aug 1, 2019 at 17:22

Tim Williams · Accepted Answer · 2019-08-01 18:56:58Z

1

Here's a working example:

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

answered Aug 1, 2019 at 18:56

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ahmed AU Over a year ago

Sir, I tested your code within a loop fetching TestStr and CondStr from the excel cells, It is working like a charm. It proves beyond doubt and enlighten me with fact, that renaming the dynamic function in each iteration forces VBA to re-compile. I also apologies for my poorly worded post in a confused state of mind in a hurry after 7-8 hrs hours of continuous try to achieve this desired outcome. And Finally I am out of any words to express thanks from my heart for your valuable guidance (It relives me and thousands in SO community of similar torment).

Collectives™ on Stack Overflow

Is VBA compilation of changing code is responsible for wrong results in a loop?

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related