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

OaklandJim

Well-known Member
Joined
Nov 29, 2018
Messages
855
Office Version
  1. 365
Platform
  1. Windows
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.


VBA Code:
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
 
Last edited:

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
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
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,224,965
Messages
6,182,041
Members
453,083
Latest member
Sayed Kassem

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top