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]

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

Why?

 

[jbaich]

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

Unfortunately this still seems to be producing a #REF! error... not sure why

 

[jbaich]

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

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

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

Well this is interesting... changing the name as suggested with my original code (including the specialcells ect.) didn't work and using 6String's suggested code didn't work, but when i changed the name of 6String's suggested code to F_COD it produces the correct value on an unfiltered range, but the incorrect value on the filtered range... interestingly it does produce a number rather than an error... i have no idea why, but unfortunately it's still not the right number.

 

[jbaich]

Well this is interesting... changing the name as suggested with my original code (including the specialcells ect.) didn't work and using 6String's suggested code didn't work, but when i changed the name of 6String's suggested code to F_COD it produces the correct value on an unfiltered range, but the incorrect value on the filtered range... interestingly it does produce a number rather than an error... i have no idea why, but unfortunately it's still not the right number.

Well this is interesting... changing the name as suggested with my original code (including the specialcells ect.) didn't work and using 6String's suggested code didn't work, but when i changed the name of 6String's suggested code to F_COD it produces the correct value on an unfiltered range, but the incorrect value on the filtered range... interestingly it does produce a number rather than an error... i have no idea why, but unfortunately it's still not the right number.

I think i figured out why it was calculating the wrong value on the filtered range, my Median calculation wasn't set up to exclude the filtered rows. i've tried changing from WorksheetFunction.Median to WorksheetFunction.Aggregate(12,3,UserRange) and i think that might have just done the trick!!! Still not sure how renaming the function from COD to F_COD makes a difference, but it seems to! If someone could explain that to me i'd appreciate it.