UDF returning #NAME error.

[dreid1011]

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.

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?

 

[Marcelo Branco]

You are welcome

M.

 

[jasonb75]

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.

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

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.