Excel File (range) SaveAs .htm ( Publish Excel Range )
Excel File (range) SaveAs .htm
As noted in the previous post, we can simply save an existing Microsoft Word or Excel file with the extension .htm
For a Word file there is not much more to it than that: the entire file is saved in a HTML code format, ( https://tinyurl.com/yash9gta
SaveAs Word.JPG Save As Word doc to htm.JPG https://imgur.com/h7XFAQX , https://imgur.com/vhRE9CC )
In this post , the case of an Excel file saved as .htm format is considered. In this case, if a range is selected before the SaveAs .htm, then at the Save action , an additional dialogue box comes up.
Consider us saving the header range:
Using Excel 2007 32 bit
Row\Col |
A |
B |
C |
D |
E |
F |
G |
H |
I |
J |
K |
2 |
Machine EQ.ID |
Manufacture |
Model |
Description |
Serial Number |
Weekly
Date of Service |
Weekly
Next Service |
Monthly
Date of Service |
Monthly
Next Service |
Quarterly
Date of Service |
Quarterly
Next Service |
Worksheet: Equipment PM
SaveAs.jpg : https://imgur.com/ISfW7tH
SaveAshtm.jpg: https://imgur.com/muyoVqQ
After selecting .htm and Save for an Excel File, another Dialogue box pops up.
ExcelDialogueBoxRangeSelect htm.JPG : https://imgur.com/14iF2SM
At this point no file has been saved. One can see that one can choose to just save the selected Range, in this case, 'Equipment PM'!$A$2:$K$2
On attempting to save a second time, another Dialogue box pops up , which I don’t quite understand, _...
PublishExcelRange.JPG : https://imgur.com/6b5TV9j
_.. but on hitting Publish, the File appears to be saved:
SavedExcel htm.JPG : https://imgur.com/WapD7Sa
_.....
Examining saved htm file
A noted, such a file can be directly sent as the main body of an Email sent. This is commonly done.
I prefer not to do that.
My way of doing this is to examine the file and look for the relevant HTML coding which I need. In this case I need information about that spreadsheet range 'Equipment PM'!$A$2:$K$2
To simply examine the file, I open it with a simple text editor, ( and for convenience save it as such also )
RightClickOn htmFile OpenWithTextEditor.JPG : https://imgur.com/OpKEf8o
SaveAs txt File.JPG : https://imgur.com/ab1gXN8
Saved as Txt File.jpg. : https://imgur.com/ohhQ7td
Here is that File : “Equipment- Maint Records A2-K2 Header.txt” : https://app.box.com/s/uk3qnvuxizm10bvwoye1epg2zi1fs8qz
I am not an HTML expert, but with a bit of common sense , I start examining the code and looking for info relevant to an 11 cell row, A2-K2
This part looks promising:
HTML Code:
<tr class=xl18712769 height=64 style='mso-height-source:userset;height:48.0pt'>
<td height=64 class=xl18312769 width=80 style='height:48.0pt;width:60pt'>Machine
EQ.ID</td>
<td class=xl18412769 width=207 style='width:155pt'>Manufacture</td>
<td class=xl18512769 width=113 style='border-left:none;width:85pt'>Model</td>
<td class=xl18612769 width=143 style='border-left:none;width:107pt'>Description</td>
<td class=xl18512769 width=158 style='width:119pt'>Serial Number</td>
<td class=xl19012769 width=115 style='border-left:none;width:86pt'>Weekly<br>
<span style='mso-spacerun:yes'> </span>Date of Service</td>
<td class=xl19012769 width=97 style='border-left:none;width:73pt'>Weekly<br>
<span style='mso-spacerun:yes'> </span>Next Service<span
style='mso-spacerun:yes'> </span></td>
<td class=xl19112769 width=154 style='border-left:none;width:116pt'>Monthly<br>
Date of Service</td>
<td class=xl19112769 width=154 style='border-left:none;width:116pt'>Monthly
<br>
Next<span style='mso-spacerun:yes'> </span>Service</td>
<td class=xl18812769 width=161 style='border-left:none;width:121pt'>Quarterly<br>
Date of Service</td>
<td class=xl19312769 width=161 style='width:121pt'>Quarterly <br>
Next Service</td>
Information that looks like it might be relevant is
_ The reference to widths
80
207
113
143
158
115
97
154
154
161
161
_ The reference to a “class”. If I look further in the code then I can glean some info about the background colour associated with those “classes” .
Class info in HTML text file.JPG : https://imgur.com/JmZdzmx
So this looks like the final 11 cell (column) information that could be relevant:
80
207 #D8D8D8
113
143 #D8D8D8
158
115 #92D050
97 #92D050
154 yellow
154 yellow
161 #D8D8D8
161 #D8D8D8
_._____________
Modifying code for Formatted header
Currently no header is included in the sent HTML string.
So it is convenient to construct a header manually as an addition to the start of the HTML string to be .Sended
This will be done in the next post
Simple HTML code string for a header table incl. background colour
Simple HTML code string for a header table incl. background colour
We want a string to send as the .htmlbody in our code which will produce something of this form:
Machine EQ.ID |
Manufacture |
Model |
Description |
Serial Number |
Weekly
Date of Service |
Weekly
Next Service |
Monthly
Date of Service |
Monthly
Next Service |
Quarterly
Date of Service |
Quarterly
Next Service |
Worksheet: Equipment PM
Lets say we put this in a sting variable, strHeader
The current String, held in variable strHTML already contains the data to be sent for that table, held in variable, ProTble.
Code:
Dim strHTML As String: Let strHTML = ProTble
So we simply can add our header then finally via
__Let strHTML = strHeader + ProTble
From the last post, we have the following information for column width and background color
80
207 #D8D8D8
113
143 #D8D8D8
158
115 #92D050
97 #92D050
154 yellow
154 yellow
161 #D8D8D8
161 #D8D8D8
Correspondingly from the Excel range we see the header text and so have
80 EQ.ID
207 #D8D8D8 Manufacture
113 Model
143 #D8D8D8 Description
158 Serial Number
115 #92D050 Weekly
97 #92D050 Weekly Nxt
154 yellow Monthly
154 yellow Monthly Nxt
161 #D8D8D8 Quarterly
161 #D8D8D8 Quarterly Nxt
Here is a basic HTML code string for the required table, ( The vbCrLf :are not needed and are ignored by any interpreting of the HTML string. We have them for convenience of viewing the code in an editor such as a simple Notepad text file. Also the _ _ _ are just for VBA code splitting of code lines, for convenience of viewing in VB Editors )
HTML Code:
' Table start column info
Let strHeader = "<table width=1623>" & vbCrLf & _
"<col width=80>" & vbCrLf & _
"<col width=207>" & vbCrLf & _
"<col width=113>" & vbCrLf & _
"<col width=143>" & vbCrLf & _
"<col width=158>" & vbCrLf & _
"<col width=115>" & vbCrLf & _
"<col width=97>" & vbCrLf & _
"<col width=154>" & vbCrLf & _
"<col width=154>" & vbCrLf & _
"<col width=161>" & vbCrLf & _
"<col width=161>" & vbCrLf & vbCrLf
' single header row
Let strHeader = strHeader & _
"<tr height=17>" & vbCrLf & _
"<td> EQ.ID </td>" & vbCrLf & _
"<td style=""background:#D8D8D8""> Manufacture </td>" & vbCrLf & _
"<td> Model </td>" & vbCrLf & _
"<td style=""color:Black; background:#D8D8D8""> Description </td>" & vbCrLf & _
"<td> Serial Number </td>" & vbCrLf & _
"<td style=""background:#92D050""> Weekly </td>" & vbCrLf & _
"<td style=""background:#92D050""> Weekly Nxt </td>" & vbCrLf & _
"<td style=""background:yellow""> Monthly </td>" & vbCrLf & _
"<td style=""background:yellow""> Monthly Nxt </td>" & vbCrLf & _
"<td style=""background:#D8D8D8""> Quarterly </td>" & vbCrLf & _
"<td style=""background:#D8D8D8""> Quarterly Nxt </td>" & vbCrLf & _
"</tr>"
_.______
Results:
The initial results suggest that a bit of adjustments are necessary to get a convenient total width
InitialHeader gmail.JPG : https://imgur.com/D3xsfnu
InitialHeader t-online.JPG : https://imgur.com/QK68KcS
One possible simple way to do this adjustment is included in the final initial test code:
Code:
Rem 4.5) header row as HTML table
Dim strHeader As String, Adj As Double: Let Adj = 0.5
' 4.5a) Table start column info
Let strHeader = "<table width=" & 1623 * Adj & ">" & vbCrLf & _
"<col width=" & 80 * Adj & ">" & vbCrLf & _
"<col width=" & 207 * Adj & ">" & vbCrLf & _
"<col width=" & 113 * Adj & ">" & vbCrLf & _
"<col width=" & 143 * Adj & ">" & vbCrLf & _
"<col width=" & 158 * Adj & ">" & vbCrLf & _
"<col width=" & 115 * Adj & ">" & vbCrLf & _
"<col width=" & 97 * Adj & ">" & vbCrLf & _
"<col width=" & 154 * Adj & ">" & vbCrLf & _
"<col width=" & 154 * Adj & ">" & vbCrLf & _
"<col width=" & 161 * Adj & ">" & vbCrLf & _
"<col width=" & 161 * Adj & ">" & vbCrLf & vbCrLf
' 4.5b) Single header row
Let strHeader = strHeader & _
"<tr height=17>" & vbCrLf & _
"<td>EQ. ID</td>" & vbCrLf & _
"<td style=""background:#D8D8D8""> Manufacture </td>" & vbCrLf & _
"<td>Model</td>" & vbCrLf & _
"<td style=""color:Black;background:#D8D8D8"">Description</td>" & vbCrLf & _
"<td>Serial Number</td>" & vbCrLf & _
"<td style=""background:#92D050"">Weekly</td>" & vbCrLf & _
"<td style=""background:#92D050"">Weekly Next</td>" & vbCrLf & _
"<td style=""background:yellow"">Monthly</td>" & vbCrLf & _
"<td style=""background:yellow"">Monthly Next</td>" & vbCrLf & _
"<td style=""background:#D8D8D8"">Quarterly</td>" & vbCrLf & _
"<td style=""background:#D8D8D8"">Quarterly Next</td>" & vbCrLf & _
"</tr>"
The following results are then obtained
HalfWidthHeader t-online.JPG : https://imgur.com/Gup4W9t
HalfWidthHeader gmail.JPG : https://imgur.com/PpWtDKM
Named Ranges: Name and object string reference .Value
Copy of Reply to this:
https://www.thespreadsheetguru.com/b...ent-4082118270
Quote:
Originally Posted by PG CodeRider
... to scope a workbook in a named range , best syntax is: ThisWorkbook.Names("YourName").RefersToRange.
Using ThisWorkbook.Range("YourName") will not work!
@ PG CodeRider. Hi!
_1) I’m not familiar with a syntax like ThisWorkbook.Names("YourName").RefersToRange.___ So I am not quite completely sure what you are saying. Can you clarify a bit please, thanks.
_2) & _3) :- These couple of points might be relevant somehow to the issue you are talking about
_Point 2).
This sort of thing, ThisWorkbook.Names("YourName").____ , I would expect would “do something sensible” as it will return a Name object. So then, to that Name object you could , for example , apply the Name property, and so , for example, rename the Named Range object. ( see '2(ii) in my demo code below).
In addition, it seems that the named Range object is one of those objects which if you “use” the object in a code part where a string is expected, rather than an object, then , instead of erroring with type mismatch as you might at first expect, a different phenomena occurs: In such a “usage” it actually “gives you” some string reference. In the case of a named Range, what it “gives you” appears to be the string reference of the range to which the Named Range refers, (see '2(iii)a) in my demo code below). You can use this strange phenomena then to actually change the referred to range , (see '2(iii)b) in my demo code below).
(This phenomena might be something to do with that the .Value property is usually the default property for an object)
_Point 3) I would not expect something like ThisWorkbook.Range("YourName") to ever “work” in most circumstances. The reason for this. I think, is simply that there is no Range property of a workbook.
As far as I know the main range “things” are the Worksheet range property and the Application Range “thing”. ( The latter is often called a property, but I sometimes think of it as a Method.. or “thing” )
In Rem _Point 3) of my demo code below I look at these ways of getting at a named range.
So, for example, something of this form ThisWorkbook.Application.Range(YourName").____ will usually “do something sensible”. For example, '3)(ii)b) and '3)(ii)c) in my demo code below is a version of what I call the “short hand way” of making a workbooks scope Named Range.
( I don’t think I can do anything similar to '2(iii)b) via the range things to actually change the referred to range , as I guess that might cause a chaos …. Maybe…. )
_.________________
If you want to try my demo routine below, Sub ObjRefValueWonkEtc() , make sure you also copy the other two routines, so three in total.
Alan
Code:
Sub ObjRefValueWonkEtc() ' https://www.thespreadsheetguru.com/blog/the-vba-guide-to-named-ranges#comment-4082118270
Rem 0) Clean the sheet of names
Call FukOffNames
Call getWbNames
Rem _Point 2) Name Object Names and Name string range references
'2(i) Add a Named range name object with workbook scope
ThisWorkbook.Names.Add Name:="YourFirstName", RefersTo:=ActiveSheet.Range("B1")
Call getWbNames
'2(ii) Use .Name property to change the name
Let ThisWorkbook.Names("YourFirstName").Name = "YourSecondName" ' Change name using name property of Name object
Call getWbNames
Dim objYourName As Object: Set objYourName = ThisWorkbook.Names("YourSecondName") ' use an object variable for the Name object
Call getWbNames
Let objYourName.Name = "YourThirdName" ' Change name using Name property of Name object again, ( using the object variable this time )
Call getWbNames
' Check info
'2(iii)a)
MsgBox prompt:="You last name for the cell B1 was """ & objYourName.Name & """" & vbCrLf & "and if you ""use"" that object where a string is expected like here in this MsgBox, then you get a reference looking like this: " & """" & objYourName & """"
'2(iii)b) use the string ""Value" property" to change the refered to range reference
Let objYourName.Value = "=" & ActiveSheet.Name & "!$B$2" ' give the typical full range reference for a cell
Call getWbNames
Dim strRef As String: Let strRef = objYourName ' this defaults probably to the next line
Let strRef = objYourName.Value
Rem _Point 3) Use range to access a Named Range
'3)(i) use Worksheets range property
Let ActiveSheet.Range(strRef).Value = "Allo4"
' Let Worksheets.Item(1).Range(strRef).Value = "Allo5" ' This will only work if the Named Range is in, that is to say, refers to, the first worksheet
' Let Worksheets.Item("NamedRanges").Range(strRef).Value = "Allo6" ' This will only work if the Named Range is in, that is to say, refers to, the worksheet with tab Name "NamedRanges"
'3)(ii)a) Use Application.Range "thing" to get at the named range
Let ThisWorkbook.Application.Range("YourThirdName").Value = "Allo1"
Let ThisWorkbook.Application.Range(strRef).Value = "Allo2"
Let strRef = Replace(strRef, "$B$2", "YourThirdName") ' replace the cell address with the name we last gave that cell
Let ThisWorkbook.Application.Range(strRef).Value = "Allo3"
Let Application.Range("=" & "'" & ThisWorkbook.Path & "\" & "[" & ThisWorkbook.Name & "]" & ActiveSheet.Name & "'" & "!" & "YourThirdName").Value = "Allo7" ' Just for fun to demo that Application.Range(" ") syntax will accept a full reference
'3)(ii)b) Give another names to an existing Named Range using "short hand way"
Let ThisWorkbook.Application.Range(strRef).Name = "YourforthName" ' Note this adds another name, it does not replace the existing one
Let ThisWorkbook.Application.Range(ActiveSheet.Name & "!" & "YourThirdName").Name = "YourfifthName" ' Note this adds another name, it does not replace the existing one
'3)(ii)c) Add a new named range using "short hand way"
Let ThisWorkbook.Worksheets.Item(2).Range("G4").Name = "WorkbookScopeNamedRangeInCellG4InSecondWorksheetOfThisWorkbook"
Call getWbNames
Rem 4 Just for passing fun .. strange phenomena .. the next line won't work initially _....
Call FukOffNames: Call getWbNames
'4a) .. strange phenomena .. the next line won't work initially _....
'Let ThisWorkbook.Worksheets.Item(2).Range("G5").Name.Name = "WorkbookScopeNamedRangeInCellG5InSecondWorksheetOfThisWorkbook"
' _.... But if I do this next line first, then it will work
Let ThisWorkbook.Worksheets.Item(2).Range("G5").Name = "WorkbookScopeNamedRangeInCellG5InSecondWorksheetOfThisWorkbookName1"
Call getWbNames
Let ThisWorkbook.Worksheets.Item(2).Range("G5").Name.Name = "WorkbookScopeNamedRangeInCellG5InSecondWorksheetOfThisWorkbookName2"
Call getWbNames
'4b) .. what is going on ... attempt to explain , ThisWorkbook.Worksheets.Item(2).Range("G5").Name = "WorkbookScopeNamedRangeInCellG5InSecondWorksheetOfThisWorkbookName1" somehow made a named range object, and gave it the name "WorkbookScopeNamedRangeInCellG5InSecondWorksheetOfThisWorkbookName1"
Dim objNameG5 As Name: Set objNameG5 = ThisWorkbook.Worksheets.Item(2).Range("G5").Name ' .. as I have a name object, then I can assign an object variable to it.
Call getWbNames
Let objNameG5.Name = "WorkbookScopeNamedRangeInCellG5InSecondWorksheetOfThisWorkbookName3" ' this has now given a new name to the name Object, objNameG5
Call getWbNames
Let ThisWorkbook.Worksheets.Item(2).Range("G5").Name.Name = "WorkbookScopeNamedRangeInCellG5InSecondWorksheetOfThisWorkbookName4" ' This does the same as objNameG5.Name = "WorkbookScopeNamedRangeInCellG5InSecondWorksheetOfThisWorkbookName4"
Call getWbNames
End Sub
Sub getWbNames()
Dim Nme As Name, Cnt As Long
For Each Nme In ThisWorkbook.Names
Let Cnt = Cnt + 1
Dim strNames As String: Let strNames = strNames & Cnt & " "
If TypeOf Nme.Parent Is Worksheet Then ' Answer 2 - https://stackoverflow.com/questions/8656793/progammatically-determine-if-a-named-range-is-scoped-to-a-workbook
Let strNames = strNames & """" & Nme.Name & """ refers to the range ref """ & Nme & """ and and can be referenced only from worksheet with tab Name """ & Nme.Parent.Name & """ ( Worksheet Scope ). ( That worksheet is in the workbook """ & Nme.Parent.Parent.Name & """ )" & vbCrLf & vbCrLf
Else
Let strNames = strNames & """" & Nme.Name & """ refers to the range ref """ & Nme & """ and can be referenced from any sheet in the Workbook """ & Nme.Parent.Name & """ ( Workbook Scope )" & vbCrLf & vbCrLf
End If
Next Nme
If strNames = "" Then
MsgBox prompt:="I don't think you have any Names at the moment luvy"
Else
MsgBox prompt:=strNames, Title:="Spreadsheet Named range objects in " & ThisWorkbook.Name & " are:-"
End If
End Sub
Sub FukOffNames()
Dim Nme As Name
For Each Nme In ThisWorkbook.Names
Nme.Delete
Next Nme
End Sub
Edit: I added Rem 4 just to the post here at excelfox for fun as I have more space, and the code 'comments go to the right , so the code looks less cluttered
_.___________________
Edit 2 ...… a few hours later… after answering a few comments at the blog ( https://www.thespreadsheetguru.com/b...o-named-ranges ) I understand now what you PG CodeRider means by “….to scope a workbook in a named range , best syntax is: ThisWorkbook.Names("YourName").RefersToRange….”
I see now that if I have an existing Named range with the name , "YourName" , then something like this will change the range it refers to
ThisWorkbook.Names("YourName").RefersToRange = "=Sheet1!$A$1"
So that answers my first question, 1)
_.________
Something like this will also work
Range("=Sheet1!$A$1").Name = "YourName"
The first time you use that, a Name object with the string name "YourName" is made if it does not already exist. ( It has workbooks scope and refers to the range $A$1 in Sheet1). Once it exists, then code lines like this will work to change the range it refers to, or its string Name
Application.Range("=Sheet1!$A$1").Name.RefersTo = "=Sheet1!$X$700"
Application.Range("YourName").Name.RefersTo = "=Sheet2!$A$100"
Application.Range("=Sheet2!$A$100").Name.RefersTo = "=Sheet3!$A$100"
Application.Range("YourName").Name.Name = "YourNewName"
I refer to a code line like Range("=Sheet1!$A$1").Name = "YourName" as a shorthand way to make a workbook scoped Named range. It is quite curious how/ why it works. I am not sure that I understand exactly how/ why it works. … Initially this, Range("=Sheet1!$A$1").Name , is returning a name object, not a name string. But a named range seems to be one of those objects which returns a reference string if you use the object variable in a place where a string is given or expected. Also I note that the default property of a lot of objects is .Value. I find that if I apply the .Value property to a Name object, then I get the same as if I apply the . RefersTo property. Somehow the result of all this is that a code line like Range("=Sheet1!$A$1").Name = "YourName" seems to make a named range object and give it the string name of "YourName”
5 Attachment(s)
Positioning of procedure separation Line in a code module in the VBIDE
Positioning of procedure separation Line in the Visual Basic Development Environment
These are some notes based on a discussion here.. http://www.eileenslounge.com/viewtopic.php?f=30&t=31756
Lisa Green had noticed something strange in how VBA divides procedures.....
It appears that in VBA, that is to say in the Visual Basic Development Environment Window , ( that window seen by hitting Alt+F11 from a spreadsheet ) , the convention has been set to separate procedures by a line extending across the code pane Window.
We see these as appearing as a series of underscores, __________________ , extending across the Visual Basic Development Environment Window
Code:
End Sub ' The dividing line appears to us as a line of underscores ____
Usually, if we did write exactly this ' The dividing line appears to us as a line of underscores ____ ' , on that terminating line above , then we would not see those underscores, ____ , as they get hidden in the terminating line:
Hidden_____InDividingLine.JPG : https://imgur.com/7DyP9Om
Attachment 2142Attachment 2176
The above screenshot shows the simplest case of routines with no "space" in between. In that simple case, the position of the dividing line is as expected in between the procedures. The situation is a bit more complicated if there is a separation in between procedures….
Effect of blank lines ( or 'commented lines ) In Between
Between procedures we may add blank lines or ' comment lines. If this is done, it appears that the convention has been set to place the line somewhere between the procedures in this blank/ 'comment range, and the lines above the line "belong" to the procedure above, that is to say the last or preeceding procedure, and the lines below the line "belong" to the procedure below, that is to say the next procedure, http://www.eileenslounge.com/viewtop...=31756#p245845
The documentation is not 100% clear on how the position of the dividing is determined , that is to say how the row on which it physically appears as a long series of underscores, __________________ is determined
There is no obvious logic to the way in which the dividing line can be positioned, that is to say , how to determine on which the dividing line appears as a long series of underscores, __________________
Some initial experiments suggest that is influenced by positioning of blank lines and any single underscores _
Line continuation / Break points : single underscores _
We note in passing , that single underscores are used in coding generally to allow us to divide a single line of code into several lines for ease of reading. For example:
Code:
' http://www.excelfox.com/forum/showthread.php/2293-Move-values-in-rows-at-the-end-of-the-preceding-row-*SOLVED*?p=10891#post10891
Sub LineContunuationUnderscores() ' https://docs.microsoft.com/en-us/dotnet/visual-basic/programming-guide/program-structure/how-to-break-and-combine-statements-in-code
Dim LastRow As Long
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
' Without line breaks
Range("A1:A" & LastRow) = Evaluate(Replace(Replace("IF(ISNUMBER(0+SUBSTITUTE(SUBSTITUTE(A2:A#,"" "",""""),"","","""")),IF(LEFT(A1:A@,4)=""2018"",TRIM(A1:A@&"" ""&A2:A#),""""),IF(LEFT(A1:A@,4)=""2018"",A1:A@,""""))", "#", LastRow + 1), "@", LastRow))
' With Line breaks
LastRow = _
Cells(Rows.Count, "A").End(xlUp).Row
Range("A1:A" & LastRow) = Evaluate(Replace(Replace( _
"IF(ISNUMBER(0+SUBSTITUTE(SUBSTITUTE(" & _
"A2:A#,"" "",""""),"","","""")),IF(LEFT(A1:A@,4)" & _
"=""2018"",TRIM(A1:A@&"" ""&A2:A#),"""")," & _
"IF(LEFT(A1:A@,4)=""2018"",A1:A@,""""))", "#", _
LastRow + 1), "@", LastRow))
' This is _
acceptable in _
or out of a procedure
End Sub
' This is _
acceptable in _
or out of a procedure__________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________
Further, we note that the line continuation , sometimes called a line break, _ , also applies to comments whether in a procedure or between procedures:
' This is _
acceptable in _
or out of a procedure
_._________
Determining position of horizontal line dividing procedures when blank or comment lines are between procedures
Sir Narios .
The documentation is not 100% clear on how the position of the dividing is determined , that is to say how the row on which it physically appears as a long series of underscores, __________________ is determined
There is no obvious logic to the way in which the dividing line can be positioned, that is to say , how to determine on which the dividing line appears as a long series of underscores, __________________
Some initial experiments suggest that is influenced by positioning of blank lines and any single underscores _
There appear to be 3 scenarios to consider in order to place the line somewhere in between, ( 4 if you consider the simple case of all lines containing comments or all lines being blank )
Scenario 0
' _(0)
If all lines are blank, or all lines are full with comments ( which exclude line continuations )
No single underscores in any line
The break is immediately after the Last/ upper procedure. (This is the same as the case for no separation between routines )
Scenario 0 .JPG : https://imgur.com/pA4grFL
Attachment 2143 Attachment 2177
Code:
Sub Scenario_0()
' _(0)
End Sub___________________________________________________________________________________________________________________________________________________________________________________________________________
Sub senario_0()
' _(0)
End Sub_____________________________________________________________________________________________________________________________________________________________________________________________________________________
'
'
'
Sub surnario_0()
' _(0)
End Sub_____________________________________________________________________________________________________________________________________________________________________________________________________________________________
Scenario 1
' _(i) Attachment 2141 SirNario_1.JPG . https://imgur.com/zmr2up2 Attachment 2178
If no line continuations are present and there is a one or more blank lines, then the line before the first blank line down from the upper routine is taken as the break point.
No single underscores in any line
Code:
Sub Senario_1()
' _(i)
End Sub
'
'________________________________________________________________________________________________________________________________________________________________________________________________________________________________
Sub surnaria_1()
' _(i)
End Sub
'____________________________________________________________________________________________________________________________________________________________________________________________________________________________________
''
'
Sub Sirnario_1()
' _(i)
End Sub_______________________________________________________________________________________________________________________________________________________________________________________________________________
'
'
Sub snaria_1()
' _(i)
End Sub
Scenario 2
' _(ii) Attachment 2144 SirNario_2.JPG : https://imgur.com/D2LqloV Attachment 2179
If there are one or more line continuations present then the break point will be placed at the first blank line down after the last line after the line continuation … unless scenario (iii)
Code:
Sub Scnari_2()
' _(ii)
End Sub
''
'
' _
'____________________________________________________________________________________________________________________________________________________________________________________________________________________________________
'
Sub Sernario_2()
' _(ii)
End Sub
'
'
' _
'
'___________________________________________________________________________________________________________________________________________________________________________________________________________________________________
'
Sub Sirnarnio_2()
' _(ii)
End Sub
Scenario 3
' _ (iii) Attachment 2146 SirNario_3.JPG : https://imgur.com/ho56uBN Attachment 2180
There are no blank lines after the first line looking down after the last line continuation looking down, or after the first line looking down after the last line continuation looking down all lines contain comments . In this case, the break is at the line after the line on which the line continuation is on.
Code:
Sub scenario_3()
' _(iii)
End Sub
''
' _
____________________________________________________________________________________________________________________________________________________________________________________________________________________________________
'
'
Sub SirNario_3()
' _(iii)
End Sub
'
' _
'____________________________________________________________________________________________________________________________________________________________________________________________________________________________________
'
'
Sub snuaro_3()
' _(iii)
End Sub
'
'
' _
____________________________________________________________________________________________________________________________________________________________________________________________________________________________________
Sub SirNario_3()
End Sub
'
' _
'____________________________________________________________________________________________________________________________________________________________________________________________________________________________________
Sub SurNario_3()
End Sub
In the next post is some attempt at a worded explanation of the situation.
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