UDF returning #NAME error.

dreid1011

Well-known Member
Joined
Jun 4, 2015
Messages
3,645
Office Version
  1. 365
Platform
  1. Windows
Good morning,

I have a UDF that is returning a #NAME error, and I cannot figure out why.

This is what I am doing(or trying):

Averaging cells 1 and 2, then squaring it. Averaging cells 2 and 3, then squaring it. 3 and 4, 4 and 5, etc and summing all of them until the end of the row.
Code:
Function ColCalcs(ByVal cRange As String) As Double
    Dim ws As Worksheet
    Set ws = ActiveSheet
    
    Dim cRow As Long
    Dim cCol As Long
    cRow = ws.Range(cRange).Row
    cCol = ws.Range(cRange).Column
    
    Dim lastCol As Long
    lastCol = ws.Cells(cRow, Columns.Count).End(xlLeft).Column
    
    Dim i As Long
    i = cCol
    
    Dim cValue As Double
    cValue = 0
    
    For i = i To lastCol
        cValue = cValue + (((ws.Cells(cRow, cCol).Value + ws.Cells(cRow, cCol + 1).Value) / 2) ^ 2)
        cCol = cCol + 1
    Next
    
    ColCalcs = cValue
End Function

Being that it's a UDF, there is no help on the error, and I can't seem to use F8 to step through it in the VBA editor. Any thoughts?
 
But this isn't for anything but my understanding, so efficiency is trivial.

In which case, have a look at these changes. This calculates anything to the right of the single cell specified as cRange, but will not error when a different sheet is active, as I suggested earlier, this version is volatile.

Code:
Option Explicit
Function ColCalcs(ByVal cRange As Range) As Double
    Application.Volatile
    Dim ws As Worksheet
    Set ws = cRange.Parent
    
    Dim cRow As Long
    Dim cCol As Long
    cRow = cRange.Row
    cCol = cRange.Column
       
    Dim lastCol As Long
    lastCol = ws.Cells(cRow, Columns.Count).End(xlToLeft).Column
        
    Dim i As Long
    Dim cValue As Double
    cValue = 0
    
    For i = cCol To lastCol - 1
        cValue = cValue + (((ws.Cells(cRow, i).Value + ws.Cells(cRow, i + 1).Value) / 2) ^ 2)
    Next

    ColCalcs = cValue
End Function

An additional change below incorporates both methods, if cRange is only a single cell then the function becomes volatile and the rest of the row is used. If cRange is more than one cell then the function becomes non-volatile and only the specified range is used and the rest of the row is ignored

Code:
Option Explicit
Function ColCalcs(ByVal cRange As Range) As Double
    Dim ws As Worksheet
    Set ws = cRange.Parent
    
    Dim cRow As Long
    Dim cCol As Long
    cRow = cRange.Row
    cCol = cRange.Column
       
    Dim lastCol As Long
    If cRange.Cells.Count = 1 Then
        lastCol = ws.Cells(cRow, Columns.Count).End(xlToLeft).Column
         Application.Volatile
    Else
        lastCol = cRange.Cells.Count
        Application.Volatile (False)
    End If
    
    Dim i As Long
    Dim cValue As Double
    cValue = 0
    
    For i = cCol To lastCol - 1
        cValue = cValue + (((ws.Cells(cRow, i).Value + ws.Cells(cRow, i + 1).Value) / 2) ^ 2)
    Next

    ColCalcs = cValue
End Function

A couple of slight changes can make a significant difference to functionality.
 
Upvote 0

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