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?
 
First of all, this is a typo

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

try

Code:
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.
 
Upvote 0
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
    [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.

Rich (BB code):
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 :)
 
Last edited:
Upvote 0
Also i think your loop is not correct

See if this does what you need

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(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?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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 ?
 
Last edited:
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
By the way, Jason's code is more concise, easier to understand and probably more efficient.

M.
 
Upvote 0
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.
 
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