HTML Code Test --post8798

[Excel Fox]

DocAElstein, to add a VBA code, you can use the code tags button in the editor, or just type the tags. so use [Code]....[|Code], [PHP].....[|PHP], [HTML]....[|HTML]etc... (replace the | with /)

[DocAElstein]

DocAElstein, to add a VBA code, you can use the code tags button in the editor, or just type the tags. so use [Code]....[|Code], [PHP].....[|PHP], [HTML]....[|HTML]etc... (replace the | with /)

Hi Thanks for Reply. Yeah with CODE TAGS I am familiar
Hi Fox
. Thanks for Reply. Yeah with CODE TAGS I am familiar. I have never seen them retaining the color format as you have them above. I am not sure how to achieve that in the practice without first posting a code in HTML format in a thread (as I did for the above code) and then copying That to the next Thread and putting it in code tags??? In the other forum everything in the code tags is Black/White regardless of the original format.

. There are a lot of tricky formatting problems with a typical Forums Editor, as I noticed in the other Forum I am in.

. With my last thread I had problems and could not get my HTML made table to come up, (so I posted a file over Filesnack as a "Plan B").

. I have an interesting reply to my last (First and Only here) thread. But I think I must now spend some time experimenting with the editor. I assume that is OK?

[DocAElstein]

Dim ilSanityCheck As Integer
Dim llEndLine As Long

Set olPane = Application.VBE.ActiveCodePane
olPane.GetSelection Startline:=llSRow, startcolumn:=llSCol, Endline:=llERow, Endcolumn:=llECol

slProcName = olPane.CodeModule.ProcOfLine(llSRow, vbext_pk_Proc)
llLine1 = olPane.CodeModule.ProcBodyLine(slProcName, vbext_pk_Proc)
llCountLines = olPane.CodeModule.ProcCountLines(slProcName, vbext_pk_Proc)
llStartLine = olPane.CodeModule.ProcStartLine(slProcName, vbext_pk_Proc)
llEndLine = llStartLine + llCountLines - 1

' Find Dim Line.
llCompLine1 = llLine1

Do
  slOLine1 = Trim$(olPane.CodeModule.Lines(llCompLine1, 1))
  
  If Left$(slOLine1, 4) = "Dim " Then
    Exit Do
  ElseIf Left$(slOLine1, 6) = "Const " Then

[DocAElstein]

"What’s in a String"- VBA break down Loop through character contents of a string

__Hello (or _ vbTab & “He” & “l” & “l” & “o” & vbCr & vbLf _ )
In VBA coding and probably a lot of things to do with computers what is “actually” there is a long string of “characters” . These “characters” can be what we may recognise as every day characters, like H e l l o , as well as other things which technically still go be the name of characters. Some times these other characters may be referred to as hidden characters. In this usage of the word, hidden is not really an official term, but more of an everyday term used to mean some characters in the string that in many systems which we use to “view” strings, those characters are not obvious to see to us Humans

Check what ya got in ya string
I have found it can be interesting, informing and occasionally essential, to know what I have in a string. This can be done very easily in VBA with a simple loop. In the simplest form you need to use just two simple VBA functions , one, Len , to initially get the character length so that you know how many times to loop. In the Loop you use a second function, Mid , to get at each character.

In most practical situations you will have to get the string that you want to actually look at by some means that may not be straight forward. Exactly how you do that may vary from time to time, so it is usually convenient to write a routine which will work on some string which you present it. That routine will be a Sub routine which is written to take in a string, or string variable with a string in it.

So as example we will make a routine with first (signature) line of, say
Sub LoopThroughString(ByVal MyString As String)
So we have a routin like

Sub LoopThroughString(ByVal MyString As String)



End Sub

The first ( signature ) line means that that routine will work from within another routine as a sort of a method, which when you Call it in to use, will need to be given some string value . You are allowed to pass it a variable containing a string variable as well, if you prefer: The signature line specifies that it will take the Value of that

For the purposes of this demo we will first need to have a simple routine that Calls the main routine, Sub LoopThroughString( ByVal MyString [color=Blue]As String[/color] )
It is that simple routine that we will run in our demos. You have to do that, because you cannot easily run a code such as Sub LoopThroughString( ByVal MyString [color=Blue]As String[/color] ) . VBA syntax simply does not allow you to do that easily. The simplest way to get it to run is to Call it from a simple routine which must at the Call line pass the string that I want to look at.
Either of the 4 Calling lines in the next routine are syntaxly satisfactory . So running the routine Sub MyTestString() will result in the routine FONT=Courier New] Sub LoopThroughString( ByVal MyString [color=Blue]As String[/color] ) [/FONT] running 8 times: You will get the pop up message box 8 times :
StringInfoMsgBox.JPG : https://imgur.com/cWG7z5s

Sub MyTestString()
 Call LoopThroughString(MyString:="Hello")
 LoopThroughString MyString:="Hello"
 Call LoopThroughString("Hello")
 LoopThroughString "Hello"
' In the practice we would likely have our string obtained from some mehtod and would have it held in some string variable
Dim varForMyString As String
 Let varForMyString = "Hello"
 Call LoopThroughString(MyString:=varForMyString)
 LoopThroughString MyString:=varForMyString
 Call LoopThroughString(varForMyString)
 LoopThroughString varForMyString
