Appendix-Thread-Evaluate-Range-(-Codes-for-other-Threads-HTML-Tables-etc-)

[DocAElstein]

___Function MainUDF(__) ' UDF to be used in a worksheet cell like__ = MainUDF(ArgX)
____ ' any conventional UDF coding
____ '
_______ Evaluate__ "SomeOtherProcedureToDoAnythingAnywhere(ArgY, __)"
____ '
____ ' any conventional UDF coding
____ '
___End Function

___Sub SomeOtherProcedureToDoAnythingAnywhere (__) ' ____ ' Any conventional procedure
____ ' Any conventional procedure coding
____ ' coding to change other cell values
____ '
____ ' Any conventional procedure coding
___End Sub

---

What’s going on? Here is my take on it, - how I got the slightly modified version of Rick’s discovery

Back to the start of Rick’s Post : Rick reported a report, which was a report of the discovery that hovering over a hyperlink in a cell ( where the first argument of the hyperlink function was a Function ) caused the firing of the Function
Like this: Put this in a normal module

Function SayHello()  '  '  In any cell type something of this form  ( You will need to change the path to suit where you have yopur workbook )    =HYPERLINK('F:\Excel0202015Jan2016\ExcelFox\BlogsBugs\BugsNovelShortTips\UDFinCellChangeOtherCells\UDF to modify other cells.xls'!Module1.SayHello(),"Hover over this cell to say Hello")
' Stop '     This  Stop  will only work if you navigate to the cell using arrow keys, and then hit  Enter
 MsgBox prompt:="Hello"
End Function

Now type _ =HYPERLINK(SayHello()) _ in a cell, and then (after hitting Enter) hover over the cell with the mouse
http://i.imgur.com/1zydn98.jpg
http://i.imgur.com/5hlZEbU.jpg
That message box should pop up


Two things struck me as strange about that
_ (i) That its happening, that is to say that hovering over the cell causes the firing of the function
_ (ii) The hyperlink function is accepting a procedure name rather than the string reference to where the hyperlink "goes"

The second, _ (ii) , exposed a few memory locations in my brain releasing some thoughts and previous observations of mine…
I have frequently seen that “exposing an interface”, ( “exposing an interface” : a technical term I have learnt is often applied to physically writing, or using, a full string reference to some object or function ), makes it do the main thing it is designed to "do". “Exposing an interface" in Visual Basic triggering functions allowing us to write some beautiful flowing single line codes. I think this is somehow fundamental to how excel works. There is something very fundamental to the workings of Excel whereby a full reference to a procedure or function will somehow be like "Calling" it into action. I get the feeling that the most work and fundamental writing of Excel was done at the beginning, and apparent advancements like the move from the original Excel 4 macros to VBA is somewhat of a cover up, or wrap up. People a lot smarter than me in computing tell me that Excel VBA is not really Object Oriented Programming Visual Basic, but rather an attempt after Excel version 4 to make it look and work as if it was.
The original Excel 4 macros worked by listing the commands. We can still do that, listing them in a Macro 4 worksheet ( For example from a new excel version worksheet hit Ctrl+F11 to see what happens ! )
We also have the availability of them Excel 4 macro commands via fiddling the using of the named range dialogue box such as to put the macro 4 command in place of where the “applies to” range would be. Then when we reference such a named range from a worksheet, the Excel 4 macro is fired.
Its my belief that functions and procedures, ( which could loosely all be called commands , where the actual command is the function or procedure name ), get “put somewhere” and can be got at in different ways…. This leads me on to the next couple of observations

Some of the other observations I made
_a) Fully referencing.
This was less of a surprise to me and is a direct consequence of my last few ramblings.
I can replace the simple procedure reference to a full macro Run syntax like
=HYPERLINK('F:\AFolderOfMine\AnotherFolderOfMine\TheFolderWit hTheFileInIt\UDF to modify other cells.xls'!Module1.SayHello())
( Don’t be surprised if you try that, and Excel reduces it to something of the form
=HYPERLINK('UDF to modify other cells.xls'!Module1.SayHello())
- Excel has a general habit of reducing the full reference to a workbook when that reference is used in the opened workbook of that which the reference is used in )
( You can check the tricky syntax of such procedure run calls, including the argument versions here : https://stackoverflow.com/questions/...12342#59812342 )
_b) Strange non Stoping : Dependency tree and decoupling from the worksheet of a run macro.
You will find that you can fire our simple macro both by the passing over the cell with the mouse, as already discussed, but in addition by using the arrow keys to navigate to the cell, then clicking in the formula bar and hitting Enter. That in itself is no surprise. This latter way, using the Enter key triggers the worksheet re calculation, according to various processes governed by things that come under the Heading of Dependency trees.
What might come as a surprise is the following…. Modify the simple macro to include the Stop and/ or click in the margin to make some brown circle stop points
http://i.imgur.com/xc1QiW4.jpg
The latter way, using the Enter , will get you Stoped in the VB Editor. That is still no surprise.
But hover over the cell with the mouse, .. and you won’t get Stopped!
That was less of a surprise for me, but did help me think that the consideration of the Hyperlink function directly is slightly off course, at least to my way of thinking ….._

