Hi
This Thread sparked something in my possibly
not quite normal / "out of whack lateral thinking head"….
What Adeel wanted here is something near to totally opposite to what was wanted and solved by me a few years back here:
https://excelfox.com/forum/showthread.p ... #post18445
http://www.eileenslounge.com/viewtopic. ... 03#p280303
( I hesitate to say what ( I thought ) I was doing there, as it makes smarter people hate me and want to kill me, and I have not figured out that one yet – my ignorance as usual again I expect )
But,
Ignoring for now what that solution
there is, or is not doing, 3 issues there and here seemed similar to me, at least to my warped way of thinking:
_1) Something is not working from inside a UDF
when used in a spreadsheet , something that otherwise works, (for example if the function is used in normal coding).
_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. In other words when used in a formula in a spreadsheet it don’t work. ( I am not sure if the definition of a UDF is like when you use it in a spreadsheet)
So I investigated, as I like to do :)
I just took the simplified final solution, (
( https://excelfox.com/forum/showthread.p ... #post18452 )
http://www.eileenslounge.com/viewtopic.php?f=30&t=36075 ) , and kept it in its general form, but modified so as to making it do close to the opposite of what ( I thought ) it was doing.
That made
DisplayFormat work in a UDF, or may be I should say
used in a UDF formula in a spreadsheet. I am not quite sure what is correct to say.
_._______________________________
Applied to Adeel’s original issue, it gives these 2 solutions.
Solution 1
This is a bit of an in between solution, more out of academic interest.
Code: Select all
' ' 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
Share ‘DisplayFormatInUDFAdeel1.xlsm’ https://app.box.com/s/5nvqh5r8pggc11ulz4lti3yhk39b9wda
That UDF put in a cell,
=DoSomeColor(D6:G15,C17) , actually puts the result in a different cell. That seems to give the correct result of 54
Full story here:
https://excelfox.com/forum/showthread.p ... #post18453
https://excelfox.com/forum/showthread.p ... #post18454
_.__________________________________________________________________________
Solution 2 (Final Solution)
This does exactly what was wanted, as far as I can tell. ( I just tweaked solution 1 very slightly, - it was actually a lot easier than I was expecting )
Put this in a normal code module in Adeel’s original file.
Code: Select all
' ' 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=18456&viewfull=1#post18456 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/page2#post18456
' First thing
Function DoSum_Colour(ByVal RngA As Range, ByVal RngB As Range) As String ' From Adeel spreadsheet ' =sum_color(D6:G15;C17) ' http://www.eileenslounge.com/viewtopic.php?p=300075#p300075
Let DoSum_Colour = Evaluate("Sum_Colour(" & RngA.Address & ", " & RngB.Address & ")")
End Function
' Second thing
Function Sum_Colour(ByVal RgA As Range, RgB As Range) As String
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 Sum_Colour = Vee
End Function
The UDF you want to put in a cell is then
=DoSum_Colour(D6:G15,C17)
Share ‘DisplayFormatInUDFAdeel2.xlsm’ https://app.box.com/s/zpusmzv7f6ygz4bpduypvklc79rlflv9
Full story here:
https://excelfox.com/forum/showthread.p ... #post18455
https://excelfox.com/forum/showthread.p ... #post18455
https://excelfox.com/forum/showthread.p ... #post18456
( There seems to be some similarity going on with all that and what SpeakEasy did. I need to look at all that in detail with a clear head and it might help me understand a bit better what my solutions are doing, (Then people may stop wanting to kill me, when I say what I think I was doing, Lol!) )
Alan
You do not have the required permissions to view the files attached to this post.