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.