End Sub
Sub LoopThroughString(ByVal MyString As String)
 MsgBox prompt:="You did pass" & vbCr & vbLf & "  the following string: " & vbCr & vbLf & vbTab & """" & MyString & """", Buttons:=vbInformation, Title:="Info about the string you gave me"
End Sub

I personally prefer the syntax form which helps remind me what is going on, and so I would reduce the demo coding to Call the main routine, Sub LoopThroughString( ByVal MyString [color=Blue]As String[/color] ) , just once and supply it the string under investigation within a variable:

Sub MyTestString()
' In the practice we would likely have our string obtained from some method and would have it held in some string variable
Dim varForMyString As String
 Let varForMyString = "Hello"
 Call LoopThroughString(MyString:=varForMyString)
End Sub

Sub LoopThroughString(ByVal MyString As String)
 MsgBox prompt:="You did pass" & vbCr & vbLf & "  the following string: " & vbCr & vbLf & vbTab & """" & MyString & """", Buttons:=vbInformation, Title:="Info about the string you gave me"
End Sub

In that coding the various & vbCr & vbLf & vbTab & """" stiff is just to pretty up the format a bit and to make us aware of some of the most common hidden characters.
This shortened version might be more familiar to a complete beginner:

Sub MyTestString()
Dim varForMyString As String
 Let varForMyString = "Hello"
 LoopThroughString varForMyString
End Sub

Sub LoopThroughString(ByVal MyString As String)
 MsgBox MyString
End Sub

vbTab vbCr vbLf """"
I have not mentioned it yet, it may have been obvious, but just in case not.. The first three things there are the most common used “hidden characters” and so are really worth with getting familiar with if you are interested in looking at contents of a string. Also the way we handle quotes in a string is very awkward leading often to problems, so it is really worth getting a feel for that at an early stage.
vbCr vbLf
These come about the early days of computing. Back then strings and other things in strings passing around computers and the early days of the internet tended to find there way fed into a mechanical printer of mechanical typewriter which had paper fed into it Cr means something along the lines of carriage return which in turn means go back to the start. Usually this start means the left side of a piece of paper . You would need to do that if you are typing out along a piece of paper as eventually you would get to the other side of the paper. Almost always when you did a Cr you would need to move the piece of paper by a bit more than the height of a line so that the next printing did not go on top of thee last line printed. Typically the word “LlineFeed” was used for this process of shifting the paper, hence the Lf abbreviation
So those “hidden characters” would have been recognised by an old printer as telling it to move to a new line and go back to the start side of the paper before printing further. As coding and screens and word processing developed, those two hidden characters were the natural things to keep using to indicate a new line on what ever media we “look at” computer stuff. There are two characters there. Often in coding you can use something like vbCrLf instead. But that is still “seen” as 2 characters by most computer things: it will almost always be measured to have a Length of 2. Some computer systems will recognise it as vbCrLf. Others will “see” it as vbCr & vbLf
vbTab
This can be a bit inconsistent. Or rather, the results it gives can be very dependant on various computer settings, so that can make it tricky to use effectively. The simplest explanation is a space. More specifically it can be use to define a specific place where something my begin. In some situations an argument version is available vbTab( ) to define specifically “where something may be”. Exactly how it works can be a bit variable.

Important uses of vbTab vbCr vbLf
In computing generally the use of vbCr & vbLf will signalise a display window or text file or similar to separate a string into lines. For modern uses there often is not the requirement to have the two and it would appear that in most cases either of these 3 will result in a new line being displayed.
vbCr & vbLf
vbCr
vbLf
In some situations Excel will use vbCr & vbLf to separate rows. It appears that within a cell it usually just uses vbLf
In some situations Excel will use the vbTab
These uses of vbTab vbCr vbLf allow for some interesting alternative ways to manipulate ranges





These are the 3 situations I think, and the returned docx has three statements that I think summarise them

Scenario (i) https://imgur.com/k9hJhFG
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.

Scenario (ii) https://imgur.com/6yexJo2
Scenario( ii) https://imgur.com/C20dep7
Scenario (ii) https://imgur.com/CVqUwRC
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)

Scenario (iii) https://imgur.com/zkvMkBB
Scenario (iii) https://imgur.com/9ekvwCn
there are no blank lines after the first line looking down after the last line continuation looking down. In this case, the break is at the line after the line continuation

above
left
Insideright
under

[CODE]

End Sub ' The dividing line appears to us as a line of underscores ____

---

Sub Scenario_0()
' _(0)
End Sub
 


 Sub senario_0()
' _(0)
End Sub
'
 
'
'
Sub surnario_0()
' _(0)
End Sub

[DocAElstein]

Positioning of procedure separation 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
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

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 , then we would not see those underscores, ____
Hidden_____InDividingLine.JPG : https://imgur.com/7DyP9Om

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 _
We not 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:

' 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.
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 )
' _(0)
If all lines are blank, or all lines are full with comments ( which exclude line continuations )
No underscores in any line
The break is immediately after the upper procedure.
Scenario 0 .JPG : https://imgur.com/pA4grFL

Sub Scenario_0()
' _(0)
End Sub___________________________________________________________________________________________________________________________________________________________________________________________________________


 Sub senario_0()
' _(0)
End Sub_____________________________________________________________________________________________________________________________________________________________________________________________________________________
'
'
'
Sub surnario_0()
' _(0)
End Sub_____________________________________________________________________________________________________________________________________________________________________________________________________________________________