UDF that can change values in cells other than the cell in which the UDF is used

**DocAElstein** · 12-26-2018, 04:37 PM

**DocAElstein** · 01-09-2019, 09:11 PM

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 3

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

ChangeNext Cell.JPG : http://i.imgur.com/iAQFUrj.jpg https://imgur.com/iAQFUrj

Change Next Cell.JPG : http://i.imgur.com/V7Lowxp.jpg https://imgur.com/V7Lowxp

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

**DocAElstein** · 01-09-2019, 09:27 PM

___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/6022...-in-a-differen
https://stackoverflow.com/questions/...ult-in-another

**DocAElstein** · 01-09-2019, 09:29 PM

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** · 01-09-2019, 09:54 PM

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	C	D
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	C	J
2
3	Number in B3 is greater than, or equal to, zero.	The square of (in B3) is 0.
4

_Row\^Col	B	C	J
2
3	-2	Number in B3 is less than zero.	The square of -2 (in B3) is 4.
4

**DocAElstein** · 01-09-2019, 09:55 PM

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** · 01-24-2021, 07:33 PM

I forgot about this Blog tip thread for a while. …

The reason for that was that I answered a few threads, where someone asked for help because something was not doing what they wanted, and the reason for that was that they were trying to get a UDF to change the value of another cell, or something very similar.
My solution seemed to work. Sometimes the OP seemed happy. But more often than not, I got a lot of hate from some senior member, “expert Guru”, Moderator, or similar, insisting that a UDF cannot change the value of any cell other than the one it is in.

It’s about 2 years later now. Maybe it’s safe to post something about it again.
In the meantime, I still don’t have any newer versions of Office/ Excel, so maybe I will post a very simple example, and ask people to test it for me on newer versions. I won’t present it as "a UDF that can change the value of another cell, other than the one the UDF is in".

See how it goes.

I made a much simplified sample file, as enclosed …… This is what I would like you to try for me please:
Download and open the uploaded file, then
enable macros and then
type anything in cell A1, then tell me what happens.

_.________________________________________________ ______________________________
EDIT: Or, alternatively, if you prefer not to risk downloading the file
Please do this
In a new virgin File, insert a normal code module and put this coding in it

Code:

