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?

 

[mikerickson]

What is the formula that you enter in a cell that returns that error?

 

[Rick Rothstein]

A #NAME? error usually means a function name used in the formula is spelled wrong, not that there is a problem in the code itself. Another possibility is that you have a piece of unquoted text that Excel is trying to interpret as a Defined Name. So, did you spell your function name correctly? If so, did you put quotes around its argument (needed because you declared the argument as a String).

As for being able to debug a UDF, put a break point on the line of code you want to start debugging at, then go to the worksheet and select the cell with that UDF in it that is giving you trouble, then press F2 to go into edit mode and then hit the Enter key. You will then be able to step through your code one line at a time using the F8 key.

 

[dreid1011]

What is the formula that you enter in a cell that returns that error?

I am entering =ColCalcs(A1)

A #NAME? error usually means a function name used in the formula is spelled wrong, not that there is a problem in the code itself.

As for being able to debug a UDF, put a break point on the line of code you want to start debugging at, then go to the worksheet and select the cell with that UDF in it that is giving you trouble, then press F2 to go into edit mode and then hit the Enter key. You will then be able to step through your code one line at a time using the F8 key.

I will give this a try, thanks.

 

[Rick Rothstein]

I am entering =ColCalcs(A1)

I will give this a try, thanks.

Your are too fast... I edited my response and your quote indicates you did not see the update. Your problem is you need quotes around A1 because you declared the argument to the ColCalcs function as a String (and handle it as a String within your code).

=ColCalcs("A1")

 

[dreid1011]

Ok, I will add the quotes and see what happens. I originally had it declared as a Range, thus no quotes in the cell formula.

Either way, I did try a breakpoint too and it did not seem to do anything when I pressed F2 then Enter in the cell with the formula.

 

[Marcelo Branco]

First of all, this is a typo

lastCol = ws.Cells(cRow, Columns.Count).End([COLOR=#ff0000]xlLeft[/COLOR]).Column

try

lastCol = ws.Cells(cRow, Columns.Count).End([COLOR=#0000ff]xlToLeft[/COLOR]).Column

M.

 

[Rick Rothstein]

Either way, I did try a breakpoint too and it did not seem to do anything when I pressed F2 then Enter in the cell with the formula.

That is because your code never started to execute... the #NAME? error blocked that from happening because Excel did not know how to start the function... after you add the quotes, the function should execute and the breakpoint would be activated.

 

[Marcelo Branco]

Also i think your loop is not correct

See if this does what you need

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(xlToLeft).Column
        
    Dim i As Long
    Dim cValue As Double
    cValue = 0
    
[COLOR=#0000ff]    For i = cCol To lastCol - 1[/COLOR]
[COLOR=#0000ff]        cValue = cValue + (((ws.Cells(cRow, i).Value + ws.Cells(cRow, i + 1).Value) / 2) ^ 2)[/COLOR]
[COLOR=#0000ff]    Next[/COLOR]
[COLOR=#0000ff]    [/COLOR]
    ColCalcs = cValue
End Function

M.

 

[jasonb75]

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
    [COLOR="#FF0000"]lastCol = ws.Cells(cRow, Columns.Count).End(xlLeft).Column
[/COLOR]    
    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

Because of the highlighted line, you will also need to make the function volatile. Although I think that this could be the start of the problem. When I debug the function the code exits after that line, nothing else happens, which is why it gives an error.

This method works, or we can try to fix your version.

Function ColCalcsV2(ByVal cRange As Range) As Double
Dim i As Long, tmp As Double

For i = 1 To cRange.Cells.Count - 1
    tmp = tmp + (((cRange(i) + cRange(i + 1)) / 2) ^ 2)
Next
    ColCalcsV2 = tmp
End Function

=ColCalcsV2(A1:A10)