Add-In UDF problem, help?

[ClimoC]

Hi guys

This works fine as a macro, but when I tried sticking it in a module, saving it as an add-in, and using it in the worksheet, it fails with a non-descript 1004 error.

Public Function HexColour(ByRef Rng As Range, Ar As Range)

Dim myScale As Double
Dim Av As Double, Mi As Double, Ma As Double
Dim Rd As Integer, Gr As Integer, Bl As Integer
Application.Calculation = xlCalculationManual
Av = Application.WorksheetFunction.Average(Ar)
Mi = Application.WorksheetFunction.Min(Ar)
Ma = Application.WorksheetFunction.Max(Ar)

Debug.Print Application.WorksheetFunction.Average(Ar)
Debug.Print Application.WorksheetFunction.Min(Ar)
Debug.Print Application.WorksheetFunction.Max(Ar)

myScale = (255 / Ma)
Err.Clear
On Error Resume Next
Debug.Print RGB(Round(Rng.Value * myScale, 0), 0, Round(255 - Round(Rng.Value * myScale), 0))
    Debug.Print VBA.Round(Rng.Value * myScale, 0)
    Debug.Print VBA.Round(255 - (Rng.Value * myScale), 0)
    Rd = CInt(VBA.Round(Rng.Value * myScale, 0))
    Gr = 0
    Bl = VBA.Round(255 - (Rng.Value * myScale), 0)
    
    
    
    Rng.Interior.Color = RGB(Rd, Gr, Bl)
    If Round(Rng.Value * myScale) < 100 Then Rng.Font.ColorIndex = 2 Else Rng.Font.ColorIndex = 1
    
Application.Calculation = xlCalculationAutomatic
MsgBox Err.Number
End Function

It's for Excel 2007-> , and is to colour cells on a scale (Yes, I am aware of colour-scaling conditional formatting, but I need the actual Cell.Interior to change, not conditional formatting, for other scripts). The parameters are the cell value (a double), and it's relation to the average of a range of numbers (2nd parameter)

As I said, works fine as a standalone function. As a UDF in an addin to be used as a worksheet function, it fails.

Any ideas?

Thanks
C

 

[Weaver]

I'm not 100% on this, but I don't think UDFs work well (if at all) with application.worksheetfunction.

 

[ClimoC]

Hi Weaver

I tried replacing the Worksheet Function used (though they did return the right results into the variables with

Mi = Rng.Value
Ma = Rng.Value

For Each PP In Ar
    ii = ii + 1
    Pi = Pi + PP.Value
    If Pi < Mi Then Mi = Pi
    If Pi > Ma Then Ma = Pi
Next

Av = Pi / ii

to set the min, max, and average

However, the code magically stops on the line (I think this is where the problem is)
on:

Rng.Interior.Color = VBA.RGB(Rd, Gr, Bl)

The cell in question then also comes up with "#VALUE!"

As I said, works fine as a macro. As a UDF, this function fails.

JIC, I have included 'Visual Basic Extensibility 5.3' and 'Microsoft Scripting Runtime' in the libraries of both the book itself, and the addin book.

Still no result

 

[RoryA]

You cannot change the environment from a UDF called from a cell, so you cannot colour cells.

 

[ClimoC]

You cannot change the environment from a UDF called from a cell, so you cannot colour cells.

Okay, Thanks Ror. Good to know for future reference.

Will just assign it to a macro with a shortcut.

 

[RoryA]

For the record, worksheetfunction works fine in UDFs.

 

[Weaver]

For the record, worksheetfunction works fine in UDFs.

That's good to know - someone told me that ages ago, when I was struggling with something. Never tried it since, which I have to say isn't like me.