HTML Code Test --post8798

[DocAElstein]

' Slightly modified with lots of Comments
Sub RickRothsteinsConcatenatingBalls()
'NOTE: Change the ## A1 to the address of the top left cell of your
'existing data and change the '### G1 to the address of the top left cell where you want the converted table to go to.
**Const ExistingTableAnyCellLocation As String = "A1" '##
**Const NewTableLHCornerLocation As String = "G1" '###
**
**Dim SourceTableRange As Range 'Give a name and allow all methods and properties of range object to it
**Set SourceTableRange = Range(ExistingTableAnyCellLocation).CurrentRegion ' Give this a specific Range. CurrentRegiuon Property applied to a cell returns a complete range incorporating that cell and any ranges that it either is in or touches
**Dim SourceTableRangeTableRowsCount As Byte ' For a small Table allow it to hve up to 255 Rows
**Let SourceTableRangeTableRowsCount = SourceTableRange.Rows.Count ' This returns the number of rows in the original table

**Dim FinalTableFirstColumnRange As Range
**Set FinalTableFirstColumnRange = Range(NewTableLHCornerLocation).Resize(SourceTable RangeTableRowsCount) ' Here the resize Property applied to the Range G1 (or Cell G1 here) returns a range increased by the row number, that is to say a range equal to the first column
**
**SourceTableRange.Columns(1).Resize(, 2).Copy Destination:=FinalTableFirstColumnRange ' This is one way of copying the first two columns of the original table to The final table
**FinalTableFirstColumnRange.Columns(2).NumberForm at = "@" ' This gives a format to the second column in the final Table
**
**'FinalTableFirstColumnRange.Offset(0, 1) = _
**'Evaluate("IF(ROW()," & SourceTableRange.Columns(2).Address & "&"" - ""&" & SourceTableRange.Columns(3).Address & "&"" - ""&" & SourceTableRange.Columns(4).Address & ")")
**'FinalTableFirstColumnRange.Offset(0, 1) = _
**'Evaluate("** " & SourceTableRange.Columns(2).Address & "&"" - ""&" & SourceTableRange.Columns(3).Address & "&"" - ""&" & SourceTableRange.Columns(4).Address & "")
** FinalTableFirstColumnRange.Offset(0, 1) = _
****************Evaluate("** " & SourceTableRange.Columns(2).Address & "**** " & "&"" - ""&" & " " & SourceTableRange.Columns(3).Address & "**" & "&"" - ""&" & "" & SourceTableRange.Columns(4).Address & "")
**
**SourceTableRange.Columns(5).Copy Destination:=FinalTableFirstColumnRange.Offset(, 2) 'Column 5 of Original table is copied to column 3 of the Final table by setting the destination to 2 colums offset from the first column
**
**FinalTableFirstColumnRange.Cells(1, 0).Offset(0, 1).Value = "Numbers" ' The current heading in the second column is finally overwriten with "Numbers".**This is done here by putting the value"Numbers" in the cell which is offset by 1 column to the first cell in the Final Table First Column
End Sub 'RickRothsteinsConcatenatingBalls()
'


https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
https://www.youtube.com/watch?v=SIDLFRkUEIo&lc=UgzTF5vvB67Zbfs9qvx4AaABAg (https://www.youtube.com/watch?v=SIDLFRkUEIo&lc=UgzTF5vvB67Zbfs9qvx4AaABAg)
https://www.youtube.com/watch?v=v_1iqtOnUMg&lc=UgxLtKj969oiIu7zNb94AaABAg (https://www.youtube.com/watch?v=v_1iqtOnUMg&lc=UgxLtKj969oiIu7zNb94AaABAg)
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugxq4JHRza_zx3sz0fx4AaABAg (https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugxq4JHRza_zx3sz0fx4AaABAg)
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugxq4JHRza_zx3sz0fx4AaABAg (https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugxq4JHRza_zx3sz0fx4AaABAg)
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=UgzMCQUIQgrbec400jl4AaABAg (https://www.youtube.com/watch?v=f7xZivqLZxc&lc=UgzMCQUIQgrbec400jl4AaABAg)
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz0Uy2bCSCTb1W-0_14AaABAg (https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz0Uy2bCSCTb1W-0_14AaABAg)
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=Ugx12mI-a39T41NaZ8F4AaABAg.9iDQqIP56NV9iFD0AkeeJG (https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=Ugx12mI-a39T41NaZ8F4AaABAg.9iDQqIP56NV9iFD0AkeeJG)
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=Ugxa2VYHMWJWXA6QI294AaABAg.9irLgSdeU3r9itU7zdnW Hw (https://www.youtube.com/watch?v=vXyMScSbhk4&lc=Ugxa2VYHMWJWXA6QI294AaABAg.9irLgSdeU3r9itU7zdnW Hw)
https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgzFkoI0n_BxwnwVMcZ4AaABAg (https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgzFkoI0n_BxwnwVMcZ4AaABAg)
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz0Uy2bCSCTb1W-0_14AaABAg.9htChVuaX9W9htG01cKBzX (https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz0Uy2bCSCTb1W-0_14AaABAg.9htChVuaX9W9htG01cKBzX)
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugw6UrV69zpeKvLOeOV4AaABAg.9ht16tzryC49htJ6TpIO XR (https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugw6UrV69zpeKvLOeOV4AaABAg.9ht16tzryC49htJ6TpIO XR)
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=UgwMKwGZpDjv7vi7pCx4AaABAg (https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=UgwMKwGZpDjv7vi7pCx4AaABAg)
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugw6UrV69zpeKvLOeOV4AaABAg.9ht16tzryC49htOKs4jh 3M (https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugw6UrV69zpeKvLOeOV4AaABAg.9ht16tzryC49htOKs4jh 3M)
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=UgxVW-am20rQ5GFuJ9F4AaABAg (https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=UgxVW-am20rQ5GFuJ9F4AaABAg)
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)

[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 As String )
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 As String ) . 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 As String ) [/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 As String ) , 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
leftInsideright
under











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


[Code]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/viewtopic.php?f=30&t=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_______________________________________________ __________________________________________________ __________________________________________________ __________________________________________________ ________________________

[DocAElstein]

Further Practice with using named ranges.

Codes accompanying the notes in this post can be found here:


There two main routines , and a few Called routines
There are three files.
The main file, “MasturFile.xlsm” , has all the codes in it. You need that file open when running the codes.
The other files, “Data1.xls” and “Data2.xlsx” are intended to be data files which normally would be closed in normal use as a data file, for example when looking for or retrieving data. ( They will be opened temporarily by the main code when needed for named range work associated with the demos )

The data files look like this, a header and some data

“Data1.xls”
Row\Col
A
B
C
D

4


5FoodKcal


6Orange
50


7Apfel
60


8
Worksheet: Tabelle1

“Data2.xlsx”
Row\Col
A
B
C
D

8


9


10SupplimentKcal


11BCAA
398


12EAA
400


13
Worksheet: Tabelle1

The main File looks like this initially:
MasturFile.xlsm
Row\Col
A
B
C
D

3


4NutritionEnergy


5


6


7


8


9


10


11


12


13


14
Worksheet: Tabelle1

Here are the files at a file share site:
“MasturFile.xlsm” :
“Data1.xls” :
“Data2.xlsx” :
You should download them all into the same folder. To run the demo code , you only need to open “MasturFile.xlsm” and run the main routine, Sub FoxyNamedRanges()

The demo will involve making some named ranges, and incorporating them into code lines to bring in the data from data files ( with them closed )

The simplified form of what we will be considering is, as example, considering our brief introduction sketches from the first post, we would start with putting some string formula into a cell, which , without named ranges would be like writing in cell B5
“=B2”
Using named ranges this would look something like
“=Name3”

We will do this writing in of the formula in VBA, with a code line like, simplified,
Range(“=C5”) = ”=Name3”

We will extend this VBA approach to investigate using named ranges in the complete coding, like, simplified
Range(“=myNameforB5”).Value = ”=Name3”

Considerations of variations of the right hand side of that formula are similar to those for writing a formula manually in a cell.



