Page 2 of 2 FirstFirst 12
Results 11 to 20 of 20

Thread: HTML Code Test --post8798

  1. #11
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,270
    Rep Power
    10

    Example VBIDE Microsoft Visual Basic for Applications Extensibility 5.3 For coding coding

    Code:
    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


    Code:
    Option Explicit
    Sub DumDim()
      For Cnt = 1 To 10
      
      Next Cnt
    Dim Cnt As Long
    End Sub


    Code:
    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


    [Code]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...tion-shortcuts
    ' https://stackoverflow.com/questions/...riables-in-vba
    ' http://www.excelforum.com/excel-prog...ml#post4194972
    ' https://www.excelforum.com/excel-pro...ml#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





    Code:
                    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
    Last edited by DocAElstein; 02-13-2019 at 01:44 AM.

  2. #12
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,270
    Rep Power
    10
    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
    1
    Quantity Unit Product
    2
    4
    Pint Milk
    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
    1
    Quantity Unit Product
    2
    4
    Pint Milk =A2 & " " & B2 & "s of " & C2
    3

    Row\Col
    A
    B
    C
    D
    1
    Quantity Unit Product
    2
    4
    Pint Milk 4 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
    Code:
     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.
    Last edited by DocAElstein; 02-07-2019 at 01:28 AM.

  3. #13
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,270
    Rep Power
    10
    _____ Workbook: formula request ynnn.xlsb ( Using Excel 2007 32 bit )
    Row\Col
    BB
    BC
    BD
    4
    yyyyynynyyynnynnnynnnnynynnnnnnnynnnnynnnnnnnnnnnn
    14
    14
    5
    yyyyyyyyyyyyynynyyyynyynnyynnnnnnnnnnnnnnyynynnnnn
    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
    Last edited by DocAElstein; 02-14-2019 at 10:59 PM.

  4. #14
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,270
    Rep Power
    10
    "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


    Code:
    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:
    Code:
    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
    1
    07 Feb 2019 Hi
    "u."
    07 Feb 2019 Hi
    "u."
    2
    1 1
    3
    H 72 H 72
    4
    i 105 i 105
    5
    13 13
    6
    10 10
    7
    9 9
    8
    " 34 " 34
    9
    u 117 u 117
    10
    . 46 . 46
    11
    " 34 " 34
    12
    13
    Last edited by DocAElstein; 02-07-2019 at 03:46 PM.

  5. #15
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,270
    Rep Power
    10
    Still in IE 9

    Sub TestEvaluateVBA1b_n1n2n3()
    Range("H2") = Evaluate("**" & Range("B2").Address & "&""****""&" & Range("C2").Address & "&""****""&" & Range("D2").Address & "")
    End Sub


    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
    Last edited by DocAElstein; 03-20-2018 at 06:38 PM.

  6. #16
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,270
    Rep Power
    10
    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.

    Code:
    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.

    Code:
    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
    Last edited by DocAElstein; 03-20-2018 at 06:51 PM.

  7. #17
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,270
    Rep Power
    10
    Chrome

    Sub TestEvaluateVBA1b_n1n2n3FoxTest()
    Range("H2") = Evaluate("**" & Range("B2").Address & "&""****""&" & Range("C2").Address & "&""****""&" & Range("D2").Address & "")
    End Sub
    Sub TestEvaluateVBA1b_n1n2n3FoxTest()
    Range("H2") = Evaluate("**" & Range("B2").Address & "&""****""&" & Range("C2").Address & "&""****""&" & Range("D2").Address & "")
    End Sub





    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/developme...ml#post4820111
    https://www.excelforum.com/excel-pro...ml#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:




    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

    Code:
    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: _ ….
    Code:
       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
    Code:
       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
    Last edited by DocAElstein; 03-20-2018 at 07:43 PM.

  8. #18
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,270
    Rep Power
    10
    Sub TestEvaluateVBA2_n1n2n3()
    Range("H3:H4") = Evaluate("**" & Range("B3:B4").Address & "** " & "&""****""&" & "**** " & Range("C3:C4").Address & "" & "&""****""&" & "" & Range("D3:D4").Address & "")
    End Sub




    Sub TestEvaluateVBA2_n1n2n3()
    Range("H3:H4") = Evaluate("**" & Range("B3:B4").Address & "** " & "&""****""&" & "**** " & Range("C3:C4").Address & "" & "&""****""&" & "" & Range("D3:D4").Address & "")
    End Sub


    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”
    Last edited by DocAElstein; 03-20-2018 at 08:28 PM.

  9. #19
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,270
    Rep Power
    10

    Just testing HTML Tables for Email sending

    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/showth...0715#post10715

    Posted in HTML code tags:
    HTML Code:
    <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:

























    Machine EQ. ID Manufacture Model Description Serial Number Weekly Date of Service Weekly Next Service Monthly Date of Service Monthly Next Service Quarterly Date of Service Quarterly Next Service
    Last edited by DocAElstein; 06-17-2018 at 02:51 PM.
    ….If you are my competitor, I will try all I can to beat you. But if I do, I will not belittle you. I will Salute you, because without you, I am nothing.
    If you are my enemy, we will try to kick the fucking shit out of you…..
    Winston Churchill, 1939
    Save your Forum..._
    _...KILL A MODERATOR!!

  10. #20
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,270
    Rep Power
    10
    Further testing for this excelforum Post:
    http://www.excelfox.com/forum/showth...0715#post10715

    Code sent:
    HTML Code:
    <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:

























    Machine EQ. ID Manufacture Model Description Serial Number Weekly Date of Service Weekly Next Service Monthly Date of Service Monthly Next Service Quarterly Date of Service Quarterly Next Service


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

    _.__________________________________
    Code:
    HTML 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:

























    Machine EQ. ID Manufacture Model Description Serial Number Weekly Date of Service Weekly Next Service Monthly Date of Service Monthly Next Service Quarterly Date of Service Quarterly Next Service


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

    _.__________________________________________

    Code:
    HTML 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:

























    Machine EQ. ID Manufacture Model Description Serial Number Weekly Date of Service Weekly Next Service Monthly Date of Service Monthly Next Service Quarterly Date of Service Quarterly Next Service


    recived EMail
    Column1RecievedHeader.JPG : https://imgur.com/bctC5Yl
    Column1RecievedHeader.JPG
    Last edited by DocAElstein; 06-20-2018 at 11:35 AM.
    ….If you are my competitor, I will try all I can to beat you. But if I do, I will not belittle you. I will Salute you, because without you, I am nothing.
    If you are my enemy, we will try to kick the fucking shit out of you…..
    Winston Churchill, 1939
    Save your Forum..._
    _...KILL A MODERATOR!!

Similar Threads

  1. Replies: 5
    Last Post: 06-10-2019, 10:14 PM
  2. This is a test Test Let it be
    By Admin in forum Test Area
    Replies: 6
    Last Post: 05-30-2014, 09:44 AM
  3. change table top row to a different colour with html code
    By peter renton in forum Excel Help
    Replies: 2
    Last Post: 02-17-2014, 08:08 PM
  4. Test
    By Excel Fox in forum Den Of The Fox
    Replies: 0
    Last Post: 07-31-2013, 08:15 AM
  5. Test
    By Excel Fox in forum Word Help
    Replies: 0
    Last Post: 07-05-2011, 01:51 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •