Create lines of VBA code called by a key-combination

[BeckerHarald1]

Hi, I use Access 2010 with a lot of forms and many thousands of lines of VBA code. When searching manually thru the codelines in the various projects
I will find e.g. the following line of code (let's call it current line):

    DoCmd.OpenReport "320rpt_Mitgliederliste", 

acPreview
While the cursor is inside this line I would like to create a new line of code behind the line with the cursor in.
The new line should be:

    CreateLogFile2 "320rpt_Mitgliederliste", 4

I will do the task by means of VBA-Code (a Sub), in which I will extract the information "320rpt_Mitgliederliste" and insert it in the new line.

My first question is: how can I start the Sub via Key-Combination? Can I use a key-combination while I am just seeing some lines of code?

My second question is: how can address the VBA-Project (me.???) and the line in which the cursor is (.lines???) ?
How can I insert the new line immediately behind the current line.

My VBA-knowledge is sufficent enough to extract the information "320rpt_Mitgliederliste" and build the string for the new line.

Thanks a lot for your help

[jforbes]

Here are a few procedures that are similar to what you are attempting to do:

Public Function currentVBELine() As Long
    ' Get current position in the VB Editor
    Dim lStartColumn As Long
    Dim lEndLine As Long
    Dim lEndColumn As Long
    Application.VBE.ActiveCodePane.GetSelection currentVBELine, lStartColumn, lEndLine, lEndColumn
End Function
 
Public Sub insertNewVBEFunction()
    ' Insert a Function into the VB Editor
    Dim lCurrentLine As Long
    lCurrentLine = currentVBELine()
    Call Application.VBE.ActiveCodePane.CodeModule.InsertLines(lCurrentLine + 1, "Public Function newFunction() As Boolean")
    Call Application.VBE.ActiveCodePane.CodeModule.InsertLines(lCurrentLine + 2, "End Function")
    Call Application.VBE.ActiveCodePane.CodeModule.InsertLines(lCurrentLine + 3, "")
End Sub
 
Public Sub insertNewVBESub()
    ' Insert a Sub into the VB Editor
    Dim lCurrentLine As Long
    lCurrentLine = currentVBELine()
    Call Application.VBE.ActiveCodePane.CodeModule.InsertLines(lCurrentLine + 1, "Public Sub newSub()")
    Call Application.VBE.ActiveCodePane.CodeModule.InsertLines(lCurrentLine + 2, "End Sub")
    Call Application.VBE.ActiveCodePane.CodeModule.InsertLines(lCurrentLine + 3, "")
End Sub

Also, this should give you the current line of code in the Editor:

Application.VBE.ActiveCodePane.CodeModule.Lines(currentVBELine, 1)

The insertNewVBEFunction and insertNewVBESub can be run by hitting <F5> while on a line that is not within a code block. The <F5> (Run Sub/User Form) command works differently depending on where the cursor is located. If the cursor is located in a block of code, it will try to execute that block of code. If it is outside a code block, it will popup a Form to select the code to execute.

The only way I can think of to get the code to run while the cursor is in a block of code is to type in the name of the procedure into the Immediate Window, or add a button to a Form, or possibly add the command/macro to the Quick Access Toolbar. I usually add this kind of thing to a button on a Development only Form as my memory isn't so good. I've never tried to add something like this to the Quick Access Toolbar, but quick look at the setup, it seems to want to use a macro and not VBA.

[BeckerHarald1]

Thank you very much, jforbes. In the past I tried only once to handle with the codemodule. In some other way your code is very helpfull for me, unfortunetely not for my small problem.

To start your proposed SUB from my current line is essential. Whenever I have to type other commands before, I alternatively can just copy&paste my new line from the clipboard - this seems to be easier.

Do you think there is a way to write a procedure with the same duty from a scriptlanguage and start this by a key-combination?

However your answer will be: I again have learned a lot and therefor again: THANK YOU

[jimatqsi]

This has been a very educational thread for me. Thank you both.

Jim

[jforbes]

Glad to hear it Jimatqsi!

BeckerHarald1, the ways that I can think of using Access alone to launch the code while on a line inside the VBA Editor:

• Typing the name of the function into the Immediate Window. Which might be a pain the first time, but from then on you would only have to click on the command that was previously typed in and hit Enter.
• Calling the Method from a Button's OnClick Event on a Development Form that is never shown to the End User. Then you would click on the Line you want to manipulate, then flip over to Form and click the button. It would work just fine, it just wouldn't be elegant.
• If you want to have the Method available from the Quick Access Toolbar or a keyboard shortcut, you could wrap the function call in a Macro. The Macro could be an AutoKeys macro to get it to work as a keyboard shortcut from an Access Form, or the Macro could be added to the Quick Access toolbar and launched from there. But again, neither of these would be launched from the VBA Editor.

I'd like to thank you. I would have never have thought of the Quick Access Toolbar method. I've already started using it.

[BeckerHarald1]

Hi jforbes, again thank you for your ideas.

Let me first emphasize that this is no longer a duty but a case of education (like Jimatqsi commented). So if you get tired of this problem, I will understand.

My problem can be solved just be inserting manually about 100 lines of code which can’t be done just by browsing thru all codemodules, find the concerning lines and insert a new line behind. It will cost me about one day to do that manually. This is (I guess) less than learning the technic to do this in the way I have posted.

Nevertheless, this is the current situation:
1. As you proposed, I created a macro (I have never done that before). The help-function is incomplete at the point, to give these macro a key-combination. With the detour of a submacro, I could manage it. Now, I am able to start this macro by keyboard shortcut, call within a submacro, which calls a VBA-function.
2. It is not possible to start this macro from a VBA-Window. Therefor I tried to create a button for the Quick Access Toolbar. These Toolbar is only available in an access window but not inside a VBA window. To learn how to create another toolbar (like “debuggen”) in the VBA-Window I wasn’t brave enough. I have this also never done before.
3. I started a sub (without button or keyboard shortcut) inside the VBA-Window in step-by-step (tracing) modus. This is the code:

Public Sub TestHaraldInsertLine()
Dim MyCodeModule As CodeModule
Dim MyCodePane As CodePane
Dim MyMod As Module
Dim lStartColumn As Long
Dim lEndLine As Long
Dim lEndColumn As Long
Dim LineNumber As Long
  Stop 'Switch/activate to desired VBA-Code project manually
    Set MyCodePane = Application.VBE.ActiveCodePane 'Switch HERE to desired VBA-Modul!
'    Set MyCodeModule = Application.VBE.ActiveCodePane.CodeModule 'Delivers wrong Modul!!
    Set MyMod = Modules(MyCodePane.CodeModule) 'Delivers correct Modul
 
    With MyMod
'     Public Function currentVBELine() As Long
      MsgBox .Lines(currentVBELine, 1) ‘Delivers wrong line number because of wrong modul
    End With
‘    Application.VBE.MyCodePane.MyCodeModule.GetSelection LineNumber, lStartColumn, lEndLine, lEndColumn
‘    Application.VBE.ActiveCodePane.GetSelection lCurrentLine, lStartColumn, lEndLine, lEndColumn
'    x = Application.VBE.ActiveCodePane.CodeModule.Lines(lCurrentLine, 1)
'    x = Application.VBE.ActiveCodePane.CodeModule.Lines(currentVBELine, 1)
'    MsgBox x
End Sub

Public Function currentVBELine() As Long
    ' Get current position in the VB Editor
    Dim lStartColumn As Long
    Dim lEndLine As Long
    Dim lEndColumn As Long
    Application.VBE.ActiveCodePane.GetSelection currentVBELine, lStartColumn, lEndLine, lEndColumn
End Function

As far as I understand, there are 2 problems:
1. I am still not able to handle the sub without manually switching to the module in which I will insert a new line.
2. Even if I get the current linenumber by Public Function currentVBELine() As Long, I can’t read it. But without this function I will not get the activated linenumber – in fact, I don’t find the correct synthax: See my 4 lines behind “End with” They all produce errors.

I hope at the end of these activities of all of us, I (we?) will be truelly more educated ...

[jforbes]

I've been going about things differently than what you are attempting. To insert some Code Snippets, I used to use the Immediate Window, or hitting <F5> (only works outside a code block), or a Development Form that is never shown to the user to fire off the Sub Routines. .NET has this Code Snippet functionality built in and I was missing it when working in VBA. The Development Form came about mainly because I couldn't always remember the name of the Methods and it was a place to store some other development tricks.

Since this thread started, I changed the Code Snippet functionality to Functions so they would be callable from the Quick Access Toolbar and I'm currently giving them a Test Drive and kind of like it.

So to get yours working, at least basically, I'll give you what I have and tell you how I got it going...

This is a copy of the Code I currently have for the Code Snippet stuff. There's some stuff that you don't need, like the error handling, but I kept it in there just in case you continue to have trouble and we need to compare apples to apples. ...It has been converted to Functions:

Option Compare Database
Option Explicit
 
    'currentVBELine
Public Function currentVBELine() As Long
    ' Get current position in the VB Editor
    Dim lStartColumn As Long
    Dim lEndLine As Long
    Dim lEndColumn As Long
    Application.VBE.ActiveCodePane.GetSelection currentVBELine, lStartColumn, lEndLine, lEndColumn
End Function
 
    'insertNewVBEFunction
Public Function insertNewVBEFunction()
    ' Insert a Function into the VB Editor
    Dim lCurrentLine As Long
    lCurrentLine = currentVBELine()
    Call Application.VBE.ActiveCodePane.CodeModule.InsertLines(lCurrentLine + 0, "Public Function newFunction() As Boolean")
    Call Application.VBE.ActiveCodePane.CodeModule.InsertLines(lCurrentLine + 1, "    On Error GoTo ErrorOut")
    Call Application.VBE.ActiveCodePane.CodeModule.InsertLines(lCurrentLine + 2, "    newFunction = False")
    Call Application.VBE.ActiveCodePane.CodeModule.InsertLines(lCurrentLine + 3, "")
    Call Application.VBE.ActiveCodePane.CodeModule.InsertLines(lCurrentLine + 4, "ExitOut:")
    Call Application.VBE.ActiveCodePane.CodeModule.InsertLines(lCurrentLine + 5, "   Exit Function")
    Call Application.VBE.ActiveCodePane.CodeModule.InsertLines(lCurrentLine + 6, "")
    Call Application.VBE.ActiveCodePane.CodeModule.InsertLines(lCurrentLine + 7, "ErrorOut:")
    Call Application.VBE.ActiveCodePane.CodeModule.InsertLines(lCurrentLine + 8, "    gErrorMessage = """"")
    Call Application.VBE.ActiveCodePane.CodeModule.InsertLines(lCurrentLine + 9, "    Call customErrorHandler(mProcedureName, True, Err, Erl, gErrorMessage)")
    Call Application.VBE.ActiveCodePane.CodeModule.InsertLines(lCurrentLine + 10, "    Resume ExitOut")
    Call Application.VBE.ActiveCodePane.CodeModule.InsertLines(lCurrentLine + 11, "End Function")
    Call Application.VBE.ActiveCodePane.CodeModule.InsertLines(lCurrentLine + 12, "")
    Call Application.VBE.ActiveCodePane.Show
End Function
 
    'insertNewVBESub
Public Function insertNewVBESub()
    ' Insert a Sub into the VB Editor
    Dim lCurrentLine As Long
    lCurrentLine = currentVBELine()
    Call Application.VBE.ActiveCodePane.CodeModule.InsertLines(lCurrentLine + 0, "Public Sub newSub()")
    Call Application.VBE.ActiveCodePane.CodeModule.InsertLines(lCurrentLine + 1, "    On Error GoTo ErrorOut")
    Call Application.VBE.ActiveCodePane.CodeModule.InsertLines(lCurrentLine + 2, "")
    Call Application.VBE.ActiveCodePane.CodeModule.InsertLines(lCurrentLine + 3, "ExitOut:")
    Call Application.VBE.ActiveCodePane.CodeModule.InsertLines(lCurrentLine + 4, "   Exit Sub")
    Call Application.VBE.ActiveCodePane.CodeModule.InsertLines(lCurrentLine + 5, "")
    Call Application.VBE.ActiveCodePane.CodeModule.InsertLines(lCurrentLine + 6, "ErrorOut:")
    Call Application.VBE.ActiveCodePane.CodeModule.InsertLines(lCurrentLine + 7, "    gErrorMessage = """"")
    Call Application.VBE.ActiveCodePane.CodeModule.InsertLines(lCurrentLine + 8, "    Call customErrorHandler(mProcedureName, True, Err, Erl, gErrorMessage)")
    Call Application.VBE.ActiveCodePane.CodeModule.InsertLines(lCurrentLine + 9, "    Resume ExitOut")
    Call Application.VBE.ActiveCodePane.CodeModule.InsertLines(lCurrentLine + 10, "End Sub")
    Call Application.VBE.ActiveCodePane.CodeModule.InsertLines(lCurrentLine + 11, "")
    Call Application.VBE.ActiveCodePane.Show
End Function
 
    'insertNewVBEInteger
Public Function insertNewVBEInteger()
    ' Insert a Dim Integer Statement into the VB Editor
    Dim lCurrentLine As Long
    lCurrentLine = currentVBELine()
    Call Application.VBE.ActiveCodePane.CodeModule.InsertLines(lCurrentLine + 0, "    Dim iCount As Integer")
    Call Application.VBE.ActiveCodePane.Show
End Function
 
    'insertNewVBEString
Public Function insertNewVBEString()
    ' Insert a Dim String Statement into the VB Editor
    Dim lCurrentLine As Long
    lCurrentLine = currentVBELine()
    Call Application.VBE.ActiveCodePane.CodeModule.InsertLines(lCurrentLine + 0, "    Dim sSQL As String")
    Call Application.VBE.ActiveCodePane.Show
End Function
 
    'insertNewVBEOpenRecordset
Public Function insertNewVBEOpenRecordset()
    ' Insert a Open Recordset Block of Code into the VB Editor
    Dim lCurrentLine As Long
    lCurrentLine = currentVBELine()
    Call Application.VBE.ActiveCodePane.CodeModule.InsertLines(lCurrentLine + 0, "    Dim oRst As DAO.Recordset")
    Call Application.VBE.ActiveCodePane.CodeModule.InsertLines(lCurrentLine + 1, "    Dim sSQL As String")
    Call Application.VBE.ActiveCodePane.CodeModule.InsertLines(lCurrentLine + 2, "    sSQL = """"")
    Call Application.VBE.ActiveCodePane.CodeModule.InsertLines(lCurrentLine + 3, "    sSQL = sSQL & ""SELECT * """)
    Call Application.VBE.ActiveCodePane.CodeModule.InsertLines(lCurrentLine + 4, "    sSQL = sSQL & ""FROM Table """)
    Call Application.VBE.ActiveCodePane.CodeModule.InsertLines(lCurrentLine + 5, "    sSQL = sSQL & ""WHERE Something='"" & sValue & ""' """)
    Call Application.VBE.ActiveCodePane.CodeModule.InsertLines(lCurrentLine + 6, "    Set oRst = dbLocal.OpenRecordset(sSQL, dbOpenSnapshot, dbForwardOnly + dbSeeChanges)")
    Call Application.VBE.ActiveCodePane.CodeModule.InsertLines(lCurrentLine + 7, "    Set oRst = dbLocal.OpenRecordset(sSQL, dbOpenDynaset, dbFailOnError + dbSeeChanges)")
    Call Application.VBE.ActiveCodePane.CodeModule.InsertLines(lCurrentLine + 8, "        If oRst.RecordCount > 0 Then")
    Call Application.VBE.ActiveCodePane.CodeModule.InsertLines(lCurrentLine + 9, "            sSomething = Nz(oRst!Something, """")")
    Call Application.VBE.ActiveCodePane.CodeModule.InsertLines(lCurrentLine + 10, "        End If")
    Call Application.VBE.ActiveCodePane.CodeModule.InsertLines(lCurrentLine + 11, "        Do While Not oRst.EOF")
    Call Application.VBE.ActiveCodePane.CodeModule.InsertLines(lCurrentLine + 12, "            sSomething = Nz(oRst!Something, """")")
    Call Application.VBE.ActiveCodePane.CodeModule.InsertLines(lCurrentLine + 13, "            oRst.MoveNext")
    Call Application.VBE.ActiveCodePane.CodeModule.InsertLines(lCurrentLine + 14, "        Loop")
    Call Application.VBE.ActiveCodePane.CodeModule.InsertLines(lCurrentLine + 15, "    oRst.Close")
    Call Application.VBE.ActiveCodePane.Show
End Function
 
    'insertNewVBEOpenRecordset
Public Function insertNewVBESQLInsert()
    ' Insert code to Execute SQL into the VB Editor
    Dim lCurrentLine As Long
    lCurrentLine = currentVBELine()
    Call Application.VBE.ActiveCodePane.CodeModule.InsertLines(lCurrentLine + 0, "    Dim sSQL As String")
    Call Application.VBE.ActiveCodePane.CodeModule.InsertLines(lCurrentLine + 1, "    sSQL = """"")
    Call Application.VBE.ActiveCodePane.CodeModule.InsertLines(lCurrentLine + 2, "    sSQL = sSQL & ""INSERT INTO SomeTable (""")
    Call Application.VBE.ActiveCodePane.CodeModule.InsertLines(lCurrentLine + 3, "    sSQL = sSQL & ""  FirstValue""")
    Call Application.VBE.ActiveCodePane.CodeModule.InsertLines(lCurrentLine + 4, "    sSQL = sSQL & "", SecondValue""")
    Call Application.VBE.ActiveCodePane.CodeModule.InsertLines(lCurrentLine + 5, "    sSQL = sSQL & "") VALUES (""")
    Call Application.VBE.ActiveCodePane.CodeModule.InsertLines(lCurrentLine + 6, "    sSQL = sSQL & ""  '"" & sFirstValue & ""'""")
    Call Application.VBE.ActiveCodePane.CodeModule.InsertLines(lCurrentLine + 7, "    sSQL = sSQL & "", '"" & sSecondValue & ""'""")
    Call Application.VBE.ActiveCodePane.CodeModule.InsertLines(lCurrentLine + 8, "    sSQL = sSQL & "")""")
    Call Application.VBE.ActiveCodePane.CodeModule.InsertLines(lCurrentLine + 9, "   ")
    Call Application.VBE.ActiveCodePane.CodeModule.InsertLines(lCurrentLine + 10, "   Call executeSQL(sSQL)")
    Call Application.VBE.ActiveCodePane.Show
End Function

If you want to follow along, paste the above code into a code module, mine is called CodeSnippetLibrary.

Once you have the Library, position the cursor in another Library/code block. Things could get messy if you attempt to change the code while you are executing it. From the other code block, press <Ctrl>+G to open the Immediate Window. Then in the Immediate Window, type insertNewVBEString and hit enter.

The following should now be typed into the Visual Basic Editor where your cursor was previously positioned:

    Dim sSQL As String

That is the basics of it. If you can get it to work from the Immediate Window, you should be able to get it to work from the Quick Access Toolbar, if you want. If you attempt it and get stuck, let us know. From what I can tell, there's not a way to add a macro to the Visual Basic Editor Toolbar, short of creating an Add-in, which would be a nightmare.

Lastly, Pressing <F5> to run method will only work with Subroutines. So the <F5> key no longer works with the above library. My plan was to add Wrapper Subroutines to call the Functions if I missed starting them with <F5>, but so far that hasn't been the case.

[jimatqsi]

I've written a generic VBA source code generator. It's especially useful when there's a great deal of repetitive code to be written, but I see a lot of value in what you have done here for small blocks of code that are needed often.

If you're willing to add the overhead of a table to your utility, I would suggest this improvement. I have a CodeGen_tbl" table that has predefined VBA code in it. Each row has one line of code and 3 identifying columns. This lets me call a routine to generate the specific code I'm interested by specifying these 3 columns. You could use 1 identifier instead of 3. Each of those 3 identifiers can be used according to your own imagination, what you find useful. For me, those identifiers are Region, CodeSection and Subsection. I use Region to specify the kind of module, which so far is either a Class, a Public module, Form etc... Section in a Class module is GET, LET, DIM, INIT and so on. Subsection is usually blank but I sometimes have need to identify the code further. And finally there is an integer to sort the code so it comes out in the right order.

Using this table lets me add to or change the generated code without changing my code generation routine.
Normally I just generate it into a text box and then copy/paste it into a module. But your code above shows me how I can just dump it right into a module or at the current cursor position.

Thanks again.

Jim

[BeckerHarald1]

Hi jforbes, it works with immediate window. That means:
I place the cursor into my specific line of code than call a function, e.g. insertNewVBEString – and get a new line behind the line with the cursor. Of course Function currentVBELine works also and gives me as result the current line into a variable with lCurrentLine = currentVBELine(). Everything is fine.
Now let’s say the line in which I placed the cursor contains doCmd.OpenForm "160frm_MitgliederstammÄndern". The line to be created should be CreateLogFile2 "160frm_MitgliederstammÄndern", 4.
It is easy to change your function to insert this new line – except the string "160frm_MitgliederstammÄndern". This string is different for each line in which I place the cursor. Therefor do I need the contents of the line with the cursor inside your function. I will extract the string above and build this string into the new line.
3 Steps:
1. Find the line with the cursor in it – this works
2. Get this line into your function, extract the variable string, build it into the new line – this is missing
3. Insert the new line – this works.
Maybe I am blind but with my knowledge I get only errors.
In another task I was browsing thru all modules – and was able to read a line into my own functions and do some work with it – see step 2 above. I did this by

Dim MyMod As Module
For Each obj In CurrentProject.AllModules
If BuildfrmModulReferenz(obj.Name) = True Then  ‘Call a function, give Module name
…….  
    Function BuildfrmModulReferenz(ModName As String) As Boolean
       Set MyMod = Modules(ModName)
         With MyMod
            For iCounter = 1 To .CountOfLines
            ……. Identify a specific line
               RefArray(x) = .Lines(iCounter, 1)  
‘THIS IS THE KEY!!!!!!!!

I have now the desired information in RefArray and I am able to work with it, e.g. use it for a new line to be inserted.
In the immediate window I tried the following:
? Call Application.VBE.ActiveCodePane.CodeModule.Lines(lC urrentLine,1)
With other words: I just need a method to get the line with the cursor in it into a variable, e.g. RefArray. It works with my code above but I can’t transform my methods into your methods.
What is wrong with my thinking, what is wrong with the VBA-Synthax?

[jforbes]

I think this will work for you right out of the Box:

Public Function addLogEvent() As Boolean
 
    Dim lCurrentLine As Long
    Dim sCurrentLine As String
    Dim sFormName As String
    Dim iStart As Integer
    Dim iIndent As Integer
 
    ' Insert a Log Event
    lCurrentLine = currentVBELine()                         ' Determine the Current VBE line
    sCurrentLine = Application.VBE.ActiveCodePane.CodeModule.Lines(currentVBELine, 1)   ' Get the Current Line of Code
    iStart = InStr(1, sCurrentLine, "DoCmd.OpenForm")       ' Find the start of the Form Name
    iIndent = Len(sCurrentLine) - Len(Trim(sCurrentLine))   ' Find how much the Current Line is Indented
    If iStart > 0 Then                                      ' Only insert a line if a "DoCmd.OpenForm" is on the Current Line
        sFormName = Mid(sCurrentLine, iStart + 15, InStrRev(sCurrentLine, """") - (iStart + 15))
        Call Application.VBE.ActiveCodePane.CodeModule.InsertLines(lCurrentLine + 1, Left("                ", iIndent) & "CreateLogFile2 " & sFormName & """, 4")
    End If
 
End Function

2. Get this line into your function, extract the variable string, build it into the new line – this is missing

I think this is what you are talking about here:

Application.VBE.ActiveCodePane.CodeModule.Lines(currentVBELine, 1)

It returns the text of the Current Row of code. This is the same Method that you were using to load the Array with Lines of Code.

[jforbes]

I think you are on to something jimatqsi. I'm going to have to give that some serious thought.

[BeckerHarald1]

Hi jforbes & jimatqsi
Yes! Very educational! It really opens a lot of possibilities and I'm going to have to give that many more serious thought.
Jforbes, it is unbelievable how easy it was to get the contents of the current line

sCurrentLine = Application.VBE.ActiveCodePane.CodeModule.Lines(currentVBELine, 1)

I have no idea why this won’t work when I used this code!! May be because this code is very fragile. It is like Schrödingers cat in the box (If you look into the box the cat will be dead …)
Your Function currentVBELine() works but if you work inside the immediate window the currentline and even the Codemodule changes – I don’t really know.
But now it is easy. My problem is 100% solved! Thank you!!!!!
Because a part of the currentline is variable - it is not always

iStart = InStr(1, sCurrentLine, "DoCmd.OpenForm")

I changed the code a little bit. Just to be correct I give the code hereby:

   Public Function addLogEvent() As Boolean
 
        Dim lCurrentLine As Long
        Dim sCurrentLine As String
        Dim sFormName As String
        Dim iStart As Integer
        Dim iEnd As Integer
        Dim sObjekt As String
        Dim iIndent As Integer
 
        ' Insert a Log Event
        lCurrentLine = currentVBELine()                         ' Determine the Current VBE line
        sCurrentLine = Application.VBE.ActiveCodePane.CodeModule.Lines(currentVBELine, 1)   ' Get the Current Line of Code
'        iStart = InStr(1, sCurrentLine, "DoCmd.OpenForm")       ' Find the start of the Form Name
        iStart = InStr(1, sCurrentLine, """")       ' Find the start of the Form Name
        iEnd = InStr(iStart + 1, sCurrentLine, """")     ' Find the start of the Form Name
        iIndent = Len(sCurrentLine) - Len(Trim(sCurrentLine))   ' Find how much the Current Line is Indented
        If iStart > 0 Then                                      ' Only insert a line if a "variable Name of an objeckt" is on the Current Line
'            sFormName = MId(sCurrentLine, iStart + 15, InStrRev(sCurrentLine, """") - (iStart + 15))
            sFormName = MId(sCurrentLine, iStart, iEnd - iStart + 1)
            Call Application.VBE.ActiveCodePane.CodeModule.InsertLines(lCurrentLine + 1, Left("                ", iIndent) & "CreateLogFile2 " & sFormName & ", 4")
        End If
    End Function
 

Very genious is to even think of indent. I did not used this before. Educational!!

[zmbd]

BeckerHarald1:

+ You must use the [CODE/] formatting tool in the reply box tool bar when posting script such as VBA and/or SQL.

+ I have mentioned this several times in the moderations comments and have sent you a PM regarding this site requirement.

+ Please start using this format for your code.

[hvsummer]

Hey guy, why don't we simple this task by using
Find and Replace

DoCmd.OpenReport "320rpt_Mitgliederliste",

DoCmd.OpenReport "320rpt_Mitgliederliste",
 CreateLogFile2 "320rpt_Mitgliederliste", 4

with in VBA project and press replace all ?

[jforbes]

Glad you got it working, BeckerHarald1.

hvsummer, That's a great solution if the exact text is known and is the same through out the code. But for this thread, that is not the case.

[BeckerHarald1]

zmbd, this is not an excuse. My English isn't that bad that I am not able to read (and understand?) the moderation comments. I will try better next time.

Let me tell you all the following: In Germany we have a forum, it is called "wer-weiss-was". I asked there a few times for help - and I got answers a lot, but no help. Most forists didn't even understand the problem. One could think, the problem was not good described, but believe me, me German is excellent. They called themselves as experts but did not try to think themselves into a problem.

I write this, because the results of your forum are outstanding. Thanks to such guys as jforbes and I am quite sure as others.

[zmbd]

BeckerHarald1.

Actually, I have to thank you for this thread.
During the short time I've been a member here, I have learned much more than I would have from a classroom or on my own both from our experts and because of members like yourself asking the tough questions and for things outside of the box.

Looking forward to your next thread!

-z