Option Explicit
Function WotsThereWhere(ByVal Rng As Range) As String
 Evaluate "='" & ThisWorkbook.Path & "\TryThisPlease.xls'!Modul1.YouNameIt(" & Rng.Address & ", """ & ActiveSheet.Name & """)"          '    Evaluate "=YouNameIt(" & Rng.Address & ", """ & ActiveSheet.Name & """)"    '    : Debug.Print "=YouNameIt(" & Rng.Address & ", """ & ActiveSheet.Name & """)"    '  gives   YouNameIt($A$1, "Sheet1")
End Function
Sub YouNameIt(ByVal Rng As Range, ByVal Sht As String)
Stop  '  It wont
 Let Rng.Offset(0, 2).Value = ""
 Let Rng.Offset(0, 2).Value = "You wrote " & Rng.Value & " in cell " & Rng.Address(0, 0) & ",  in worksheet " & Sht
End Sub

The first function can be simplified if relying on defaults

Code:

Function WotsThereWhere(ByVal Rng As Range) As String
' Evaluate "='" & ThisWorkbook.Path & "\TryThisPlease.xls'!Modul1.YouNameIt(" & Rng.Address & ", """ & ActiveSheet.Name & """)"          '    Evaluate "=YouNameIt(" & Rng.Address & ", """ & ActiveSheet.Name & """)"    '    : Debug.Print "=YouNameIt(" & Rng.Address & ", """ & ActiveSheet.Name & """)"    '  gives   YouNameIt($A$1, "Sheet1")
 Evaluate "YouNameIt($A$1, ""Sheet1"")"  ' Shortened version relying on default
End Function

Feedback so far

http://www.eileenslounge.com/viewtop...ff4995#p280303

Any Other Threads doing something similar

https://stackoverflow.com/questions/...nal-formatting
https://www.mrexcel.com/board/thread...a-udf.1154593/
https://www.eileenslounge.com/viewto...300091#p300091

**DocAElstein** · 10-21-2022, 04:34 PM

Another example, possibly, indirectly
http://www.eileenslounge.com/viewtopic.php?f=30&t=38798
That is a different problem. Indeed we want the UDF to give a result in the cell in which it is, as more typically using a UDF.
But a couple of things reminded me of discussions here…
_1) Something is not working from inside a UDF, something that otherwise works.
_2) A sudden abrupt termination, without an error, in the step ( F8 ) debug mode code execution.
_3) The thing not working is generally “wired” to interact with things in cells in a spreadsheet

So I investigated.

The first problem I had was that the thing ( DisplayFormat ) not working at all in my earlier Office/Excel versions.
But I got to check this later in 2013, https://excelfox.com/forum/showthrea...ll=1#post18404 , and confirmed the issue. DisplayFormat is working to return a value in a normal sub routine and a function, and the snag is just it working in a spreadsheet ( applying the function as available in a spreadsheet formula, what we typically call a UDF )

I actually wont close to the opposite of what I was doing here.
Lets not talk about what I was doing. ( That seems controversial anyway )
Lets approach it a bit laterally thinking

What is/ was the goings on:

We got two things:

Code:

' First thing
Function WotsThereWhere(ByVal Rng As Range) As String
 Evaluate "='" & ThisWorkbook.Path  & "\" & ThisWorkbook.Name & "'!UDFchangeotherCells.YouNameIt(" & Rng.Address & ", """ & ActiveSheet.Name & """)"          '    Evaluate "=YouNameIt(" & Rng.Address & ", """ & ActiveSheet.Name & """)"    '    : Debug.Print "=YouNameIt(" & Rng.Address & ", """ & ActiveSheet.Name & """)"    '  gives   YouNameIt($A$1, "Sheet1")
' Evaluate "YouNameIt($A$1, ""Sheet1"")"  ' Shortened version relying on default
End Function


' Second thing
Sub YouNameIt(ByVal Rng As Range, ByVal Sht As String)
Stop  '  It wont
 Let Rng.Offset(0, 2).Value = ""
 Let Rng.Offset(0, 2).Value = "You wrote " & Rng.Value & " in cell " & Rng.Address(0, 0) & ",  in worksheet " & Sht
End Sub

The first thing, Function ( used as a spreadsheet UDF) , WotsThereWhere , is on the signature line, normal looking, ( and in this case it takes an argument, a range object. )
( The only reason in this case why it is As String declared , is that it is not wanted to return anything as I was using it, at least nothing to us visible. If , for example, it was alternatively As Long declared, then I would see a zero )
The coding in that function is just one line. It Evaluates the string name of a sub routine, that sub routine is our second thing

That single code line somehow sets off the second thing , a sub routine which does what did not work directly inside the function.

That’s it

What do we conclude relevant to the current problem It seems like the thing to try is to get the second thing to
_(i) do something with DisplayFormat, and
_(ii) put the result of that ) do something with DisplayFormat in the cell where we have the UDF using the first thing function

So here we go!

Lets duplicate the first and second thing, put them in a new code module, , and give things different names more appropriate to what we are talking about, the current issues: requirements and problems
So in normal code module I name , DisplayFormatUDF, I have

Code:

Option Explicit
' First thing
Function DoSubDoDisplayFormat(ByVal Rng As Range) As String
 Evaluate "='" & ThisWorkbook.Path & "\" & ThisWorkbook.Name & "'!DisplayFormatUDF.DoDisplayFormat(" & Rng.Address & ", """ & ActiveSheet.Name & """)"          '    Evaluate "=YouNameIt(" & Rng.Address & ", """ & ActiveSheet.Name & """)"    '    : Debug.Print "=YouNameIt(" & Rng.Address & ", """ & ActiveSheet.Name & """)"    '  gives   YouNameIt($A$1, "Sheet1")
' Evaluate "DoDisplayFormat ($A$1, ""Sheet1"")"  ' Shortened version relying on default
End Function


' Second thing
Sub DoDisplayFormat(ByVal Rng As Range, ByVal Sht As String)
Stop  '  It wont
 Let Rng.Offset(0, 2).Value = ""
 Let Rng.Offset(0, 2).Value = "You wrote " & Rng.Value & " in cell " & Rng.Address(0, 0) & ",  in worksheet " & Sht
End Sub

I remove Adeel’s formula in his cell D17, ( {=sum_color(D6:G15;C17)} ) and I replace it with =DoSubDoDisplayFormat(C17)
It seems to then give similar results to what I expect. All is well: the formula in D17 writes something in cell E17 if you edit cell C17

So lets start in the next post with the “AdeelSolution1” ( )

Share ‘DisplayFormatInUDF.xlsm’ https://app.box.com/s/e4307kqrwx6zqk9uwswlpfziz6air9gy

**DocAElstein** · 10-21-2022, 05:40 PM

“AdeelSolution1” ( )

Simple modifications to the second thing , for now, just to see if we can get a result from DisplayFormat
I will forget the sheet referring, as that is not of interest to the final solution,
and
I will do some simple use of DisplayFormat, for example, the DisplayFormat.Interior.ColorIndex of cell C17

Example, change

Code:

Sub DoDisplayFormat(ByVal Rng As Range, ByVal Sht As String)
Stop  '  It wont
 Let Rng.Offset(0, 2).Value = ""
 Let Rng.Offset(0, 2).Value = "You wrote " & Rng.Value & " in cell " & Rng.Address(0, 0) & ",  in worksheet " & Sht
End Sub

to

Code:

' Second thing
Sub DoDisplayFormat(ByVal Rng As Range, ByVal Sht As String)
Stop  '  It wont
 Let Rng.Offset(0, 2).Value = ""
 Let Rng.Offset(0, 2).Value = "For cell " & Rng.Address(0, 0) & ", you have a DisplayFormat.Interior.ColorIndex of  " & Rng.DisplayFormat.Interior.ColorIndex
End Sub

This appears to be giving me sensible results in Office Excel 2013:

Towards a final solution for Adeel
We want a replacement for a formula of this form =sum_color(D6:G15,C17)
So we need
a second range argument in the first thing,
[color=blueviolet]Function[/color] DoSomeColor(ByVal RngA As Range, ByVal RngB As Range) As String
and
in the second thing we no longer need to be taking in a sheet name, but do need to take in a second range argument.
Sub SomeColor(ByVal RgA As Range, RgB As Range)

The rest of the modification is just doing something similar in the second thing to Adeel’s original function.

Code:

'   '   From  Adeel  spreadsheet    '  =sum_color(D6:G15;C17)    '    http://www.eileenslounge.com/viewtopic.php?p=300075#p300075
' First thing
Function DoSomeColor(ByVal RngA As Range, ByVal RngB As Range) As String
 Evaluate "='" & ThisWorkbook.Path & "\" & ThisWorkbook.Name & "'!DisplayFormatUDF.SomeColor(" & RngA.Address & ", " & RngB.Address & ")"         '
' Evaluate "SomeColor(" & RngA.Address & ", " & RngB.Address & ")"  ' Shortened version relying on default
End Function


' Second thing
Sub SomeColor(ByVal RgA As Range, RgB As Range)
Stop  '  It wont
Dim Vee As Long, Sea As Range
    For Each Sea In RgA
        If Sea.DisplayFormat.Interior.ColorIndex = RgB.DisplayFormat.Interior.ColorIndex Then
         Let Vee = Vee + Sea.Value
        Else
        End If
    Next Sea
 Let RgB.Offset(1, 2).Value = ""
 Let RgB.Offset(1, 2).Value = Vee
End Sub

That seems to give the correct result of 54

( For comparison, see the results of 54 here:
http://www.excelfox.com/forum/showth...ll=1#post18404

)

Share ‘DisplayFormatInUDFAdeel1.xlsm’ https://app.box.com/s/5nvqh5r8pggc11ulz4lti3yhk39b9wda

**DocAElstein** · 10-21-2022, 07:30 PM

“AdeelSolution2” ( )

(Some investigations)

Me giving a solution to that thread with the ideas from my thread here, is something of strange ironic/ contradictory nature, because the whole point of my thread here is to do the unconventional thing of getting a UDF to change the value in a cell other than the one that it is in, ( as Solution 1 does ) . But Adeel wants the UDF to work in the conventional way, giving a single result in the cell that it is in – it should work as a normal Excel formula in a cell – you type in the formula, which you always see up top in the formula bar, but Excel displays for you the .Value that it gives. (VBA lets you see either, or rather a lot more, in coding, the .Value , a few .Formula ____formats and a few other things )
So…
How about…
Lets try to turn that last solution into working such that the thing we type in a cell, the first thing, returns a value.
( The problem of making Solution 1 write the result in the cell that the formula is in, is that it would overwrite the formula – the formula would vanish – just the final value would be there. So it would work just once )

Currently, the very initial solutions I came up with in url=http://www.excelfox.com/forum/showthread.php/2831-UDF-that-can-change-values-in-cells-other-than-the-cell-in-which-the-UDF-is-used] this main thread of mine here, [/url] were using the Evaluate in a less common form, like this
__ Evaluate __" Do something "
There is no interest or means of getting any result retuned from Evaluate there.

We want to be investigating stuff in the more conventional way, perhaps starting form something like this
Dim vTemp As Variant
_ Let vTemp =__ Evaluate("Do something")

I am not sure what generally will be returned from the second thing to the first thing, in my general arrangement: For all I know at this stage, things might get lost in some virtual space and I will get nothing!! – after all one of the main things behind the general solution is to somehow separate things which are normally together, but for that reason cause conflict problems.

So
Initially, just out of interest, I will make just that one modification , and see what happens. All I am doing there is investigating if Evaluate is giving me anything back at all. In the current situation the Evaluate seems to be kicking off the second thing which is a simple sub routine which does take arguments, but is not intended to give anything back. So the first guess is that vTemp wont get changed, … but I was wrong:
I set a watch on vTemp , and clicked a Stop in the left margin so as to then step debug F8 mode through the first thing, and had a look:

So it seems to be returning a Double of value 0 into . I don’t know what the significance of that is. As I already said, I am not sure what generally will be returned from the second thing to the first thing, in my general arrangement.

_.________________________

Next step is just to change the second thing from a sub routine to a function. The result I see in vTemp is the same as the last time.

Once again, I don’t know what the significance of that is, but one thing to note is that by default, because I specified no function type, I am effectively doing the second thing like
Function _____________ As Variant

_.____________________________________________

Next experiment is to give the first thing function a type, As String

That seems to change the returned thing to an empty string. Once again I am not sure what is going on here, somehow a nothing or empty or zero value is wanted to be returned.
But what is slightly encouraging is that I am influencing what does come back.

_._______________________

It is rather a shot in the dark, this next step: a first attempt now, to include the code line that assigns the final wanted result to the function, which in conventional function use, is always needed to make the function return something.
If I am lucky, this will get caught somehow by the Evaluate in the first thing

Bingo! It seems to have worked. A lucky break I guess, Lol.

I am interested in thinking a bit more about what is going on here, so I will leave this post for now. Possibly I will come back later.

In the next post I will finalise the solution which luckily seemed to come out quicker than I expected.

Thread: UDF that can change values in cells other than the cell in which the UDF is used

Thread Tools

Search Thread

Display

Hybrid View