UDF Issue

adeel1
3StarLounger
Posts: 264
Joined: 04 Oct 2017, 15:47

UDF Issue

Post by adeel1 »

Hi All


why Function isn't working and i also want to know how i can trouble shoot it in UDF

Code: Select all

Option Explicit
Function sum_color(a As Range, b As Range) As Long
Dim v As Long
Dim c As Range

For Each c In a

 If c.DisplayFormat.Interior.ColorIndex = b.DisplayFormat.Interior.ColorIndex Then
 v = v + c.Value
End If
Next c

sum_color = v
End Function
Adeel
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78232
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: UDF Issue

Post by HansV »

DisplayFormat cannot be used in a UDF - it causes an error as you have found.
Your conditional formatting rule highlights a cell if its value is greater than 2, so you can simply use

=SUMIF(D6:G15,">2")

If you want to troubleshoot a UDF:
- Click in its first line: Function ...
- Press F9 to set a so-called breakpoint.
- Switch to Excel.
- Click in a cell with the function in its formula.
- Press F2 then Enter.
- The code will pause at the line with the breakpoint.
- Each time you press F8, one instruction will be executed.

In your UDF, you'll see that code execution end abruptly when you try to execute the line with DisplayFormat.
Best wishes,
Hans

adeel1
3StarLounger
Posts: 264
Joined: 04 Oct 2017, 15:47

Re: UDF Issue

Post by adeel1 »

thx for info, does below line also will not work in any UDF, way back i have also issue with this might be this will cause.

Application.FindFormat.Interior.ColorIndex

Adeel

User avatar
HansV
Administrator
Posts: 78232
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: UDF Issue

Post by HansV »

FindFormat cannot be used in UDFs either.
Best wishes,
Hans

adeel1
3StarLounger
Posts: 264
Joined: 04 Oct 2017, 15:47

Re: UDF Issue

Post by adeel1 »

yes, thx i just tested the way you told, much thx for info :thankyou: :thankyou:

Adeel

User avatar
SpeakEasy
4StarLounger
Posts: 535
Joined: 27 Jun 2021, 10:46

Re: UDF Issue

Post by SpeakEasy »

Ah - but we CAN use DisplayFormat in a UDF. Just takes a teeny bit of redirection ...

Consider:

Code: Select all

Option Explicit

' Example UDF
Public Function GetDispFormat(ByVal myrng As Range) As Double
    Application.Volatile
    GetDispFormat = ReallyGetDispFormat(myrng)
    ' Alternatively, we could ignore the helper stub, and evaluate the proxie function directly
    ' GetDispFormat = (Evaluate("GetDispFormatproxie(" & myrng.Address() & ")")
End Function

' Interim stub function
Private Function ReallyGetDispFormat(ByVal myrng As Range) As Double
    ReallyGetDispFormat = Evaluate("GetDispFormatProxie(" & myrng.Address() & ")")
End Function

' Proxie function that we can Evaluate so no error raised
Private Function GetDispFormatProxie(ByVal myrng As Range) As Double
    GetDispFormatProxie = myrng.DisplayFormat.Interior.ColorIndex
End Function
Your UDF would then become:

Code: Select all

Function sum_color(a As Range, b As Range) As Long
    Dim v As Long
    Dim c As Range
    
    For Each c In a
        If ReallyGetDispFormat(c) = ReallyGetDispFormat(b) Then
            v = v + c.Value
        End If
    Next c
    
    sum_color = v
End Function

User avatar
HansV
Administrator
Posts: 78232
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: UDF Issue

Post by HansV »

Clever!
Best wishes,
Hans

adeel1
3StarLounger
Posts: 264
Joined: 04 Oct 2017, 15:47

Re: UDF Issue

Post by adeel1 »

SpeakEasy wrote:
18 Oct 2022, 11:26
Ah - but we CAN use DisplayFormat in a UDF. Just takes a teeny bit of redirection ...

Consider:

Code: Select all

Option Explicit

' Example UDF
Public Function GetDispFormat(ByVal myrng As Range) As Double
    Application.Volatile
    GetDispFormat = ReallyGetDispFormat(myrng)
    ' Alternatively, we could ignore the helper stub, and evaluate the proxie function directly
    ' GetDispFormat = (Evaluate("GetDispFormatproxie(" & myrng.Address() & ")")
End Function

' Interim stub function
Private Function ReallyGetDispFormat(ByVal myrng As Range) As Double
    ReallyGetDispFormat = Evaluate("GetDispFormatProxie(" & myrng.Address() & ")")
End Function

' Proxie function that we can Evaluate so no error raised
Private Function GetDispFormatProxie(ByVal myrng As Range) As Double
    GetDispFormatProxie = myrng.DisplayFormat.Interior.ColorIndex
End Function
Your UDF would then become:

Code: Select all

Function sum_color(a As Range, b As Range) As Long
    Dim v As Long
    Dim c As Range
    
    For Each c In a
        If ReallyGetDispFormat(c) = ReallyGetDispFormat(b) Then
            v = v + c.Value
        End If
    Next c
    
    sum_color = v
End Function
really appreciated and much thx for this :clapping: :clapping: :clapping: , i will be back if i have a question for understanding this...

User avatar
DocAElstein
4StarLounger
Posts: 542
Joined: 18 Jan 2022, 15:59
Location: Re-routing rivers, in Hof, Beautiful Bavaria

Re: UDF Issue

Post by DocAElstein »

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 Image

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.
Last edited by DocAElstein on 22 Oct 2022, 11:48, edited 1 time in total.
I seriously don’t ever try to annoy. Maybe I am just the kid that missed being told about the King’s new magic suit, :(

User avatar
SpeakEasy
4StarLounger
Posts: 535
Joined: 27 Jun 2021, 10:46

Re: UDF Issue

Post by SpeakEasy »

>some similarity going on

Alan - your solution is pretty much exactly how mine works.

User avatar
DocAElstein
4StarLounger
Posts: 542
Joined: 18 Jan 2022, 15:59
Location: Re-routing rivers, in Hof, Beautiful Bavaria

Re: UDF Issue

Post by DocAElstein »

I think whatever “thing” it is that I am doing ( which I’m not sure myself what it is** ), I do once, and your coding does it for every wanted use of the DisplayFormat

** I did a long explanation attempt at what the thing is that I am doing a few years ago. I rarely understand any of my theories later, but I think it is something to do with evaluating the name of something has a similar effect of calling it but somehow does that in some way a bit more isolated than doing it by a normal procedure call. Back then I was not interested in the evaluating bit giving me anything back. I was not sure it would until I tried today: I thought it might get lost somewhere. I had not tried that before and that is the new bit I did to get the Solution 2. Solution 1 was closer to what I had been doing before which was to set off a sub routine, not a function, to do something that the UDF did not want to do directly. I do realise that Evaluate generally returns something – I do that all the time in normal coding. But back then I had some abstract ideas about trying to do something like setting off older Excel 4 macro things by calling a named range. So I was doing like
Evaluate “Full reference path name of something”
, rather than
x = Evaluate(“ “)
I don’t know if that makes any sense to anyone. It doesn’t to me always.
I seriously don’t ever try to annoy. Maybe I am just the kid that missed being told about the King’s new magic suit, :(