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 finf´ding 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
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...
:rolleyes:
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
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
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
1 Attachment(s)
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
VBA Application.InputBox Option HelpFile:= HelpContextID:= (VBA.InputBox helpfile:= , context:=)
This is a solution ( from me ) to this excelforum Thread :
https://www.excelforum.com/excel-new...contextid.html
It is also required in support of this excelfox Thread :
http://www.excelfox.com/forum/showth...utBox-Function
Original Question
Quote:
Originally Posted by
Doc.AElstein
VBA Application.InputBox Option HelpFile:= HelpContextID:=
Hi,
. I am trying to learn and practice with the .InputBox just now.
. I am getting there with most aspects. But I am struggling to find good explanations of, or rather, examples of actually using the two optional arguments Helpfile and HelpContextID
. Can anyone give me or point me in the direction of a working example / examples using specifically these options, with a brief explanation of what is going on
Thanks
Alan
_
A Bump on the question:
Quote:
Originally Posted by
Doc.AElstein
Bump
(
on getting these optional things, HelpFile:= _ , HelpContextID:= , to do what ever it is they are supposed to )
Hi
I am getting clued up on the simpler “Pop up User Info getting / giving Window” stuff currently.
I would like to consider them all at their full potential for a better comparison.
So I could do with having an answer to the question that is the main subject of this Thread.
It is a couple of years along the way now – I had just started with computers back then.
I think I may have a better chance now of understanding the Link given to me in post #2
https://www.excelguru.ca/content.php...r-Applications
It is clearly not straight forward so I had no chance back then of having the slightest idea what that was all about. At first glance I think I might at least be able to make a start now…
I can’t find any other articles or documentation on this, -
If anyone has any input on this, for example a full working example that they are able to walk me through using, then it could probably save me a lot of time.
Otherwise I will see you here again, in a couple of weeks probably, … when I have sussed it out myself.. :)
Thanks
Alan
P.s.
By the way…. I think I will try initially to look at the HelpFile:= _ , HelpContextID:= _ in conjunction with the InputBox Function ( https://msdn.microsoft.com/en-us/vba...utbox-function ) in preference to the Excel Application.InputBox Method ( https://msdn.microsoft.com/en-us/vba...x-method-excel ) because
_a) I am aware now that the Excel Application.InputBox Method has bugs
( The only difference in the working is that the Excel Application.InputBox Method has an extra last optional argument, Type:= . (One “advantage” of this being that for giving Type:=8 will return an object, which will be the range object of the cells either typed in (as an address), or alternatively selected)……I expect this extra complication is what probably screws things up.. )
_b) I believe the InputBox Function is possibly more fundamental, and I am hoping possibly to further my investigations into a ““more direct API route” with this ( https://www.excelforum.com/excel-pro...rary-list.html ) , although it is not clear currently if there is a “more direct API route” for this yet. )
I have also learned and partially myself perfected using string references for referring to range objects as an alternative, and this trend I will be perusing and I expect it to become possibly more trendy to do that. ( It certainly makes code a lot ‘prettier )
Ref
https://stackoverflow.com/questions/...38078#48338078
http://www.mrexcel.com/forum/excel-q...ox-method.html
http://www.vbforums.com/showthread.p...3-but-not-2007
_.______________________________________---
Last answer before final solution
Quote:
Originally Posted by
LJMetzger
You obviously did not drink the Microsoft 'Kool Aid', because its' not a bug, its a feature.
Reply _........
Quote:
Originally Posted by
Doc.AElstein
Lol.. I really should have thought of that “Feature” idea. I keep trying tell people about the special Forum Software “Features” that excelforum has.
I guess you only get this free drug
https://imgur.com/gallery/GjZgOM1 if you are a professional programmer looking to buy some software. I don’t know why but it reminds me of Cresta ( Its Frothy Man
https://www.youtube.com/watch?v=tvo2Hddqg3I ), although as I recall the Cresta that I was hooked on as a Kid was pink in color ). Maybe that has some similar secret hidden ingredient to make you like the providers stuff…
I will have to battle through and get the things to the stand that I conceive without a drugged up mind ( well not on Microsoft’s drug anyway ) as “working”. …. Well, … after a fashion …. I think maybe I have in this case … at least working enough to make the rational decision not to use it…
_._____________________
_..........with Final answer in next two posts: