Positioning of Border between routine sections in VBA. Summary
Positioning of Border between routine sections in VBA. Summary
This is an attempt at a short summary, ( Edit: which failed, ….never mind :-) ).
For more details and justifications see the last two posts.
( https://tinyurl.com/yyw85dmg , https://tinyurl.com/yy9rwf85 )
Summary
In VBA, in a code module, coding sections, such as procedures and the initial Declaration section, may be directly following on, or may have sections in between which can be ' commented or blank lines , or combinations thereof.
For the case of coding sections directly following on from each other, the border , ( shown as a continuous light grey line across the code module ) , is in the obvious position
Code:
Option Explicit
Public LudwigII As Legend___________________________________________________________________________________________________________________________________
Sub Sub1()
‘ hkshh
End Sub___________________________________________________________________________________________________________________________________
Sub Sub2()
‘ asskasb
End Sub___________________________________________________________________________________________________________________________________
Function Funnky1()
‘ askjhsdh
End Function
For the case of separations of 1 or more lines in between the situation is less obvious.
The simple conclusion from the documentation is that the “comments above a routine belong to the routine below”
With a bit of imagination and lateral thinking, and working somewhat in reverse from knowing the answer, we can accept that, at least loosely.
The start point is to say that VBA somehow looks up from the top of a routine start code line, ( A routine start is a code line something of the form Sub MySub( ) or Function MyFunction( ) ) . In looking up, VBA does…..
_(i) attempt to find an “End” , after which a blank line is somewhere after ( looking back down ) .
_(ii) If it does not find one of those situations, then it stops looking at the “End”
This logic takes care of the simple situations of
_(i) all blanks in between
_(ii) all commented lines in between.
To deal with the mixed case of blank and commented lines we must consider carefully what an “End” is: The first two are obvious, the third is not.
In all cases they are not necessarily where the border line will be placed: remember the logic:
……….. VBA somehow looks up from the top of a routine and
_(i) attempts to find an “End” , after which a blank line is Present.
_(ii) If it does not find one then it stops looking at the “End”
What is an “End”
_a) an End ______ type code line: The last line of a routine which is coding: the terminating code instruction.
_b) The last declaration statement at the top of a module
_c) Whether by design or accident, VBA “sees” the next line down after a trailing _ as an “End” : Schematically like this:
'
' _ _
_______This line is seen as an “End” code line ( but may or may not contain the border Line____ )
'
'
( VBA will stop looking at the first “End” that it finds, even the situation _c) , so we only need concern pourselves with the fisrt trailing _ looking up from a procedure start code line
_._________________________________________
Here is just one, very limited, example, showing a slightly extended version of the first example above. This mainly shows the position of the "End"s
This example shows the effect of a trailing _ _
But there are other scenarios to consider with and without a _ which can lead to many different positioning of the border line, but which I think all can be explained through the logic discussed in this post. Note, for example, in all the examples below, the border is taken as the "End" line, but that does not need to be the case. It is in those examples below because either the next line is blank or there is no blank to be found.
The border line may be, but must not necessarily be, on an "End" line.
Code:
Option Explicit
Public LudwigII As Legend '__This is seen as a b) "End"________________________________________________________________________________________________________________________
Sub Sub1()
' code
End Sub '____________________This is seen as a a) "End"___________________________________________________________________________________________________________
Sub Sub2()
' code
End Sub '_____________________This is seen as a a) "End"__________________________________________________________________________________________________________
Function Funnky1()
' code
End Function '_ _ _ _ _ _This would be seen as a a) "End", but isn't because VBA does not get this far – after looking back up from the procedure below, it mistakes the line after a _ as an “End”.
' Comments under a Sub
' last Comment line _
_______under a Sub________This is seen as a c) "End"__________________________________________________________________________________________________________
' First Comment above a Sub
' Comment above a Sub
'
Sub Ssenario2()
' code
End Sub
'
_.______________________________________________
Pseudo routine Logic
Finally a simple pseudo code of the logic. There are probably many different code logics which you could think up. Here is one.
In Words:
A prerequisite is to understand my suggested concept of a “End” line.
The routine starts at the signature or open line or routine Start Line…etc….for example, it starts at this sort of bit: Sub MySub()
It has a Main Outer Loop which goes up one row at a time, and it keeps Doing that until it finds an “End” line.
_________ If it finds an “End” then it goes back down line for line in an Inner Loop -- , looking for an blank line, and it keeps Doing that While it has not got back down to the original start point of the pseudo routine
_________________ If it is at a blank line at any time , in other words it found the fist blank line looking down, .. it jumps out that inner loop and Exit Sub after putting the thin grey border Line ___ in the previous line. So then it does not get further down in this case, and never reaches the original start point of the pseudo routine…. _
_.....The Inner Loop will not keep going down past the original start point …and if it gets that far then the grey border Line ___ is put in the “End” line. This last bit takes care of the cases of either no blank lines after the “End”, or no lines at all between code sections. If we arrive at this point the pseudo routine ends as we have done all that needs to be done.
Code:
Sub Start At_A_Procedure_Start_Line()
Do ‘ looking for a “End” line ‘ =========Main Outer Loop============================
Move up a row:- CurrentLine=CurrentLine-1
If now at End Sub, Or at End Function , Or at 1 row down from trailing _ Or at last module top Declare line then
Note this line as = “End” line
Do While not at Start of procedure ‘ Inner Loop back down to find blank line ----------
If current line is Blank then put light grey Border____in previous line : and Exit Sub‘ This will catch the next blank line as belonging to the procedure below ( also catches the case of all blank lines in between )
Move down a row ‘ I am moving down .. looking for a blank row
Loop ‘ -------------------------------‘ Inner Loop back down to find blank line -------
‘ At this point we got back as far down as the original start point without finding a blank line
Put light grey Border___in the “End” line and Exit Sub ‘ This will catch the situation of either no in between rows or all comments, because I get back to the start without finding any blank cells
Else
‘ We are still looking for a “End” line, which we do by moving up a row in the outer Loop
End If
Loop ‘ to keep trying to find a “End” line ======Main Outer Loop========================
DoneIt
_.__________________________
If you follow that above logic carefully then, as far as I can tell, it explains all the observed behaviour.
For detailed examples see the last two posts.
If you have an example and you are not clear about how your border has come about, then please post a reply here , so that I can take a look to
_ see if it fits my proposed logic
_ if it does fit the logic , then I will try to explain that in detail for you
Alan
VB Editor's Intelligent routine to determine which code sections comments between code sections "belong”.
Determining to which code section, comments outside procedures belong in VBA
This story is made up, just for fun. It assumes that the way the things discussed in the last three posts, happen was actually planned… (… it is probably anybodies guess if it was or wasn’t planned…. )
….VBA has an intelligent routine to determine to which code sections, the 'comments between code sections “belong”.…..
Dividing coding section in VBA : Dividing code sections in the VBIDE ( VB Editor )
At the top of a code module you can add things like declaration lines, global variable etc… -- things like
Option Explicit
' Any comment lines you want
Public myGlobalvariable As Long
That above can be considered as a coding section: it is a section of code.
The other types of coding sections are perhaps more obvious, thing like these two coding sections below
Sub mySub1()
Dim strStuff As String
_Let strStuff=”Hello”
_Call myFunctionToSaySomething(strStuff)
End Sub
Function myFunctionToSaySomething(ByVal Messige As String)
Msgbox(Prompt:=”Message was “ & Messige)
End Function
Those 3 sections can all be put in the same code module, ( The declaration section must be at the top ). There can be spaces in between the sections: Any amount of lines can be left in between. Those in between lines can be left blank or filled with
' comments
( Comments are usually ignored by any coding – you can put useful notes there, or write any profanities you choose, or URLs to porno sites etc… etc…. )
Border line______between code sections.
Excel adds automatically a thin grey line, across the code module window, to divide the code sections.
In the example above, if those coding sections were all in the same code module, then VBA adds two thin grey lines: One in between the two procedures and the other between the declaration section and the first procedure.
The VBA editor does not define an “in between” section. Instead, the lines in the space between is divided up between the code sections either side of the thin grey line. The convention is that the line in which the grey line appears to be in is the last line of the coding section above. To help remember this, you can imagine the border line to be made by typing a set of continuous underscores:
________–This line with the underscores in it, is the last line in the coding section above it. In fact if you typed this comment , ' ___ Some comments , as shown below, then you would not be able to see those green underscores as they get hidden in the grey border line which VBA adds to separate the code sections.
Option Explicit
Public LudwigII As Legend
Sub Sub1()
' code
End Sub ' ___ Some comments
Sub Sub2()
' code
End Sub
If you copied the above to an empty code module, then you would actually see, something approximately like this below
UnderscoresHiddenInLightGreyBorder.JPG : https://imgur.com/3kkjLW4
Option Explicit
Public LudwigII As Legend__________________________________________________ _______
Sub Sub1()
' code
End Sub '____Some comments __________________________________________________ _____
Sub Sub2()
' code
End Sub
( The underscores which you may have added, ___ , are there, - they are just “hidden behind” the grey border line )
Note: it is important in the above experiments to add some comments after a single underscore , or alternatively use more than one underscore. This is because a singly isolated trailing underscore , __ _
, can give interesting results, as we will discuss.
Intelligent routine to determine to which code sections, the 'comments between code sections “belong”.
Although the in between lines must belong to the upper or lower coding section, we can determine to a large extent to which. In other words, we can determine to a large extent where Excel places the long light grey border line_________
Excel uses an intelligent routine: Initially, as default, this is based on reasonable assumptions as to how you might typically lay out lines which you wanted to “belong” above or below. In addition an extra feature allows you to influence slightly, the final choice made by Excel.
Here is a summary of how the intelligent routine behaves:
How Excel determines where to put the____light grey border that divides coding sections in a code module
Situation 0a) All lines between used as comments.
Excel will assume you are not wanting to divide the comments up, as you have made no attempt to separate them. It will assume that it is more likely that these comments are intended to belong to the following code section. Correspondingly you will get this sort of behaviour in the lines in between:
End Sub__________________________________________________ _________________
' Comment
' Comment
Sub Hello()
Situation 0b) All lines between are blank
Excel remains at the first default of Situation 0a)
End Sub__________________________________________________ ______________________
Sub Hello()
Situation 1a) Simple split of comments
You divide your comments by one or more consecutive empty lines, as you probably would for neatness if the lines above should belong to the coding section above, and the lines below to the next coding section. Excel positions the border appropriately
End Sub
' Comment for above
' Comment for above__________________________________________________ ______________________
' Comment for below
' Comment for below
Sub Hello()
End Sub
' Comment for above
' Comment for above__________________________________________________ __________________
' Comment for below
' Comment for below
Sub Hello()
Situation 1b) Simple split of comments , lower section has blank lines
After the initial blank separating line, or lines, any more below are left as blank lines
End Sub
' Comment for above
' Comment for above__________________________________________________ ___________________
' Comment for below
' Comment for below
Sub Hello()
Situation 2 Upper comments section has blank lines , lower section has blank lines
Situation 3 Upper comments section has blank lines , lower sections has all comment lines (no blanks), or lower section has all blank lines
Excel has a feature to allow us to do this. We have some flexibility in how to use it. It works as follows: Considering all the examples above, …. we note that Excel saw the …_
End Sub
_... as signalising the last code line of the coding section above.
The extra feature is that we can use either of these 2 possibilities within our in between section, the important part is an isolated trailing underscore _ _
' comment _
Empty line
' comment _
' comment
The effect of these is such that Excel will act in regard to the positioning of the__border line the same way as it would have, had it have seen the following in the second of those two line pairs above
' comment _
End Sub
' comment _
End Sub
The end result of this is that we can
_ have any combination of comment or blank lines above this point: Excel will no longer concern itself with those lines in terms of border position considerations: Those lines will always “belong” to the coding section above.
_ The lines below that effective End Sub will be subject to the same default rules already discussed. This allows us to make any combination of blank lines and comment lines below this point.
As far as I can tell, the only situation not possible to obtain would be to have more than one last empty lines belonging to the procedure above. All other situations should be possible. But I have not done lots of testing yet…
Here just a few final examples using the isolated trailing underscore _ _
In understanding how they come about you need to consider the furthest down isolated trailing underscore _ _
, as effectively making the line below it look like End Sub
Code:
End Sub
' Comment for above _
' Comment for above________________________________________________________________________________
' Comment for below
' Comment for below
Sub Hello()
Code:
End Sub
' Comment for above
' Comment for above _
_________________________________________________________________________________________
' Comment for below
' Comment for below
Sub Hello()
Code:
End Sub
' Comment for above
' Comment for above _
____________________________________________________________________________________________________
' Comment for below
' Comment for below
'
' Comment for below
'
Sub Hello()
Code:
End Sub
' Comment for above
' Comment for above _
___________________________________________________________________________________________________
Sub Hello()
Here below are those last 4 examples again, using the idea of the effective End Sub. If you then concentrate on the lines below this and consider them based on the original default rules, then the behaviour of the_______positioning is consistent with those default rules.
Code:
End Sub
' Comment for above _
End Sub
' Comment for above_______________________________________________________________________________
' Comment for below
' Comment for below
Sub Hello()
Code:
End Sub
' Comment for above
' Comment for above _
_End Sub__________________________________________________________________________________________
' Comment for below
' Comment for below
Sub Hello()
Code:
End Sub
' Comment for above
' Comment for above _
_End Sub___________________________________________________________________________________________________
' Comment for below
' Comment for below
'
' Comment for below
'
Sub Hello()
Code:
End Sub
' Comment for above
' Comment for above _
_End Sub__________________________________________________________________________________________________
Sub Hello()
I won’t labour the point with more examples, but conclude with saying that you can use the extra feature, along with a knowledge of the default way that Excel behaves, to get the__border line at most positions, regardless of how you have your in between lines filled or not filled.
http://www.eileenslounge.com/viewtop...247408#p247408
http://www.excelfox.com/forum/showth...ll=1#post11016
