Excel VBA Module Not Updating While Running

Question

I have 2 modules, the main module updates the other module while running, and runs that module every time it updates.

The problem is that the other module seems to not being updated while running (it runs the very first module, since the outputs are all according to the first input). But after the run is completed, I checked the other module and it is updated. But the output is not according to that updated module.

I already asked the question, but did not get an answer. VBA Function Module Not Calculating All Output Values

I found a similar question but the solution did not work in my case. excel vba code module not updated during run

Option Explicit

Public Sub AddNewWorkBookTEST()

Dim nextline As Long, LastUsedRowList As Long
Dim CodeString As String

Dim x As Long
Dim KWATT As Double


Dim folderPath As String
folderPath = Application.ActiveWorkbook.Path

LastUsedRowList = Sheet4.Cells(Rows.Count, 1).End(xlUp).Row

For x = 1 To LastUsedRowList
    KWATT = Sheet4.Cells(x, 1)
    CodeString = CodeStringGenerator(KWATT)

    ''Update the module code
    With ActiveWorkbook.VBProject.VBComponents("MyNewTest").CodeModule
        .DeleteLines 1, .CountOfLines
    End With

    With ActiveWorkbook.VBProject.VBComponents("MyNewTest").CodeModule
        nextline = .CountOfLines + 1
        .InsertLines nextline, CodeString
    End With

CallOtherModule x
''Calling the function in the second module (where the code was copied).
'''Cannot call the function directly from this sub, since excel will 
''''crash:Call MyNewTest.SortedArray(x)

Next x


End Sub

''''''''''''''''''''''''''''''''''''''''''''''''''''''
Public Sub CallOtherModule(ItemsCounter As Long)
    Call MyNewTest.SortedArray(ItemsCounter)
End Sub

''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''The function that writes the code of the second module as String
Function CodeStringGenerator(KWATT As Double) As String

CodeStringGenerator = "'Option Explicit" & vbCrLf & "Public Function 
SortedArray(ItemsCounter As Long) As Variant()" & vbCrLf & vbCrLf _
& "Dim TempSortedArray() As Variant" & vbCrLf _
& "Sheet4.Cells(ItemsCounter, 2) = " & KWATT + 5 & vbCrLf _
& "End Function" & vbCrLf

End Function

In sheet 4, the (input,output) (First Column,Second Column) is: 18, 23; 20, 23; 10, 23; 9, 23; 9,23; 10,23.

However, it should be 18, 23; 20, 25; 10, 15; 9, 14; 9,14; 10,15.

These are examples just to show the problem.

You can generate code at run-time, that doesn't mean that generated code gets compiled. In fact, it doesn't. And if you can run it, you can't break inside it to debug. Generating and running code in the same "session", without recompiling the project, is a good reliable way to eventually end up crashing the host application. Generate the code in one place, run the generated code in another distinct step, with a recompilation in-between. — Mathieu Guindon
– Mathieu Guindon, Commented Jun 10, 2019 at 20:34
Thank you @MathieuGuindon, but what do you mean by "in another distinct step". In my case, should I write the second module in a different workbook ? — Yayale
– Yayale, Commented Jun 10, 2019 at 20:50
Not necessarily. What I'm saying is that it's generally a good idea to let execution terminate after generating the code. — Mathieu Guindon
– Mathieu Guindon, Commented Jun 10, 2019 at 20:53
I promise you there is a way to solve your problem without generating code. Even having an unknown number of inputs or columns, you can write an algorithm that will be able to work through the data to generate your desired results. Can you give a better example of how the problem/dataset changes with a different number of data points (rows and/or columns)? Writing a "single" function to handle all the possible ways your input data can appear is certainly possible and we can help with that. — PeterT
– PeterT, Commented Jun 10, 2019 at 20:53
@PeterT, I would be more than happy if you can tell me what algorithm solves this problem. I have fixed elements, say: 0.5 / 0.75 / 1 / 1.5 / 2 / 3 / 4. The inputs are: Total load (Say 100), and number of steps (ex: 1, 2, 3... 7... 10). Basically I have to find solutions using the above fixed elements that satisfy the following: - Each step can have number of elements which is multiple of 3 (i.e. 3, 6 , 9 , 12 elements ....) - Each step can have only one element type from the fixed elements (i.e. either 0.5, or 1, or 3 or 4 ...) - Total load should be equal to the sum of these elements. — Yayale
– Yayale, Commented Jun 11, 2019 at 7:50

Tim Williams · Accepted Answer · 2019-06-10 21:30:37Z

2

