Code creates its own If/For statements

Red_Adam · Nov 17th 2004

Hi folks

Does anybody know how to make your code dynamically create its own 'For' and 'If' statements?

I need my code to create a 'For' statement followed by an 'If' statement whenever the 'If' statement is satisfied.
eg

For a = 1 To 10
If Variable1 = 3 Then
(Create new....For b = 1 To 10
If Variable2= 3 then..............)

The reason I need this is because sometimes the conditions in my code will mean that I need many 'For' and 'If' statements within each other and sometimes I will only need one.

Am I walking into a brick wall here?

Regards
Adam

Dave Hawley · Nov 17th 2004

Re: Code creates its own If/For statements

If I understand you need to nest some loops together along with If Statements.

tinyjack · Nov 17th 2004

Re: Code creates its own If/For statements

It also sounds like you might need to rethink your variables. Possibly, Variable1 & Variable2 need to become Array(1) and Array(2).

TJ

A9192Shark · Nov 17th 2004

Re: Code creates its own If/For statements

Red Adam,

Take a look at this thread: http://www.ozgrid.com/forum/showthread.php?t=26070

I used arrays to recursively call a loop as many times as it was required.

You just have to think clearly as to how 'deep' into the loops you are.

Good luck,

Alan.

=RSI · Nov 17th 2004

Re: Code creates its own If/For statements

If you believe you really do need to dynamically generate your code you could try something like this:

Create a new module in the workbook that contains the code you are running and call it 'DynamicModule'. Enter the following code in this module:

Code

Sub DynamicSub()
End Sub

Then in your original code use something like this:

Code

Sub CallingCode()
    'Example Sub that creats some dynamic code, calls it and then deletes it
    
    Const HOLDING_MODULE_NAME As String = "DynamicModule"
    
    'some code...
    
    Call CreateVariableCode(10, HOLDING_MODULE_NAME)
    Call DynamicSub
    RemoveVariableCode (HOLDING_MODULE_NAME)
    
End Sub


Sub CreateVariableCode(NumberOfCodeLinesNeeded As Integer, ModuleName As String)
    'Sub that adds code lines to a blank sub routine
    
    Dim ThisModule As VBComponent
    Dim CodeLines As Integer
    Dim LineOfCode As String
    
    Set ThisModule = ThisWorkbook.VBProject.VBComponents(ModuleName)
    
    'Write the dynamic code to the new module
    For CodeLines = 2 To NumberOfCodeLinesNeeded + 1
        LineOfCode = ConstructCodeNeeded(CodeLines - 1) 'call to a function that builds your code
        ThisModule.CodeModule.InsertLines CodeLines, LineOfCode
    Next
    
End Sub


Sub RemoveVariableCode(Module As String)
    'Sub that deletes the dynamic code added to the previously blank sub routine
    
    Dim ThisModule As VBComponent
    
    Set ThisModule = ThisWorkbook.VBProject.VBComponents(Module)
    
    'check that the sub is not already blank
    If Not ThisModule.CodeModule.CountOfLines = 2 Then
    
        linecount = ThisModule.CodeModule.CountOfLines
        'ignore the first and last lines as they are the Sub...End Sub statements
        For Line = 2 To linecount - 1
            'always delete line 2 as the line numbers are dynamic
            ThisModule.CodeModule.DeleteLines (2)
        Next
        
    End If
    
End Sub


Function ConstructCodeNeeded(Line As Integer) As String
    'example function that builds required code
    
    ConstructCodeNeeded = "'Example Code Line " & Line
    
End Function

Display More

Red_Adam · Nov 17th 2004

Re: Code creates its own If/For statements

Thanks to everyone for their advice

I've got it working a treat.

Many, many thanks
Adam

Code creates its own If/For statements

Participate now!

Share

Users Viewing This Thread