How to modify this UDF for visible cells only?

[jbaich]

Hey everyone, I’ve got this user-defined function that I use to calculate a statistical measure for a range of cells. I’d like to modify it so that it only evaluates the visible cells within the range, but I’m not sure exactly how…

Here’s the current code:

Function COD(r As Range) As Double
  Dim sAdr As String
  Dim sFrm As String
 
  sAdr = r.Address
  sFrm = "average(abs(" & sAdr & " - median(" & sAdr & ")))/median(" & sAdr & ")"
  COD = r.Worksheet.Evaluate(sFrm)
End Function

I think I need to modify with something like this, but this doesn’t seem to work so not sure… any ideas?

sAdr = r.SpecialCells(xlCellTypeVisible).Address

When you initially run the function, it prompts the user to select a range. I'd like to be able to select a table column for example, but have the function re-evaluate based on the visible cells only as the table gets filtered...

Thanks in advance!
Joe

 

[6StringJazzer]

The assignment to sAdr will work but it won't work in your formula because it will be a discontiguous range. The string will look something like this:

$A$1:$A$2,$A$7,$A$9:$A$11,$A$14:$A$16,$A$19:A$25

I think you are going to have to use a loop and do the math in VBA instead of Evaluate.

 

[jbaich]

Thanks 6String, so i think i've written a sub that will do the trick, but I can't seem to figure out how to convert it back into a function... any advice?

Sub COD2()

    Dim myVar(1000) As Variant
    Dim i As Integer
    Dim visRange As Range, cll As Range
    Dim med As Double
    
    Set visRange = Selection.SpecialCells(xlCellTypeVisible)
    
        med = WorksheetFunction.Median(visRange)
    
    i = 0
    For Each cll In visRange
    myVar(i) = Abs(cll.Value - med) / med
    i = i + 1
    Next
    
    COD = WorksheetFunction.Average(myVar)
'    Debug.Print COD

End Sub

Thanks in advance,
Joe

 

[6StringJazzer]

Function COD2(UserRange As Range) As Double

    Dim myVar(1000) As Variant
    Dim i As Integer
    Dim visRange As Range, cll As Range
    Dim med As Double
   
    Set visRange = UserRange.SpecialCells(xlCellTypeVisible)
   
        med = WorksheetFunction.Median(visRange)
   
    i = 0
    For Each cll In visRange
       myVar(i) = Abs(cll.Value - med) / med
    i = i + 1
    Next
   
    COD2 = WorksheetFunction.Average(myVar)
'    Debug.Print COD

End Function

 

[jbaich]

Thanks 6String, I'd tried something like that and couldn't get it to work and i'm still getting a #REF! when i try to run it. I think i don't know how to pass the range to the Function.

In the previous version it was just the variable "r"... Function COD(r As Range) As Double

But for some reason, no matter what i try, this new function just keeps giving me an error, but the sub calculates correctly with no error, i can't figure out why...

Here's an example of how it's used just for clarity...

0.9

0.6

0.3

0.7

=COD2(A1:A4)

The function should produce a value of 0.269 and the original function does, as does the Sub, but this new version just gives me the #REF! error.

Any idea what might be going on?

Thanks,
Joe

 

[Domenic]

Actually, it looks like SpecialCells cannot be used in a user defined function...

Excel User-Defined Functions - Decision Models

Microsoft Excel Calculation Secrets and optimisation tips, calculation methods, calculation sequence, dependencies and memory limits.

www.decisionmodels.com

 

[jbaich]

Thanks Domenic, any suggestions for a work around that would accomplish the same thing?

 

[6StringJazzer]

Actually, it looks like SpecialCells cannot be used in a user defined function...

I did not know this and I find it inexplicable.

 

[6StringJazzer]

This should work but I haven't tested it.

Function COD2(UserRange As Range) As Double

    Dim myVar(1000) As Variant
    Dim i As Integer
    Dim visRange As Range, cll As Range
    Dim med As Double
   
    Set visRange = UserRange.SpecialCells(xlCellTypeVisible)
   
        med = WorksheetFunction.Median(visRange)
   
    i = 0
    For Each cll In UserRange
       If Not cll.EntireRow.Hidden Then
          myVar(i) = Abs(cll.Value - med) / med
       End If
    i = i + 1
    Next
   
    COD2 = WorksheetFunction.Average(myVar)
'    Debug.Print COD

End Function

 

[HongRu]

Try to replace the function name "COD2" by other name like "F_COD".
ex:

Function F_COD(UserRange As Range) As Double

    Dim myVar(1000) As Variant
    Dim i As Integer
    Dim visRange As Range, cll As Range
    Dim med As Double
  
    Set visRange = UserRange.SpecialCells(xlCellTypeVisible)
  
        med = WorksheetFunction.Median(visRange)
  
    i = 0
    For Each cll In visRange
       myVar(i) = Abs(cll.Value - med) / med
    i = i + 1
    Next
  
    F_COD= WorksheetFunction.Average(myVar)
'    Debug.Print COD

End Function