While giving a +1 to the perils of dynamically writing code, changing the method name seems to force a recompile:

Public Sub AddNewWorkBookTEST()

    Dim nextline As Long, LastUsedRowList As Long
    Dim CodeString As String
    Dim x As Long
    Dim KWATT As Double


    Dim folderPath As String
    folderPath = Application.ActiveWorkbook.Path

    LastUsedRowList = sheet4.Cells(Rows.Count, 1).End(xlUp).Row

    For x = 1 To LastUsedRowList
        KWATT = sheet4.Cells(x, 1)
        Debug.Print KWATT
        CodeString = CodeStringGenerator(x, KWATT)
        ''Update the module code
        With ActiveWorkbook.VBProject.VBComponents("MyNewTest").CodeModule
            .DeleteLines 1, .CountOfLines
            nextline = .CountOfLines + 1
            .InsertLines nextline, CodeString
        End With
        Application.Run "MyNewTest.SortedArray_" & x, x
    Next x
End Sub


Function CodeStringGenerator(x As Long, KWATT As Double) As String
    CodeStringGenerator = "'Option Explicit" & vbCrLf & _
    "Public Function SortedArray_" & x & "(ItemsCounter As Long) As Variant()" & vbCrLf & vbCrLf _
    & "Dim TempSortedArray() As Variant" & vbCrLf _
    & "Sheet4.Cells(ItemsCounter, 2) = " & KWATT + 5 & vbCrLf _
    & "End Function" & vbCrLf
End Function

edited Jun 10, 2019 at 21:30

answered Jun 10, 2019 at 21:10

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

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

2 Comments

Yayale Over a year ago

Did not understand what you mean by "perils of dynamically writing code", could you please suggest other better ways ?

Tim Williams Over a year ago

I was referring to the comments above about it not being a completely reliable way of solving a problem: even when it "works" it sometimes doesn't, and the "rules" around dynamic code are often difficult to figure out.

PeterT · Accepted Answer · 2019-06-11 14:00:23Z

This example is based on your explanation of your problem. It's highly likely that it is not a direct solution, but I'm hoping it can give you an idea how to structure your logic and code to craft a specific solution to your problem without generating code.

My suggestion is to review this example and see if you can apply it to your problem-space, then ask new questions here to overcome other problems that you encounter along the way.

The code below adjusts itself automatically for any number of fixed elements, steps, and check elements to produce a two-dimensional array of possible solutions to examine.

Option Explicit

Public Sub Main()
    Dim fixedElements As Variant
    fixedElements = Array(0.5, 0.75, 1#, 2#, 3#, 4#)

    Dim solutions As Variant
    solutions = SolveForLoad(totalLoad:=20, numberOfSteps:=3, _
                             fixedElements:=fixedElements)

    Dim solutionsRows As Long
    Dim solutionsCols As Long
    solutionsRows = UBound(solutions, 1) - LBound(solutions, 1) + 1
    solutionsCols = UBound(solutions, 2) - LBound(solutions, 2) + 1

    Sheet1.UsedRange.Clear

    Dim solutionArea As Range
    Set solutionArea = Sheet1.Range("A1").Resize(solutionsRows, solutionsCols)
    solutionArea = solutions

    '--- sort the solutions now, calulating std deviation and range from load
End Sub

Private Function SolveForLoad(ByVal totalLoad As Long, _
                              ByVal numberOfSteps As Long, _
                              ByRef fixedElements As Variant) As Variant
    Dim checkElements As Variant
    checkElements = Array(3, 6, 9, 12, 15)

    '--- two-dimensional array that will hold all possible results
    Dim results As Variant
    ReDim results(LBound(fixedElements) To UBound(fixedElements), _
                  LBound(checkElements) To UBound(checkElements))

    Dim i As Long
    Dim j As Long
    Dim checkResult As Double
    For i = LBound(fixedElements) To UBound(fixedElements)
        For j = LBound(checkElements) To UBound(checkElements)
            checkResult = numberOfSteps * (checkElements(j) * fixedElements(i))
            results(i, j) = checkResult
        Next j
    Next i
    SolveForLoad = results
End Function

Thank you Peter. Will think about it. But the core of the code is this: checkResult = numberOfSteps * (checkElements(j) * fixedElements(i)) ... The formula assumes that all steps have the same elements and number of elements, which is not the case, each step has independent combination of elements. Therefore I am doing for loops inside each others (each for loop for a step). I fix one step (in each loop), and try all the possible combinations for the other steps (in the other for loops), and so on ...

Collectives™ on Stack Overflow

Excel VBA Module Not Updating While Running

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related