VBA - Recursive Function modify Code - Output ERROR

[Sannyboy]

Dear All

Its been days , i've been bugging myself with this issue. I have a code that runs a recursive function. Before everyloop, the function Replaces a line in the code and prints the result. However , the output is not as expected.
My understanding is - the function is carrying a value in memory - something like cached... or may be the time for recursion is less than the time for replacing the code line . so the loop ignores the changes.

Please advise i shall be really greatful

Function repl(stt As String)
repl = stt & 5
End Function

Sub MAIN_ENTRY()   ' entry point for the code
Debug.Print rec("ENTRY")
End Sub

Function rec(st As String)

If st = "ENTRY" Then
    For i = 0 To 5
       clean (i)
       'MsgBox i
       rec = rec("MessaGE")
    Next i
Else
    rec = repl(st)
End If
End Function

Sub clean(i As Integer) ' function replaces code line
Dim xlmodule As Object

Set xlmodule = ThisWorkbook.VBProject.VBComponents("Module1")
xlmodule.CodeModule.replaceline 2, "repl = stt & " & i
Set xlmodule = Nothing
End Sub

the output should be
message 0
message 1
message 2
message 3
message 4
message 5

[SamT]

THis will give that output

If st = "ENTRY" Then
    For i = 0 To 5
       clean (i)
        rec = rec("MessaGE")
       MsgBox i 'Uncommented
   Next i

Since ""MessaGE" <> "ENTRY" , In the first Recursive call that segment is ignored and the ELSE part comes into play

Else
    rec = repl(st)
End If

IN all subseguent Recursives, the return from repl also <> "ENTRY"

When a Function is called, that Function is placed in RAM and stays there only as long as it is executing, At the end of the loop in the First rec, you have called six rec's, but they are running in parallel, not recursively, sort of, broadly speaking.

Note that in your code, only the original is recursing because all the rest are in the "Else" mode of the If...Then... Else.

Function Recur(X as Double, I as Integer) As Double
'Must have a way to stop the Recurrence.
If I = 5 then Exit Function 'Try commenting this line out
If X < 90 Then exit Function 'About a dozen Recurrences

I = I + 1
Recur = X * 0.99
MsgBox Chr(191 + I) & ": " & Recur

Recur = Recur(Recur, I)
End Function

Sub Test_Recur()
Dim Y
Y = Recur(100, 1)
MsgBox "The Final Result is " & Y
End Sub

[Sannyboy]

Apologies if my query was a little unclear
The code is required to recurse just once based on the Condition text "ENTRY", rest all recursives are to call the function repl.
in the function in the initial recurse , i want the code line to be replaced.

repl = stt & 5

above is the line to be replaced. so that it changes function to return the different value.

This is done by the function clean , and code line for the function repl is replaced.

so ideally the function repl should return stt & i (replaced with clean function) and this should be printed in the debug.

the commented msgbox was just to check teh output.

could you get it now .

[Sannyboy]

if i remove the recursion , still the function doesnt work well
--------------------------------------
Function repl(stt As String)
Debug.Print stt & 5
End Function
Sub MAIN_ENTRY() ' entry point for the code
Call rec("ENTRY")
End Sub
Function rec(st As String)


If st = "ENTRY" Then
For i = 0 To 5
clean (i)
'MsgBox i
rec = repl("MessaGE")
Next i
Else

End If
End Function
Sub clean(i As Integer) ' function replaces code line
Dim xlmodule As Object


Set xlmodule = ThisWorkbook.VBProject.VBComponents("Module1")
xlmodule.CodeModule.replaceline 2, "debug.print stt & " & i
Set xlmodule = Nothing
End Sub
-----------------

some issue with codemodule.replaceline

[SamT]

Your code has too many logical errors. The most obvious is...

Function rec(st As String)

rec does not return anything as the Function is Declared... nb: "Declared" has a specific meaning in Programming.

Observe rec = rec("MessaGE"). rec cannot hold a value because of the Declaration

You need to look at and understand the example I gave before you even have a chance to understand your own code.

nb: Recursive code is difficult to get right even when you do understand the underlying logic.

Edit to add: Like Paul says below, What are you trying to accomplish?

[Paul_Hossler]

This seems to be a VERY round about way of doing something

What is the final objective?

Why can't you use something simple and skip the recursion and dynamic code modifications?

Option Explicit

Sub MAIN_ENTRY()   ' entry point for the code
    Dim i As Long
    For i = 0 To 5
        Debug.Print "Message " & i
    Next i
        
End Sub

or even

Option Explicit

Sub MAIN_ENTRY()   ' entry point for the code
    Dim i As Long
    
    For i = 0 To 5
        Do_msg i
    Next i
        
End Sub

Sub Do_msg(n As Long)
        Debug.Print "Message " & n
End Sub

[Sannyboy]

Actually this is just a subpart of the code.

Actual code read a condition lines stored in a text file and returns true or false after condition is evaluated.

Example text file contains a statement ADX(0) > 20
Now I have a function as ADX
I want the replaced line to evaluate this condition and then return true or false.

So dynamic code is required... Another way was to use scripting object... But that is very slow during run time.


Recursion can be removed as posted above... But the result is not correct.

My question is why the result is correct for first loop and not for subsequent loops. If u can just run the code above pls

[SamT]

Paul, it's all yours.

Ciao,
Sam

[Paul_Hossler]

Without having samples to the text file and the workbook with the current macros, there's too many variables to offer anything other that guesses

[Sannyboy]

Mr Paul

The above code is the very problem. If you could just run the above code and get the output the problem would be solved.

I would post the excel and text file after sometime... But thr s nthng more to the problem

To summarise what I am trying to do
- list of conditions stored as string in an array
- to test the conditions are true or false replace a code line like above
- each variable of the condition is a function.
Like "ADX(0) < 20" ... Adx is a function


Another way is to use msscripting object to run the condition in a vbscript .. but it is very very slow.

[Paul_Hossler]

Yes, I agree the code is probably the very problem

BUT without having your text file AND the workbook with the module you're trying to edit on the fly, there's NO way I want to test it