Page 3 of 3 FirstFirst 123
Results 21 to 29 of 29

Thread: ब्लॉग कोशिश कर रहा है بلاگز کی ک*Trying Blogs

  1. #21
    Senior Member DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    321
    Rep Power
    4

    Code Part 3 of 4 parts

    Code Part 3 of 4 parts

    Code:
    '6b) get code as long string String. This can be very long
    Dim TextWithBBCodeEnit As String '     'Prepares "Pointer" to a "Blue Print" (or Form, Questionnaire not yet filled in, a template etc.)"Pigeon Hole" in Memory, sufficient in construction to house a piece of Paper with code text giving the relevant information for the particular Variable Type. VBA is sent to it when it passes it. In a Routine it may be given a particular Value, or (Values for Objects). There instructions say then how to do that and handle(store) that(those). At Dim the created Paper is like a Blue Print that has some empty spaces not yet filled in. String is a a bit tricky. The Blue Print code line Paper in the Pigeon Hole will allow to note the string Length and an Initial start memory Location. This Location well have to change frequently as strings of different length are assigned. Instructiions will tell how to do this. Theoretically a specilal value vbNullString is set to aid in quick checks.. But...http://www.mrexcel.com/forum/excel-q...html#post44116
     Let TextWithBBCodeEnit = objCliTextCopied.GetText() ''retrieve the text in this instance of the Class. ( Our code as a String with probably BB Code Code tags, vbCr vbLf etc.
    Rem 7) Determine length of original text
    Dim LenText As Long '                                      ' Long is very simple to handle, - final memory "size" type is known (123.456 and 000.001 have same "size" computer memory ) , and so a Address suggestion can be given for the next line when the variable is filled in.  '( Long is a Big whole Number limit (-2,147,483,648 to 2,147,483,647) If you need some sort of validation the value should only be within the range of a Byte/Integer otherwise there's no point using anything but Long.--upon/after 32-bit, Integers (Short) need converted internally anyways, so a Long is actually faster. )
     Let LenText = Len(TextWithBBCodeEnit)
     MsgBox prompt:="Text with BB Code Character count is " & LenText
    Rem 8) Take out BB Code bits from text string.
    40   Dim strRmveBBCode As String: Let strRmveBBCode = TextWithBBCodeEnit '  = strBBCode ' This will be adjusted until finally has no BBCode tag pairs
    50   Dim Lstr As Long ' This will be adjusted as necerssary for current length    ' Long is very simple to handle, - final memory "size" type is known (123.456 and 000.001 have same "size" computer memory ) , and so a Address suggestion can be given for the next line when the variable is filled in.  '( Long is a Big whole Number limit (-2,147,483,648 to 2,147,483,647) If you need some sort of validation the value should only be within the range of a Byte/Integer otherwise there's no point using anything but Long.--upon/after 32-bit, Integers (Short) need converted internally anyways, so a Long is actually faster. )
    60    Let Lstr = Len(TextWithBBCodeEnit)                                ' Len(strBBCode)
    70   Rem 2) Loop through and get a BB Code section
    80   Dim strBBCodePair As String ' for a found valid pair  '
    ' Prepares "Pointer" to a "Blue Print" (or Form, Questionaire not yet filled in, a template etc.)"Pigeon Hole" in Memory, sufficient in construction to house a piece of Paper with code text giving the relevant information for the particular Variable Type. VBA is sent to it when it passes it. In a Routine it may be given a particular Value, or (Values for Objects).  There instructions say then how to do that and handle(store) that(those). At Dim the created Paper is like a Blue Print that has some empty spaces not yet filled in. A String is a a bit tricky. The Blue Print code line Paper in the Pigeon Hole will allow to note the string Length and an Initial start memory Location. This Location well have to change frequently as strings of different length are assigned. Instructiions will tell how to do this. Theoretically a specilal value vbNullString is set to aid in quich checks.. But..http://www.mrexcel.com/forum/excel-questions/361246-vbnullstring-2.html#post44116
    90   Dim posCurrent As Long 'Current position in the string' Loop Bound variable Count '_-Main Loop==========================
    100      For posCurrent = Lstr To 2 Step -1 'Important to go backwards as we chop off behind us so have a less complicated current position. We look at pos-1 in some code parts so we would error if we went back to less than 2
    110      '2a) Check for a ]
    120          If Mid(strRmveBBCode, posCurrent, 1) = "]" Then
    130          Dim posBBCodeTagSrch As Long 'For search through possible valid BBCode Section'_-Loop for Pos tag pair section--
    140          Dim BcrdEEnd As Long: Let BcrdEEnd = posCurrent 'Position of possible End Code Tag End
    150                For posBBCodeTagSrch = posCurrent - 1 To 2 Step -1 'PosCurrent is set here at loop start. ###It is not effected by cardinally sining changing PosCurrent Loop Bound variable caount in Outer main Loop
    160                  If Mid(strRmveBBCode, posBBCodeTagSrch, 1) = "]" Then GoTo NxtCESPosPos ' If we hit another ] then start again to see if is a valid BB code section
    170                  If Mid(strRmveBBCode, posBBCodeTagSrch, 1) = "/" And Mid(strRmveBBCode, posBBCodeTagSrch - 1, 1) = "[" Then 'We hit start of a BB Code end tag
    180                  Dim BcrdESt As Long: Let BcrdESt = posBBCodeTagSrch - 1   'Position of End Code Tag Start
    190                   Exit For ' We leave this For the End Code Tag Start Search, Exit For with a [ at posBBCodeTagSrch-1
    200                  Else 'Not found start of end Tag or stop of end Tag.
    210                  End If
    220 NxtCESPosPos:  Next posBBCodeTagSrch ' go back in possible BB code string
    230          If posBBCodeTagSrch = 3 Then Exit Sub 'case we never found the start of an End Code Tag
    240                For posBBCodeTagSrch = posBBCodeTagSrch - 2 To 2 Step -1 ' Once again ### the start is set and fixed, the Loop Bound Variable Count will change
    250                  If Mid(strRmveBBCode, posBBCodeTagSrch, 1) = "]" Then
    260                  If Mid(strRmveBBCode, posBBCodeTagSrch - 1, 1) = "]" Then GoTo NxtCSSPosPos ' If we hit another ] then start again to see if is a valid BB code start section
    270                  Dim BcrdSEnd As Long: Let BcrdSEnd = posBBCodeTagSrch  'Position of possible Start Code Tag End
    280                  Dim LTagStLoop As Long 'Last loop to find start of start Code Tag
    290                      For LTagStLoop = posBBCodeTagSrch - 1 To 1 Step -1 '_- Second inner loop___________________________
    300                          If Mid(strRmveBBCode, LTagStLoop, 1) = "[" Then
    310                          Dim BcrdSSt As Long 'Have to put it here, - I may occaisionally have to set it to 0 in next line
    320                          If Mid(strRmveBBCode, LTagStLoop + 1, 1) = "/" Then Let BcrdEEnd = BcrdSEnd: Let BcrdSEnd = 0: Let BcrdESt = LTagStLoop: Let BcrdSSt = 0: Let posBBCodeTagSrch = LTagStLoop: GoTo NxtCSSPosPos ' Found a second end Code Tag. So change the previous found start and stop , and adjust the Loop Bound variable Count appropriately for a new search for a start Code Tag
    330                           Let BcrdSSt = LTagStLoop
    340                           Let posCurrent = BcrdSSt 'End of Code Tag section search
    350                           GoTo FkOffCrd ' We leave this For the start Code Tag Start Search and go to Fuk Off Crd:
    360                          Else ' Still looking for start of start Code Tag
    370                          End If
    380                      Next LTagStLoop '_- Second inner loop________________________________________________________________
    390                      If LTagStLoop = 1 Then GoTo TheEnd 'Case we found a start start [ at 1 or failed to find a start start [ for the complete possible Code Tag section search
    400                  Else 'Not found end of start Tag or start of start Tag
    410                  End If
    420 NxtCSSPosPos:  Next posBBCodeTagSrch ' go back in possible BB code string'_-Loop for Pos tag pair section-----------------
    Google first, like this site:ExcelFox.com "Short Title or Theme of wot youre looking for"
    Use Code Tags: Highlight code; click on the # icon above,
    Post screenshots COPYABLE to a Spredsheet; NOT IMAGES PLEASE:
    Tools for that:
    http://www.excelfox.com/forum/showth...=9821#post9821
    https://app.box.com/s/gjpa8mk8ko4vkwcke3ig2w8z2wkfvrtv
    http://excelmatters.com/excel-forums/

  2. #22
    Senior Member DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    321
    Rep Power
    4

    Code part 4 of 4 parts

    Code part 4 of 4 parts



    Code:
    430 FkOffCrd:  Rem 3) "I think we come here with a complete set of 4 positions"
    440              If BcrdEEnd <> 0 And BcrdESt <> 0 And BcrdSEnd <> 0 And BcrdSSt <> 0 Then 'Just to check'
    450              Dim SttBcrd As String, StpBcrd As String ' Start and stop tags
    460               Let SttBcrd = Mid(strRmveBBCode, BcrdSSt, BcrdSEnd - BcrdSSt + 1): Let StpBcrd = Mid(strRmveBBCode, BcrdESt, BcrdEEnd - BcrdESt + 1)
    470              Dim SttWrd As String, StpWrd As String          ' like |Color=Red|, |/Color|
    480               Let StpWrd = Mid(StpBcrd, 3, Len(StpBcrd) - 3) ' like Color from |/Color| - Whole Word, from 3rd Chr , for Length as  WholeWord-3
    490               Let SttWrd = Mid(SttBcrd, 2, Len(StpWrd))      ' like Color from |Color=Red|
    500                  If UCase(SttWrd) = UCase(StpWrd) And Mid(StpBcrd, 2, 1) = "/" Then 'Ucase allows for differences in cases                                     And Mid(SttBcrd, Len(SttWrd) + 2, 1) = "=" Then
    510                   Let strRmveBBCode = Replace(strRmveBBCode, SttBcrd, "", 1, 1) ' Replace in ( strRmveBBCode , the start tag , with no string , start loooking from and return from character 1 )
    515                   Let strRmveBBCode = Replace(strRmveBBCode, StpBcrd, "", 1, 1)
    520                  Else 'No shortening of string foe invalid tag word
    530                  End If
    540          Else ' no last ] of a possible Code section yet, so keep going back down in the main string
    550          End If
    560      Else
    570      End If
    580      Next posCurrent '_-===Main Loop========================================================================================
    '590 TheEnd:
    600   MsgBox prompt:="" & strRmveBBCode & "": Debug.Print "" & strRmveBBCode & "" 'Hit Ctrl+g when in VB Editor Window,  reveal Immediate window after code run
    Dim RapedText As String: Let RapedText = strRmveBBCode ' TextWithBBCodeEnit with BB Code Code tags removed
    Rem 9) This a Another Object from class to be sure we have the data in the Clipboard
    'Dim objDat As DataObject
     'Set objDat = New DataObject 'Set to a new Instance ( Blue Print ) of dataobject
    Dim objDat As Object
     Set objDat = GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
    '9b) Put in (and Get back from clipboard) the raped text.
     objDat.SetText Text:=RapedText 'Make Data object's text equal to original text without the BB Code Code Tags
     objDat.PutInClipboard    'Place current Data object into the Clipboard
     objDat.GetFromClipboard  'All that is in the Clipboard goes in this Data Object second instance of the Class.
    Dim TxtOut As String: Let TxtOut = objDat.GetText() 'retrieve the text in this second instance of the Class. ( In this case all in it is the text and possibly vbCr and vbLf )
    MsgBox prompt:="You dumped in Clipboard this " & vbCr & RapedText & vbCr & "and if you try to get it, you should get" & vbCr & TxtOut & ""
    Rem 10a) Code Part 3) Belt and braces check. A check is then done finally to see if the two raped from tags strings are the same
        If NewWordText <> RapedText Then MsgBox prompt:="Text from Word Dialogue Box Find Replace Wild card way, and " & vbCrLf & "Long String manipulation way are not the same. Oh dear"
    '10b) Tidy up.
    TheEnd:  ' ( Come here always, even on a unpredictable error ) or i wouod If i had an error Handler, Lol.
     Set objDat = Nothing '   Good practice...   maybe....
     Set objCliTextCopied = Nothing '  .......   http://www.mrexcel.com/forum/excel-questions/361246-vbnullstring.html#post4414065
    
    End Sub
    Google first, like this site:ExcelFox.com "Short Title or Theme of wot youre looking for"
    Use Code Tags: Highlight code; click on the # icon above,
    Post screenshots COPYABLE to a Spredsheet; NOT IMAGES PLEASE:
    Tools for that:
    http://www.excelfox.com/forum/showth...=9821#post9821
    https://app.box.com/s/gjpa8mk8ko4vkwcke3ig2w8z2wkfvrtv
    http://excelmatters.com/excel-forums/

  3. #23
    Senior Member DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    321
    Rep Power
    4

    Recursion Wonk. To remove BB Code tag pairs including nested ones missus

    One problem noted in the previous codes was that the case of nested BB Code tag pairs, the codes did not work. At many forums such nesting of this form is permitted in BB codes.
    [color=Green]Comments [color=Red]Highlight[/color] Comments[/color]
    The returned result is
    Comments Highlight Comments

    None of the codes discussed so far will work for such a case.
    Should the Code tag type be dissimilar, such as_..
    [CENTER][B]Title[/B][/CENTER]
    _..then at least the Find Replace Wildcard Code will work for that by virtue of matching in the String first, for example, in the above example, _..
    __ CENTER __ __ __ __ CENTER __
    _.. then
    __ B __ __ __ __ B __
    However my Long string manipulation code will not even work for that.

    I have done a Long string manipulation code to be discussed in this and the following posts which , I think, work for all situations.

    Brief Descriptions is:
    The entire string is held in a variable, strBBCode. This is declared in a Subroutine. Something similar to that was done for the last code )

    change 1) Minor to last code:
    Unlike the previous codes, ( which sequentially removed BB Code code tags ( by replacing with "" )), the code tags are replaced by some arbitrary character ( This is simply chosen as any not likely to be used as text in the main initial string https://www.mrexcel.com/forum/excel-...ml#post4214083 )

    change 2) Minor
    This itself is not such a major change in the code format, but it facilitates the main change, 3) below. This change is that bulk of the code is within a Function, (Function LongWayOfDoingIt2ReCurseCyClops(__ ) . It is Called and needs to be given_..
    _.. the string, strBBCode, By Referral to that variable, rather than its By its Value contents
    and_..
    _.. a Value for the current character position, which initially will be the end ( or length in characters ) position of strBBCode.

    But those are not the main changed characteristic to the code.
    To recap: All my long string manipulation codes, including this Function, have the general characteristic that they loop backwards in the complete string until a ] is found. An attempt is made then to find a [/ . Once this is found an attempt is made to find a ] and then a [ .
    At this point a possible code pair requirement , looking backwards, of ] [/ ] [ is achieved
    ( Reversing that to the correct order shows that clearly more clearly,
    [ _ ] __ [/ _ ],
    or
    [codeText] AnyText [/CodeText] .
    Or
    [Color=red] AnyText [/color]
    _.. etc...)
    Having found a code tag pair, a check is made for its validity ( the same matching work, such as, codetext, color, for example ) , before the adjustments are made to the main string. After that , the code then moves on and steps further back in search of another pair_..
    _.. But just before it does that check, all codes, including this new Function, at the last found [ , do a check to see if in fact the next character after the last found [ is a / , indicating that in fact the assumed start tag, [codeText], was in fact another end tag, [/Codetext_b]. Action is then taken should that be the case.
    At this point the codes are very different. The action taken is different
    In the previous code, it was assumed that in such an event, the first found end tag was a rouge text looking like an end tag , but invalid, and not matched to a start code tag. The first found end tag was then ignored, and another search made for a matching start tag to the second found end tag.
    This results in an error situation for a valid nested BB Code tags situations.
    The new code works therefore much differently.

    Main change 3):
    In the new code, this point is around lines
    430 460, Rem If [/ Then Oh dear time for a wonk.
    Recursion is the name given for the general technique which at this point is used. It is whereby within a routine an instruction is given to run the routine.
    ... In other words the Routine Calls itself.
    So what is all that about? :
    _- I will tell you, Matey boy:
    Any Function or Sub routine we write, is just a set of instructions. Like a piece of paper which is a Print out of the typed code instructions. It tells VBA what to do when it is run or called. I does not really physically go along or through the code lines: We just find that easy to visualise, or look at when stepping through to debug a code. The code is just a set of instructions that VBA understands.
    So imagine you are VBA and you are given a set of instructions ( on a piece of paper ) to do a job, (for example you are given a written copy of the instructions to read and follow).
    You start doing the instructions.
    Now, say, before you are finished, you are given a set of identical instructions and are told to do that by the boss. What do you do, ( apart from swear at him ) ?
    The logical progression is, ( and that is what VBA does ), it stops what it is doing, so like freezes the initial running through the instructions , and starts again with the new ( duplicate as it happens in the case of recursion) set of instructions.
    Once finished it goes back and picks up the first job where you / it left off. Within reason, there is no limit to how often you can do that in VBA. If a code makes a Function or Sub routine keep Calling itself , then it stacks up in order in a waiting list the stopped set of instructions. As each set of instructions is completed, the last are continued at the point where they were interrupted and frozen. That point is code line 450
    It follows, ( although may not be immediate obvious as the same names are used) that all variables used in each run of the Function within the function are unique to that run of the function so do not influence the values of the similarly named variables in the other Function runs. An exception here is our main full string, strBBCode. This is because we have deliberately chosen to carry it over By such a way as to indicate it should be Referenced to within each Function. So in this case, changes made in any Function run to strBBCode will be Reflected in the main ( unique ) variable, strBBCode, which is initially held / declared in the initial calling code.
    So what is all that to do with the main change to this code version ??
    _- I will tell you, Matey boy:
    Initially the Function is called the first time by a small Sub routine which passes to the Function two things
    _(i) the variable strBBCode. It is indicated via the use of ByRef in the Function signature ( first ) line, that specifically that variable is to be Referenced. ( You may effectively take a variable into the Function ( ByRef ) , or alternatively a Value. If an actual value is given, then giving it ByRef is just the same as By Value. If you pass a Variable ByValue the value at the time of the Call in the variable will be taken, and that variable will become local to the Function and so any changes to it made in the function will not be reflected in the variable held in the initial calling routine ........ )
    _...(ii) a number .......Value... is given which will be the start position to work back from when looping backwards through the characters in strBBCode. For this first Call, that will be the length of / or character count of , strBBCode.

    The Function code then transgresses as described and very similar to the previous code. It can go to completion in one run should no end code tag be encountered after an initial one is found and before a start tag is found.
    ____If a second end tag in sequence is found the "recursion wonk" kicks in.....
    450 _____ Call LongWayOfDoingIt2ReCurseCyClops(strBBCode, BcrdSEnd)
    So _ If this line is done, the current run freezes. A New Function begins. This is instructed to Refer to the unique main string strBBCode held in the original Calling routine. As Value for the current position in the backward stepping search is this time not the main string strBBCode length, but rather the position of the end ] in the currently found second sequential end code tag.
    The only thing that is certain at this point is that the new Function run will immediately find this second end cote tag as its first end code tag. This is probably inefficient as it repeats some steps done. ( A more complicated Function could be envisaged to carry across an Offset to be applied to jump this search and pass the end code tag )
    With no more nested code tag pairs, the second Function will complete the entire string, other than the possible code tag pair search frozen in the first Function.
    When the first function resumes, the initial code tag pair search is completed.

    There are many other scenarios including a further nested code tags section the second function finds and results in a third Function starting. That third Function would complete all but the two pairs searches frozen . At completion of the third Function, the second and then the first Functions with their respective code tag pair search would be completed.

    Another scenario would be a double nesting such that there are three sequential end code tags. Once again a second followed by a third Function would be started. Once again, the third Function would complete all but the two pairs searches frozen. At completion of the third Function, the second and then the first Functions with their respective code tag pair search would be completed.

    One can imaging many more scenarios

    One common factor is that all but the final function will be searching through the replaced replacement characters. This probably makes the code a bit inefficient

    Final usage of Function
    Initial code to Call the Function for the first time. ( The function may Call itself one or more times further )
    The Function will not run itself.
    It needs to be Called with the initial parameters passed, the main string, strBBCode, along initially with the length of that string as the start point from which to step back.
    A routine is needed for that.
    Additionally, once the initial Function run , ( which is set off by this routine ) is finished, we will have the original string, strBBCode , returned in a modified form with the replacement character in place of all found code tags. These need to be removed, which can be easily achieved by replacing them with zero length strings.
    An example code is given in the next post.

    A few minor last thoughts..
    This second solution for manipulating a long string text does not require stepping backwards. It was simply developed on from the last code. That code was easier to do backwards as in the code parts were deleted whilst stepping through the string: It was easier to deal with such a code in a backward loop, as things behind me are deleted. The problem with going forward is that deleting in front of me can require adjusting where I am, and the count to, or final point of where I am going to. Think of it as you tripping up, or effecting where the end is, if you chop out the path in front of you: VBA can get messed up in Looping if you remove points in a progression that have not yet been considered.
    Another way of thinking about that:
    If I take bits out, then things shift back or down to fill the space. If I take things out in front of me, then by shifting back, things which I have not yet considered will change their position. This will likely mess up some order of progression.
    If I go backwards, and delete things behind me, then any order changes will only affect things which I have already considered. My original progression plan and order of things not yet considered is still valid.
    Google first, like this site:ExcelFox.com "Short Title or Theme of wot youre looking for"
    Use Code Tags: Highlight code; click on the # icon above,
    Post screenshots COPYABLE to a Spredsheet; NOT IMAGES PLEASE:
    Tools for that:
    http://www.excelfox.com/forum/showth...=9821#post9821
    https://app.box.com/s/gjpa8mk8ko4vkwcke3ig2w8z2wkfvrtv
    http://excelmatters.com/excel-forums/

  4. #24
    Senior Member DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    321
    Rep Power
    4

    Calling Code example, described at end of last post.

    Final usage of Function
    Initial code to Call the Function for the first time. ( The function may Call itself one or more times further )
    The Function will not run itself.
    It needs to be Called with the initial parameters passed, the main string, strBBCode, along initially with the length of that string as the start point from which to step back.
    A routine is needed for that.
    Additionally, once the initial Function run , ( which is set off by this routine ) is finished, we will have the original string, strBBCode , returned in a modified form with the replacement character in place of all found code tags. These need to be removed, which can be easily achieved by replacing them with zero length strings.
    An example code is given in the next post.








    Example Code:
    Code:
    '10   '
    Sub TestsBBCodeString() '
    20 '
    30   Rem 2 Bits needed as Part of Solution
    40   '2a) String will need to be got. The variable for it is referred to by changes in any Functions used. The initial length is effectively just the current position stepping back through the total character string
    50   ' Some variables: So A variable in VBA is like the Link to the part of proxy site where a few things about the actual Final site is informed about. Tiny is the pointer. Amongst other things it has a link to actually where all the stuff is. Like Dim Str As String   means at Str will be stuff like how long the text in that string is as well as a link to the memory where  the whole string actually is. So that is like more efficient if you want  to get at the length  you do not need to go off to where the ( possibly massive ) thing is.. etc..  :-)
    60   Dim strBBCode As String ' ' Prepares "Pointer" to a "Blue Print" (or Form, Questionaire not yet filled in, a template etc.)"Pigeon Hole" in Memory, sufficient in construction to house a piece of Paper with code text giving the relevant information for the particular Variable Type. VBA is sent to it when it passes it. In a Routine it may be given a particular Value, or (Values for Objects).  There instructions say then how to do that and handle(store) that(those). At Dim the created Paper is like a Blue Print that has some empty spaces not yet filled in. A String is a a bit tricky. The Blue Print code line Paper in the Pigeon Hole will allow to note the string Length and an Initial start memory Location. This Location well have to change frequently as strings of different length are assigned. Instructiions will tell how to do this. Theoretically a specilal value vbNullString is set to aid in quich checks.. But..http://www.mrexcel.com/forum/excel-questions/361246-vbnullstring-2.html#post44116
    70    Let strBBCode = "gggg[d=fg]2[/d]45[/8]x[ddd[Cl=XYZ][/Cl]Any text" ' Any test string, this code part takes the place of a code section that might for example get the text that may hve been copied to the Clipboard
    80   Dim Lenf As Long '          ' Long is very simple to handle, - final memory "size" type is known (123.456 and 000.001 have same "size" computer memory ) , and so a Address suggestion can be given for the next line when the variable is filled in.  '( Long is a Big whole Number limit (-2,147,483,648 to 2,147,483,647) If you need some sort of validation the value should only be within the range of a Byte/Integer otherwise there's no point using anything but Long.--upon/after 32-bit, Integers (Short) need converted internally anyways, so a Long is actually faster. )
    90     Let Lenf = Len(strBBCode)
    100  '2b) Calling the Function (So a Sub would do also-as the Sub has arguments it would like any function not be shown in Macro list. The point of a ByRef call is to effectively return a value, a change to a value actually )
    110   Call LongWayOfDoingIt2ReCurseCyClops(strBBCode, Lenf) ' This replaces the code tag characters to be removed with some arbritrary characters.
    120  '2c) The arbritrary charachters need to be removed
    130   Let strBBCode = Replace(strBBCode, "|", "", 1, -1) ' within strBBCode   ,   "|"    ,  replaced with  ""    ,   -1 indicating all of them
    End Sub
    The Function to be called_..
    Public Function LongWayOfDoingIt2ReCurseCyClops(ByRef strBBCode As String, ByVal BkStt As Long) As String
    _.. is given in the next post.
    Google first, like this site:ExcelFox.com "Short Title or Theme of wot youre looking for"
    Use Code Tags: Highlight code; click on the # icon above,
    Post screenshots COPYABLE to a Spredsheet; NOT IMAGES PLEASE:
    Tools for that:
    http://www.excelfox.com/forum/showth...=9821#post9821
    https://app.box.com/s/gjpa8mk8ko4vkwcke3ig2w8z2wkfvrtv
    http://excelmatters.com/excel-forums/

  5. #25
    Senior Member DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    321
    Rep Power
    4

    Recursion Function to remove BB Code code tags, described in last two posts

    Recursion Function to remove BB Code code tags, described in last two posts




    Code:
    '140 '
    Public Function LongWayOfDoingIt2ReCurseCyClops(ByRef strBBCode As String, ByVal BkStt As Long) As String ' I do not actually need any As ______ as nothing is returned, but you never know a string to retuen might be useful later
    150  Dim vTemp As Variant ' For Debugs
    160 '
    170  Rem 1) Main Loop for finding BB code pair String=====================================
    180  Dim StpBk As Long
    190      For StpBk = BkStt To 1 Step -1 ' Looking for the first ] likely at end of end tag.
    200      Let vTemp = Mid(strBBCode, StpBk, 1)
    210          If Mid(strBBCode, StpBk, 1) = "]" Then ' we note Position and need to look for a [/ On a recursion call this conditiuon is always met
    220          Dim BcrdEEnd As Long: Let BcrdEEnd = StpBk '     Position of possible End Code Tag End
    230          Dim posBBCodeTagSrch As Long ' For search through possible valid BBCode Section'_-Loop for Pos tag pair section--
    240                For posBBCodeTagSrch = StpBk To 2 Step -1
    250                  If Mid(strBBCode, posBBCodeTagSrch, 1) = "]" Then Let BcrdEEnd = posBBCodeTagSrch: GoTo NxtCESPosPos ' If we hit another ] then change the stop tag stop character position and start again to see if is a valid BB code section
    260                  If Mid(strBBCode, posBBCodeTagSrch, 2) = "[/" Then     ' Found a stop tag "[/" at posBBCodeTagSrch, so mark..
    270                  Dim BcrdESt As Long: Let BcrdESt = posBBCodeTagSrch    ' ..Position of End Code Tag Start and ..
    280                   Exit For ' we have an end code tag from a possibly code tag pair section
    290                  Else 'No luck finding a "[/" start bit of stop tag yet so keep looking Matey
    300                  End If
    310 NxtCESPosPos:  Next posBBCodeTagSrch ' trying to find a start [/ pair further back keep looking Matey
    320                If posBBCodeTagSrch = 3 Then Exit Function 'case we never found the start of an End Code Tag
    330                                          ' Loop likely at end of start tag ]
    340                    For posBBCodeTagSrch = BcrdESt - 1 To 1 Step -1 ' Looping furhter back to find a "]" likely of a start code tag but possibly a nested stop tag
    350                      If Mid(strBBCode, posBBCodeTagSrch, 1) = "]" Then ' possible start from back of "]" likely of a start code tag but possibly a nested stop tag found so....
    360                      If Mid(strBBCode, posBBCodeTagSrch - 1, 1) = "]" Then GoTo NxtCSSPosPos ' we have a second "]" one back, looking back so this will be caught by next back 1
    370                      Dim BcrdSEnd As Long: Let BcrdSEnd = posBBCodeTagSrch  ' Position of possible Start Code Tag End
    380                      Dim LTagStLoop As Long ' _- Most Innerloop to find start of start Code Tag
    390                          For LTagStLoop = posBBCodeTagSrch - 1 To 1 Step -1 ' _- Most inner loop [ likely start of start tag_
    400                              If Mid(strBBCode, LTagStLoop, 1) = "[" Then
    410                              Dim BcrdSSt: Let BcrdSSt = LTagStLoop
    420                                  Rem If [/ Then Oh dear time for a "recursion wonk"
    430                                  If Mid(strBBCode, LTagStLoop, 2) = "[/" Then 'Seems like our last attempt at finding a start tag caught a nested end tag
    440  'Time to freeze this Function with current end tag info.
    450                                   Call LongWayOfDoingIt2ReCurseCyClops(strBBCode, BcrdSEnd) ' Position of possible Start Code Tag End is actually another end tag end, so give this ] position as the start position and run another copy of the Function. At the start of which the first check should catch the ] and redo the finfding of the last tag complete so is a bit ineffecient
    460  'Time to unfreeze this Function, and continue where left off having now possibly modified the main string for one or more nested tag pairs, as well as probably catching all but the possible pair that I froze on
    470                                   Let posBBCodeTagSrch = LTagStLoop ' When we went off to the copy of the function our LTagStLoop was at the start of what turned out to be a nested end code tag. That is as good a place as any to strt going further back now to try to find the match for the end tag before the nested one. We will be going throught the lkast nested one, but never mind
    480                                   GoTo NxtCSSPosPos ' unfreeze search for a matching start tag
    490                                  Else ' As soon as we come here we are likely
    500                                  End If ' ready to do the string modification for a found matching pair
    510                               Let StpBk = BcrdSSt ' - 1 will be done at Next StpBk 'End of Code Tag section search ' I am commiting the cardinal sin of changing the Loop bound variable caount in the main outer step back in character Loop
    520                               GoTo FkOffCrd ' We leave this For the start Code Tag Start Search and go to Fuk Off Crd:
    530                              Else
    540                              End If
    550                          Next LTagStLoop ' _- End Most inner loop [ likely start of start tag_
    560                          If LTagStLoop = 1 Then GoTo TheEnd 'I think here we must always be at LTagStLoop = 1 - Case I think we failed to find a start start [ for the complete possible Code Tag section search
    570                      Else ' Not found a "]" likely of a start code tag but possibly a nested stop tag found
    580                      End If
    590 NxtCSSPosPos:      Next posBBCodeTagSrch ' End Loop likely end of start tag ]
    600                    If posBBCodeTagSrch = 0 Then GoTo TheEnd ' For any outer search failed to find a ] at all or a search for matching start tag that was fruitless
    610 FkOffCrd:   Rem 3) "I think we come here with a complete set of 4 positions"
    620              If BcrdEEnd <> 0 And BcrdESt <> 0 And BcrdSEnd <> 0 And BcrdSSt <> 0 Then 'Just to check'
    630              Dim SttBcrd As String, StpBcrd As String ' Start and stop tags as complete strings including [ and ]
    640              Dim LenSttBcrd As Long, LenStpBcrd As Long ' Some extra variables for clarity
    650               Let LenSttBcrd = BcrdSEnd - BcrdSSt + 1: Let LenStpBcrd = BcrdEEnd - BcrdESt + 1 ' As taypical to get length of anything when start and stop positions are known.. -- (stoppos-startpos+1)
    660               Let SttBcrd = Mid(strBBCode, BcrdSSt, LenSttBcrd) ' In String  ,  part from [  ,  for length (stoppos-startpos+1) '  like |Color=Red|  or |b|
    670               Let StpBcrd = Mid(strBBCode, BcrdESt, LenStpBcrd) ' like |/Color| |/b|
    680              Dim SttWrd As String, StpWrd As String
    690               Let StpWrd = Mid(StpBcrd, 3, Len(StpBcrd) - 3) ' like Color from |/Color| - Whole Word, from 3rd Chr , for Length as  WholeWord-3
    700               Let SttWrd = Mid(SttBcrd, 2, Len(StpWrd))      ' like Color from |Color=Red|
    710                  If StrComp(StpWrd, SttWrd, vbTextCompare) = 0 And Mid(StpBcrd, 2, 1) = "/" Then ' If UCase(SttWrd) = UCase(StpWrd) And Mid(StpBcrd, 2, 1) = "/" Then 'Ucase allows for differences in cases
    720                   Mid(strBBCode, BcrdSSt, LenSttBcrd) = String(LenSttBcrd, "|") ' Mid can be used to put in strBBCode   ,   starting from start of tag   ,   for a length space within of the tag    =   any symbol as many times as that length space
    730                   Mid(strBBCode, BcrdESt, LenStpBcrd) = String(LenStpBcrd, "|")
    740                  Else ' ' No manipulationn of string for non matching tag word
    750                  End If
    760              Else 'No manipulation of string for not all 4 [ and ] found. Not sure if I ever come here..
    770              End If
    780          Else ' Case no "]" found in main strBBCode
    790          End If
    800      Next StpBk ' Stepping further back in Main Loop for finding first "]"============
    810 TheEnd:
    820  ' Let LongWayOfDoingIt2ReCurseCyClops = "Return from Function after running" ' Option if I wanted to give a value to be returned from Function. By virtue of the ByRef at signature line I effectively continually apply changes to the variable strBBCode
    End Function
    Google first, like this site:ExcelFox.com "Short Title or Theme of wot youre looking for"
    Use Code Tags: Highlight code; click on the # icon above,
    Post screenshots COPYABLE to a Spredsheet; NOT IMAGES PLEASE:
    Tools for that:
    http://www.excelfox.com/forum/showth...=9821#post9821
    https://app.box.com/s/gjpa8mk8ko4vkwcke3ig2w8z2wkfvrtv
    http://excelmatters.com/excel-forums/

  6. #26
    Senior Member DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    321
    Rep Power
    4

    Recursion. A simple Layman explanation. Alternative additional General explanation

    Recursion. A simple Layman explanation


    General idea, considering parallel to real life:
    Where you work there is a Filing cabinet containing the master copy of a sets of instructions for particular jobs (work sequences). These are your written code routines, ( Sub routines and Functions ).
    ( These job sequences involves you walking around the company and taking out various actions / tasks in different places ). In general you can only follow one set of instructions at a time. But you can stop them before you are finished, or pause them, and follow a different set of instructions.
    Think of those master copies as the routines you write and “see” in the VBA Editor code window. A code module is pseudo a draw in the Filing cabinet.
    ( ***The analogy falls down a bit when considering recursion due to a poor realisation in the debug step mode ( F8 ) code facility, but this might change due to some changes I have suggested at various Microsoft suggestion giving platforms )

    Case 1) The normal working case ( no recursion ). Simple single routine code.
    Your boss makes a copy of a set of instructions. He gives them to you. You carry them out. That is your job: You do what the boss says, or rather you follow the written instructions given to you at any time by the boss. Whenever you are given a set of job instructions by the boss, you carry them out, starting from when you are given the instructions. In general you can only do one job ( sequence of instructions ) at a time. In general you are waiting to receive your instructions.
    Excel VBA is like the worker. Your worker. You are the boss. If you present Excel VBA with a set of instructions ( a written code in a routine ) , then Excel VBA carries them out. It carries them out from the point of the start line. ( like Sub MyRoutine() etc.. )
    In general , Excel is waiting to get , or not doing anything but listening out for, instructions. Something must be done to initiate the initial code run.
    In general, at least for our discussions here , VBA can only carry out one routine at a time. But it can stop or pause a routine before it is finished and start another one... that is what happens when other routines are “call”ed from within a routine... as discussed in the next sections:

    Case 2) A simple routine call from within a routine
    You are doing a set of instructions given to you by the boss.
    VBA is in a running routine., following the routine, ( code ), instructions.
    The boss gives you a new set of different instructions.
    Whilst running a routine, VBA hits a code line which calls another routine or uses, ( calls ) , a routine.
    You obey. You stop what you are doing, “freeze” any actions in process, and follow the new set of instructions.
    VBA obeys. It stops ( freezes or pauses ) the running of the routine ( code instruction copy ) it was doing, and carries out the new ( code ) instructions.
    When you have done all the new instructions, you go back and finish the first job that you were doing, starting where you left off, “unfreezing” any actions stopped when you started the second set of instructions..
    When the called routine or function is finished, VBA goes back to the first routine carrying on where it left off. Any variables that may have been “filled” before in the first routine are still there, unfrozen now, with the values previously with which they were filled with.

    For the last two simple cases, the Debugging ( F8 ) step mode avaialble in code progression can be helpful to demo what is going on.
    It is worth noting that what actually is going on is a bit different:
    What actually happens can be thought of as closer to something of this form:
    The codes you write are master copies. When a routine begins a copy of that master, including all provisions necessary for a full set of any variables used is made. Once the routine ends, this copy and all variables are erased. ( The analogy to real life: For each start of a job sequence, a copy is made of the master instructions, and this is followed. It is thrown away when the job sequence is completed)
    If you step through a code in F8 step mode, the VB Editor will show you progression through the master code copies which you wrote and “see” in the VB Editor code window. That is not what is actually happening. But it is fine to imagine that the originals are the actual copies that are being gone through.
    It is worth bearing in mind that copies are made and gone through rather than your written masters. That helps to understand, or rather, helps avoid confusion, when considering the next situation, recursion***


    Case 3) Recursion. A routine calls itself
    This is not a great deal different from the last case. The word recursion is used to cover the situation of a re occurrence of the same set of instructions or procedure, at a point in code running before the previous instructions or procedure was finished. ( In real life analogy: you are given the same set of instructions written on a new copy before you have finished and following the first set of instructions. - So you pause at the point you are at and start doing the same job again before you have finished doing the last one already started)
    Another slightly closer analogy to the programming case of recursion is the following: Imagine that you are doing a set of given instructions, when one of the instructions tells you to make a new copy of the instructions and follow those. In such a case you do as in the previous case: You obey. You stop what you are doing, “freeze” any actions in process, and follow the new set of instructions. When you have done all the new instructions, you go back and finish the first job that you were doing, starting where you left off, “unfreezing” any actions stopped when you started the second set of instructions...

    You may remark, quite rightly, that you will never finish, as you will endlessly keep making a new set of instructions and begin following the new instructions. In the simple case described, that will indeed occur, ( at least until the stack of instructions you have “frozen” along with any stack of any filled variables, ( newly created for each copy ), hits some “overfull” or “overflow” limit where you are stacking them).

    In the practice you would have in your instructions some clause referring to the start of a new set of instructions of the form like: If ..something... Then do it.
    Hopefully you have chosen that clause appropriately such that at some point you will skip the step of starting a new set of instructions. Further, hopefully that should occur often enough such that eventually all copies of the instructions are finished.

    We can achieve this recursion situation in VBA in various ways. A couple of examples:
    _ one simply has a code line within some routine, say Sub x( , ) of the form Call x( , ),
    or
    _ for the case of some function, Function y( , ) , a code line within that function can be present of the form Var = y( , )
    Once again it would be important to have some clause so as to follow that code line only If .. something.

    Typical Example:
    A very typical working example of such a code is to take some actions to and / or within all the Folders and Sub Folders within some Main start folder.
    For such an example you would have two codes:
    _ a simple “normal” routine, which might, for example, ask you for the initial main Folder.
    _ a recursion routine. The recursion routine would take in a Folder name or Folder path or Folder As Object.
    The routine would initially be called from the first simple routine, which would pass the main Folder.
    For the Folder taken in by the recursion routine some action on and/ or within that Folder would be taken. This would generally mean taking some action on each Sub Folder within the Folder given. Typically, the last action would be to call the recursion routine, passing this time not the main Folder, but the current Sub Folder, but only If the Sub Folder itself has at least one Sub Folder.
    One needs to carefully think through oneself the result of this. In end effect all Folders within the main Folder will be considered.
    http://www.excelforum.com/developmen...directory.html
    http://www.excelforum.com/excel-prog...ml#post4316662
    http://excelpoweruser.blogspot.de/20...13383708093554


    Debug ( F8 ) code progression step mode is a bit misleading:
    For the recursion case following code progression in debug step ( F8 ) mode can be confusing. This is because during the recurring process you appear to jump around in the main original master copy of the recursion function.
    I would suggest one imagines that the following happens instead.
    (I would further suggest, that this may be what a more intelligent and more demonstrative debug Editor in a Visual Basic development environment might be developed to do in the future):
    Imagine the initial copy of the recursion Function is how you see it whilst stepping through the code in debug step ( F8 ) mode. So imagine that this is , in fact, the first copy of the routine. ( ***Remember I noted that actually, effectively a copy, unseen by us, is actually gone through by VBA )
    But.
    Consider that the following happens when you carry out the line where the routine calls itself.
    At this point a copy of the full recursion routine text is made. If you like , the code line calls into existence a new copy. This is than pasted into the code window below the current routine.
    The debug curser then jumps to the start of this new copy of the routine. That new copy of the routine is then stepped through.
    Imagine that any variables declared in the new copy routine are given a slightly different shade of color to those with the same name in the first routine. This would help us to remember that they are different independent variable, which just happen to have the same name. ( VBA does not get confused as it only “sees” the variables in the current running copy of the function. Any previous functions and their variables are “frozen” )
    When in stepping through this copy, the end is reached , the copy vanishes from the code window, along with any variable that were created and used in it, and the debug cursor then jumps back to the line after the line which called it into existence.
    Effectively we are doing the process described in _2) . But to visualise this correctly we need to make a new copy of a routine code text and step through that when a routine calls itself ( Currently when using step mode ( F8 ) debug, the progression appears to go through the same actual procedure. It is following the same instructions contained in the main Master copy of the instructions, but the actual instructions it is following are written on a new copy of the master set of instructions. Similarly any variables used in this copy run are unique to this copy run and “die” at the end of the copy routine run, at which point the copy of the instructions is thrown away )


    Alan
    Last edited by DocAElstein; 03-03-2017 at 05:15 PM.
    Google first, like this site:ExcelFox.com "Short Title or Theme of wot youre looking for"
    Use Code Tags: Highlight code; click on the # icon above,
    Post screenshots COPYABLE to a Spredsheet; NOT IMAGES PLEASE:
    Tools for that:
    http://www.excelfox.com/forum/showth...=9821#post9821
    https://app.box.com/s/gjpa8mk8ko4vkwcke3ig2w8z2wkfvrtv
    http://excelmatters.com/excel-forums/

  7. #27
    Senior Member DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    321
    Rep Power
    4

    Sub GetsathishsusaStarted()

    Code for sathishsusa
    Post #3
    http://www.excelfox.com/forum/showth...0177#post10177


    Sub GetsathishsusaStarted()




    Code:
    Sub GetsathishsusaStarted()                                    '  http://www.excelfox.com/forum/showthread.php/2161-Highlight-Overlap-dates?p=10177#post10177
    Rem 1) Worksheets Info
    Dim WSt As Worksheet         'EP Dim: For Object variables: Address location to a "pointer". That has all the actual memory locations (addresses) of the various property values , and it holds all the instructions what / how to change them , should that be wanted later. That helps to  explain what occurs when passing an Object to a Call ed Function or Sub Routine By Val ue. In such an occurrence, VBA actually  passes a copy of the pointer.  So that has the effect of when you change things like properties on the local variable , then the changes are reflected in changes in the original object. (The copy pointer instructs how to change those values, at the actual address held in that pointer). That would normally be the sort of thing you would expect from passing by Ref erence.  But as that copy pointer "dies" after the called routine ends, then any changes to the Addresses of the Object Properties in the local variable will not be reflected in the original pointer. So you cannot actually change the pointer.)
     Set WSt = ThisWorkbook.Worksheets("sheet1") '    'EP Set: Fill or partially Fill: Setting to a Class will involve the use of an extra New at this code line. I will then have an Object referred to as an instance of a Class. At this point I include information on my Pointer Pigeon hole for a distinct distinguishable usage of an Object of the Class. For the case of something such as a Workbook this instancing has already been done, and in addition some values are filled in specific memory locations which are also held as part of the information in the Pigeon Hole Pointer. We will have a different Pointer for each instance. In most excel versions we already have a few instances of Worksheets. Such instances Objects can be further used., - For this a Dim to the class will be necessary, but the New must be omitted at Set. I can assign as many variables that I wish to the same existing instance
    Rem 2) Checked Names info
    '2a) Arrays of data, value(Boolean) and employee name
    Dim RngChk As Range
     Set RngChk = WSt.Range("AS8:AT11")
    Dim arrChkEmp() As Variant, arrEmp() As Variant                ' A simple one line code is used typically  wherby the .Value "string values" Property is used to return the cell value from a Range object. These are contained in an Array of Variant types. These can be assigned in VBA to an array of appropriately declared (Dim ed) Member Element types, Variant in this case.
     Let arrChkEmp() = RngChk.Resize(, 1).Value   '         AS     ' Using Resize ing Property applied to a Range object -  the Range object resized to (nochange to rows,  change to 1 column) effectively returns  the first column as a Range object
     Let arrEmp() = RngChk.Offset(0, 1).Resize(, 1).Value ' AT     ' Before Resize, Offset Property applied to shift enclosed spreadsheet range (no shift in row direction , 1 column shift to the right)
    '2b)  Build string and Array of Employes checked as True
    '2b)(i)
    Dim Cnt As Long ' Loop Bound variable Count                    ' Long is very simple to handle, - final memory "size" type is known (123.456 and 000.001 have same "size" computer memory ) , and so a Address suggestion can be given for the next line when the variable is filled in.  '( Long is a Big whole Number limit (-2,147,483,648 to 2,147,483,647) If you need some sort of validation the value should only be within the range of a Byte/Integer otherwise there's no point using anything but Long.--upon/after 32-bit, Integers (Short) need converted internally anyways, so a Long is actually faster. )
    Dim strEmp As String ' ' Prepares "Pointer" to a "Blue Print" (or Form, Questionaire not yet filled in, a template etc.)"Pigeon Hole" in Memory, sufficient in construction to house a piece of Paper with code text giving the relevant information for the particular Variable Type. VBA is sent to it when it passes it. In a Routine it may be given a particular Value, or (Values for Objects).  There instructions say then how to do that and handle(store) that(those). At Dim the created Paper is like a Blue Print that has some empty spaces not yet filled in. A String is a a bit tricky. The Blue Print code line Paper in the Pigeon Hole will allow to note the string Length and an Initial start memory Location. This Location well have to change frequently as strings of different length are assigned. Instructiions will tell how to do this. Theoretically a specilal value vbNullString is set to aid in quich checks.. But..http://www.mrexcel.com/forum/excel-questions/361246-vbnullstring-2.html#post44116
     Let strEmp = "Overlap dates Between "                         'Initial string
    '2b(ii)
    Dim TrueCnt As Long: Let TrueCnt = 0                           ' Count of checked as True employees
        For Cnt = 1 To UBound(arrChkEmp(), 1)                      ' For all employee "rows"
        Dim arrChkTrueEmp() As String                              ' I know the type and am looping to fill in so can choose type
            If arrChkEmp(Cnt, 1) = True Then ' Box is checked resulting in Boolean True held in Range object of cell  -  "...I do not know anything about check boxes. :( ...  So I look at TRUE or FALSE value in Array of captured valuees from Range("AS8:AS11")
             Let strEmp = strEmp & arrEmp(Cnt, 1) & " and "        ' ( '2b)(i)  ) add checked names to initial string report for output
             Let TrueCnt = TrueCnt + 1                             ' Increase count of true checked names  ...
            ReDim Preserve arrChkTrueEmp(1 To TrueCnt)             ' ... Increase array size to accomodate another  True name
             Let arrChkTrueEmp(TrueCnt) = arrEmp(Cnt, 1)           ' Fill next space in Array with next checked as True name
            Else
            End If
        Next Cnt
     Let strEmp = Left(strEmp, Len(strEmp) - 5) ' omit last " and "
    
    Rem 3) Main Input data
    '3a) Full data range as range object
    Dim RngDta As Range
     Set RngDta = WSt.Range("B21:E26")
    '3b)
    Dim arrListNms() As Variant, arrFrm() As Variant, arrTo() As Variant, arrOvrLpDts() As Variant
     Let arrListNms() = RngDta.Resize(, 1).Value
     Let arrFrm() = RngDta.Offset(0, 1).Resize(, 1).Value2
     Let arrTo() = RngDta.Offset(0, 2).Resize(, 1).Value2
     Let arrOvrLpDts() = RngDta.Offset(0, 3).Resize(, 1).Value ' RngDta.Offset(0, 3).Resize(, 1).Interior.Color = 255
    Rem 4)  Main Outer Loop ==============Cnt=========                                                       Dim StearEmp As Variant
        For Cnt = 1 To UBound(arrListNms(), 1) ' Outer Loop is each name in column B                                                                                         'Each StearEmp In arrChkTrueEmp()
        '4a)
        Dim NmeMtch As Variant ' For result of attempt to find next name(Cnt) in arrChkTrueEmp()
         Let NmeMtch = Application.Match(arrListNms(Cnt, 1), arrChkTrueEmp(), 0)
            If Not IsError(NmeMtch) Then ' This is the case we have found a name I want to check against
            Dim CntIn As Long
    Rem 5)  Inner Loop ------------------CntIn--------
                For CntIn = 1 To UBound(arrListNms(), 1)
                    If arrListNms(Cnt, 1) <> arrListNms(CntIn, 1) Then ' To ignore compare employee with himself
                     Let NmeMtch = Application.Match(arrListNms(CntIn, 1), arrChkTrueEmp(), 0)
                        If Not IsError(NmeMtch) Then ' This is the case we have found a name I want to check against
                        ' Now is main part of criteria check
                            If (arrFrm(Cnt, 1) >= arrFrm(CntIn, 1) And arrFrm(Cnt, 1) <= arrTo(CntIn, 1)) Or (arrTo(Cnt, 1) >= arrFrm(CntIn, 1) And arrTo(Cnt, 1) <= arrTo(CntIn, 1)) Then ' main comdition for match
                             Let arrOvrLpDts(CntIn, 1) = "Overlap"
                            Else ' Main overlap condition not met
                            End If
                        Else ' Case The Employee name was not one that was checked true to check for Holiday overlap
                        End If
                    Else ' case of Checked employee is in Employee Names list
                    End If
                Next CntIn '---End Inner Loop ---------
            Else ' Case The outer loop for all names was at a name not to be compared - do nothing and go on to next name in column B
            End If ' do nothing and go on to next name in column B at next line
        Next Cnt ' --End Main Outer loop ==============                                                                         '                                               StearEmp
    
    Rem 6)  Loop through arrOvrLpDts() Array to add to report string and mark cell
        For Cnt = 1 To UBound(arrOvrLpDts(), 1)
            If arrOvrLpDts(Cnt, 1) = "Overlap" Then
             Let strEmp = strEmp & vbCrLf & arrListNms(Cnt, 1) & " From " & Format(arrFrm(Cnt, 1), "d" & "-" & "mmm" & "-" & "yyyy") & " To " & Format(arrTo(Cnt, 1), "d" & "-" & "mmm" & "-" & "yyyy") & ""
             Let RngDta.Offset(0, 3).Resize(, 1).Item(Cnt).Interior.Color = 255
            Else
            End If
        Next Cnt
    
    Rem 7) Message Box output of report string
     MsgBox prompt:="" & strEmp & ""
    End Sub
    Google first, like this site:ExcelFox.com "Short Title or Theme of wot youre looking for"
    Use Code Tags: Highlight code; click on the # icon above,
    Post screenshots COPYABLE to a Spredsheet; NOT IMAGES PLEASE:
    Tools for that:
    http://www.excelfox.com/forum/showth...=9821#post9821
    https://app.box.com/s/gjpa8mk8ko4vkwcke3ig2w8z2wkfvrtv
    http://excelmatters.com/excel-forums/

  8. #28
    Senior Member DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    321
    Rep Power
    4

    Arrays are good. You should use them , in my opinion :)

    Notes for
    http://www.snb-vba.eu/VBA_Arrays_en.html
    http://www.excelfox.com/forum/showth...0174#post10174 https://www.excelforum.com/the-water...ml#post4613906


    Code:
    Sub snbsVarTypeTypeName() ' http://www.snb-vba.eu/VBA_Arrays_en.html       http://www.excelfox.com/forum/showthread.php/2157-Re-Defining-multiple-variables-in-VBA?p=10174#post10174        https://www.excelforum.com/the-water-cooler/1174400-would-like-to-know-about-the-forum-experts-gurus-4.html#post4613906
    a_sn = Array(1, 2, 3, 4)
    a_sp = Split("1,2,3,45", ",")
    a_sq = Range("A1:F5") ' defaluts to Range("A1:F5").Value ' .Value Property returns a Field ( Arrray ) of Variant Member Elements
    Dim a_st As Shape: x = VarType(a_st) ' 9 ( Object )
    Rem 2 VarType Constants    https://msdn.microsoft.com/en-us/library/office/gg251422.aspx
    Dim VrTpe As Long
    
    '2a) Variant Variables: You can determine how the data in a Variant is treated..
    Dim Var As Variant: VrTpe = VarType(Var) ' 0  vbEmpty  Uninitialized (default)
    Dim VarDef: Let VrTpe = VarType(VarDef) ' 0
     Let Range("A25:A26").Value2 = "Some text"                         '  .Value2 gives you the underlying value of the cell (could be empty, string, error, number (double) or boolean)                         '  .Value gives you the same as .Value2 except if the cell was formatted as currency or date it gives you a VBA currency (which may truncate decimal places) or VBA date.
     Let Var = Range("A25:A26").Text                                   '  "Some text" given to Variant    attempts to coerce to a String and will fail if the underlying Variant is not coercable to a String type
     Let Range("A26").Value = "Some other text"                        '  .Value gives you the same as .Value2 except if the cell was formatted as currency or date it gives you a VBA currency (which may truncate decimal places) or VBA date.
     Let Var = Range("A25:A26").Text ' The data is invalid, a Null is given to Variant
     Let VrTpe = VarType(Var) ' 1  Contains no valid data
    Dim arrVar(1 To 1) As Variant
     Let VrTpe = VarType(arrVar())
    
    '2b) Normal Variables
    Dim Intr As Integer: VrTpe = VarType(Intr) ' 2
    Dim Lng As Long: VrTpe = VarType(Lng) ' 3
    Dim Sngle As Single: VrTpe = VarType(Sngle) ' 4
    Dim Dble As Double: VrTpe = VarType(Dble) ' 5
    
    
    Dim Str As String: VrTpe = VarType(Str) ' 8
    Dim Obj As Object: VrTpe = VarType(Obj) ' 9
    
    VrTpe = VarType(a_sn) ' 8204 = 8192 (array) + 12 (Variant)
    VrTpe = VarType(a_sp) ' 8200 = 8192 (array) + 8 (String)
    VrTpe = VarType(a_sq) ' 8204 = 8192 (array) + 12 (Variant)        ' a_sq defaults to a_sq.Value, or rather the = string refs to cells which returns a a Field ( Arrray ) of Variant Member Elements
    VrTpe = VarType(a_st) '   9 (object)
    Dim a_bt(1 To 1) As Byte
     Let VrTpe = VarType(a_bt()) ' 8209 = 8192 (array) + 17 (Byte)
    Dim Rnga_sq As Range: Set Rnga_sq = Range("A1:F5")
     Let VrTpe = VarType(Rnga_sq) ' 8204 = 8192 (array) + 12 (Variant (used only for arrays of Variants)) !!!!  VarType  interprets a Range as an array. ???????
    End Sub
    
    
    
    Sub TextValueValue2() '    https://fastexcel.wordpress.com/2011/11/30/text-vs-value-vs-value2-slow-text-and-how-to-avoid-it/        http://stackoverflow.com/questions/17359835/what-is-the-difference-between-text-value-and-value2
    Set c = Range("A27")
    c.Value = 1198.3
    c.NumberFormat = "$#,##0_);($#,##0)"
    MsgBox c.Value
    MsgBox c.Text
    MsgBox c.Value2
    End Sub
    Sub M_snb()
       sn = Array(String(300, "p"), String(700, "p"), Space(500))
    Dim v
       MsgBox Len(sn(0)) & vbLf & Len(sn(1)) & vbLf & Len(sn(2))
    End Sub



    Using Excel 2007 32 bit
    https://msdn.microsoft.com/en-us/lib.../gg251422.aspx
    Normal Variable
    0
    vbEmpty Uninitialized
    Array Variable
    8192
    Array ( Member elements Empty )
    Normal Variable
    1
    If data is invalid, a Null is given to Variant
    Array Variable
    8193
    Null
    Normal Variable
    2
    Integer
    6
    Currency
    11
    Boolean
    Array Variable 8192+2=8194
    8198
    8203
    Normal Variable
    3
    Long
    7
    Date
    12
    -------
    Array Variable
    8195
    8199
    8204
    Variant (used only for arrays of variants)
    Normal Variable
    4
    Single
    8
    String
    17
    Byte
    Array Variable
    8196
    8200
    8146
    Normal Variable
    5
    Double
    9
    Object
    36
    UserDefined
    Array Variable
    8197
    8201
    8228
    Normal Variable
    6
    Currency
    Array Variable
    8198
    Normal Variable
    7
    Date
    Array Variable
    8199
    Normal Variable
    8
    String
    Array Variable
    8200
    Normal Variable
    9
    Object
    Array Variable
    8201
    Normal Variable
    10
    Error
    Array Variable
    8202
    Normal Variable
    11
    Boolean
    Array Variable
    8203
    Normal Variable
    12
    -------
    Array Variable
    8204
    Variant (used only for arrays of variants)
    Normal Variable
    17
    Byte
    Array Variable
    8209
    Normal Variable
    36
    UserDefined
    Array Variable
    8228
    Worksheet: EmptiesVariables
    Google first, like this site:ExcelFox.com "Short Title or Theme of wot youre looking for"
    Use Code Tags: Highlight code; click on the # icon above,
    Post screenshots COPYABLE to a Spredsheet; NOT IMAGES PLEASE:
    Tools for that:
    http://www.excelfox.com/forum/showth...=9821#post9821
    https://app.box.com/s/gjpa8mk8ko4vkwcke3ig2w8z2wkfvrtv
    http://excelmatters.com/excel-forums/

  9. #29
    Senior Member DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    321
    Rep Power
    4

    Reply to Willy And Rick, at https://excelribbon.tips.net/T010768_Shortening_ZIP_Codes.html

    This is a comment reply I tried to post to Rick Rothstein and Willy Vanhaelen here
    https://excelribbon.tips.net/T010768...ZIP_Codes.html

    Possibly some text annoyed the Firewall?
    ( Also posted here https://www.excelforum.com/developme...ml#post4629001
    http://www.excelfox.com/forum/showth...0201#post10201

    and in this Text File which is also atttatched https://app.box.com/s/oz2oga0ym6bue2kqf8assy8uq8qdod0e )
    _.________________________________________________ _______


    Hi Rick,
    Thanks very much for popping by. It is really great to hear your “take” on these things. I am continually frustrated that Microsoft, for example, do not give precise information about these things. It appears they are not sure themselves. People like you seem to be the best authorities on this.
    To some extent it appears anyone may have an opinion, as no definitive documentation or understanding exists.
    I am very glad you seem to think the If({1}, ___ ) is OK: I had been a bit nervous about suggesting it to Willy, as I had not seen it before. But it seemed logical based on all that I had learnt from these sort of things so far.

    I have followed your arguments below. You explained very well and I follow your logic and reasoning. Thanks for clarifying a few things.
    I came to some general conclusions about what is going on with multi value formula / array analysis and Evaluate. I think I am more or less in agreement with your appraisal. It’s great to hear it. Most people just accept what happens and hack out some of these interesting “coercing” as they often refer to them, as a way to ”get” array analysis to work. Most seem to have no idea how their solutions work.

    This is my take, and the reasoning to the
    If({1}, ___ )
    working as an alternative to the
    If(Row(),___ )
    ( Until recently I only used or had seen the If(Row(), ___ ) or Index( ____ , 0, 0 ) )

    I possibly see things slightly the other way around, but probably in end effect the same ...

    I was thinking that one of the reasoning’s why CSE is needed is the following:
    It seems that single breadth things ( single column or single row arrays or ranges ) , as well as a single range reference works very efficiently due to some mechanism involving an effective help matrix the size of a worksheet which for a single cell reference effectively has that value in the entire cells. For a single breadth range, that matrix is filled in the “other” dimension across or down the effective matrix over the “length” of the range, effectively duplicating the values in the range. This all explains why a single reference like =A1 works very quickly, and also why single breadth things work so quickly, and also explains Implicit intersection which results in a single breadth reference like =A1:A4 or =A1:D1 not giving an error in some cells in a worksheet.
    http://www.excelfox.com/forum/showth...on-and-VLookUp

    The problem comes with a multi dimensional range reference like =A1:B2. That will error in any cell. What is required is to somehow do some Controlled Shifting of these effective matrices before the doing for each value effectively a single Evaluation on Enter. Maybe that is why they chose the key combination of Ctrl + Shift + Enter.

    In a way, the CSE could be thought of “removing” the direct default connection to the worksheet which results in =A1 giving a result in any cell ( except A1 of course ) and = A1:A2 giving a result in some cells.

    My argument then would be that Evaluate does not necessarily do array analysis as such, - It simply does not need the CSE to stop it from erroring. Further my argument is that for any =A1:B2, the array analyses does take place , the values are there, but it messes up in a single spreadsheet cell without CSE.

    ( Evaluate(“=A1:B2”) works to return an Array as by default Excel holds, I believe such references as Range objects, which then have the default Property of .Value applied in many cases to return the single value or values in an Array as appropriate. That can be proved by setting Evaluate(“=A1:B2”) to a Range object variable. It will return the Range object. Equating Evaluate(“=A1:B2”) to a Variant will result in it defaulting to a value or array of values. )

    Coming back to Evaluate and multi value ( array ) analysis. I think Evaluate just evaluates.

    In the case under consideration, the range is “there” such as A1:A10 is there in Left(A1:A10,5). This will not error in Evaluate, as it does in all but rows 1 to 10 in a spreadsheet. But Left( ) is not programmed to return an Array. Possibly this is what you are saying by “not array aware”.

    But, as you suggest if you embed that text string function within another function that is “array aware”, you can “trick” Left into giving all its available values. My argument there is that once embedded into function that is programmed to do array analysis, then in most cases *** , the following happens:
    Excel will evaluate as long and as far and in the dimensions of any arrays “available” . In the case of If(Row(), __ ) or If({1}, __ ) this will mean for our example that it “does” for over the range A1:A10.

    The If(Row(), __ ) I have seen many times, first in a Forum answer you gave.
    The If({1}, __ ) I guessed, following those arguments should do the same and it did. I had not seen that before but I expect someone else stumbled over it as well a long time before me.


    A few post down I suggested what happens when you do like_...

    Evaluate("IF(Row(A1:A11),LEFT(A1:A10,5))")

    The same reasoning suggest this will be “done” over A1:A11. The last evaluation returns an error, as expected.

    The fact that the code below does not produce errors in a second “column” in the Array is explained as follows. Excel holds a single breadth range reference like A1:A10 , as I suggested above , in an effective help matrix extending across or down the “other” dimension
    So the returned Array duplicates the results in the second “column”
    In a way it is what happens in CSE type 2 array entry in which you extend the area you select ( before adding your formula and doing CSE ) to a spreadsheet area beyond the range of values you are expecting back.

    Code:
    Sub ExtendedAcrossSingleBreadthRefPseudoCSEType2Entry()    '
    Dim Arr() As Variant
     Let Arr() = Evaluate("=IF(Row(A1:A10)*Column(A:B),LEFT(A1:A10,5))") ' Run code in Debug, F8 mode, stop at End Sub. - Highlight either Arr -  Hit Shift+F9 -  OK   You will see in Watch window that Arr is a (1 to 10, 1 to 2) array. The "row" values are duplicated across the "columns"
    End Sub
    https://usefulgyaan.wordpress.com/20...e/#comment-739
    https://www.mrexcel.com/forum/excel-...ml#post4375354

    _...................................

    That is all not a perfect answer, as it does not explain all situations*** such as The T(If(1,____)) stuff _...
    https://excelxor.com/2014/09/05/inde.../#comment-2514
    _... which recently gave me more hours of frustration than any of your one liners, which I am happy to say I think I mostly understand now.
    It was healthy frustration, in that I learned much from you.

    Alan
    Attached Files Attached Files
    Last edited by DocAElstein; 04-14-2017 at 02:59 AM.
    Google first, like this site:ExcelFox.com "Short Title or Theme of wot youre looking for"
    Use Code Tags: Highlight code; click on the # icon above,
    Post screenshots COPYABLE to a Spredsheet; NOT IMAGES PLEASE:
    Tools for that:
    http://www.excelfox.com/forum/showth...=9821#post9821
    https://app.box.com/s/gjpa8mk8ko4vkwcke3ig2w8z2wkfvrtv
    http://excelmatters.com/excel-forums/

Posting Permissions

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