Dependency tree and decoupling from the worksheet of a run macro.
_....One of my little theories, already touched on briefly, is that procedures generally are held somewhere and how and exactly , ( to the split second ), when they are run is not always the same. ( I used these thoughts before to discover that another apparently impossible thing could be done, - Passing arguments ByRef using Application Run- https://web.archive.org/web/20190816...comment-205853 )
We mentioned "exposing an interface" triggers functions allowing us to write some beautiful flowing single line codes. To prevent a possible chaos caused by this, Excel has a dependency tree which informs Excel about which cells depend on which others, or equivalently, which cells are precedents for which others. From this tree, Excel constructs a calculation chain. ( Array formulas use this extensively, or rather, the possibility to have them sets the simplest basic structure, and the so called CSE Entry is a way to exclude cells from processes that require a more detailed dependency tree. )..
By examining dependency trees we can find ways to force a reconstruction of the calculation chain, “after the event” , as it were. There are many ways to do this, probably most of them requiring a convoluted workaround like passing memory locations and copying memory blocks.
Simple ways can often be found. For Application Run , Late Binding is a simple workaround.
Although the Hyperlink considerations have slightly distracted me, Rick’s post has shown me something and partially reminded me that Evaluate has more than the ability to process the text string equivalent of a worksheet formula.
It’s rare that Microsoft documentation is on its own helpful, but occasionally there documentation may still contain some snippets, or clues. Form parts of Microsoft documentation on the Evaluate… .."Processes … using the naming convention of Microsoft Excel . " …….
Ricks interest in Evaluate infected me with it a bit, but I only ever used it to return something.
Rick’s post introduced me to the way of using it like
______ Evaluate__ "Process….. using the naming convention of Microsoft Excel "
Its another one of my little theories that using Evaluate will force a reconstruction of the calculation chain removing dependencies on a worksheet, or at least leading them to be in a form of later recalculation. I think this is what we are looking for:
The snag often regarded as leading to the claim that a UDF can only change values in the cell that it is in, is a direct consequence of the way an Excel spreadsheet is updated in the simplest case. In Layman’s terms, “you can’t lift up a rake when your standing on it….”: To aid efficiency a simple UDF is in the simplest calculation change: things are done in something which can approximately regarded as a simple sequential way , ( we may experience it as along columns then down rows ). That simple dependency chain screws us up in trying to change other cell values.
We have already discussed that evaluating a name is a fundamental part at low level of triggering a run. A good thing to try to "remove an awkward worksheet dependence" would be maybe …
__ Evaluate "YouNameIt"

I tried…
Bingo – It works!

Practical example in the next post.

( after those last "Hello" experiments, it might be best to remove or ' comment out that function, as it might interfere with some of the next experiments
Indeed, in general it is a good idea to remove or ' comment out any used test procedures before moving on to other procedures , as occasionally when debugging and testing functions, UDFs can sometimes be fired off erratically or by accident )

Conclusions. What’s going on
I am not sure yet. I only have constant and stable success with changing values. Values are a more fundamental things and are accessible also from closed workbooks using similar reference strings to some discussed here, but the significance of this I am not yet fully clear on. There are parallels to some of my findings with Running of macros and Excel 4 macro commands.
I think it needs a lot of detailed research, including cross referencing in different Excel versions to do this topic full justice.
I will come back to this Thread probably and either edit or post more replies as time goes on.

Ref
https://www.myonlinetraininghub.com/...acro-functions
https://docs.microsoft.com/en-us/off...-recalculation
https://www.excelforum.com/excel-pro...ml#post5265903
https://www.excelforum.com/excel-cha...ml#post4343285
https://excelfox.com/forum/showthrea...ned-Functions)




https://superuser.com/questions/6022...-in-a-differen
https://stackoverflow.com/questions/...ult-in-another

[DocAElstein]

This is required for the single Main routine which is posted in two parts in the last two posts


[Code]'2 Alan http://www.excelforum.com/showthread.php?t=1148621&page=3#post4441761
'5 Leith Ross http://www.mrexcel.com/forum/excel-questions/367030-copy-table-website-into-excel-visual-basic-applications-2.html#post4031122
'10 '....' "This is a recursive procedure to extract text from between an element's start tag and end tag and everything in between. Usually the Calling program will have passed a HTML code ( either from, for example, a .HTML File, a .Tex File, a .txt file, or from a returned such file after a request to a web page) into a Document Object Model. ( DOM ). This somehow organises things in a tree type structure , approximately as like you might see if you carefully indented the HTML code yourself, such that tag pairs were clear to see within tag pairs, each level down as it were. ( a "next level down" is often referred to as a "Child" ). The exact structure is less obvious, but in any case the DOM will have some ordered structure and every constitute part of the code is referred to as an Element. In a simple case most Elements have a start and stop pointed bracket. They are all nodes. Text is usually squeezed in between somewhere within a paired tag set, but is also referred to as a node.
'12 'I think a node is a point, usually a junction point in the tree type structure. Usually before the procedure is run a first time, an Element will have been obtained from the DOM and this is to be passed in the signature line of the procedure, as an Object. VBA then makes a Copy of the procedure and runs that with the given Element.
'15 'The macro will examine this Element Object brought in for a Text Node: If the element .NodeType is not 3 (a text node) then there are possibly child nodes ( Nodes "next down" in a Tree type listing ) that need to examined. The procedure then "Calls itself". In other words the first Copy stops at the Call Point. At the Call point another Copy of the procedure is made and runs in a loop for each child node.
'20 'The next Copy of the macro will again examine the element for a Text Node. If found (If element node type is 3), the text is concatenated with the ElemText String. If this is the ElemText string is empty then ElemText is set to this value. If not then this value is concatenated with any previous text and separated by a tilde character. This character can be used later to parse the text string into the individual strings from each element. The macro will exit the Sub at this point. When this happens, this copy of the macro is "removed from the call stack", in other words it Ends, and the last Copy continues from the Call point at which it was stopped.
Public Sub GetElemsText(ByRef Elem As Object, ByRef ElemText As String) 'It takes an Object, (variable Elem), a HTML Element, or a ( child ) node thereof. (Wiki says "An HTML element is an individual component of an HTML document or web page, once this has been parsed into the Document Object Model. (DOM). HTML is composed of a tree of HTML Elements and other nodes, such as text nodes." May be close to but not excactly what you se by carefully indenting down "Child" levels
'25 Dim strobjElem As String: Let strobjElem = TypeName(Elem)' http://www.excelforum.com/excel-programming-vba-macros/1149427-vba-determine-object-type-from-html-dom-object-put-type-in-string-variable-as-shown-in.html
65 Rem 1) Do we have an Element
70 If Elem Is Nothing Then GoTo LEndSub [color=darkgreen]'If the Object Elem is empty, or rather we are not given one, Then we End

[DocAElstein]

Post 3 of 3 ... 4 ... 5 etc

Here are some simple working examples. I will probably edit and add to them as time goes on. So far I have tried all these macros and get similar results in Excel 2002 2003 2007 2010 2013 in operating systems of XP Vista Windows 7 Windows 10
I will come back to this Thread probably and either edit or post more replies as time goes on.
Any feedback in particular for results in newer Excel versions would be very welcome

(Note: In general it is a good idea to remove or ' comment out any test procedures before moving on to test other procedures, as occasionally when debugging and testing functions, UDFs can sometimes be fired off erratically or by accident )

If you are familiar with Ricks ways of doing this, then basically I am using his solutions but just not using the hyperlink. There’s not much more to it then that. I came there from a long winded way, explained in post #1, but the key to it working is based on, or using, the way Rick used the Evaluate within the coding for the UDF that is in the worksheet

___________ Evaluate "SomeOtherProcedureToDoAnythingAnywhere(Arg _ , _ )"

[DocAElstein]

Post to support this Thread:
http://www.excelfox.com/forum/showth...0888#post10888
_1) This part of Rick’s solution
Evaluate(Replace(Replace("IF(ISNUMBER(0+SUBSTITUTE(SUBSTITUTE(A2:A#,"" "",""""),"","","""")),IF(LEFT(A1:A@,4)=""2018"",TRIM(A1:A@&"" ""&A2:A#),""""),IF(LEFT(A1:A@,4)=""2018"",A1:A@,""""))", "#", LastRow + 1), "@", LastRow))

I have seen something similar to this before, but it is lost to mankind hidden down in the comment section of a Blog site, Allen Wyatt’s I think…… so its nice that something like this has seen the light of day here…

If I am not mistaken, this non-looping macro should also work...

Sub ThisShouldWork()
 Dim LastRow As Long
 LastRow = Cells(Rows.Count, "A").End(xlUp).Row
 Range("A1:A" & LastRow) = Evaluate(Replace(Replace("IF(ISNUMBER(0+SUBSTITUTE(SUBSTITUTE(A2:A#,"" "",""""),"","","""")),IF(LEFT(A1:A@,4)=""2018"",TRIM(A1:A@&"" ""&A2:A#),""""),IF(LEFT(A1:A@,4)=""2018"",A1:A@,""""))", "#", LastRow + 1), "@", LastRow))
 Range("A1:A" & LastRow).SpecialCells(xlBlanks).EntireRow.Delete
End Sub

To help simplify the explanation, lets take it that we know our range , ( http://www.excelfox.com/forum/showth...-row#post10870 ) so we have LastRow = 40
Two arbitrarily chosen characters, @ and # , are being used to enter into the main formula the LastRow or LastRow +1
Pseudo like we are doing this sort of thing
Replace( “A#” , “#” , “40” ) in order to end up with like “A40”
By inspection of the main formula, and with a bit of eye straining you can probably see where you replace those @ and # with 40 and 41
Just to be sure , running this will get you a nice copy able version of the main formula in the immediate window , ( after running you Hit Ctrl+g from the VB Editor to get the immediate window up):

Sub ThisShouldWork()
Dim LastRow As Long, strEval As String
 Let LastRow = Cells(Rows.Count, "A").End(xlUp).Row
 Let strEval = Replace(Replace("IF(ISNUMBER(0+SUBSTITUTE(SUBSTITUTE(A2:A#,"" "",""""),"","","""")),IF(LEFT(A1:A@,4)=""2018"",TRIM(A1:A@&"" ""&A2:A#),""""),IF(LEFT(A1:A@,4)=""2018"",A1:A@,""""))", "#", LastRow + 1), "@", LastRow)
 'Range("B1:B" & LastRow).FormulaArray = "=" & strEval
 Debug.Print strEval  'IF(ISNUMBER(0+SUBSTITUTE(SUBSTITUTE(A2:A41," ",""),",","")),IF(LEFT(A1:A40,4)="2018",TRIM(A1:A40&" "&A2:A41),""),IF(LEFT(A1:A40,4)="2018",A1:A40,""))

That did work.JPG : https://imgur.com/01sQ91X

_._______________________-
Before moving on a useful note: It is always useful when developing these formulas to view the string in the Immediate window: That can help with tricky syntaxes : The formula seen on the Immediate window must look like a formula in the same syntax as you would manually type it into a cell. So you can see immediately if you get something wrong , such as an error in the finally seen quotes.
_.__________________________
So we have our final formula:
IF(ISNUMBER(0+SUBSTITUTE(SUBSTITUTE(A2:A41," ",""),",","")),IF(LEFT(A1:A40,4)="2018",TRIM(A1:A40&" "&A2:A41),""),IF(LEFT(A1:A40,4)="2018",A1:A40,""))
The way these formulas appear to work within the Evaluate(“ “) appears to be tapping into an along the columns , down a row, then along the columns… type updating raster to update a worksheet. The available output then seems to be that which encompasses the deepest and widest ranges. It is a ,little bit more complicated than that ( http://www.excelfox.com/forum/showth...on-and-VLookUp ) , but for our formula we have nice regular equally sized ranges so we are expecting an output of 1 “wide” and 40 “deep”. So for analysis purposes, we can reduce the formula to 40 similar ones.

Lets take the example of the formula for the 13th “down” output ..
IF(ISNUMBER(0+SUBSTITUTE(SUBSTITUTE(A14," ",""),",","")),IF(LEFT(A13,4)="2018",TRIM(A13&" "&A14),""),IF(LEFT(A13,4)="2018",A13,""))
Clearly we need to look at this data to see what that formula will do, because this data is used in that formula
_____ Workbook: NormanOrrinRickFilter.xlsm ( Using Excel 2007 32 bit )

Row\Col	A
13	2018, 1, 90709, 10020009, P4, Blabla og Blalala (NY), CHE, Blabla,Bla-ah,
14	10006098, 15392.64

Worksheet: Rick

We have some nested IFs , and I find it is always a good idea to break those down so that we can start doing them as Excel or VBA would do them, that is to say from the middle working outwards. I tend to do this in a text editor with a horizontal scroll bar, or in the VB Editor window
Formula in VB Editor as comment.JPG : https://imgur.com/3cjyqSR

So this is what we have, broken down into the constituent IF sections.
( It may be better to copy this and view in your VB Editor in a wide window. I am working from the bottom , upwards )

' IF( ISNUMBER(0+SUBSTITUTE(SUBSTITUTE(A14," ",""),",","")), IF(LEFT(A13,4)="2018" , TRIM(A13" "&A14) , "") , IF( LEFT(A13,4)="2018" , A13 ,"" ) ) 
' IF( ISNUMBER(0+SUBSTITUTE(SUBSTITUTE(A14," ",""),",","")),IF(LEFT(A13,4)="2018",TRIM(A13" "&A14),"") , IF(LEFT(A13,4)="2018",A13,"") )
' IF(ISNUMBER(0+SUBSTITUTE(SUBSTITUTE(A14," ",""),",","")),IF(LEFT(A13,4)="2018",TRIM(A13" "&A14),""),IF(LEFT(A13,4)="2018",A13,""))

Examining the first line , I can evaluate the two innermost IFs and reduce the formula to

' IF( ISNUMBER(0+SUBSTITUTE(SUBSTITUTE(A14," ",""),",","")), TRIM(A13" "&A14) , A13 ) 
' IF( ISNUMBER(0+SUBSTITUTE(SUBSTITUTE(A14," ",""),",","")), IF(LEFT(A13,4)="2018" , TRIM(A13" "&A14) , "") , IF( LEFT(A13,4)="2018" , A13 ,"" ) ) 

I will now evaluate some of those SUBSTITUTEs
( Excel Substitute, seems to work similarly to VBA Replace )

' IF( ISNUMBER(0+1000609815392.64), TRIM(A13" "&A14) , A13 ) 
' IF( ISNUMBER(0+SUBSTITUTE(10006098,15392.64),",","")), TRIM(A13" "&A14) , A13 ) 

( I am guessing that 0+ will ensure that a number will not be mistaken as a text )

For the case of the 13th “down” formula the final steps in the evaluation go as follows

' 2018, 1, 90709, 10020009, P4, Blabla og Blalala (NY), CHE, Blabla,Bla-ah, 10006098, 15392.64
' TRIM(A13" "&A14)

' IF( True , TRIM(A13" "&A14) , A13 )

Here are all the steps together again

' 2018, 1, 90709, 10020009, P4, Blabla og Blalala (NY), CHE, Blabla,Bla-ah, 10006098, 15392.64
' TRIM(A13" "&A14)

' IF( True , TRIM(A13" "&A14) , A13 ) 


' IF( ISNUMBER(0+1000609815392.64), TRIM(A13" "&A14) , A13 ) 
' IF( ISNUMBER(0+SUBSTITUTE(10006098,15392.64),",","")), TRIM(A13" "&A14) , A13 ) 

' IF( ISNUMBER(0+SUBSTITUTE(SUBSTITUTE(A14," ",""),",","")), TRIM(A13" "&A14) , A13 ) 
' IF( ISNUMBER(0+SUBSTITUTE(SUBSTITUTE(A14," ",""),",","")), IF(LEFT(A13,4)="2018" , TRIM(A13" "&A14) , "") , IF( LEFT(A13,4)="2018" , A13 ,"" ) ) 


' IF( ISNUMBER(0+SUBSTITUTE(SUBSTITUTE(A14," ",""),",","")), IF(LEFT(A13,4)="2018" , TRIM(A13" "&A14) , "") , IF( LEFT(A13,4)="2018" , A13 ,"" ) ) 
' IF( ISNUMBER(0+SUBSTITUTE(SUBSTITUTE(A14," ",""),",","")),IF(LEFT(A13,4)="2018",TRIM(A13" "&A14),"") , IF(LEFT(A13,4)="2018",A13,"") )
' IF(ISNUMBER(0+SUBSTITUTE(SUBSTITUTE(A14," ",""),",","")),IF(LEFT(A13,4)="2018",TRIM(A13" "&A14),""),IF(LEFT(A13,4)="2018",A13,""))

The final result will appear in the 13th down position of the 40 “deep” array final results for the entire formula evaluation.
If you can view that last summary on a wide window, it should be able to see how the differing results for the other 39 results are achieved from the formula
Just to make clear once again what seems to go on in these sort of Evaluate formulas, in the next post is a table showing the actual Evaluateions done by VBA

_._____

_2 The final part of Rick’s solution is
Range("A1:A" & LastRow).SpecialCells(xlBlanks).EntireRow.Delete
This uses the VBA SpecialCells Method to get at the cells with nothing in them. Those are then deleted
Explanation:
VBA SpecialCells Method ( https://www.mrexcel.com/forum/excel-...onditions.html , https://docs.microsoft.com/en-us/off...e.specialcells ) returns you a range object ( that range object must not be contiguous ( connected ) cells ) consisting of those cells meeting a specific characteristic. We can choose from a number of characteristics. Here we choose xlBlanks , which refers to the characteristic of the cell being empty. So, if we applied that .SpecialCells(xlBlanks) to this range:.._

Row\Col	B
9
10
11	2018, 1, 90515, 10024515, G9, SBlabla (HQ), CHE, BLABLA, blabla, 10012098, 12003.5
12	2018, 1, 90629, 10022334, P3, BLABLA blabla (blablabla), CHE, BLABLA,blabla, 10033609, 13941.72
13	2018, 1, 90709, 10020009, P4, Blabla og Blalala (NY), CHE, Blabla,Bla-ah, 10006098, 15392.64
14
15	2018, 1, 90765, 10012123, P4, Ch of Blabla(Blabla of Blabla), CHE, BLA-BLA,Bla Blabla, 10005678, 16231.7

_ … then the returned range from that would be Range(“B9:B10,B14”).
If we then apply .Delete to that range then those cells are removed. If you remove a cell via .Delete then initially there is a real hole, like a “black hole” that can’t really exist in a spreadsheet. So Excel might explode or implode, or you would be sucked into that hole , never to return!!! To prevent that happening, Excel shifts all cells to close that hole, ( and adds a new virgin cell at the bottom or right side to fill the indent there caused by the shift. The default Delete option for the direction of that shift is in our case upwards. Hence after applying the .Delete after applying .SpecialCells(xlBlanks) to the above range, ( pseudo like doing something this Range(“B9:B10,B14”).Delete(Shift:=xlUp) ) we will be left with

Row\Col	B
9	2018, 1, 90515, 10024515, G9, SBlabla (HQ), CHE, BLABLA, blabla, 10012098, 12003.5
10	2018, 1, 90629, 10022334, P3, BLABLA blabla (blablabla), CHE, BLABLA,blabla, 10033609, 13941.72
11	2018, 1, 90709, 10020009, P4, Blabla og Blalala (NY), CHE, Blabla,Bla-ah, 10006098, 15392.64
12	2018, 1, 90765, 10012123, P4, Ch of Blabla(Blabla of Blabla), CHE, BLA-BLA,Bla Blabla, 10005678, 16231.7
13
14

What has happened there is the following: Those empty cells ( which were yellow ) have been removed. Other cells have been shifted up to fill up the “holes” created by the removal
( Rick’s code line actually deletes the EntireRow of that row on which the empty cells are found )

_.______________________________________________

Just to make clear once again what seems to go on in these sort of Evaluate formulas, in the next post is a table showing the actual Evaluateions done by VBA

[DocAElstein]

Simplest Examples: using a UDF to change the values of other cells
Doing a P in a few cells, the number determined by some cell value.
Consider that a number in some arbitrary cell , say A2 , should determine how many cells in column C get a P in them.
In addition , cell D2 should be a message telling you all about it. This latter requirement would be a normal simple use of either a simple formula or a very simple UDF formula in the cell D2
But the requirement of putting a certain number of Ps down column C would normally be regarded as one requiring
_ a complicated array formula, and that formula would need to extend as far down as the likely maximum wanted number of Ps
or
_ the use of VBA, possibly event coding,
or
_ human interaction to manually do a P in some cells.
_
But we can do it with the simple UDF formula which we would use in the cell D2 , if that UDF also uses an
_ Evaluate "ProcedureToPutThePsIn"
code line in it which will set off the procedure with the name _ ProcedureToPutThePsIn
ProcedureToPutThePsIn can be any normal procedure, ( with a few restrictions. )
_
Solution:
Paste these two codes into a normal code module.

'   Paste these two codes into a normal code module.  The type in cell D2  =PInCells(A2)  then hit  Enter.   If you now type a number into cell  A2  then  both the procedures above will excecute
Function PInCells(ByVal Rng As Range) As String         '   The function can be thought of as a "variable" that gets filled with a string ( below in the last line** ) , so it needs to be of string type
Dim Nmbr As Long: Let Nmbr = Rng.Value                  '   A typical bit of coding in the UDF
 Evaluate "PutInCells(" & Nmbr & ")"                      '   This is the  special   Evaluate "ProcedureToPutThePsIn"   piece which helps us contradict the falsly held belief thatz
 Let PInCells = "You did " & Nmbr & " Ps in column C" '   A typical piece of coding often towards the end in a UDF giving the function "variable" the return values **
End Function
Sub PutInCells(ByVal Nbr As Long)    '  This is a simple normal procedure. It takes in a number which it then uses to determing the size of the range to apply a single value to
Stop ' This won't work. it will be ignored
ActiveSheet.Range("C1:C20").ClearContents   ' this wont work. it will be ignored
 Let ActiveSheet.Range("C1:C20").Value = "" ' this is a workaround to achieve what the last line would normally do
 Let ActiveSheet.Range("C1:C" & Nbr & "").Value = "P"
End Sub

Now type in cell D2 =PInCells(A2) and then hit Enter.

Row\Col	C	D	E
1
2		=PInCells(A2)
3

If you now type a number into cell A2 then both the procedures above will execute. For example typing 2 in cell A2 you will get

Row\Col	A	B	C	D	E
1			P
2	2		P	You did 2 Ps in column C
3

Rick’s example
The main difference, ( and the only significant difference in my version of Rick’s coding ) is that here there is no use of the Excel Hyperlink function
Put these macros in a normal code module

'   Put these macros in a normal code module.  Now put this UDF calling formula, =DoCool(B3) , in a cell, say, C3…  The procedure, TooCool(arg ,   ) , is the one used in the  Evaluate "Procedure to do stuff to other cells"   code line. That procedure gives us the output in , that is to say changes the cell value of , J3
' The second part of the function below is conventional UDF stuff.  The first part is the bit what lets us change values in cells other than that in which our UDF is placed in the worksheet
Function DoCool(ByVal Rng As Range) As String
 Rem 1 ' The next code line(s) is the    Evaluate "SomeOtherProcedureToDoAnythingAnywhere(Arg _ , _ )"   code line
' Evaluate "'" & ThisWorkbook.Path & "\UDF to modify other cells.xls'!Module1.TooCool(" & Rng.Address & ",J3)
'' or
' Evaluate "'UDF to modify other cells.xls'!Module1.TooCool(" & Rng.Address & ",J3)"
'' or
' Evaluate "Module1.TooCool(" & Rng.Address & ",J3)"
'' or
 Evaluate "TooCool(" & Rng.Address & ",J3)"
'_---------------------------------------------------------------------
Rem 2 ' Conventional UDF coding below
  If Rng.Value < 0 Then
   Let DoCool = "Number in " & Rng.Address(RowAbsolute:=False, ColumnAbsolute:=False) & " is less than zero."
  Else
   Let DoCool = "Number in " & Rng.Address(RowAbsolute:=False, ColumnAbsolute:=False) & " is greater than, or equal to, zero."
  End If
End[ Function
'
' The procedure below is a conventional procedure to put something in a cell. 
Sub TooCool(ByVal InCell As Range, ByVal PushTo As Range)
 Let PushTo.Value = "The square of " & InCell.Value & " (in " & InCell.Address(RowAbsolute:=False, ColumnAbsolute:=False) & ") is " & InCell.Value ^ 2 & "."
End Sub

Now put this UDF calling formula, =DoCool(B3) , in a cell, say, C3 …
The procedure, TooCool(arg , _ ) , is the one used in the
_ Evaluate "Procedure to do stuff to other cells"
code line. That procedure gives us the output in , that is to say changes the cell value of , J3

Row\Col	B	C	D
2
3		=DoCool(B3)
4

Row\Col	B	C	D	E	F	G	H	I	J	K
2
3		Number in B3 is greater than, or equal to, zero.							The square of (in B3) is 0.
4

Row\Col	B	C	D	E	F	G	H	I	J	K
2
3	-2	Number in B3 is less than zero.							The square of -2 (in B3) is 4.
4

[DocAElstein]

Continued from last post

In a range evaluate type code line like the one we are considering, Excel VBA seems to do the following ( simplified ) ( refs *** )

Excel will have an output "window" ( this could be considered as an output table or output array ). The dimensions of this will be that rectangle that allows all used ranges in the formula to be fitted in,
There are some complicated ways in which Excel handles the situation of ranges of varying size, ( http://www.excelfox.com/forum/showth...on-and-VLookUp ) but for a simpler case of all ranges having the same size, ( in terms of "width" and "depth" ) , as we have, Excel VBA will "expand" its "output window" to this sort of thing:

Excel VBA will do its normal "along the columns, down a row, along the columns…" type thing, in any "Evaluation run". In our case this will mean that it does an evaluation at each row, going down the rows. This is what Excel VBA does in order to fill that last window of cells, ( I am just showing the first 7 of 40 similar formulas as the full list is to big to fit in a forum post )

=IF(ISNUMBER(0+SUBSTITUTE(SUBSTITUTE(A2," ",""),",","")),IF(LEFT(A1,4)="2018",TRIM(A1&" "&A2),""),IF(LEFT(A1,4)="2018",A1,""))

=IF(ISNUMBER(0+SUBSTITUTE(SUBSTITUTE(A3," ",""),",","")),IF(LEFT(A2,4)="2018",TRIM(A2&" "&A3),""),IF(LEFT(A2,4)="2018",A2,""))

=IF(ISNUMBER(0+SUBSTITUTE(SUBSTITUTE(A4," ",""),",","")),IF(LEFT(A3,4)="2018",TRIM(A3&" "&A4),""),IF(LEFT(A3,4)="2018",A3,""))

=IF(ISNUMBER(0+SUBSTITUTE(SUBSTITUTE(A5," ",""),",","")),IF(LEFT(A4,4)="2018",TRIM(A4&" "&A5),""),IF(LEFT(A4,4)="2018",A4,""))

=IF(ISNUMBER(0+SUBSTITUTE(SUBSTITUTE(A6," ",""),",","")),IF(LEFT(A5,4)="2018",TRIM(A5&" "&A6),""),IF(LEFT(A5,4)="2018",A5,""))

=IF(ISNUMBER(0+SUBSTITUTE(SUBSTITUTE(A7," ",""),",","")),IF(LEFT(A6,4)="2018",TRIM(A6&" "&A7),""),IF(LEFT(A6,4)="2018",A6,""))

=IF(ISNUMBER(0+SUBSTITUTE(SUBSTITUTE(A8," ",""),",","")),IF(LEFT(A7,4)="2018",TRIM(A7&" "&A8),""),IF(LEFT(A7,4)="2018",A7,""))

Excel VBA will effectively make 40 formulas and place in the "output window" the result of the evaluation of those formulas
The full demo code in the next post includes a code line to put in all 40 formulas in an arbitrary 40 "deep" x 1 "wide" range ("J5:J44")






refs ***
http://www.excelfox.com/forum/showth...age3#post10201

[DocAElstein]

Some other notes for a Thread post , I forgot where, ignore for now....

Some other notes for a Thread post , I forgot where


Put this coding in a normal code module

Now type the following in any cell , ( other than cell (A2) ) , and hit enter

If this works for you, then in cell A2 you will get the text, Changed by UDF in cell A2


The following information may be difficult for you to understand, but possibly it is useful to include in this Thread for others…
What you are wanting to do is often regarded as impossible. For example, in post #2 , https://www.myonlinetraininghub.com/...unction#p18256 , Veloria, she did say…. you can't select another cell in a UDF called from a cell ….
More commonly we hear it said…. a UDF can only change values in the cell that it is in ….
That is not true, mostly.
The snag often regarded as leading to the claim that a UDF can only change values in the cell that it is in, is a direct consequence of the way an Excel spreadsheet is updated in the simplest case. In Layman’s terms, “you can’t lift up a rake when your standing on it….”: To aid efficiency a simple UDF is in the simplest calculation change: things are done in something which can approximately regarded as a simple sequential way , ( we may experience it as along columns then down rows ).
We know that **exposing an interface in Visual Basic triggers functions allowing us to write some beautiful flowing single line codes. To prevent a possible chaos caused by this, Excel has a dependency tree which informs Excel about which cells depend on which others, or equivalently, which cells are precedents for which others. From this tree, Excel constructs a calculation chain
By examining dependency trees we can find way to force a reconstruction of the calculation chain, “after the event” , as it were. There are many ways to do this, probably most of them requiring a convoluted workaround like passing memory locations and copying memory blocks.
A few people, Rick Rothstein, for example, have noticed that we can achieve the same very simply by exposing the UDF interface within a simple Hyperlink
=Hyperlink(MyUDF())
It really is as simple as that.

Exploring the working of that in more detail
**Calling macros is linked into range names , and names in general, as we see by one method used to run Excel 4 Macros: Named Ranges and string references path links are synonymous.
Although there is no documentation on this, it is a direct consequence that a function which requires a string reference will also take a name and result in exposing an interface. That’s what’s going on here.
**This is so fundamental to the working of Excel , and cannot be so easily changed, and explains for example, why we still have many novel solutions available to us via Excel 4 Macros. Its unlikely therefore to not work in newer versions, but as a last disclaimer, I should say I have not tested in newer versions.
But I can do, and have done , this consistently in my Excel 2002 to 2013 working on a number of different computers with operating systems from XP to Windows 10
This means that in general something of the following works, and we have no restrictions on our UDF changing values of any cells anywhere
=Hyperlink('C:Mypath\MyWorkbook.xls'!Module1. MyUDF())
Excel is good at guessing what we mean when we miss things out, and so we can , in the practice, reduce that to
=Hyperlink(MyUDF())

Ref
https://www.myonlinetraininghub.com/...acro-functions
https://docs.microsoft.com/en-us/off...-recalculation
https://www.excelforum.com/excel-pro...ml#post5265903
https://www.excelforum.com/excel-cha...ml#post4343285

_._____________________________-

Let me bring that all back to your specific example.
First you create this UDF

[DocAElstein]

Full demo code to accompany last post:

Option Explicit
Sub ThisShouldWork()
Dim LastRow As Long, strEval As String
 Let LastRow = Cells(Rows.Count, "A").End(xlUp).Row
 Let strEval = Replace(Replace("IF(ISNUMBER(0+SUBSTITUTE(SUBSTITUTE(A2:A#,"" "",""""),"","","""")),IF(LEFT(A1:A@,4)=""2018"",TRIM(A1:A@&"" ""&A2:A#),""""),IF(LEFT(A1:A@,4)=""2018"",A1:A@,""""))", "#", LastRow + 1), "@", LastRow)
 Debug.Print strEval ' Hit Ctrl+g from the VB Editor to get the Immediate window up.                                                                                              'IF(ISNUMBER(0+SUBSTITUTE(SUBSTITUTE(A2:A41," ",""),",","")),IF(LEFT(A1:A40,4)="2018",TRIM(A1:A40&" "&A2:A41),""),IF(LEFT(A1:A40,4)="2018",A1:A40,""))
'This is the spreadsheet equivalent to Rick's Evaluate
 Range("B1:B" & LastRow).FormulaArray = "=" & strEval
'This gives a demo of the actual formulas that Excel VBA does
 Range("J5:J44").Value = "=IF(ISNUMBER(0+SUBSTITUTE(SUBSTITUTE(A2,"" "",""""),"","","""")),IF(LEFT(A1,4)=""2018"",TRIM(A1&"" ""&A2),""""),IF(LEFT(A1,4)=""2018"",A1,""""))" ' Applying the fixed vector notation (Excel instructed to do that by no $s) will result in the same relative formula. Displayed will be the actual formula ( in the relative form, but that is not important)
  
' Final solution  Rick : http://www.excelfox.com/forum/showthread.php/2293-Move-values-in-rows-at-the-end-of-the-preceding-row?p=10888#post10888
  Range("A1:A" & LastRow) = Evaluate(Replace(Replace("IF(ISNUMBER(0+SUBSTITUTE(SUBSTITUTE(A2:A#,"" "",""""),"","","""")),IF(LEFT(A1:A@,4)=""2018"",TRIM(A1:A@&"" ""&A2:A#),""""),IF(LEFT(A1:A@,4)=""2018"",A1:A@,""""))", "#", LastRow + 1), "@", LastRow))
'  Range("A1:A" & LastRow).SpecialCells(xlBlanks).EntireRow.Delete ' This will mess up now due to my .FormulaArray  as you can't delete bits of that
End Sub



'          2018, 1, 90709, 10020009, P4, Blabla og Blalala (NY), CHE, Blabla,Bla-ah, 10006098, 15392.64
'                        TRIM(A13" "&A14)

'   IF(      True        ,   TRIM(A13" "&A14)        ,       A13       )


'   IF(       ISNUMBER(0+1000609815392.64),   TRIM(A13" "&A14)        ,       A13       )
'   IF(       ISNUMBER(0+SUBSTITUTE(10006098,15392.64),",","")),   TRIM(A13" "&A14)        ,       A13       )

'   IF(       ISNUMBER(0+SUBSTITUTE(SUBSTITUTE(A14," ",""),",","")),   TRIM(A13" "&A14)        ,       A13       )           )
'   IF(       ISNUMBER(0+SUBSTITUTE(SUBSTITUTE(A14," ",""),",","")), IF(LEFT(A13,4)="2018"  ,  TRIM(A13" "&A14)  ,  "")      ,     IF( LEFT(A13,4)="2018"  ,  A13  ,"" )     )


'   IF(       ISNUMBER(0+SUBSTITUTE(SUBSTITUTE(A14," ",""),",","")), IF(LEFT(A13,4)="2018"  ,  TRIM(A13" "&A14)  ,  "")      ,     IF( LEFT(A13,4)="2018"  ,  A13  ,"" )     )
'      IF(       ISNUMBER(0+SUBSTITUTE(SUBSTITUTE(A14," ",""),",","")),IF(LEFT(A13,4)="2018",TRIM(A13" "&A14),"")      ,     IF(LEFT(A13,4)="2018",A13,"")     )
'            IF(ISNUMBER(0+SUBSTITUTE(SUBSTITUTE(A14," ",""),",","")),IF(LEFT(A13,4)="2018",TRIM(A13" "&A14),""),IF(LEFT(A13,4)="2018",A13,""))

and here it is again ... in "Ricks Table Code Tags" ( http://www.excelfox.com/forum/showth...0902#post10902 )

Option Explicit
Sub ThisShouldWork()
Dim LastRow As Long, strEval As String
 Let LastRow = Cells(Rows.Count, "A").End(xlUp).Row
 Let strEval = Replace(Replace("IF(ISNUMBER(0+SUBSTITUTE(SUBSTITUTE(A2:A#,"" "",""""),"","","""")),IF(LEFT(A1:A@,4)=""2018"",TRIM(A1:A@&"" ""&A2:A#),""""),IF(LEFT(A1:A@,4)=""2018"",A1:A@,""""))", "#", LastRow + 1), "@", LastRow)
 Debug.Print strEval ' Hit Ctrl+g from the VB Editor to get the Immediate window up.                                                                                              'IF(ISNUMBER(0+SUBSTITUTE(SUBSTITUTE(A2:A41," ",""),",","")),IF(LEFT(A1:A40,4)="2018",TRIM(A1:A40&" "&A2:A41),""),IF(LEFT(A1:A40,4)="2018",A1:A40,""))
'This is the spreadsheet equivalent to Rick's Evaluate
 Range("B1:B" & LastRow).FormulaArray = "=" & strEval
'This gives a demo of the actual formulas that Excel VBA does
 Range("J5:J44").Value = "=IF(ISNUMBER(0+SUBSTITUTE(SUBSTITUTE(A2,"" "",""""),"","","""")),IF(LEFT(A1,4)=""2018"",TRIM(A1&"" ""&A2),""""),IF(LEFT(A1,4)=""2018"",A1,""""))" ' Applying the fixed vector notation (Excel instructed to do that by no $s) will result in the same relative formula. Displayed will be the actual formula ( in the relative form, but that is not important)
  
' Final solution  Rick : http://www.excelfox.com/forum/showthread.php/2293-Move-values-in-rows-at-the-end-of-the-preceding-row?p=10888#post10888
  Range("A1:A" & LastRow) = Evaluate(Replace(Replace("IF(ISNUMBER(0+SUBSTITUTE(SUBSTITUTE(A2:A#,"" "",""""),"","","""")),IF(LEFT(A1:A@,4)=""2018"",TRIM(A1:A@&"" ""&A2:A#),""""),IF(LEFT(A1:A@,4)=""2018"",A1:A@,""""))", "#", LastRow + 1), "@", LastRow))
'  Range("A1:A" & LastRow).SpecialCells(xlBlanks).EntireRow.Delete ' This will mess up now due to my .FormulaArray  as you can't delete bits of that
End Sub



'          2018, 1, 90709, 10020009, P4, Blabla og Blalala (NY), CHE, Blabla,Bla-ah, 10006098, 15392.64
'                        TRIM(A13" "&A14)

'   IF(      True        ,   TRIM(A13" "&A14)        ,       A13       )


'   IF(       ISNUMBER(0+1000609815392.64),   TRIM(A13" "&A14)        ,       A13       )
'   IF(       ISNUMBER(0+SUBSTITUTE(10006098,15392.64),",","")),   TRIM(A13" "&A14)        ,       A13       )

'   IF(       ISNUMBER(0+SUBSTITUTE(SUBSTITUTE(A14," ",""),",","")),   TRIM(A13" "&A14)        ,       A13       )           )
'   IF(       ISNUMBER(0+SUBSTITUTE(SUBSTITUTE(A14," ",""),",","")), IF(LEFT(A13,4)="2018"  ,  TRIM(A13" "&A14)  ,  "")      ,     IF( LEFT(A13,4)="2018"  ,  A13  ,"" )     )


'   IF(       ISNUMBER(0+SUBSTITUTE(SUBSTITUTE(A14," ",""),",","")), IF(LEFT(A13,4)="2018"  ,  TRIM(A13" "&A14)  ,  "")      ,     IF( LEFT(A13,4)="2018"  ,  A13  ,"" )     )
'      IF(       ISNUMBER(0+SUBSTITUTE(SUBSTITUTE(A14," ",""),",","")),IF(LEFT(A13,4)="2018",TRIM(A13" "&A14),"")      ,     IF(LEFT(A13,4)="2018",A13,"")     )
'            IF(ISNUMBER(0+SUBSTITUTE(SUBSTITUTE(A14," ",""),",","")),IF(LEFT(A13,4)="2018",TRIM(A13" "&A14),""),IF(LEFT(A13,4)="2018",A13,""))

remember to scroll down first to find the scroll bar:
Scroll down to find Ricks Code bar.JPG : https://imgur.com/R3jgXek
Attachment 2136

[DocAElstein]

XXNVCX:;YNXYN

[DocAElstein]

test post in support of this forum question
http://www.eileenslounge.com/viewtop...245488#p245485


Yellow is effectively the array fed to a sort routine.
Green is how that array looks like after running the sort routine

_____ Workbook: YassBub.xlsm ( Using Excel 2007 32 bit )

2	10	8	2	16	8	1	10	15	2
8	1	10	15	2	19	6	3	14	13
15	15	10	6	13	13	7	6	15	16
2	17	2	8	3	5	9	11	12	8
15	12	15	4	5	2	10	8	2	16
13	13	6	4	11	15	12	15	4	5
19	6	3	14	13	13	13	6	4	11
5	9	11	12	8	15	15	10	6	13
14	18	18	16	20	2	17	2	8	3
13	7	6	15	16	14	18	18	16	20

Worksheet: Sheet1


_____ Workbook: YassBub.xlsm ( Using Excel 2007 32 bit )

14	2	2.9986	17	1	1.9983
15	6	6.9985	19	1	1.9981
16	3	3.9984	20	1	1.998
17	1	1.9983	14	2	2.9986
18	2	2.9982	18	2	2.9982
19	1	1.9981	16	3	3.9984
20	1	1.998	15	6	6.9985

Worksheet: Sheet1

_____ Workbook: YassBub.xlsm ( Using Excel 2007 32 bit )

15	4	5	15	4	5
6	4	11	6	4	11
3	14	13	3	14	13

Worksheet: Sheet1



Test calling routine : ( called routines in next 2 posts )

Sub TestsStringArray() ' http://www.eileenslounge.com/viewtopic.php?f=30&t=31691&p=245488#p245488
Dim arrSel() As Variant
 Let arrSel() = Selection.Value
Dim DumDom() As String: ReDim DumDom(0 To UBound(arrSel(), 1) - 1, 0 To UBound(arrSel(), 2) - 1)
Dim rCnt As Long, cCnt As Long
    For rCnt = 0 To UBound(arrSel(), 1) - 1
        For cCnt = 0 To UBound(arrSel(), 2) - 1
         Let DumDom(rCnt, cCnt) = CStr(arrSel(rCnt + 1, cCnt + 1))
        Next cCnt
    Next rCnt
 Call subSort2DArrayMultiElements(DumDom(), "1 2")
' Paste reorganised Array next to the selection
Dim OutRange As Range: Set OutRange = Selection.Offset(0, Selection.Columns.Count)
 Let OutRange.Value = DumDom()
End Sub

_____ Workbook: YassBub.xlsm ( Using Excel 2007 32 bit )

Sub	sub	d	Sub	func	h
Sub	func	h	Pub	pub	a
sub	pub	x	func	pub	m
func	pub	m	Pub	pub	p
func	pub	r	func	pub	r
Pub	pub	a	sub	pub	x
Pub	pub	p	Sub	sub	d

Worksheet: Sheet1