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?

 

[dreid1011]

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.

Ok, fixed that one, thanks.

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.

Ok, I got the breakpoint to work. I was playing with renaming my modules while writing this UDF and named the module the same as the Function for ease of identification. Renaming the module has allowed the breakpoint to do it's job.

 

[dreid1011]

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)

See post above, the function is now running. However, returning a different value than expected. Thanks for your help

 

[dreid1011]

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.

That worked to fix the incorrect value. What is the significance of the -1 there?

 

[jasonb75]

My comment about making the function volatile still applies.

Try entering your formula, then changing one of the values that should be calculated. Then check the result.

 

[Marcelo Branco]

That worked to fix the incorrect value. What is the significance of the -1 there?

The loop should get only the cells inside the range. If we do not use -1, in the last step of the loop (when i = lastCol) a cell outside the range will come in the calculations (I think this is not desired).

M.

 

[dreid1011]

My comment about making the function volatile still applies.

Try entering your formula, then changing one of the values that should be calculated. Then check the result.

The result did not auto update, but otherwise the result is the same once updated. Is this what Volatile is for?

Edit: Just looked it up, thanks for the suggestion.

The loop should get only the cells inside the range. If we do not use -1, in the last step of the loop (when i = lastCol) a cell outside the range will come in the calculations (I think this is not desired).

M.

Yes, that is not desired, thanks. Could I circumvent the use of the -1 there in the loop if I had set lastCol with ...End(xlToLeft).Column - 1 ?

 

[jasonb75]

Yes, making the function volatile would cause the formula to function to calculate normally, however it can have undesired side effects.

Because your function refers to 'ActiveSheet' anything that causes excel to recalculate when a different sheet is active will cause incorrect results.

Can you work with ColCalcsV2 that I provided you with earlier, which eliminates these problems, or do you need the range used in the function to be dynamic?

 

[Marcelo Branco]

Yes, that is not desired, thanks. Could I circumvent the use of the -1 there in the loop if I had set lastCol with ...End(xlToLeft).Column - 1 ?

It works, but I think the other way is clearer and easier to understand - the variable lastCol means (is equals to) the last column of the range.

M.

 

[Marcelo Branco]

By the way, Jason's code is more concise, easier to understand and probably more efficient.

M.

 

[dreid1011]

Of course it is. But this isn't for anything but my understanding, so efficiency is trivial. Otherwise, I was only writing what made sense to me at the time. Thanks for the help.