I'm trying to reuse my code module for loop runs (which will change some data during different loop);however, I realized the from 2nd loop onwards, the macro is still running the old script. Any idea how I can ensure that the macro runs the updated script (which I will always delete and re-create during every loop)?

The actual scrip is very long, to simplify it i will just extract the area which I'm referring to. I have no error running this, just that from 2nd loop onwards, I checked that the module I re-created has the updated script, but macro still running the first loop script, which is very strange for me.

The script to be written into module is stored in excel sheet3 and it changes after every new loop starts

Sub write_module()
For i = 1 To 2
Dim VBProj As VBIDE.VBProject
Dim VBComp, comp As VBIDE.VBComponent
Dim CodeMod As VBIDE.CodeModule

Set VBProj = ActiveWorkbook.VBProject
Set VBComp = VBProj.VBComponents("ZC553_MS")
Set CodeMod = VBComp.CodeModule

script_lr = Sheet3.Cells(Rows.Count, 8).End(xlUp).Row
LineNum = 2

With ActiveWorkbook.VBProject.VBComponents("ZC553_MS").CodeModule
' .DeleteLines 2, VBComp.CodeModule.CountOfLines - 2
For j = 1 To script_lr
.InsertLines LineNum, Sheet3.Cells(j, 8)
LineNum = LineNum + 1
Next j
End With

zc553_master

Next i

End Sub


1 Answer
1

Try using

Application.Run "zc553_master"


in place of

zc553_master


for calling your dynamically-written procedure

This worked fine for me:

Sub Tester()

Dim i As Long, cm As CodeModule

For i = 1 To 5

Set cm = ActiveWorkbook.VBProject.VBComponents("ZC553_MS").CodeModule
With cm

.DeleteLines 2, 3

.InsertLines 2, "Sub zc553_master()"
.InsertLines 3, " Debug.Print ""Version " & i & """"
.InsertLines 4, "End Sub"

End With
DoEvents
Debug.Print "calling", i

Application.Run "zc553_master"
'zc553_master

Next i

End Sub


Output:

calling 1
Version 1
calling 2
Version 2
calling 3
Version 3
calling 4
Version 4
calling 5
Version 5


...but using the straight call to zc553_master just was crash crash or nothing seemed to run.

zc553_master

What is the actual problem you're trying to solve with this approach though? It's difficult to imagine something which couldn't be mimicked using a static procedure and passing parameters.

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.