excel vba code module not updated during run

Question

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

Seems like a recipe for constant crashes and difficult-to-debug behavior... — Tim Williams
– Tim Williams, Commented Jul 26, 2018 at 6:11

Tim Williams · Accepted Answer · 2018-07-26 16:06:24Z

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.

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.

edited Jul 26, 2018 at 16:06

answered Jul 26, 2018 at 6:16

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

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

6 Comments

Ah Cheng Over a year ago

Hi Tim, thank you very much for your reply. I tried your code and added [msgbox i] to show if the number is updated on every loop, the pop up showing [version 1] for 5 times, this is the problem I am facing. Whenever the module was amended, the Macro still running as the first version. On the topline, I am actually trying to write back to SAP via Excel Macro (as it contains data that will change how the SAP VBS is written. So for every loop, there will be parameters change and execute to write back to SAP. This is what I cannot achieve at the moment, would appreciate if you can help.

Tim Williams Over a year ago

I got the expected output in the Immediate pane. It's not really clear from your description but if you could update your Q with a less-simplified example that would help.

Ah Cheng Over a year ago

Hi Tim, thank you very much for your great help in cross validating with my result! After going through every steps in your code, i realise the small difference that cause a big difference. In your code, you cleared all lines within the code module, this works perfectly. In my code, i only deleted the content between [Sub ()] and [End Sub] and re-write the content, this cause the running of module still using the old code. You can test this out too. Not sure the reason, i think unless you deleted the entire code within the code module, somewhere somehow the code are stored elsewhere during run

Tim Williams Over a year ago

In truth I thought I was doing exactly what you were doing! I've still not managed to figure out the "rules" (if there are any) about how this works, what triggers recompile of what parts of the project,etc when you dynamically add/change code at runtime.

Eric Over a year ago

I've got the same problem and can't get mine to recompile dynamically. Have either of you found any insight into this?

|

Ah Cheng · Accepted Answer · 2018-07-27 01:16:00Z

0

I need to clear the entire content of the code module including [Sub ()] and [End Sub] for vba to recognise the updated code in the code module after 1st loop run. Tim had provided an excellent example and the correct practice when you need to reuse the same code module name but changes the content after every loop run, all credits to him :)

answered Jul 27, 2018 at 1:16

Ah Cheng

133 bronze badges

Collectives™ on Stack Overflow

excel vba code module not updated during run

2 Answers 2

6 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related