A UDF that WAS working now returns #NAME? error.

[OaklandJim]

Team Mr. Excel

I am quite disappointed about this. I have an investment portfolio workbook that took a lot of effort to complete. For that workbook I wrote a pretty basic UDF that returns a weighted average of values excluding zero values. It also excludes hidden rows so the values are correct when filtering data.

It worked well before but now it returns a #NAME? error. Other UDFs work.

I tried renaming the UDF but that did not work. Other UDFs do work.

I sure hope that someone can assist with this frustrating situation. The UDF is below. I could provide the workbook if necessary.

BTW, if there is a way to do this with worksheet functions that'd be great. But could not imagine how to do that.

Have a great day.

Option Explicit

' ----------------------------------------------------------------
' Procedure Name: WeightedValuesNoZeros
' Purpose: Calculate the weighted average of a series of numbers, weighted
'          by the value of the respective investment holding.
' Procedure Kind: Function
' Procedure Access: Public
' Parameter prHoldingValues (Range): Holds the range containing investment holdings' value.
' Parameter prValuesToWeight (Range): Holds the values to be weighted.
' Author: Jim
' Date: 7/22/2024
' ----------------------------------------------------------------

Function WeightedValuesNoZeros(prHoldingValues As Range, prValuesToWeight As Range)

    Dim rCell As Range

    Dim iRow As Long

    Dim dHoldingValuesRowValue As Double

    Dim dTotalHoldingsValue As Double
   
    Dim dValueToWeight As Double
   
    Dim dTotalValuesToWeight As Double
   
    Dim iIndexHeaderRow As Long

'   Actual data begins in the row after data headers. Need to know that
'   row to adjust the rCell row value used to index the value in prValues
'   to get the value for dHoldingValuesRowValue.
    iIndexHeaderRow = [Portfolio].Range("Header_Index").Row

    For Each rCell In prValuesToWeight
   
'       Only process visible rows.
        If Not rCell.EntireRow.Hidden _
         Then
           
'           Row # to index the range prValues, where the holding's value is located.
            iRow = rCell.Row - iIndexHeaderRow
           
'           Exclude non-numeric values and values = 0.
            If IsNumeric(rCell.Value) And rCell.Value > 0.000001 _
             Then

'               Get holding value for current row in range prValues.
                dHoldingValuesRowValue = prHoldingValues.Cells(iRow, 1).Value
               
'               Add the holding value for the current row being processed to
'               the var that sums up those values.
                dTotalHoldingsValue = dTotalHoldingsValue + dHoldingValuesRowValue
                           
'               Get the row value for the value to weight.
                dValueToWeight = rCell.Value
               
'               Add the weighted value to the var used to sum the weighted values.
                WeightedValuesNoZeros = WeightedValuesNoZeros + (dValueToWeight * dHoldingValuesRowValue)
           
            End If

        End If

    Next rCell
   
'   Return the weighted value.
    WeightedValuesNoZeros = WeightedValuesNoZeros / dTotalHoldingsValue

End Function

 

[OaklandJim]

I'd still like to know why I was getting that #Name? error for a UDF, but I build a new UDF that does what the first one does and that worked. It seems that somewhow Excel does not recognize the name of the original UDF

 

[RoryA]

If the module name was the same as the function name, you would get a #NAME? error.

Normally you'd use something like SUMPRODUCT to produce weighted values; I can't see from a quick glance why you couldn't do that here. To avoid hidden rows you can either use a helper column with SUBTOTAL/AGGREGATE formulas in it, or given that you have 365 you could calculate those dynamically in a lambda.

 

[OaklandJim]

I did not know that module name and UDF with the same name might conflict. Thank you for helping.

It is not clear to me how helper columns would assist. I need to exclude cells in rows that are hidden and that are not zero values.

I've not used lambda. Can I use that to exclude hidden rows and rows containing zero. Might you give a nudge?