Inserting code lines
The line number of the code to which I am talking about here is , as defined by, or rather as held internally by, and accessed in code coding by, a sequential integer starting at 1 at the top of the code window and counting by +1 for every successive line/row. In other words , VBA holds somewhere in memory a set of numbers like pseudo the 1 2 3 and 4 here:
1 Option Explicit
2 Sub MyCode()
3 ‘Code
4 End Sub
These numbers we do not see and they are independent of any code lines which we may add: ( We can in addition , use any line numbers or labels as we choose. ( We can choose to use the same line numbers as those held internally, which I do in some places below, just to aid in the demonstration. Therefore those numbers which I use can be regarded , for demonstration purposes, as those held internally ) )
Single lines
Important to note here is that a virgin code module has no code lines, (or possibly one empty one, a pseudo a "row of zero length" ). It cannot be thought of in terms of a spreadsheet of rows waiting to be filed in. You cannot reference any row in the code module until either
_ typed lines are present
or
_ at least the rows "exist" as for example , by hitting the Enter key.
However, if you try to insert lines/ rows above the current maximum row, using coding, then the coding will not error: it will add a new line at one line above the last "row of zero length" used line.
If you insert lines/rows at up to and including the last used row, then all existing lines get shifted down
Because of these facts, it is easy to get disorientated in coding that inserts lines.
Here an example: we insert lines at arbitrary non existent line numbers well above the last line, here the code before and after running the code:
Before:
Code:
' Line 1 Note: I add these numbers just for demonstration to represent the numbers held internally by VBA for referring to lines by number in a code module: We may use any numbers in any order. But they have nothing to do with the internally held numbers. I choose to use the same value as the internally held corresponding numbers for the line here just for demonstration purposes
Sub Insuerts() ' Line 2
3 Dim CodMod As Object: Set CodMod = ThisWorkbook.VBProject.VBComponents.VBE.ActiveCodePane.CodeModule
4 CodMod.insertlines Line:=2000, String:="' Line 9"
5 CodMod.insertlines Line:=15, String:="' Line 10"
6 CodMod.insertlines Line:=159, String:="' Line " & CodMod.countoflines + 1 ' CodMod.countoflines evaluates to 10 after lines 9 and 10 were added in the last two lines , and then this line is added at the end so becomes line 11
End Sub ' Line 7
' line 8 This is the last line, or possibly the last but one line, used Before the code is run - .countoflines will return 8 initially before the macro adds any lines
After:
Code:
' Line 1 Note: I add these numbers just for demonstration to represent the numbers held internally by VBA for referring to lines by number in a code module: We may use any numbers in any order. But they have nothing to do with the internally held numbers. I choose to use the same value as the internally held corresponding numbers for the line here just for demonstration purposes
Sub Insuerts() ' Line 2
3 Dim CodMod As Object: Set CodMod = ThisWorkbook.VBProject.VBComponents.VBE.ActiveCodePane.CodeModule
4 CodMod.insertlines Line:=2000, String:="' Line 9"
5 CodMod.insertlines Line:=15, String:="' Line 10"
6 CodMod.insertlines Line:=159, String:="' Line " & CodMod.countoflines + 1 ' CodMod.countoflines evaluates to 10 after lines 9 and 10 were added in the last two lines , and then this line is added at the end so becomes line 11
End Sub ' Line 7
' line 8 This is the last line, or possibly the last but one line, used Before the code is run - .countoflines will return 8 initially before the macro adds any lines
' Line 9
' Line 10
' Line 11
The above shows us that "inserting" above the last existing code line will actually add a single code line at the next line/row above the last existing line. Looping to add lines at the end of a code module is therefore somewhat redundant as a code line such as the following would ensure that lines are added sequentially
__.insertlines Line:=__ .countoflines + 1, String:=" ' This will be at the next free line. "
In that code line, any number >=1 can be used in place of 1
Care must be taken when “adding” code lines using insertlines . For example if the first insertlines from the last ( After ) , code is modified to insert/add at the last line such:…_
Code:
' Line 1
Sub Insuerts()
3 Dim CodMod As Object: Set CodMod = ThisWorkbook.VBProject.VBComponents.VBE.ActiveCodePane.CodeModule
4 CodMod.insertlines Line:=CodMod.countoflines, String:="' New Line" ' this will insert at line 11 – currently CodMod.countoflines=11
'5 CodMod.insertlines Line:=15, String:="' Line 10"
'6 CodMod.insertlines Line:=159, String:="' Line " & CodMod.countoflines + 1 11
End Sub
' line 8
' Line 9
' Line 10
' Line 11
_.....then the results after running that above code will be as follows…._
Code:
' Line 1
Sub Insuerts()
3 Dim CodMod As Object: Set CodMod = ThisWorkbook.VBProject.VBComponents.VBE.ActiveCodePane.CodeModule
4 CodMod.insertlines Line:=CodMod.countoflines, String:="' New Line"
'5 CodMod.insertlines Line:=15, String:="' Line 10"
'6 CodMod.insertlines Line:=159, String:="' Line " & CodMod.countoflines + 1 ' CodMod.countoflines evaluates to 10 , and then this line is added at the end so beacomes line 11
End Sub
' line 8
' Line 9
' Line 10
' New Line
' Line 11
_.... as we see the code at ( and above if there had been ) the insert line is shifted down. In this case we inserted at the last line, rather than, as previously , trying to insert above the last line
Multi line inserting. In one go. Single string
Like many seemingly “page” type things in computers, the “page text” is actually held in a single long string. By analysing the string in detail , ( http://www.eileenslounge.com/viewtop...=31395#p242941 ) , we often find that we have a “character” or characters of this form : vbCr & vbLf . This is a throw back to old computer printer things and this forces a carriage return and line feed. In other word it “makes a new line. We find that these constants are in the string held for a spreadsheet multi line range as well as that for the text in a code module. It can therefore be convenient to use a string of that form in a single string insert line to copy a multi line range into a code module. http://www.eileenslounge.com/viewtop...=31395#p242941
For this Thread we will insert/add single lines at a time, to allow some formatting of each line.
Multi line inserting. Via looping.
If lines are inserted in a loop, then things occur in a simple way, (We are considering here that each loop inserts one line ): a line is inserted exactly as indicated in the argument line:=. All existing lines are shifted upwards. So finally all lines above the code section inserted by the looping will be at a line number equall to its original number + the number of loops done.
Inserting, or rather adding, lines beyond the current last line is a bit more subtle to understand.
As noted previously, we can’t actually directly add a line or insert above the last existing line. If we try to add/insert above the last line then a new line is added. This means that the actual line number given will be ignored, and the added code section will be directly above the previous coding. However, if we want our given line number to “pseudo” define the line number finally of the added lines, then we can do that if we give the start line number that of one more than the current last line number.
Here a demo example
Before:
Code:
'line 1 Note: I add these numbers just for demonstration to represent the numbers held internally by VBA for referring to lines by number in a code module: We may use any numbers in any order. But they have nothing to do with the internally held numbers. I choose to use the same value as the internally held corresponding numbers for the line here just for demonstration purposes
'Line 2
Sub LoopYinLinesTiddlyHiFoo()
4 Dim VBIDEVBAProj As Object
5 Set VBIDEVBAProj = ThisWorkbook.VBProject.VBE.ActiveCodePane.codemodule
6 Dim LineNo As Long, StartLine As Long, StopLine As Long
7 Let StartLine = VBIDEVBAProj.countoflines + 1: Let StopLine = StartLine + 4
8 For LineNo = StartLine To StopLine
9 VBIDEVBAProj.insertlines Line:=LineNo, String:="'Line " & LineNo
10 Next LineNo
End Sub
After:
Code:
'line 1
'Line 2
Sub LoopYinLinesTiddlyHiFoo()
4 Dim VBIDEVBAProj As Object
5 Set VBIDEVBAProj = ThisWorkbook.VBProject.VBE.ActiveCodePane.codemodule
6 Dim LineNo As Long, StartLine As Long, StopLine As Long
7 Let StartLine = VBIDEVBAProj.countoflines + 1: Let StopLine = StartLine + 4
8 For LineNo = StartLine To StopLine
9 VBIDEVBAProj.insertlines Line:=LineNo, String:="'Line " & LineNo
10 Next LineNo
End Sub
'Line 12
'Line 13
'Line 14
'Line 15
'Line 16
The important point to note here is that we have not defined where the lines go directly in terms of the .insertlines Line:= given. We have simply adjusted the numbers used in the loop so that it appears that way.
Just to help make that point clear. Consider the same experiment again, with just one change. This time in the Before we are attempting to insert lines way down in the code module, by adjusting the number given in .insertlines Line:= by 100
Before:
Code:
'line 1
'Line 2
Sub LoopYinLinesTiddlyHiFoo()
4 Dim VBIDEVBAProj As Object
5 Set VBIDEVBAProj = ThisWorkbook.VBProject.VBE.ActiveCodePane.codemodule
6 Dim LineNo As Long, StartLine As Long, StopLine As Long
7 Let StartLine = VBIDEVBAProj.countoflines + 1: Let StopLine = StartLine + 4
8 For LineNo = StartLine To StopLine
9 VBIDEVBAProj.insertlines Line:=LineNo + 100, String:="'Line " & LineNo
10 Next LineNo
End Sub
The corresponding After , in terms of the added lines is exactly the same as before:
Code:
'line 1
'Line 2
Sub LoopYinLinesTiddlyHiFoo()
4 Dim VBIDEVBAProj As Object
5 Set VBIDEVBAProj = ThisWorkbook.VBProject.VBE.ActiveCodePane.codemodule
6 Dim LineNo As Long, StartLine As Long, StopLine As Long
7 Let StartLine = VBIDEVBAProj.countoflines + 1: Let StopLine = StartLine + 4
8 For LineNo = StartLine To StopLine
9 VBIDEVBAProj.insertlines Line:=LineNo + 100, String:="'Line " & LineNo
10 Next LineNo
End Sub
'Line 12
'Line 13
'Line 14
'Line 15
'Line 16
Reply With Quote
Bookmarks