General notes to code:
Called routine Sub GeTchaNms(ByVal CodLn As Long, ByVal WnkBuk As Workbook)
This routine is used at various points in the main code to check the current situation regarding named range objects. For convenience it goes through the Workbook named objects collection object for a workbook, as this has “its own” named range objects, that is to say the Workbooks scoped named range objects, and also the named range objects for all the worksheets. So I do not need to go through the named range objects collection object of every worksheet in that workbook separately for every worksheet.
To determine if a name is workbook scoped or worksheet scoped…
We remember that Excel adds a bit onto the name we give to a name Added to a Worksheet’s named objects collection ( Add name object to a Worksheet’s named objects collection = worksheet “scoping” ). That added bit is something like “Sheet1!” . In other words, if you had given Name:=”MyName” in a code line for a worksheets scope Named range object Addition, like, …_
Worksheets("Sheet2").Names.Add Name:="FoodHeader", RefersTo:=____
_.. then after we do that, Excel seems to hold and use a name like “Sheet2!FoodHeader"
So, for example , in the Adding code line above you use , _ Worksheets("Sheet2").Names.Add Name:="FoodHeader" , RefersTo:=___ _ , but we find that if we then use the Name property to return that string name like : …_
= Worksheets(“Sheet2”).Names(“FoodHeader").Name
-.. then we will be returned a string like
“Sheet2!FoodHeader"
The routine uses a check for that “!” in the returned .Name string in order to determine If the name object is worksheet scoped, Else the name object is assumed to be Workbooks scoped named object

The routine then builds up a string with text information about that named range. That string is then given in a message box. ( Additionally, the information is printed to the Immediate window. If you are in the VB Editor and Hit Ctrl + g , then you will see this window. You may be able to drag that window to a convenient place where you can enlarge it. You can then copy all or some of this information. This is useful, for example, to get the correct reference path syntaxes. Note also, you are less limited for space in the immediate window, compared to the message box window )



Using a named range
A major part of my discussion in this Thread has discussed the scoping issue, which in simple terms, we have found determines where you can “get away with” using a simple =MyRangedname or MyRangedName in a spreadsheet cell or in a code part such as Range(“___”) , like Range(“MyRangedName”) or Range(“=MyRangedName”) . By “Get away with” we mean that Excel will guess correct what it adds to make a full path reference string to find the information it needs about that named range, such as where the range is that it Refers To. What clearly Excel seems to do is to go to the Names Object collection where we Added the Name object. This is what the phrase “scoping to” means in the case of named ranges:

Worksheet Scope:
We scoped to the Names object of a particular Worksheet = We Added the named range Name object to the names objects collection object of that particular Worksheet = We scoped that named range to that Worksheet = That named range has Worksheet Scope
So in a practical Example, let me say I want to scope to Sheet1 ( Sheet1 is , say , in a file , “MyWorkbook.xls” )
To Scope MyWshtScp to Sheet1 is done like this in code:
__ Sheet1.Names.Add _ Name:=”MyWshtScp” ,_ Refers To:=some range somewhere
After doing this creating/Adding of the named range, If I then use MyWshtScp anywhere in Excel, then Excel will not use that but will increase the string reference that it uses so as to get to the appropriate worksheet, after which the use of the MyWshtScp will be recognised as a name object “held” there in the Names objects collection object of Sheet1. So Excel will actually use something like this
"'C:\MyFolder\MySubFolder\[MyWorkbook.xls]Sheet1'!MyWShtScp"
As that is effectively the so called “Implicit default” , then I am free to use either that or just MyWShtScp interchangeably

Workbook Scope:
We scoped to the Names object of a particular Workbook = We Added the named range Name object to the names objects collection object of that particular Workbook = We scoped that named range to that Workbook = That named range has Workbook Scope
So in a practical Example, let me say I want to workbook scope to a file , “MyWorkbook.xls”
To Scope MyWkBookScp to MyWorkbook.xls is done like this in code:
__ Workbooks(“MyWorkbook.xls”).Names.Add _ Name:=”MyWkBookScp” ,_ Refers To:=some range somewhere
After doing this creating/Adding of the named range, If I then use MyWKBookScp anywhere in Excel, then Excel will not use that but will increase the string reference that it uses so as to get to the appropriate workbook ( MyWorkbook.xls ) , after which the use of the MyWkBookScp will be recognised as a name object “held” there in the Names objects collection object of MyWorkbook.xls. So Excel will actually use something like this
"'C:\MyFolder\MySubFolder\[MyWorkbook.xls]'!MyWkBookScp"
As that is effectively the so called “Implicit default” , then I am free to use either that or just MyWShtScp interchangeably
(Note that Excel seems to accept also for a workbook scoped named range an alternative full string reference to any of the worksheets in that workbook, So for example, if I my second worksheet had the name Sheet2 , then this would also be accepted:
"'C:\MyFolder\MySubFolder\[MyWorkbook.xls]Sheet2'!MyWkBookScp" )

The reason I have just explained that last bit about the “implied” default full references, is that I personally prefer to use them, and so have done so throughout the coding


The next post describes the main demo codes in detail.

[DocAElstein]

Main codes
- Initially a single cell will be named. The use of the named range is fairly straight forward for this. When using a named range for a multiple call range, there can be some difficulties due to some restrictions caused by being forced into referencing the entire range. The use of Array formula entry , ( the , ( “the CSE stuff” ) will sometimes need to be used, so as an aside this will be revised before going on to multi cell named ranges in the second main code




Main Code, Sub FoxySingleCellNamedRanges()
The code deals principally with named ranges referring to single cells.

'0b) This section is not specifically to do with named ranges, but concerns my personal preference to use a full reference. As mentioned in the introduction first post, this is generally a good practice to make sure the correct cell is referenced at any time or code part. In addition, as we will also be considering named ranges in closed workbooks, it is useful to have the full reference stored in a string variable.
We find that VBA generally will accept that variable containing the complete reference in most situations where it only needs part of it, so it rarely does any harm to “give too much” in any cell reference.
It is my belief that VBA itself converts all cell references internally to the full form before it uses them. So if we always give the full reference we want, then that avoids annoying problems that often catch you out unawares, for example, when Excel guesses wrong the full path that it then uses: If you give the full path, then Excel takes that , and makes no attempt to replace any parts: it makes no attempt to guess anything if you give the full path.
So code section '-1b) just gives us some variables to hold a full reference string which we will use in places where we might need any of these variations for a cell reference, say B5:
B5
Sheet7!B5
[myWorkbook.xlsm]Sheet4!B5
'G:\Desktop\MyFolder\[DataFile.xlsx]Tabelle1'!B5
The last one is the form we hold in the variables. As noted, Excel and Excel VBA , usually has no issues if you use the full reference in situations where one of the shorter versions may have been sufficient. But on the other hand, you may get unexpected problems if you used a shorter version , and Excel then guesses wrongly for the remaining part, which I believe it always adds internally, ( possibly at some compiling stage ) , before it uses it.


Rem _1 Data1 Food header, ( value “Food” from first data file screenshot ) , as a named range
So here we looked at the right hand side of the basic code line of, simplified,
Range(“B5”).Value = “ = myNamedRange “
This is very similar to investigating manual uses of named ranges, that it to say, typing things like …_
“ = myNamedRange “
_... in a spreadsheet cell: That simple code line basically writes the string in the cell , as a person would do manually.

The practical example considered here is to write a simple formula in the main workbook, that will bring in the “Food” heading name cell, B5, from the data file, “data1.xls” into B5 in the main workbook.

The simplified form of this would be , without named ranges
Range(“B5”).Value = “ = [data1.xls]Tabelle1!B5 “
Using a named range for the range in the data1 workbook, say “Dta1Foodheader1” , then this would be like
Range(“B5”).Value = “ = [data1.xls]Tabelle1!Dta1Foodheader “
I say like because of two things
_ With any range referencing we need to be careful that the actual range Excel “goes” to is where we want. That goes for the range referred to in both side of the equation
_ and then we have the scope issue for a named range … The “scope” issue confuses very easily: The right hand side of the last formula can actually be written differently when using named ranges depending on where the name “Dta1Foodheader” is “scoped”.
I continually attempt to explain this all clearly, but you must bear in mind that it takes some very careful thought in order not to get confused. I have seen many experienced professional totally mixed up with scoping issues, and this may be part of the reason for the, incorrect in my opinion, statement that one often hears like “….…scope means what worksheets the name can be accessed from… “
A near statement that is true would be .…scope means what worksheets the name can be accessed from if you only give the string name and no other information about where to find the name object to which that string name belongs…

Once again to attempt to add clarity…
Consider initially just that right hand side above
“ = [data1.xls]Tabelle1!Dta1Foodheader “
This “takes us” to worksheet “Tabelle1” in file “data1.xls”
Once there, the named object with string name “Dta1Foodheader” is attempted to be accessed. It does not follow directly that the range Refered To is the B5 we wanted. That information about what range is Refered To is kept in the name object with the string name “Dta1Foodheader”
Going back to that code line, right hand side…
There are two possibilities based on that code part ( assuming it “works”. In other words for that part not to error we must have one of these:
_Possibility 1: At worksheet “Tabelle1” in workbook “data1.xls” we have a worksheets scoped named range object with the string name “Dta1Foodheader1”. That named object “belongs” to the named objects collection object of worksheet “Tabelle1”, so we must include the part “Tabelle1!” so that we get to that worksheet
_Possibility 2: The workbook “data1.xls” has a workbook scoped named range object with the string name “Dta1Foodheader1”. That named object “belongs” to the named objects collection object of workbook “data1.xls” . For this possibility, we only need to use in the spreadsheet , “=Dta1Foodheader” in any worksheet. This means we can “go” to any worksheet, so for example if we have the worksheets “Tabelle2” , “Sheet3” , “MySheet” in the workbook, “data1.xls” , then all these are valid also
“ = [data1.xls]Tabelle1!Dta1Foodheader “
“ = [data1.xls]Tabelle2!Dta1Foodheader “
“ = [data1.xls]Sheet3!Dta1Foodheader “
“ = [data1.xls]MySheet!Dta1Foodheader “
They will all give us the same result.
In addition we have an extra valid formula: This arises because we can “go” to the Workbook. The syntax to do this would, we find, is:
“ = data1.xls!Dta1Foodheader “
( I have no idea why the syntax is not “ = [data1.xls]!Dta1Foodheader “. Probably the Microsoft programmers were equally confused with what named ranges were about )


'1a) - '1b)
Code lines - scope to the workbook names object and then code lines - scope one of the worksheet’s names object of the Data1 file, workbook “data1.xls” . For the scoping the data 1 File had to be open, but it was closed before the named range object was referenced in the lines or . We see that we can reference the named ranges in the closed workbook. Note here , that the Referes To range is in the same workbook as the named ranges. ( A personal preference of mine is , once again, to use a full reference, also in the Refers To range. This Refers To:= argument would never need the full file path reference, as the range referenced must be to a range in an open book. Never the less, as usual, VBA accepts the full reference )
I finally end up with a string in cell B5 in the main workbook for like
'1a) _ "='C:\Folder\Data1.xls'!Dta1Foodheader "
'1b) _ "='C:\Folder\[Data1.xls]Tabelle2'!Ws2Dta1Foodheader"
Code section '1b)(ii) Is similar to '1b) , except that the data 1 workbook is open and I reference the named object with just the required reference of [Data1.xls]Tabelle2!Ws2Dta1Foodheader
As expected I then end up with in cell B5 in the main workbook
__ =[Data1.xls]Tabelle2!Ws2Dta1Foodheader
But I note, that after the line which closes the data 1 workbook, code line , that formula does not error but changes to the closed workbook reference like we had before, like
__"='C:\Folder\[Data1.xls]Tabelle2'!Ws2Dta1Foodheader"
This behaviour is typically observed when a range is referenced in a workbook which is then closed. It would appear that a reference to named object behaves in a similar way.
In '1c) the scope is to the main workbook. The name object used is therefore in a different workbook to that where the referred to range is, but the reference to this named range works as before. Note the main workbook is open
'1d) This is an attempt to get at the named range object in a roundabout sort of a way. Here the data 1 cell s scoped to the second data file, “Data2.xlsx” ( Workbooks scoped to workbook “Data2.xlsx” )
Both files must be open , for the scoping code line: data 1 file must be open as usual as it is needed in the range assignment argument Refers To:= _ ; The data 2 file must be opened as that has its names object referenced to , ( dataWb2xlsx.Names.Add ____ ) . With both files open we see in the formula bar the expected string reference to the named range:
=Data2.xlsx!Dta2Dta1Foodheader
If we close data 1 file, then that string does not change, and the link still works , ( we have the word “Food” in the cell B5 in the main file.)
If we close the data 2 file also, then initially the string reference in the formula changes to a closed reference like
='C:\Folder\Data2.xlsx'!Dta2Dta1Foodheader
Also initially the value “Food” still appears in the cell B5 in the main file. But if you re enter that formula, then it errors. I am not quite sure why…
If data 2 file is re opened and the formula is re entered, then all is well
If now data 2 file is closed, and data 1 file is opened and the value of “Food” changed, then the value “Food” still remains in cell B5 in the main file.
So I am not quite sure what is going on there…

Maybe I will come back to this post one day and comment further on this.




Further with this first demo code in the next post
_._________

[DocAElstein]

Rem 2 Second part of first main code



Rem 2
So far we have looked at the right hand side of the basic code line of, simplified,
Range(“B5”).Value = “ = myNamedRange “
This is very similar to investigating manual uses of named ranges, that it to say, typing things like …_
__ “ = myNamedRange “
____ _... in a spreadsheet cell: That simple code line basically writes the string in the cell , as a person would do manually.

We take the experimenting a little further now , so as to include a named range in the left hand side, like
Range(“RangeName”).Value = “ = myNamedRange “

Range ( “ _ “ ) What it is
As far as I know, this Range(“ “) thing, ( that confusingly pops up all over the place as an object or property, ( an occasionally in some senior professionals opinion as a method) ), is not really supposed to be used with nothing before it, but usually it is, and usually Excel guess correct what to put in front of it, so it usually works as expected.
The Range(“ “) thing is usually used in two main situations, in a worksheets range property
or
“ Application Range “ .
The worksheets range property would have a syntax like, for example, to reference the second cell in Sheet1
Sheet1.Range(“B1”)
Application Range is something similar, and is what in most situations is the default that Excel uses when you just write like:…_
Range(“ ”)
_.... _ In many situations, Excel will take that as:
Application.Range(“ ”)

There is a bit more to it than all that, and that can easier be explained by looking at what that Range(“ “) thing seems to do..

( One thing to note here is that the official documentation is often wrong or at least questionable. It seems that nobody really understands what goes on in the internal “wiring” anymore. Often what it seems to have been done is to interpret what happens, and then Methods, Properties are given to explain what seems to happen. I have often had heated discussions with professionals that disagree with my interpretations. But sometimes literature from Microsoft has been changed to reflect my interpretations, whilst the same professionally , sometimes Microsoft MVP’s (Most Valuable Professional’s) at the time, haven’t been able to get any response from Microsoft about anything… )

Range ( “ _ “ ) What it does
This usually returns a range object. It takes in the (“ “) a string reference to that wanted range. That reference is similar to those discussed already. ( Once again, I believe that Excel will add to what you give, so as to give a full reference , should you only give part of it).
If you give a full reference to say, a range in Sheet2 using Sheet1 range property, like …_
Sheet1.Range(“=’C:\MyFolder\[MyFile.xls]Sheet2’!G5”)
_... then that will error as it will not find G5 from Sheet2 in Sheet1
As far as I know, Application.Range(“ “) will take any valid range reference and return the range object of that range.

Range(“ ”) will accept a full range reference ( which is a reference to a closed workbook ) , and it will return the range object wanted, but only if that workbook is open. I expect it is designed that way as Excel will not let you make a range object of a range in a closed workbook.
_.___________


On now with the code part Rem 2 description.
Initially I will make a named range for the range B5 which we were referencing so far like:
__ Application.Range(“='C:\Folder\[MasturFile.xlsm]Tabelle1'!B5”).Value = ____
'2a) I scope to one of the data files, data 2 file, “Data2.xlsx”. Then the code line4 above is used inn this form.
(For the right hand side of the equation( which is required to get the value from data 1 file, B5 , we use a reference containing one of the existing named ranges Added/created in Rem 1)
Application.Range(“='C:\Folder[Data2.xlsx]Tabelle1'!MainFoodheader").Value = ____
Just to refresh our memories of what we are doing with that last line in the left hand side: We have in Range(“ “) a reference to a named range object in data 2 file. That in turn has the info we want of the range Referred To by that name which is B5 in the main file. This will result in range(“ “) returning us the range object of that cell. Then assigning a .Value to that range object will result in that .Value appearing in the cell in the spreadsheet. That .Value is a full reference to the ( closed) data file, ( ='C:\Folder\Data1.xls'!Dta1Foodheader ) , which brings the text “Food” into the cell B5 in the main workbook.
With the data 2 file open, the code line ( ) works . What is perhaps slightly surprising is that with the data 2 file closed, the code line ( ) errors as it can’t define the range. ( 1004 The Range method for the _Application object failed ) . Possibly the “wiring” of Range(“ “) is set to error if any workbook referenced is closed. That is required for the more usual range reference in the (“ “), and possibly such a usage as I am doing here was simply not envisaged at the time….

Rem 3
As a quick reminder to simple referencing of ranges , this simply brings in the Header "Suppliment" from data 2 workbook directly without named ranges. The code line shows similar strings on both sides

The code line is this sort of form:
Range("=" & "'" & WbMain.Path & "" & "[" & WbMain.Name & "]" & WbMain.Worksheets.Item(1).Name & "'" & "!" & "B10").Value = "=" & "'" & dataWb2xlsx.Path & "" & "[" & dataWb2xlsx.Name & "]" & dataWb2xlsx.Worksheets.Item(1).Name & "'" & "!" & "B10"
The actual string references are like:
Range("='C:\MyFolder\[MasturFile.xlsm]Tabelle1'!B10 ").Value = " ='C:\MyFolder\[Data2.xlsx]Tabelle1'!B10"

_.___

For comparison, some corresponding code lines for bringing in the Header "Food" from data 1 workbook , using some of our created named ranges are:
Range("='C:\MyFolder\[MasturFile.xlsm]Tabelle1'!B5 ").Value = " ='C:\MyFolder\Data1.xls'!Dta1Foodheader"
Range("='C:\MyFolder\[MasturFile.xlsm]Tabelle1'!B5 ").Value = " ='C:\MyFolder\MasturFile.xlsm'!MainDta1Foodheader"
Range("='C:\MyFolder\MasturFile.xlsm'!MainFoodheader ").Value = " ='C:\MyFolder\[Data1.xls]Tabelle2'!Ws2Dta1Foodheader"
The same basic code line without using named ranges would be
Range("='C:\MyFolder\[MasturFile.xlsm]Tabelle1'!B5 ").Value = " ='C:\MyFolder\[Data1.xls]Tabelle1'!B5""
Remember the difference in what “goes on” with and without the named ranges is: Without the named ranges we are referencing the referred to range directly. With the named ranges, we reference somehow to the relevant named range Name object ( via its string Name) . That Name object contains, and somehow “gives out” to Excel, the referred to range: We give that Refered To range, along with the string Name when we create/Add that named range Name object to either a workbook’s named objects collection or a worksheets named objects collection.
_._____________________________




In the next post we consider how to bring in the data to the master workbook from the two data workbooks.


So far we have got this far, that is to say we all the headers, that originally in the main book,
Nutrition _ | _ Energy
along with now the sub headers also
Food


Suppliment



Using Excel 2007 32 bit
Row\Col
A
B
C
D

1


2


3


4NutritionEnergy


5Food


6


7


8


9


10Suppliment


11


12


13


14


15


16


17


18


19


20


21


22
Worksheet: Tabelle1


The words “Food” and “Suppliment” are seen in the spreadsheet cells, but in the formula bar we see for
B5 _ - _
B10 _ - _
MainFoodHeader.JPG : https://imgur.com/uJCkJwb



The words “Food” and “Suppliment” are seen in the spreadsheet cells, but in the formula bar we see for
B5 _ - _ ='C:\MyFolder\Data1.xls'!Dta1Foodheader
MainFoodHeader.JPG : https://imgur.com/uJCkJwb

B10 _ - _ ='C:\MyFolder\[Data2.xlsx]Tabelle1'!B10
MainSupplimentHeader.JPG : https://imgur.com/wQD5FPB

[DocAElstein]

<o:p> </o:p>
<o:p> INsert right mouse click</o:p>
<o:p> </o:p>
<o:p> </o:p>
<o:p> </o:p>
Sub EvalutateExample()<o:p></o:p> 'Concatenate values in range B2:10 & C2:C10 and<o:p></o:p> 'display the result in A2:A10<o:p></o:p> <o:p></o:p> Dim rng As Range<o:p></o:p> Set rng = Sheet1.Range("A2:A10") 'Change it as per you requirement<o:p></o:p> <o:p></o:p> 'This is what is generated as a parameter of Evaluate in this procedure<o:p></o:p> 'If(Row(1:7),$B$2:$B$10&$C$2:$C$10)<o:p></o:p> <o:p></o:p> rng.Value = Evaluate("If(Row(1:" & rng.Rows.Count & ")," & rng.Offset(, _<o:p></o:p> 1).Address(, , , True) & " & " & rng.Offset(, 2).Address(, , , True) & ")")<o:p></o:p>End Sub<o:p></o:p>
<o:p></o:p>



CTRL V

Sub EvalutateExample() 'Concatenate values in range B2:10 & C2:C10 and 'display the result in A2:A10 Dim rng As Range Set rng = Sheet1.Range("A2:A10") 'Change it as per you requirement 'This is what is generated as a parameter of Evaluate in this procedure 'If(Row(1:7),$B$2:$B$10&$C$2:$C$10) rng.Value = Evaluate("If(Row(1:" & rng.Rows.Count & ")," & rng.Offset(, _ 1).Address(, , , True) & " & " & rng.Offset(, 2).Address(, , , True) & ")")End Sub







Second Main Code. Sub FoxyMultiCellNamedRanges()
Range referencing In Excel and VBA

Code section up to and including Rem 3 take us as far as the last code

Before going onto the rest of the code, as an aside, a review of some basic techniques for bringing a range of data values into a main workbook, such as our "MasturFile.xlsm" , from a closed data workbook, " Data1.xls"

Excel has two basic ways to Hold a single cell reference. Either
it holds it as a fixed co ordinate,
or
it holds it as a fixed vector. The vector corresponds, ( by default settings, if you don’t specify otherwise ), as a fixed angle and direction from the cell to the worksheet origin.
One way in which Excel can be told which system to use by including a $ sign if we want to use the co ordinate system. If no $ is included then Excel holds the fixed vector.
The fixed vector will mean that if a cell reference in a particular cell , such as =B6 , is copied to the next cell to the right, the fixed vector is copied and shifted one place to the right. The fixed vector is responsible for bringing the value from B6 into the original cell. That same vector if placed in the next cell to the right will bring in the value of B7. Excel will then display correspondingly the appropriate reference. This would be either =B7 or $B$7 , but Excel conventionally keeps the convention given to it, so it will display =B7
In VBA things work similarly. If I fill in a single cell with the reference =B6 using the , ( simplified) code line of .._
__ Range(“B2“).Value = “=B6”
____ _.. then the vector is placed in cell B2, so I get

C6.jpg : https://imgur.com/RrR2zrA
Row\Col
A
B
C
D

1


2
=C6


3


4



If I put the same reference, =C6 , across a range, whether manually by
copy / paste, or draging
or
by VBA thus: .. _
__ __ Range(“B2:C3“).Value = “=B6”

____ _.. then the same fixed vector is copied thus:
C6inB2toC3.JPG : https://imgur.com/BrMGrqn

The convention remains to stay in the fixed vector notation, so the reference put in the cells is: Row\Col
A
B
C
D
E

1


2=C6=D6


3=C7=D7


4


5


6ab


7cd


8

In the spreadsheet we will see:
Row\Col
A
B
C
D
E

1


2ab


3cd


4


5


6ab


7cd


8

[DocAElstein]

Dim llCountLines As Long
Dim ilSanityCheck As Integer
Dim llEndLine As Long
Dim procKind As Long
'Set up a codepane object for where the cursor is in the sub. For this example, where you placed it. Setting a variable makes the code more readable and a mite shorter instead of using Application.VBE.ActiveCodePane all the time.
Set olPane = Application.VBE.ActiveCodePane ' Setting a variable makes the code more readable and a mite shorter instead of using Application.VBE.ActiveCodePane all the time.
' GetSelection returns the position info for our selection and places those row and column numbers in our chosen variables
olPane.GetSelection Startline:=llSRow, startcolumn:=llSCol, Endline:=llERow, Endcolumn:=llECol
' This is a bit of a wierdo: ProcOfLine function returns the name of the Procedure for the given line. We can use for example the returned selection start row. The wierd thing is that the second srgument is returned to us, that is to say that returns the number VBA uses to identify the procedure type. For ProcBodyLine, ProcCountLines, and ProcStartLine, we need the procedure name. PocOfLine will return it. For those functions, we also need to know the pk (procedure kind) or type. ProcOfLine returns that as well. In fact, it's the only procedure that will give us the proc type. Once we have it, we can plug it into the other calls. This makes where you put this call important. It has to be before the calls that need procKind
Let slProcName = olPane.CodeModule.ProcOfLine(llSRow, procKind) '
Let llLine1 = olPane.CodeModule.ProcBodyLine(slProcName, procKind) ' Get the procedure "start" line: the line on which the Declaration/Definition is for that procedure name, slProcName
Let llCountLines = olPane.CodeModule.ProcCountLines(slProcName, procKind) ' Get the count of lines in this procedure. In this case .....
Let llStartLine = olPane.CodeModule.ProcStartLine(slProcName, procKind) ' Get the start line of the procedure. In this case ...
Let llEndLine = llStartLine + llCountLines - 1 ' this we calculate

:whistling::reallyconfused:



Option Explicit
Sub DumDim()
For Cnt = 1 To 10

Next Cnt
Dim Cnt As Long
End Sub




Sub DumDim()
Dim Cnt As Long
For Cnt = 1 To 3
Dim Count As Long
Count = Count + 1
' count value will be 3
Next Cnt
MsgBox Prompt:=Count: Debug.Print Count
End Sub










This must be correct Time Format



This must be Correct Date Format
Worksheet: BluePrint




This must be correct Time Format



This must be Correct Date Format


Dim a! ' same as Dim a as Short
Dim b@ ' same as Dim b as Currency
Dim c# ' same as Dim c as Double
Dim d$ ' same as Dim d as String
Dim e% ' same as Dim e as Integer
Dim f& ' same as Dim f as Long



Dim a! ' same as Dim a as Short
Dim b@ ' same as Dim b as Currency
Dim c# ' same as Dim c as Double
Dim d$ ' same as Dim d as String
Dim e% ' same as Dim e as Integer
[Code]Dim strA As String, strB As String
Dim lA As Long, Lr As Long
' Or maybe this ???
Dim strA$, strB$
Dim lA&, Lr&


'
' https://bytes.com/topic/visual-basic/answers/643371-declaration-shortcuts
' https://stackoverflow.com/questions/28238292/declaring-variables-in-vba
' http://www.excelforum.com/excel-programming-vba-macros/1100751-excel-vba-to-copy-and-paste-from-horizontal-to-vertical-format-2.html#post4194972
' https://www.excelforum.com/excel-programming-vba-macros/1116127-avoiding-variants-multiple-declarations-per-line-assign-variants-to-all-but-last-variable.html#post4256569
Dim a! ' same as Dim a as Short
Dim b@ ' same as Dim b as Currency
Dim c# ' same as Dim c as Double
Dim d$ ' same as Dim d as String
Dim e% ' same as Dim e as Integer
Dim f& ' same as Dim f as Long



'
' https://bytes.com/topic/visual-basic/answers/643371-declaration-shortcuts
' https://stackoverflow.com/questions/28238292/declaring-variables-in-vba
' http://www.excelforum.com/excel-programming-vba-macros/1100751-excel-vba-to-copy-and-paste-from-horizontal-to-vertical-format-2.html#post4194972
' https://www.excelforum.com/excel-programming-vba-macros/1116127-avoiding-variants-multiple-declarations-per-line-assign-variants-to-all-but-last-variable.html#post4256569
Dim a! ' same as Dim a as Short
Dim b@ ' same as Dim b as Currency
Dim c# ' same as Dim c as Double
Dim d$ ' same as Dim d as String
Dim e% ' same as Dim e as Integer
Dim f& ' same as Dim f as Long






lnglProcCountLines, _
lnglModuleProcEndLine

Stop ' You got stopped - now go up there and do a bit of harvesting
' ************************************************** *********************
End Sub


_____ Workbook: NeuProAktuelleMakros.xlsm ( Using Excel 2007 32 bit )
Row\Col
F
G

39


40
µg% Empholen


41
0


42
0
Worksheet: Leith2

[DocAElstein]

Quotes in VBA

"""" : “Producing a Single quote”
Quotes in VBA can be very tricky. Often in code lines within VBA we see a complicated mess of multiple quotes. Often they are there in order to “produce” a single quote.
There does not seem to be any clear documentation on this theme. I have a theory that helps me get both a feeling of understanding and usually helps me get the correct combination of multiple quotes.
A Theory
In Excel generally a pair of “enclosing” quotes is required to indicate something that will be ignored at the compile stage and will be “read” at run time. This will be text required which can itself be the requirement, that is to say plain text to be put or read somewhere. It can get a further level complicated when we wish to deal with the quotes to be applied to a spreadsheet via VBA – we need to produce a quote to produce a quote as it were.
I find it useful to analyse 4 quotes in detail , as this reveal a lot of what is going on.
So it would appear that Excel and VBA will tend to ignore things enclosed in a pair of quotes at compile time and as such read that “text”. VBA has both the characteristic of going backwards and also for the case of a possibly accepted matched pair of quotes it will on finding a matched pair in a code line accept them syntaxly. It makes some sense that a combination of three quotes """ is likely to confuse any such programmed system, and we find that this does generally cause problems, that is to say errors.
But for the case of 4 something goes on which may seems to allow for some interpretation and explanation. ….
The key to the thing “working” as it appear to do is the first pair not yet being paired up at the time when the forth is seen and allows for the syntax acceptance of the complete 4 quotes. We find that the syntax will allow three basic arrangements in addition to the original """"
_ " "" " ____ " """ ____ """ "
I believe some sort of attempt is made to satisfy a merging process to allow things to somehow co exist in the same place, a bit like in shuffling a deck of cards. The result is that the 4 quotes are somehow paired in this sort of fashion
_ " "" " ____ " """ ____ """ "
VBA sees this accepted double pair simultaneously from both side as a single captured/ enclosed quote at the point of the double quote. So in those three situations a single length quote is seen by VBA with the corresponding spaces where shown within the outer 2 quotes. Similarly for the original """" just a single quote is seen at “positron 1” if, in VBA, this was used
____ = """"

Final seen Quotes by VBA, In a cell , in Immediate window, Debug.Print
It is easy to loose track of what is typically finally required when one is involved in Quotes in VBA.
Normally, our final interaction, or required output is to have a display of text , which might also include one , or more likely two quotes in a spreadsheet cell.
Using some simple techniques to look at the string can greatly help.
Take a simple example:
I might want to construct a simple string based on this spreadsheet
Row\Col
A
B
C
D

1QuantityUnitProduct


2
4PintMilk


3

Lets say in column D , you might want to have shown
___4 Pints of Milk
You may be familiar with the initial requirement of what you could type manually in cell D2
Row\Col
A
B
C
D

1QuantityUnitProduct


2
4PintMilk=A2 & " " & B2 & "s of " & C2


3

Row\Col
A
B
C
D

1QuantityUnitProduct


2
4PintMilk4 Pints of Milk


3

We are already using here Excel and Excel VBA’s way to see a string and construct a string. The use of the enclosing quotes indicates for Excel just to read the text within as text. The use of the & is a common way used in computing to link things, particularly when building strings, like “a” & “b” is typically equivalent to “ab”
If we wish to construct that via VBA, we need to remember in particular our method for producing those single quotes , as well as the basic requirement to enclose any text in an enclosing quote pair.
So for example the first part of that forumula, as seen from within VBA will be
= “=” & “A2”
Or we could simplify that to
= “=A2”
If you have understood how we arrived at the use of 4 quotes, to make VBA “see” a single quote, then the full construction follows fairly simply. Just to remind us of what was attempted to explain …. _
_.... If VBA is given in a code line 4 quotes it will “see” a single quote. Correspondingly our full formula is
= "=A2" & "&" & """" & " " & """" & "&" & "B2" & "&" & """" & "s of" & """" & "&" & "C2"
Note that within VBA we are using & , and we must also include within the string those 4 &s which we would physically type in the cell ( I am using the colours green and blue here just to help with the explanation. In a cell and in VBA all will typically seen in normal black colour )
More typically the “ & “ are needed for joining a string with a VBA variable, like, “Row count is “ & Rows.Count. We have seen that for simple strings we can usually remove that, for example :
"""" & " " & """" is the same as """ """
We will show in the next post that we can simplify our string thus to
= "=A2&"" ""&B2&""s of""&C2"
But in the practice it is often easier to keep the entire form when developing a string as it can help to make adjustments

Using VBA to type in the values for us
Strictly speaking, the above would be considered as one of the formula properties of the range object associated with the cell D2.
We also find that if we assign the cell value by referring to the .Value property of the cell, then this appears to have the same effect as if we physically write the same formula into the cell. In other words, the equivalent in VBA to us writing =A2 into cell D2 would be a code line of this form
Range("D2").Value = "=A2"
Here is a full stand alone code example , assuming you have a worksheet with tab Name of QuotesInVBA

Sub Write_in_formula_using_VBA()
Dim WsQuotesInVBA As Worksheet: Set WsQuotesInVBA = ThisWorkbook.Worksheets("QuotesInVBA")
WsQuotesInVBA.Range("D2").ClearContents
Let WsQuotesInVBA.Range("D2").Value = "=A2" & "&" & """" & " " & """" & "&" & "B2" & "&" & """" & "s of" & """" & "&" & "C2"
End Sub

Using Debug to help “see” what VBA “sees”
If you are not confused with the issue of Quotes in VBA at this point, then you are much more clever than me, as I needed a couple of years to get all this clear in my head.
There is however, a simple aid to constructing the required string. These are discussed in the next post.

[DocAElstein]

_____ Workbook: formula request ynnn.xlsb ( Using Excel 2007 32 bit )
Row\Col
BB
BC
BD

4yyyyynynyyynnynnnynnnnynynnnnnnnynnnnynnnnnnnnnnn n
14
14


5yyyyyyyyyyyyynynyyyynyynnyynnnnnnnnnnnnnnyynynnnn n
27
27
Worksheet: List 1



_____ Workbook: formula request ynnn.xlsb ( Using Excel 2007 32 bit )
Row\Col
BB
BC
BD

4=B4 & C4 & D4 & E4 & F4 & G4 & H4 & I4 & J4 & K4 & L4 & M4 & N4 & O4 & P4 & Q4 & R4 & S4 & T4 & U4 & V4 & W4 & X4 & Y4 & Z4 & AA4 & AB4 & AC4 & AD4 & AE4 & AF4 & AG4 & AH4 & AI4 & AJ4 & AK4 & AL4 & AM4 & AN4 & AO4 & AP4 & AQ4 & AR4 & AS4 & AT4 & AU4 & AV4 & AW4 & AX4 & AY4
=FIND("ynnn",BB4)
=FIND("ynnn",B4 & C4 & D4 & E4 & F4 & G4 & H4 & I4 & J4 & K4 & L4 & M4 & N4 & O4 & P4 & Q4 & R4 & S4 & T4 & U4 & V4 & W4 & X4 & Y4 & Z4 & AA4 & AB4 & AC4 & AD4 & AE4 & AF4 & AG4 & AH4 & AI4 & AJ4 & AK4 & AL4 & AM4 & AN4 & AO4 & AP4 & AQ4 & AR4 & AS4 & AT4 & AU4 & AV4 & AW4 & AX4 & AY4)


5=B5 & C5 & D5 & E5 & F5 & G5 & H5 & I5 & J5 & K5 & L5 & M5 & N5 & O5 & P5 & Q5 & R5 & S5 & T5 & U5 & V5 & W5 & X5 & Y5 & Z5 & AA5 & AB5 & AC5 & AD5 & AE5 & AF5 & AG5 & AH5 & AI5 & AJ5 & AK5 & AL5 & AM5 & AN5 & AO5 & AP5 & AQ5 & AR5 & AS5 & AT5 & AU5 & AV5 & AW5 & AX5 & AY5
=FIND("ynnn",BB5)
=FIND("ynnn",B5 & C5 & D5 & E5 & F5 & G5 & H5 & I5 & J5 & K5 & L5 & M5 & N5 & O5 & P5 & Q5 & R5 & S5 & T5 & U5 & V5 & W5 & X5 & Y5 & Z5 & AA5 & AB5 & AC5 & AD5 & AE5 & AF5 & AG5 & AH5 & AI5 & AJ5 & AK5 & AL5 & AM5 & AN5 & AO5 & AP5 & AQ5 & AR5 & AS5 & AT5 & AU5 & AV5 & AW5 & AX5 & AY5)
Worksheet: List 1

[DocAElstein]

"What’s in a String"- VBA break down Loop through character contents of a string
Coding to Loop through character contents of a string
Recap :
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 as you loop.

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.

In post #1 ( lSDLKJsdjldjldjkldjlkjdlASJFAKHIVDNGOISDUSON ) , the merits of different basic procedure formats were discussed.
In this post we will start from the coding below , with the aim to develop the second procedure to give us a clear indication of exactly what is in the string under investigation, MyString



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 WtchaGot(strIn:=varForMyString)
End Sub

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

The coding , on running procedure, Sub MyTestString() currently simply gives a message.
WotChaGotSimpleMsgBox.JPG : https://imgur.com/eYXDqSB

String output of characters and character listing .
Long VBA code string representation
The full un simplified long string , as discussed in the example at the start of this Thread, in the syntax that would be required to add the string via coding is both a nice visual representation when used for all characters in a string, as well as being convenient to then use in coding for further investigations. So one of the two main outputs of the full routine, Sub WtchaGot(ByVal strIn As String) , will be a form which will show characters
_ that can be “seen” in their typical form,
and
_ “hidden” characters will be shown in either a form which can be used in VBA coding, which if does not have a specific VBA syntax constant will resort to the official listed ASCII ( http://www.asciitable.com/ ) number form: For example, the carriage return has the official number of 13, which can be used in VBA coding as Chr(13) , but as this character also has a VBA constant form , vbCr , this will be used in our string output.

As example, say at the start of the following text , some “hidden” character was present

Hi
__”u”.

This would be an example of the testing procedure used to test our main routine:

Sub TestWtchaGot()
' In the practice we would likely have our string obtained from some method and would have it held in some string variable
Dim strTest As String
Let strTest = Chr(1) & "Hi" & vbCrLf & vbTab & """u."""
Call WtchaGot(strIn:=strTest)
' Call WtchaGot(Chr(1) & "Hi" & vbCrLf & vbTab & """u.""")
End Sub


Our long VBA syntaxly acceptable string, which our routine should give us would be of this form

Chr(1) & "H" & "i" & vbCr & vbLf & vbTab & """" & "u" & "." & """"

This will be output in a message box and also in the Immediate window, ( from the immediate window we could obtain a copy in which to paste into the VBA code pane window as part of a routine )

Character listing
A second output will be given which will be a simple 2 column list. One column will be the “see able” version of the character, if excel manages to do that, and the other column will be its ASCII character.
This is intended to act as a notepad type thing , and if columns are already filled, then the latest will be added to the right of any existing ones. A date is given as well as the first part of the string for ease of identification. It would be intended that the user manually deletes columns from time to time is they are no linger needed. This would be the results after two consecutive runs for the above example
Row\Col
A
B
C
D
E

107 Feb 2019 Hi
"u."07 Feb 2019 Hi
"u."


2 1 1


3H72H72


4i105i105


5 13 13


6
10
10


7 9 9


8"34"34


9u117u117


10.46.46


11"34"34


12


13

[DocAElstein]

Still in IE 9

<font face=Calibri><SPAN style="color:#00007F">Sub</SPAN> TestEvaluateVBA1b_n1n2n3()<br>Range("H2") = Evaluate("**" & Range("B2").Address & "&""****""&" & Range("C2").Address & "&""****""&" & Range("D2").Address & "")<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>


Error Handling statement Resumes
On Error Resume Next

The next posts will look at the second main Error handling statement , On Error Resume Next .
I will look at this in a similar way as the ideas in the last two posts: I will initially discus the more fundamental general idea of the Resume .
Building on that, I will derive user defined error handlers that works in a similar way.

In my opinion the whole VBA error handling is an unintuitive mess. In the unlikely event that you have read and understood most of my previous ramblings, then a couple logical conclusion might be the following:

_ We might reasonably guess that there is no On Error Resume
This is because that would imply that at an error the code tries again. And again. And again And again. And again.. … Conceivable there might be a situation where that might work if some external influence meant that the code line erroring suddenly didn’t. But that is unlikely. More likely is that having such an option would cause the attempt at an infinite loop or retrying.
In fact that assumption is correct. There is no On Error Resume

_ We might reasonably guess that there might be a On Error Resume LabelOrLineNumber
That would be a reasonable thing to do – always to go to a specific point with the exception cleared. There is not that option. There is no On Error Resume LabelOrLineNumber I don’t know why. It might not be too dangerous a thing to have. It might be quite useful. Never mind. :)

_ What might be quite dangerous is to have something that just keeps going like a blind dumb Bull in a China shop making no account of any errors that occur.
Such a thing would logically go under the command statement of On Error Resume Next
Well we have one. It almost does what would logically be expected: The main diversion from logic is that the Err object still has information about the last error.

On Error Resume Next
The last bit of illogic will at least make for an interesting attempt to mimic this handler. It will mean that we have some good practice at using the Err object, as we will need to fill it after other things we use, such as the On Error GoTo -1 have emptied it!

Pseudo On Error Resume Next
In the first instance, the code is very similar to that for the Pseudo Resume Next
It just means that we are considering the initial On Error GoTo LabelOrLineNumber as part of the “hidden internal” coding that we are trying to imitate. (In the case of all the 3 resumes an initial On Error GoTo LabelOrLineNumber was necessary as they won’t work without one, but it was not an integral part of them, as it is in the VBA On Error Resume Next statement )
In addition we must add some coding to refill the Err object.
As the final real On Error Resume Next is a single line we would not have the ability to do any changes after the error, so our mimicking code will only concentrate on
_ organising that the code continues after an error in normal run mode ( not in exceptional erections ) and also
_ the Err object must contain information about the last error.

The first requirement is already fulfilled in with our previous code for the pseudo Resume Next.
The code below follows closely that previous code.
It has the part removed that “does something” to effect the outcome of an error
The code demonstrates that we have information available about the error that occurred. This is accessed just after the code continues from the line below that which errored. In typical applications, where the error handler On Error Resume Next is used, a check on the Err object number or description is used to determine if an error occurred
Note: we can do this On Error Resume Next at any point even after the On Error Resume Next has been used, as the exception is cleared by On Error Resume Next
If you must use On Error GoTo 0 then it is good practice as soon as possible after to use On Error GoTo 0 as soon as possible after.

Code for pseudo On Error Resume Next in next post

[DocAElstein]

shame about the ******

(caan of couse in VBA editor get rid of them by Find and Replace ( Find * Replace with space !!!!)



Codes for On Error Resume Next

Pseudo On Error Resume Next
The code has two errors . Effectively both are “ignored” – The code continues in un aroused normal modus just after the erroring lines, but we have in the Err object information about the last error.


Sub PseudoOnErrorResumeNextGoComeBackAJackQuickCrap()
10 On Error GoTo GetMilkLuv
20 Dim TNominator As Long, RslTwat As Long
30 ' Other Code
40 Let TNominator = 0
50 Let RslTwat = 10 / TNominator ' This will error because of an attempt to divide by zero
55 MsgBox Err.Number & " " & Err.Description ' This does give infomation despite that the On Erro GoTo -1 has cleared the Err object of infomation. We put the infomation back
60 ' other code
70 Dim Rng As Range
80 Let Rng.Value = "Anyfink" ' This line should error as we have not assigned any object to rng. ( We cannot therefore asssing a Value to a non existing range
85 MsgBox Err.Number & " " & Err.Description
90 ' 0ther code
100 Exit Sub
110 GetMilkLuv: ' "Error handling Code section" is from here until the End
120 Dim errLine As Long: Let errLine = Erl ' this must be done before On Error GoTo -1 , as that clears the recorded error line
130 Dim errNumber As Long, errDescription As String ' ' this must be done before On Error GoTo -1 , as that clears Err object of error information
132 Let errNumber = Err.Number: Let errDescription = Err.Description ' This would be a fairly typical use of the Err object to get infomation about the error from that held in the object. As typical in object orintated programming, these "thing" of the object are referred to as Properties. We are retrieving the Properties of Errror Number and Description and holding them in apprpriately declared ( Dim ed ) variables
140 On Error GoTo -1
141 MsgBox prompt:="We want to go back to just after the erroring line " & errLine & vbCrLf & "and continue in normal code run mode" & vbCrLf & "but we want the Err object to hold infomation about the last error" & vbCrLf & "In the real On Error Resume Next we can not display this message as" & vbCrLf & "the error handling code section is effectively internal and we cannot hook a call back code into it"
143 Let Err.Number = errNumber ' Like many object properties, here we may referrence them and assign them using VBA.
145 Let Err.Description = errDescription
150 Select Case errLine:
Case 10: GoTo 20
Case 20: GoTo 30
Case 30: GoTo 40
Case 40: GoTo 50
Case 50: GoTo 55
Case 55: GoTo 60
Case 60: GoTo 70
Case 70: GoTo 80
Case 80: GoTo 85
Case 85: GoTo 90
Case 90: GoTo 100
Case 100: GoTo 110
Case 110: GoTo 120
Case 120: GoTo 130
Case 130: GoTo 140
Case 140: GoTo 150
End Select
End Sub
'









VBA On Error Resume Next

Here the code using the actual VBA On Error Resume Next error handling statement is used to do the same as the previous code with the exception that we cannot have the MsgBox come up which was previously within the error handling code section: Effectively the actual error handling code section used in the previous code is what VBA internally does , ( with the exception that VBA does not give us any message, and we do not have a simple way to hook our code into it). But as seen we can get the information as previously in the main code relating to the type of error that occurred.
This code one is one of the most simplest considered so far. The Pseudo version was one of the most complicated. There is a lot of crap hidden behind this On Error Resume Next. It is generally not thought of as a good idea to do by most experts. The fact that a lot of illogical stuff is “hidden behind it” is probably another reason to avoid it if at all possible.


Sub VBAOnErrorResumeNext()
On Error Resume Next
Dim TNominator As Long, RslTwat As Long
' Other Code
Let TNominator = 0
Let RslTwat = 10 / TNominator ' This will error because of an attempt to divide by zero
MsgBox Err.Number & " " & Err.Description ' This does give infomation despite that the exception has been cleared.. wierd and not as one might have expected.
' other code
Dim Rng As Range
Let Rng.Value = "Anyfink" ' This line should error as we have not assigned any object to rng. ( We cannot therefore asssing a Value to a non existing range
MsgBox Err.Number & " " & Err.Description
' 0ther code
End Sub

[DocAElstein]

Chrome

<font face=Calibri><SPAN style="color:#00007F">Sub</SPAN> TestEvaluateVBA1b_n1n2n3FoxTest()<br>Range("H2") = Evaluate("**" & Range("B2").Address & "&""****""&" & Range("C2").Address & "&""****""&" & Range("D2").Address & "")<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT><font face=Calibri><SPAN style="color:#00007F">Sub</SPAN> TestEvaluateVBA1b_n1n2n3FoxTest()<br>Range("H2") = Evaluate("**" & Range("B2").Address & "&""****""&" & Range("C2").Address & "&""****""&" & Range("D2").Address & "")<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>





Some Notes on On Error Resume Next usage
On Error Resume Next is bad
It is like a dopey bitch acting like a blind Bull in a china shop, with the exception that some coding at least notes what error was last hidden.
Usually the advice is to only use an On Error Resume Next when you are expecting an error but can’t think of any other way to check for it that does not raise an exception . Further you should then use the On Error GoTo 0 as soon as possible after to remove the error handler ( “turn it off”, “un plug it”, disable it )
Remember we can do this On Error GoTo 0 at any point even after the On Error Resume Next has been used, as the exception is cleared by On Error Resume Next (Actually, On Error GoTo 0 can also be used in the exception state and also disables the error handler but has no noticeable effect if the exception is still raised )
Both On Error GoTo -1 and On Error GoTo 0 clear the Err object
It was perhaps reasonable to expect that On Error GoTo -1 cleared the information from the Err object. It may not be so obvious that On Error GoTo 0 also does this. So if you wanted to use a check on the Err properties after an On Error Resume Next in order to ascertain if and what error had been “hidden” , then you must do that before any On Error GoTo 0 or On Error GoTo -1.

On Error GoTo -1 and Err.Clear clear the Err object of information
As we noted above, initially one might think that On Error GoTo -1 has no useful function when a On Error Resume Next is in place, as effectively any resume type statement effectively does On Error GoTo -1, but for the unique case of On Error Resume Next which maintains the Err properties of the last error, the use of On Error GoTo -1 gives the possibility to clear the properties of the Err object, without disabling the error handler. But note, that the method Clear, that is to say Err.Clear, can also be used to do that.
But you never know, some crazy combination of all the statements might best suit some messy system



On Error Resume Next is bad. In most cases there is a better alternative to using On Error Resume Next .
Often it is a quick workaround. That tends to be how I have used it.
I don’t think I should have used it in such cases.
_a) By definition a work a round is bad.
-b) Often it is jus ignorance as I don’t know ( yet ) the alternatives

Here some example of how I am using it. Maybe I will add to them, or give the better alternative not using error handling, if I ever figure it out.
Maybe from time to time I will add other examples of error handling generally to the end of this thread and welcome any comments or additions

Late Early Binding.
Only very rarely there are advantages in using Early Binding in preference to late Binding in a final shared File. For development the Early Binding is often preferable as this somehow seems to make an initial link or reference such that intellisense knows what is available. This requires however the checking of a library in the _ VB Editor – - - Tools – - - references _ list
The Late Binding alternative uses the CreateObject Method whose (“string”) argument , ignored by compile , is used at run time to “find” the library of the given name.
Well… I had some existing files which had a lot of Early Binding, and for the time being I did not want to change them.
The current problem example had an Early Binding reference to Word, done on a Office 2007 machine.
I got broken reference errors then on 2010 office versions. I also wanted the File to work in Excel 2003

I found by a bit of experimenting and Forum involvement _..
https://www.excelforum.com/development-testing-forum/1215283-gimmie-da-codexamples-call-in-the-appendix-posts-2018-no-reply-needed-but-if-u.html#post4820111
https://www.excelforum.com/excel-programming-vba-macros/1214789-late-binding-2.html#post4821675
_.. that a Globally Unique Identifier (GUID) appeared a fairly reliable to reference the appropriate libraries. A short code I found could be reliably used to check the reference programmatically via its GUID.
I don’t know yet if there is a good reference list for all GUIDs, but a simple code I could use to get a list of my checked references. For my example I checked the reference to Word on different Office versions and ran this code:





Sub RefItsGUIDsAndStuff()
Dim It As Variant
For Each It In ThisWorkbook.VBProject.References
Dim strIts As String
Let strIts = strIts & "Description:" & vbTab & It.Description & vbCr & "Name:" & vbTab & vbTab & It.Name & vbCr & "Buitin:" & vbTab & vbTab & It.BuiltIn & vbCr & "Minor:" & vbTab & vbTab & It.minor & vbCr & "Major:" & vbTab & vbTab & It.major & vbCr & "FullPath:" & vbTab & vbTab & It.fullpath & vbCr & "GUID:" & vbTab & vbTab & It.GUID & vbCr & "Type:" & vbTab & vbTab & It.Type & vbCr & "Isbroken:" & vbTab & vbTab & It.isbroken & vbCr & vbCr
Next It
Debug.Print strIts ' From VB Editor Ctrl+g to Immediate Window
End Sub
Some results for different Excel Versions



Excel 2007
Description: Visual Basic For Applications
Name: VBA
Buitin: Wahr
Minor: 0
Major: 4
FullPath: C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\VBE6.DLL
GUID: {000204EF-0000-0000-C000-000000000046}
Type: 0
Isbroken: Falsch

Description: Microsoft Excel 12.0 Object Library
Name: Excel
Buitin: Wahr
Minor: 6
Major: 1
FullPath: C:\Program Files\Microsoft Office\Office12\EXCEL.EXE
GUID: {00020813-0000-0000-C000-000000000046}
Type: 0
Isbroken: Falsch

Description: OLE Automation
Name: stdole
Buitin: Falsch
Minor: 0
Major: 2
FullPath: C:\Windows\system32\stdole2.tlb
GUID: {00020430-0000-0000-C000-000000000046}
Type: 0
Isbroken: Falsch

Description: Microsoft Office 12.0 Object Library
Name: Office
Buitin: Falsch
Minor: 4
Major: 2
FullPath: C:\Program Files\Common Files\Microsoft Shared\OFFICE12\MSO.DLL
GUID: {2DF8D04C-5BFA-101B-BDE5-00AA0044DE52}
Type: 0
Isbroken: Falsch

Description: Microsoft Forms 2.0 Object Library
Name: MSForms
Buitin: Falsch
Minor: 0
Major: 2
FullPath: C:\Windows\system32\FM20.DLL
GUID: {0D452EE1-E08F-101A-852E-02608C4D0BB4}
Type: 0
Isbroken: Falsch

Description: Microsoft Scripting Runtime
Name: Scripting
Buitin: Falsch
Minor: 0
Major: 1
FullPath: C:\Windows\system32\scrrun.dll
GUID: {420B2830-E718-11CF-893D-00A0C9054228}
Type: 0
Isbroken: Falsch

Description: Microsoft Word 12.0 Object Library
Name: Word
Buitin: Falsch
Minor: 4
Major: 8
FullPath: C:\Program Files\Microsoft Office\Office12\MSWORD.OLB
GUID: {00020905-0000-0000-C000-000000000046}
Type: 0
Isbroken: Falsch

Excel 2003

Description: Visual Basic For Applications
Name: VBA
Buitin: Wahr
Minor: 0
Major: 4
FullPath: C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\VBE6.DLL
GUID: {000204EF-0000-0000-C000-000000000046}
Type: 0
Isbroken: Falsch

Description: Microsoft Excel 11.0 Object Library
Name: Excel
Buitin: Wahr
Minor: 5
Major: 1
FullPath: C:\Program Files\Microsoft Office\OFFICE11\EXCEL.EXE
GUID: {00020813-0000-0000-C000-000000000046}
Type: 0
Isbroken: Falsch

Description: OLE Automation
Name: stdole
Buitin: Falsch
Minor: 0
Major: 2
FullPath: C:\Windows\system32\stdole2.tlb
GUID: {00020430-0000-0000-C000-000000000046}
Type: 0
Isbroken: Falsch

Description: Microsoft Office 11.0 Object Library
Name: Office
Buitin: Falsch
Minor: 3
Major: 2
FullPath: C:\Program Files\Common Files\Microsoft Shared\OFFICE11\MSO.DLL
GUID: {2DF8D04C-5BFA-101B-BDE5-00AA0044DE52}
Type: 0
Isbroken: Falsch

Description: Microsoft Word 12.0 Object Library
Name: Word
Buitin: Falsch
Minor: 4
Major: 8
FullPath: C:\Program Files\Microsoft Office\Office12\MSWORD.OLB
GUID: {00020905-0000-0000-C000-000000000046}
Type: 0
Isbroken: Falsch


Excel 2010
Description: Visual Basic For Applications
Name: VBA
Buitin: Wahr
Minor: 1
Major: 4
FullPath: C:\PROGRA~2\COMMON~1\MICROS~1\VBA\VBA7\VBE7.DLL
GUID: {000204EF-0000-0000-C000-000000000046}
Type: 0
Isbroken: Falsch

Description: Microsoft Excel 14.0 Object Library
Name: Excel
Buitin: Wahr
Minor: 7
Major: 1
FullPath: C:\Program Files (x86)\Microsoft Office\Office14\EXCEL.EXE
GUID: {00020813-0000-0000-C000-000000000046}
Type: 0
Isbroken: Falsch

Description: OLE Automation
Name: stdole
Buitin: Falsch
Minor: 0
Major: 2
FullPath: C:\Windows\SysWOW64\stdole2.tlb
GUID: {00020430-0000-0000-C000-000000000046}
Type: 0
Isbroken: Falsch

Description: Microsoft Office 14.0 Object Library
Name: Office
Buitin: Falsch
Minor: 5
Major: 2
FullPath: C:\Program Files (x86)\Common Files\Microsoft Shared\OFFICE14\MSO.DLL
GUID: {2DF8D04C-5BFA-101B-BDE5-00AA0044DE52}
Type: 0
Isbroken: Falsch

Description: Microsoft Word 14.0 Object Library
Name: Word
Buitin: Falsch
Minor: 5
Major: 8
FullPath: C:\Program Files (x86)\Microsoft Office\Office14\MSWORD.OLB
GUID: {00020905-0000-0000-C000-000000000046}
Type: 0
Isbroken: Falsch




I use the following codes to add programmatically the reference. ( The codes are in the ThisWorkbook code module). The reason for the error handler is that I cannot know if the check has already be made where the File might be in use. I think I can only check references that are made. The code would error at the attempt to check a reference already checked.
I could do the following which would be very simple: _ ….

With ThisWorkbook.VBProject.References
On Error Resume Next '
.AddFromguid GUID:="{00020905-0000-0000-C000-000000000046}", major:=8, minor:=4 ' Office 2003
.AddFromguid GUID:="{00020905-0000-0000-C000-000000000046}", major:=8, minor:=4 ' Office 2007
.AddFromguid GUID:="{00020905-0000-0000-C000-000000000046}", major:=8, minor:=5 ' Office 2010
On Error GoTo 0
End With
_.. Typically, and a bad habit, is to use On Error Resume Next for convenience as above


With this following code, I have at least narrowed the chances of the code errorong

With ThisWorkbook.VBProject.References
On Error Resume Next '
Select Case CLng(Val(Application.Version))
Case 9: ' Excel 2000
Case 10: ' Excel 2002
Case 11: .AddFromguid GUID:="{00020905-0000-0000-C000-000000000046}", major:=8, minor:=4 ' Office 2003
Case 12: .AddFromguid GUID:="{00020905-0000-0000-C000-000000000046}", major:=8, minor:=4 ' Office 2007
Case 14: .AddFromguid GUID:="{00020905-0000-0000-C000-000000000046}", major:=8, minor:=5 ' Office 2010
Case 15: temp = "Excel 2013"
Case 16: temp = "Excel 2016 (Windows)"
Case Else: temp = "Unknown"
End Select
On Error GoTo 0
End With

[DocAElstein]

<font face=Calibri><SPAN style="color:#00007F">Sub</SPAN> TestEvaluateVBA2_n1n2n3()<br>Range("H3:H4") = Evaluate("**" & Range("B3:B4").Address & "** " & "&""****""&" & "**** " & Range("C3:C4").Address & "" & "&""****""&" & "" & Range("D3:D4").Address & "")<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>



<font face=Calibri><SPAN style="color:#00007F">Sub</SPAN> TestEvaluateVBA2_n1n2n3()<br>Range("H3:H4") = Evaluate("**" & Range("B3:B4").Address & "** " & "&""****""&" & "**** " & Range("C3:C4").Address & "" & "&""****""&" & "" & Range("D3:D4").Address & "")<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>


Err object. Err.Raise. Custom Error handler

One possible last area of VBA error things that can be considered is the possibility to raise an exception without actually having a code line that causes an error to occur, and possibly to modify the responses, or rather the given details of the error from the VBA default error handler pop up message
I am guessing that this means that you can cause the Exception software to start, or start that software running in a similar way to which it would automatically be triggered by an actual error occurring.
It seems that a few Blogs are not quite clear on exactly what this is about. I don’t think anyone quite remembers anymore exactly what it does. Certainly no one knows the things about the arguments that I think I do and probably don’t.
It is probably therefore a good idea to tackle this in two parts. First Part 1), an investigation into what the Err object and in particulate the Method .Raise is, and then Part 2), using it in a “Custom Error handler”

[DocAElstein]

This is just a test of HTML tables in support of this excelfox Thread
http://www.excelfox.com/forum/showthread.php/2253-Automatic-sort-due-date-and-send-email?p=10715#post10715
http://www.excelfox.com/forum/showthread.php/2253-Automatic-sort-due-date-and-send-email?p=10715#post10715

Posted in HTML code tags:

<table width=811.5>
<col width=40>
<col width=103.5>
<col width=56.5>
<col width=71.5>
<col width=79>
<col width=57.5>
<col width=48.5>
<col width=77>
<col width=77>
<col width=80.5>
<col width=80.5>

<tr height=17>
<td>Machine EQ. ID</td>
<td style="background:#D8D8D8"> Manufacture </td>
<td>Model</td>
<td style="color:Black;background:#D8D8D8">Description</td>
<td>Serial Number</td>
<td style="background:#92D050">Weekly Date of Service</td>
<td style="background:#92D050">Weekly Next Service</td>
<td style="background:yellow">Monthly Date of Service</td>
<td style="background:yellow">Monthly Next Service</td>
<td style="background:#D8D8D8">Quarterly Date of Service</td>
<td style="background:#D8D8D8">Quarterly Next Service</td>
</tr></table>


Posted without HTML code tags:
<table width=811.5>
<col width=40>
<col width=103.5>
<col width=56.5>
<col width=71.5>
<col width=79>
<col width=57.5>
<col width=48.5>
<col width=77>
<col width=77>
<col width=80.5>
<col width=80.5>

<tr height=17>
<td>Machine EQ. ID</td>
<td style="background:#D8D8D8"> Manufacture </td>
<td>Model</td>
<td style="color:Black;background:#D8D8D8">Description</td>
<td>Serial Number</td>
<td style="background:#92D050">Weekly Date of Service</td>
<td style="background:#92D050">Weekly Next Service</td>
<td style="background:yellow">Monthly Date of Service</td>
<td style="background:yellow">Monthly Next Service</td>
<td style="background:#D8D8D8">Quarterly Date of Service</td>
<td style="background:#D8D8D8">Quarterly Next Service</td>
</tr></table>

[DocAElstein]

Further testing for this excelforum Post:
http://www.excelfox.com/forum/showthread.php/2253-Automatic-sort-due-date-and-send-email?p=10715#post10715

Code sent:

<table width=811.5>
<col width=40>
<col width=103.5>
<col width=56.5>
<col width=71.5>
<col width=79>
<col width=57.5>
<col width=48.5>
<col width=77>
<col width=77>
<col width=80.5>
<col width=80.5>

<tr height=17>
<td>Machine EQ. ID</td>
<td style="background:#D8D8D8"> Manufacture </td>
<td>Model</td>
<td style="color:Black;background:#D8D8D8">Description</td>
<td>Serial Number</td>
<td style="background:#92D050">Weekly Date of Service</td>
<td style="background:#92D050">Weekly Next Service</td>
<td style="background:yellow">Monthly Date of Service</td>
<td style="background:yellow">Monthly Next Service</td>
<td style="background:#D8D8D8">Quarterly Date of Service</td>
<td style="background:#D8D8D8">Quarterly Next Service</td>
</tr></table>

As seen here:
<table width=811.5>
<col width=40>
<col width=103.5>
<col width=56.5>
<col width=71.5>
<col width=79>
<col width=57.5>
<col width=48.5>
<col width=77>
<col width=77>
<col width=80.5>
<col width=80.5>

<tr height=17>
<td>Machine EQ. ID</td>
<td style="background:#D8D8D8"> Manufacture </td>
<td>Model</td>
<td style="color:Black;background:#D8D8D8">Description</td>
<td>Serial Number</td>
<td style="background:#92D050">Weekly Date of Service</td>
<td style="background:#92D050">Weekly Next Service</td>
<td style="background:yellow">Monthly Date of Service</td>
<td style="background:yellow">Monthly Next Service</td>
<td style="background:#D8D8D8">Quarterly Date of Service</td>
<td style="background:#D8D8D8">Quarterly Next Service</td>
</tr></table>

As seen in final recieved EMail:
RecievedHeader.JPG : https://imgur.com/wtyQ4QW
2071

_.__________________________________
Code:
<table width=811.5;border="1">
<col width=40>
<col width=103.5>
<col width=56.5>
<col width=71.5>
<col width=79>
<col width=57.5>
<col width=48.5>
<col width=77>
<col width=77>
<col width=80.5>
<col width=80.5>

<tr height=17>
<td>Machine EQ. ID</td>
<td style="background:#D8D8D8"> Manufacture </td>
<td>Model</td>
<td style="color:Black;background:#D8D8D8">Description</td>
<td>Serial Number</td>
<td style="background:#92D050">Weekly Date of Service</td>
<td style="background:#92D050">Weekly Next Service</td>
<td style="background:yellow">Monthly Date of Service</td>
<td style="background:yellow">Monthly Next Service</td>
<td style="background:#D8D8D8">Quarterly Date of Service</td>
<td style="background:#D8D8D8">Quarterly Next Service</td>
</tr></table>

here:
<table width=811.5;border="1">
<col width=40>
<col width=103.5>
<col width=56.5>
<col width=71.5>
<col width=79>
<col width=57.5>
<col width=48.5>
<col width=77>
<col width=77>
<col width=80.5>
<col width=80.5>

<tr height=17>
<td>Machine EQ. ID</td>
<td style="background:#D8D8D8"> Manufacture </td>
<td>Model</td>
<td style="color:Black;background:#D8D8D8">Description</td>
<td>Serial Number</td>
<td style="background:#92D050">Weekly Date of Service</td>
<td style="background:#92D050">Weekly Next Service</td>
<td style="background:yellow">Monthly Date of Service</td>
<td style="background:yellow">Monthly Next Service</td>
<td style="background:#D8D8D8">Quarterly Date of Service</td>
<td style="background:#D8D8D8">Quarterly Next Service</td>
</tr></table>

recived EMail:
RecievedHeaderColumn1.JPG : https://imgur.com/XPcCDEe
2072

_.__________________________________________

Code:

<table border="1";width=811.5>
<col width=40>
<col width=103.5>
<col width=56.5>
<col width=71.5>
<col width=79>
<col width=57.5>
<col width=48.5>
<col width=77>
<col width=77>
<col width=80.5>
<col width=80.5>

<tr height=17>
<td>Machine EQ. ID</td>
<td style="background:#D8D8D8"> Manufacture </td>
<td>Model</td>
<td style="color:Black;background:#D8D8D8">Description</td>
<td>Serial Number</td>
<td style="background:#92D050">Weekly Date of Service</td>
<td style="background:#92D050">Weekly Next Service</td>
<td style="background:yellow">Monthly Date of Service</td>
<td style="background:yellow">Monthly Next Service</td>
<td style="background:#D8D8D8">Quarterly Date of Service</td>
<td style="background:#D8D8D8">Quarterly Next Service</td>
</tr></table>

seen here:
<table border="1";width=811.5>
<col width=40>
<col width=103.5>
<col width=56.5>
<col width=71.5>
<col width=79>
<col width=57.5>
<col width=48.5>
<col width=77>
<col width=77>
<col width=80.5>
<col width=80.5>

<tr height=17>
<td>Machine EQ. ID</td>
<td style="background:#D8D8D8"> Manufacture </td>
<td>Model</td>
<td style="color:Black;background:#D8D8D8">Description</td>
<td>Serial Number</td>
<td style="background:#92D050">Weekly Date of Service</td>
<td style="background:#92D050">Weekly Next Service</td>
<td style="background:yellow">Monthly Date of Service</td>
<td style="background:yellow">Monthly Next Service</td>
<td style="background:#D8D8D8">Quarterly Date of Service</td>
<td style="background:#D8D8D8">Quarterly Next Service</td>
</tr></table>

recived EMail
Column1RecievedHeader.JPG : https://imgur.com/bctC5Yl
2073