UDF that can change values in cells other than the cell in which the UDF is used
Spare post for later, around the time I started preparing notes for this Thread
UDF that can change values in cells other than the cell in which the UDF is used
UDF that can change values in cells other than the cell in which the UDF is used
This Tips Thread was inspired by this one posted by Rick Rothstein
I have referred to that posting from Rick quite a few times when showing people how to use a UDF to do things with a UDF in cells other than that in which the UDF is, and mostly pretty impressed they have been too. ( Those that weren’t impressed were those that did not believe it and never tried…. )
I finally got around to trying to get my head around this, and here is my take on it. If you are only interested in seeing the working examples, then jump to post 4 , or post a question in the main Excel Help forum question sub forum
https://www.excelfox.com/forum/forum...p/2-Excel-Help
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 which I am discussing in the next post, 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(ArgY, __)"
Here again below, the solution a bit more fully: Below is the coding as you would write it in a normal code module , assuming that the Function MainUDF( ) is the UDF which you would use in the usual formula type way in a worksheet cell like __ = MainUDF( )
http://i.imgur.com/58IFQoQ.jpg
___Function MainUDF(__) ' UDF to be used in a worksheet cell like__ = MainUDF(ArgX)
____ ' any coding to do anything not related to changing things in the worksheet in which the UDF is used
____ '
_______ Evaluate__ "SomeOtherProcedureToDoAnythingAnywhere(ArgY, __)"
____ '
____ ' any coding to do anything not related to changing things in the worksheet in which the UDF is used
____ '
___End Function
Simplest Working Example:
The simplest example is something like the following:
Copy both procedures to a normal code module , …_
Code:
' Paste both procedures in a normal code module, then type =ChangeNextCell() in any cell followed by Enter
Function ChangeNextCell()
' Call NextCell ' This wont work. We will be on the same dependancy in the called procedure and cell dependancies are already calculated so attempts to access cells will be screwed up
Evaluate "NextCell()" ' The dependance tree is recalculated for the procedure NextCell() which is excecuted from a copy of the procedure not dependant on the spreadsheet update cycle in progress for the excecution of the function ChangeNextCell()
End Function
Sub NextCell()
ActiveCell.Offset(0, 1).Value = "Next cell." ' Excel has a memory of the last Active Cell and does not rely on dependance to the spreadsheet for this macro to run
End Sub
_... then type in any cell the function _ =ChangeNextCell() _ then _ Enter
ChangeNextCell.JPG : http://i.imgur.com/nqHHEfb.jpg https://imgur.com/nqHHEfb
https://i.imgur.com/nqHHEfb.jpg
ChangeNext Cell.JPG : http://i.imgur.com/iAQFUrj.jpg https://imgur.com/iAQFUrj
https://i.imgur.com/iAQFUrj.jpg
Change Next Cell.JPG : http://i.imgur.com/V7Lowxp.jpg https://imgur.com/V7Lowxp
https://i.imgur.com/V7Lowxp.jpg
In the next post some attempt to examine what’s going on. It is not complete; I may need to come back again on this one
UDF that can change values in cells other than the cell in which the UDF is used
OK, this is what we have so far
___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
Code:
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/602216/how-do-you-write-an-excel-formula-that-will-paste-a-specific-value-in-a-differen
https://stackoverflow.com/questions/41624463/excel-calculation-in-one-cell-result-in-another
https://stackoverflow.com/a/8711582
https://stackoverflow.com/questions/12501759/vba-update-other-cells-via-user-defined-function
How do you write an Excel formula that will paste a specific value in a different cell?
UDF that can change values in cells other than the cell in which the UDF is used
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 _ , _ )"
1 Attachment(s)
UDF that can change values in cells other than the cell in which the UDF is used
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.
Code:
' 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
Code:
' 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 |
|
|
|
|
|
|
|
|
|
|
Some other notes for a Thread post , I forgot where
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
Doing the "impossible" again
Question from the web: Excel formula that will paste a specific value in a different cell?
https://superuser.com/a/1926549/1142601
The question:
….So basically I want to write a formula that will check a condition, and if that condition is met, then I want to paste a specific line of text in a different cell. I should note that I do not want the formula to exist in the cell I want to paste a value into. So for example, if I want to paste a value into B5, I don't want the formula to be in cell B5...
Comments there of this sort as usual:
…..it is impossible for a formula in one cell to modify another cell. – ….
I have seen this question asked a lot. Often it comes form people less familiar with things like VBA.
It’s usually a bad idea to say, something can’t be done, which is usually the answer to the question there, as is in some of the comments there. It can be done, and very easily. (I am not necessarily saying that it should be done. But an incorrect blanket statement that it can’t be done is a bad idea, JIMVHO)
My contribution is a simple way, using a formula to change values in another cell, ( I will use it to paste something in B5, as in the original request example). No formula will appear in cell B5.
I wanted to answer close to what is asked for in the Thread title, and in the sin of the original poster knowledge level, as I know that thread has been caught a few times by others in the same "newbie" vain.
(The solution I offer here does mean that you need to know some very basic VBA coding - VBA coding is some coding that is "behind" Microsoft Office, but exists already there for you freely available in all Microsoft Office versions from the last 30 years or so. (Using Microsoft Office without the VBA you already have, can be compared with having some all purpose vehicle parked outside your door, that can do just about everything that any vehicle can and much more like flying to Mars, submerging to the centre of the earth, digging out a trench to make a river to save a Forest for younger generations etc., etc., but you just use it once a week for the same short journey to go to the local shops)
If you have no ideas about VBA then for the short term it is not a problem as I will give you a solution example in the uploaded file.
You need to use the Excel formula / function Boolox, which I wrote.
This is that VBA coding “behind” the formula / function Boolox that I wrote.
Code:
' https://www.excelfox.com/forum/showthread.php/2831-UDF-that-can-change-values-in-cells-other-than-the-cell-in-which-the-UDF-is-used?p=27592&viewfull=1#post27592 https://superuser.com/a/1926549/1142601
Option Explicit
' First main "UDF" Procedure. In a spreadsheet cell we use "it" something like this: =Boolox(A2)
Public Function Boolox(ByVal Rng As Range) As String
Rem 1 Unconventional use of the general thing we might call a UDF
Worksheets("RBobJeff").Evaluate Name:="=AnuverProc(" & Rng.Address & ")" ' The Evaluate(" ") string, that is to say the Name:=" " bit in this case, uses Excel spreadsheet syntax, not VBA syntax, and we can get that spreadsheet A1 type address syntax from the .Address stuff in VBA
Rem 2 The conventional use of a UDF to return a value in the cell that it is in, in a similar way to how a formula or in built function does
If Rng = "Boolox" Then
Let Boolox = "you got Boolox" ' This sets/ defines the String returned from the function, so if used in the spreadsheet, this is what comes in the cell where you type the formula/ function
Else
Let Boolox = "you got no Boolox" ' This sets/ defines the String returned from the function, so if used in the spreadsheet, this is what comes in the cell where you type the formula/ function
End If
End Function
' Second Procedure. Debateable whether or not it is part of the "UDF"
Sub AnuverProc(Rng As Range)
If Rng = "Boolox" Then
Let Worksheets("RBobJeff").Range("B5") = "you got Boolox"
Else
Let Worksheets("RBobJeff").Range("B5") = "you got no Boolox"
End If
End Sub
You need all the coding, and one typical convenient place to keep it is in the workbook where you are wanting to use the “it”. “It” being the formula and necessarily the coding which it won’t work without
To keep it simple, the coding needs to go in what we call a normal code module.
Although not always necessarily necessary, for the purposes of the demo, you need to confine yourself to using the worksheet named "RBobJeff" in the uploaded file, ( Or name a worksheet in another workbook "RBobJeff", and do the demonstration in that )
Now, in the first cell, A1, I have written Boolox, ( or if you are using your own file, then write Boolox in the first cell, A1 )
https://i.postimg.cc/ydTyjn3n/Boolox-in-first-cell.jpg
https://i.postimg.cc/mkqf6HdZ/Boolox-in-first-cell.jpg
Now you can do the demo
If you write in any other cell ( other than B5 – as per the original request if a condition is met something will be pasted there in B5 ) this formula,
=Boolox(A1)
, then the line of text, you got Boolox , will be written in B5, and nothing else – no formula or anything other than that text will be in B5
If you use the formula in a similar way, but with anything other than A1 as the cell, such as using
=Boolox(A12)
, then a similar thing happens except the text says you got no Boolox
For examples
For example, after writing in cell C4 =Boolox(A2) , you should see
https://i.postimg.cc/1RYQzkyN/you-got-no-boolox.jpg
https://i.postimg.cc/1RYQzkyN/you-got-no-boolox.jpg
For example, after writing in cell C8 =Boolox(A1) , you should see
https://i.postimg.cc/05HsNT9K/you-got-boolox.jpg
https://i.postimg.cc/05HsNT9K/you-got-boolox.jpg
_.___-
As a further experiment, remove the text in the first cell, and the text in B5 will change
https://i.postimg.cc/d3XPSyWV/Boolox-gone.jpg
https://i.postimg.cc/d3XPSyWV/Boolox-gone.jpg
In those experiment examples two basic things happen,
_(i) what appears in C4 or C8 is as anyone with basic Excel and Excel VBA would expect. It reflects the conventional use of a "UDF" .- There is a formulas / function on a cell, and by default setting you will see the result in the cell, ( and the formula can be seen in the formula bar)
_(ii) what happens in B5 is the "impossible", which is what the original request asked for …. formula ( I use as example that in C4 or C8 ) , that will check a condition, and if that condition is met, then I want to paste a specific line of text in a different cell. I should note that I do not want the formula to exist in the cell I want to paste a value into. So for example, if I want to paste a value into B5, I don't want the formula to be in cell B5
Coding description
There are two main coding sections in the first procedure, ( Function Boolox( ___ ) , corresponding to the main two things that happen, Depending on your point of view
, the first procedure may be regarded as the main "User Defined Function", UDF
or
, in everyday language you might refer to the entire coding as the UDF which is the Boolox.
_(i) Rem 2
In this particular example coding, The same text is also is put in the cell you type the formula in, but if you do not want that, then just remove the second section in the first procedure, Rem 2.
Rem 2 in the first procedure is the conventional way to use this type of coding.. Such conventional coding can be called just some VBA coding, or a Function procedure in VBA. It can be used generally as coding, and it can be set off (run, Called) in several ways. But if it is intended to be set off/ called from a formula in a spreadsheet, then usually it goes by the name of a UDF (User Defined Function). That name makes sense if you think about it, a user, me in this case, has made/ defined a function / formula to use in a spreadsheet, rather than the existing built in ones. The procedure / Function, Boolox, and the associated formula / function used in the spreadsheet would loosely go by the name of UDF if you were talking about it in everyday conservations, like you might say, “Hey man, wow, check out Alan’s UDF, it’s the Boolox”
_(ii) Rem 1
It is usually said that a UDF cannot change values in a different cell. Well, I guess we can debate that until the Cows come home, and/or debate whether I am strictly doing that in the section Rem 1
Rem 1 is the bit that does something alone the lines of what was asked for. Or depending on your pint of view, it may set in motion the second procedure and that does something alone the lines of what was asked for.
In simple layman terms it sets off a second procedure, which you probably would not call a UDF. But someone might in general all day talking say it is part of the UDF since it gets the job done from the user made/defined formula/ function in the spreadsheet.
I suspect nobody really knows what is going on here, but it is generally thought that one reason it works is because Excel has lost it’s orientation a bit on where the second procedure is or has come from.. Maybe that helps us debate if the UDF is changing the other cells or some lost soul set off in the Excel world.
‘StackOverflowUDFChangeOtherCells.xls’ https://app.box.com/s/knpm51iolgr1pu3ek2j96rju8aifu4ow
1 Attachment(s)
Put formula in a cell with coding
We are almost there.
We need to make some construction using that WorkRange.Address within VBA that allows it to be used within the main formula string: We cannot just insert WorkRange.Address any more as we cannot just insert WorkRange, as it will take WorkRange.Address as just that actual text, as if we had given excel some text, like
Let ActiveCell = "WorkRange.Address" ___ https://i.postimg.cc/sBYcwhW3/Active...ge-Address.jpg
, but we don't want that, we want the short text notation used in an Excel spreadsheet for a range, (that of the cell in the Excel spreadsheet column letter and row number format), that is returned from WorkRange.Address when it is used in VBA coding.
The VBA coding syntax for doing that sort of thing is to insert it into the main formula string at the appropriate position in a special way like this
_______________ " & WorkRange.Address & "
What that does, is do the VBA business on WorkRange.Address and then stick the result into the literal text
, pseudo like, if , for example, WorkRange was the second cell in a spreadsheet, ( Set WorkRange = Range("B1") ) then:
" & WorkRange.Address & " will be turned by VBA into $B$1 , and that $B$1 will be inserted into the text.
We are using it in a formula, but the idea is easier to see in a simple text, thus
Code:
Sub TextIn()
Dim Rng As Range
Set Rng = Range("A1")
Let Range("D10") = "The first cell has an address of " & Rng.Address & ", honest"
End Sub
Rub that macro and then In the cell, D10 you will see
The first cell has an address of $A$1, honest
https://i.postimg.cc/94Cs4tTT/Range-A1.jpg
In this post we will now do some examples of how to put the basic formula into a cell. Some will be relevant to this Thread, some won't necessarily, - at the end of the day we are Human here and are not so keen to encourage the robots to take over with their well ordered cold logic!
From now on I will use this simple test range, which is in both worksheets in the uploaded file. ( the letter f in cell E5 is the last filled cell in column E)
https://i.postimg.cc/Kz4bFvvc/Simple-test-range.jpg https://i.postimg.cc/gX9QHpZ1/Simple-test-range.jpg
https://i.postimg.cc/7YKpPrcj/Simple-test-range.jpg
_____ Workbook: SimpleCodingPutFormulasInCell.xls ( Using Excel 2007 32 bit )
| Row\Col |
E |
|---|
| 1 |
|
| 2 |
a |
| 3 |
b |
| 4 |
|
| 5 |
f |
| 6 |
|
Often, or at least initially, I will tend to put the formula somewhere to the left of the range, for no particular reason other than a guess that it could bee convenient there. Of course, if I am passing a work range, then that will determine where things are relatively speaking.
Simplest Codings Put formula in
I assume here that I want my formula in column D, and I have selected the cell in column D that I want the formula in.
Here are three coding offerings
Code:
Sub PutFormulaIn_1()
Let ActiveCell = "=LOOKUP(3,1/(" & ActiveCell.Offset(0, 1).EntireColumn.Address & "<>"""")," & ActiveCell.Offset(0, 1).EntireColumn.Address & ")"
End Sub
Sub PutFormulaIn_2a()
Call PutFormulaIn_2b(ActiveCell.Offset(0, 1).EntireColumn)
End Sub
Sub PutFormulaIn_2b(ByVal WorkRange As Range)
Let ActiveCell = "=LOOKUP(3,1/(" & WorkRange.Address & "<>"""")," & WorkRange.Address & ")"
End Sub
Sub PutFormulaIn_3a()
Call PutFormulaIn_3b(ActiveCell.Offset(0, 1).EntireColumn)
End Sub
Function PutFormulaIn_3b(ByVal WorkRange As Range)
Let ActiveCell = "=LOOKUP(3,1/(" & WorkRange.Address & "<>"""")," & WorkRange.Address & ")"
End Function
(The coding is also in the uploaded file, SimpleCodingPutFormulasInCell.xls )
Run any of those codings, ( by running either of these:
Sub PutFormulaIn_1()
Sub PutFormulaIn_2a()
Sub PutFormulaIn_3a() __
), and the result will be the same:
In the selected cell, if it is in column D you will see f
In the formula bar you will see =LOOKUP(3,1/($E:$E<>""),$E:$E)
https://i.postimg.cc/BnZzPSR3/Result...ist-Coding.jpg
https://i.postimg.cc/BnZzPSR3/Result...ist-Coding.jpg
Strictly speaking, none of those solutions is a UDF solution.
The last function could be considered a UDF, if I called it from the spreadsheet by typing in any cell something like
=PutFormulaIn_3b(E:E)
If you try doing it just like that it will not work. That is where the typical answers come from, those answers of like ….
…. A UDF cannot add a formula to a cell. …..
In the next post I will show how that can be done. But before that I will throw in a solution of a different sort, just in case it might be worth considering….
Simple alternative way to do it Event Coding
This solution does not really fit in to this Thread. But we are not prejudice here at excel fox.
Event Coding
VBA can be thought very approximately as the coding that actually makes Excel or Microsoft Office work. Very graciously Microsoft let us tap into it, and so we can do an amazing number of things.
(Using Microsoft Office without the VBA you already have, can be compared with having some all purpose vehicle parked outside your door, that can do just about everything that any vehicle can and much more like flying to Mars, submerging to the centre of the earth, digging out a trench to make a river to save a Forest for younger generations etc., etc., but you just use it once a week for the same short journey to go to the local shops )
Microsoft do not let us do everything, understandably perhaps, or else we would make everything ourselves, or more likely make a mess.
When things happen, for example, a cell value gets changed, various things happen, a chain of events occur, as it were. We can tap into those as well.
We will do that in worksheet, Sheet2
We go to the second worksheet, Sheet2, right click on the Sheet2 tab at the bottom, select View Code , that gets us the VB Editor we might be familiar with, but the biggest empty window showing is a special code module belonging to the worksheet. There is one such code module for each worksheet.
https://i.postimg.cc/F15RRvcj/Mess-a...eet-Change.jpg
https://i.postimg.cc/5Qg17Zs0/Mess-a...eet-Change.jpg
Now mess around selecting things from the two drop downs you see at the top of the worksheet code module until you get the Worksheet_Change sub routine showing
It is empty initially. It is part of the chain events that happen when something is changed in a worksheet. Microsoft do not want to show us all that goes on, but we can see this sub routine that is always done, or rather run, even though it does nothing as it is empty. But we can add coding to it, so that something else is done when there is a change in the worksheet.
The Target variable is given to us and filled with the range object of what was changed. So that is handy, me thinks…..
How about this:
I will write a bit of coding to put in the empty routine, that checks what range got changed. If it was a single cell, and say, a small z was typed in, then I will put the formula in that cell, the formula that gives me the last value in the column to the right of that cell
Here is the coding
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count = 1 And Target.Value2 = "z" Then
Target.Select ' After I hit Enter I will be in the next cell down, so this puts me back in the cell I typed z in
Call PutFormulaIn_3b(ActiveCell.Offset(0, 1).EntireColumn) '
Else
' do nothing
End If
End Sub
The coding is also in the uploaded file, SimpleCodingPutFormulasInCell.xls
So just to recap, clarify what you do and what goes on in Sheet2:
Select a cell (in Sheet2), to the left of the column you want to get the last value of, for example D4.
Type a small z in it, and hit Enter https://i.postimg.cc/K1GWSsYL/type-z-in-D4.jpg
The formula we want then gets put in that cell, ( the z is overwritten with the formula )
https://i.postimg.cc/L8LFPcFK/z-over...th-formula.jpg
For convenience I use the function PutFormulaIn_3b . I could just as well have put all the coding in , or indeed used any of the other routines .
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count = 1 And Target.Value2 = "z" Then
Target.Select ' After I hit Enter I will be in the next cell down, so this puts me back in the cell I typed z in
' Call PutFormulaIn_3b(ActiveCell.Offset(0, 1).EntireColumn) '
' Call PutFormulaIn_1
' Call PutFormulaIn_2a
' Call PutFormulaIn_2b(ActiveCell.Offset(0, 1).EntireColumn)
Call PutFormulaIn_3a
Else
' do nothing
End If
End Sub
1 Attachment(s)
Doing the impossible, again
A UDF to change the contents of any cell, in our case to put the formula in to get the last value in a column
Here, we develop a UDF to put our formula in any cell of our choosing, ( well almost any cell, in any case a cell other than the one we use the UDF formula in – which is regarded as impossible)
We will use the same test range in column E, as in the previous posts, in a third worksheet which I will name Marnhullman
https://i.postimg.cc/KYs7c9cD/Ws-Marnhullman.jpg
https://i.postimg.cc/QB1K0JTF/Ws-Marnhullman.jpg
This is an example coding, and it is in th uploaded file. (You need all that coding)
Code:
Function PutFormulaIn_4a(rngInput As Range) As String
Rem 1 ' The conventional use of a UDF to return a value in the cell that it is in, in a similar way to how a formula or in built function does
Let PutFormulaIn_4a = "last in column " & Split(rngInput.Cells.Item(1).Address, "$")(1) ' rngInput.Cells.Item(1).Address will give us a simple string which will have a form like $E$19 If isplit that using $ as the delimeter, I end up with three elements indicie 0 1 2 (0)is an empty string, (1) is E , (2) will be the number
Rem 2 ' Unconventional use of the general thing we might call a UDF
Worksheets("Marnhullman").Evaluate Name:="=PutFormulaIn_4b(" & rngInput.Address & "," & ActiveCell.Address & ")" ' The Evaluate(" ") string, that is to say the Name:=" " bit in this case, uses Excel spreadsheet syntax, not VBA syntax, and we can get that spreadsheet A1 type address syntax from the .Address stuff in VBA
End Function
Sub PutFormulaIn_4b(ByVal rngInput As Range, ActivCel As Range)
Dim WorkRange As Range
Set WorkRange = rngInput.Columns(1).EntireColumn
Let ActivCel.Offset(1, 0).Value = "=LOOKUP(3,1/(" & WorkRange.Address & "<>"""")," & WorkRange.Address & ")"
End Sub
The first procedure, Function PutFormulaIn_4a, is what we might call the UDF.
If, for example you were to type in cell D4
=PutFormulaIn_4a(E3)
https://i.postimg.cc/nL4vtHjx/Use-UDF-in-D4.jpg
https://i.postimg.cc/xkdkvV3p/Use-UDF-in-D4.jpg
, then we will get the following formula written in a cell
=LOOKUP(3;1/($E:$E<>"");$E:$E)
https://i.postimg.cc/KYPnyZkS/Formula-put-in-D5.jpg
https://i.postimg.cc/xkdkvV3t/Formula-put-in-D5.jpg
This solution probably comes closes to answering the very initial post over at excelforum.com
However, there are just a few deviations/ features to discuss , as well as a general explanation of what is going on.
A couple of non perfections:
_ This does not work to put the formula in the same cell as where you use the UDF. But it does work to put the formula in any other cell, (which is the thing regarded by most as impossible) . I have arranged that it puts the formula in the next cell down. That is what the
.Offset(1, 0)
, in the last code line is about – it is offset by 1 row down and 0 column offset. So you can change that as you wish to put the formula somewhere else, but avoid no offset, as that can cause a nasty crash. (I suspect that with no offset it may be trying to do two things simultaneously in a cell, which causes the nasty crash)
_ Initially the formula always shows 0. I suspect that may be because for some reason it has not yet been done/ evaluated/calculated. But as soon as you do most other things in the worksheet, or save the file, or probably do various other things, then the worksheet seems to get refreshed, and the formula gets done. ( After that it will update as any formula would if you change any cell values that it is working on )
General Explanation of what is going on
If we consider the first part of Function PutFormulaIn_4a ( section Rem 1 ) , along with the example use I did above, then that is what a typical UDF is all about: What is going on there is that, some of the information you pass to it, (when you call it from a cell ), is used to put something in the cell you wrote in. In this example I have arranged that it puts some text in telling you which column you are trying to get the last value from.
Rem 2 section and the other procedure, Sub PutFormulaIn_4b is part of the trick to get a formula out in a cell from when using a UDF. ( Whether or not Rem 2 section and the other procedure, Sub PutFormulaIn_4b is part of what we might refer to as the UDF named PutFormulaIn_4a is open to debate. That is perhaps convenient as the Experts claiming this cannot be done, can say the UDF is not changing the contents of any cell if they regard only Rem 1 section as a UDF )
So Rem 2 section and the other procedure, Sub PutFormulaIn_4b gets the job done.
Nobody is too sure what is going on here. But we know that in general the
VBA Evaluate(" ")
Or
VBA Evaluate Name:=" "
( those are just 2 different syntaxes of the same thing )
, is something that allows us to do things in VBA that we do in a spreadsheet cell. In other words, you can write in between the quotes pair what you can write in a cell. VBA then tries to give you the result as it would in the cell. So using it in the way I am here, seems to set off an independent second procedure. As it is somehow independent, it seems to work similarly to how any procedure I run normally from within VBA would work. So I do not have the restrictions that I have with the typical standard UDF.
That is basically it.
One small point is that we do have 2 VBA Evaluate things, a general one, and one for every worksheet. I have sometimes found that if I have a lot of these sorts of UDFs in different worksheets, then using one of them can inadvertently set the others off in the wrong worksheet. Restricting things to a worksheet, ( as I am doing in Rem 2 with Worksheets("Marnhullman"). ) seems to cure that problem. If you miss out the Worksheets("Marnhullman"). , then you default back the general Evaluate, and it may be OK, and you remove the restriction to a particular worksheet.
An interesting observation is that in the second procedure, ActiveCell does not work as we might expect. ( That is why I had to pass over the address of the active cell in order to use it ). This observation supports some ideas about why this trick works: It is thought that Excel has lost its orientation a bit, when it runs the second procedure, which gives it the independence that helps make it work for our purpose. Now, surprisingly, ActiveCell is not a property of a worksheet, nor generally a property of Excel. It is a Windows property. So using Evaluate is doing spreadsheet things but somehow not directly linked to a worksheet. Hence we have interesting new possibilities using it. The VBA Evaluate function is often regarded by smart people as one of the most powerful functions.
Alan
