#Name? error when using user defined function

[StructEng1]

Hello,

I am creating a copy of an existing spreadsheet that has a bunch of macros and user defined functions. I am recreating the spreadsheet by starting a new workbook and copy and pasting the sheets into the new workbook and reorganizing the vba codes in the new workbook. As I am doing this, I am running into an #Name? error on one of the cells that uses a user defined function. I don't understand what the problem is because everything (the inputs, the sheet, the vba code) is all the same and it works on the original workbook and not the clone copy. I tried many different things but I can't seem to get it to work and I am not understanding what the problem is. Could you please help me?

Below is the code for the user defined function:

Option Explicit

Type BoltInfo
   Dv As Double
   Dh As Double
End Type
Function BoltCoefficient(Bolt_Row As Integer, Bolt_Column As Integer, Row_Spacing As Double, Column_Spacing As Double, Eccentricity As Double, Optional Rotation As Double = 0)
    
    Dim i As Integer, k As Integer, n As Integer
    Dim mP As Double, vP As Double, Ro As Double
    Dim Mo As Double, Fy As Double
    Dim xi As Double, yi As Double
    Dim ri As Double, x1 As Double
    Dim y1 As Double, Rot As Double
    Dim Rn As Double, iRn As Double
    Dim Rv As Integer, Rh As Integer
    Dim Sh As Double, Sv As Double
    Dim Ec As Double
    Dim Delta As Double, rmax As Double
    Dim BoltLoc() As BoltInfo
    Dim Stp As Boolean
    Dim j As Double
    Dim FACTOR As Double
    
    Rv = Bolt_Row
    Rh = Bolt_Column
    Sv = Row_Spacing
    Sh = Column_Spacing
    
    ReDim BoltLoc(Rv * Rh - 1)
    
    On Error Resume Next
    
    Rot = Rotation * 3.14159265358979 / 180
    Ec = Eccentricity * Cos(Rot)
    
    If Ec = 0 Then GoTo ForcedExit
    
    n = 0
    For i = 0 To Rv - 1
        For k = 0 To Rh - 1
            y1 = (i * Sv) - (Rv - 1) * Sv / 2
            x1 = (k * Sh) - (Rh - 1) * Sh / 2
            With BoltLoc(n)
                .Dv = x1 * Sin(Rot) + y1 * Cos(Rot) '''Rotate Vertical Coordinate
                .Dh = x1 * Cos(Rot) - y1 * Sin(Rot) '''Rotate Horizontal Coordinate
            End With
            n = n + 1
        Next
    Next
    
    Rn = 74 * (1 - Exp(-10 * 0.34)) ^ 0.55
    Ro = 0: Stp = False
    n = 0
    Do While Stp = False
        rmax = 0
        For i = 0 To Rv * Rh - 1
            xi = BoltLoc(i).Dh + Ro
            yi = BoltLoc(i).Dv
            rmax = Application.WorksheetFunction.Max(rmax, Sqr(xi ^ 2 + yi ^ 2))
        Next
        
        Mo = 0:  Fy = 0
        mP = 0:  vP = 0
        j = 0
        For i = 0 To Rv * Rh - 1
            xi = BoltLoc(i).Dh + Ro
            yi = BoltLoc(i).Dv
            
            ri = Sqr(xi ^ 2 + yi ^ 2): If ri = 0 Then ri = 0.00001
            Delta = 0.34 * ri / rmax
            iRn = 74 * (1 - Exp(-10 * Delta)) ^ 0.55
            Mo = Mo + (iRn / Rn) * ri                           '''Moment
            Fy = Fy + (iRn / Rn) * Abs(xi / ri) * Sgn(xi)       '''Vertical
            j = j + ri ^ 2
        Next
        mP = Mo / (Abs(Ec) + Ro)
        vP = Fy
        Stp = Abs(mP - vP) <= 0.0001
        FACTOR = j / (Rv * Rh * Mo)
        FACTOR = FACTOR / (1 + n / 5000 * 2.5)
        Ro = Ro + (mP - vP) * FACTOR
        DoEvents
        If n = 5000 Then GoTo CantFind
        n = n + 1
    Loop
    
    BoltCoefficient = (mP + vP) / 2
    SendKeys "{esc}"
    Exit Function

CantFind:
    BoltCoefficient = "Cant Find Solution"
    SendKeys "{esc}"
    Exit Function
    
ForcedExit:
    BoltCoefficient = Rv * Rh
    SendKeys "{esc}"
End Function

The formula for the cell that calls the user defined function is as follows:

=IF(G63="DBB",G64*D119-IF(G72="Yes",1,0),boltcoefficient(G64,1,G65,0,(G83+C83/2),DEGREES(ATAN(C64/C63))))

Thank you for your help

 

[CSmith]

Private Sub Worksheet_Change(ByVal Target As Range)

If Range("C34").Value = "DWB" Then
   
    If Target.Address = "$C$36" Or Target.Address = "$C$39" Or Target.Address = "$C$40" Or Target.Address = "$C$41" Or Target.Address = "$C$43" Or Target.Address = "$C$44" Then
       
        Application.EnableEvents = False
        WeldCalc_Click
        Application.EnableEvents = True
       
    End If
       
End If
  

End Sub

View attachment 90875

Please show screenshot with the highlighted code line as this looks like a causal break not code error break . . .

 

[rlv01]

Do you know if they are working on this issue?

Unlikely. This has been a problem for a fair number of years now, and many people have complained. This has been on my radar for at least six years.

It is very unfortunate that the Ctrl Break/Pause solution does not work for me but has worked for many people.... quite frustrating.

There are a number of so-called workarounds; but in my experience the problem always returns. The best one I have found is to 1) Use a code cleaner, 2)Export all forms and code modules to disk. 3) Save as an .xlsx file to strip out all macros. 4) Close xlsx file 5) Reopen xlsx file. 6) Save as xlsm, 7)Reimport all forms and code modules. 8) Final save. But you want to have most of your debugging done before that. That will often 'fix' the problem for day or a week, but in my case it always returns even on 'cleaned' workbooks that I have not set any new breakpoints for.

I wonder if there is a way to overwrite that error and have the program to click continue when such error appears when running the program.

If you find one, let us know. But note that the "code execution has been interrupted" message box is not actually a runtime error and I doubt you can trap it that way.

 

[MARK858]

But note that the "code execution has been interrupted" message box is not actually a runtime error and I doubt you can trap it that way

He should be able to trap it with Error 18 which is normally for User interrupt occurred

 

[StructEng1]

Please show screenshot with the highlighted code line as this looks like a causal break not code error break . . .

This is a different code that does the same thing but the error is at the same location.

 

[StructEng1]

He should be able to trap it with Error 18 which is normally for User interrupt occurred

How do I use this Error 18??

 

[StructEng1]

Unlikely. This has been a problem for a fair number of years now, and many people have complained. This has been on my radar for at least six years.


There are a number of so-called workarounds; but in my experience the problem always returns. The best one I have found is to 1) Use a code cleaner, 2)Export all forms and code modules to disk. 3) Save as an .xlsx file to strip out all macros. 4) Close xlsx file 5) Reopen xlsx file. 6) Save as xlsm, 7)Reimport all forms and code modules. 8) Final save. But you want to have most of your debugging done before that. That will often 'fix' the problem for day or a week, but in my case it always returns even on 'cleaned' workbooks that I have not set any new breakpoints for.


If you find one, let us know. But note that the "code execution has been interrupted" message box is not actually a runtime error and I doubt you can trap it that way.

Oh wow that is quite the process.... interesting.

 

[CSmith]

View attachment 90895

This is a different code that does the same thing but the error is at the same location.

See what this does:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim MyRange As Range
    Set MyRange = Application.Union(Range("$C$36"), Range("$C$39"), Range("$C$40"), Range("$C$41"), Range("$C$43"))   'and any other areas you want.
    If (Not Application.Intersect(Target, MyRange) Is Nothing) And Me.Range("$C$34").Value = "DWB" Then ' Code should be attached to proper worksheet  
        Application.EnableEvents = False
        WeldCalc_Click
        Application.EnableEvents = True
    End If
    Set MyRange = Nothing
End Sub

 

[StructEng1]

See what this does:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim MyRange As Range
    Set MyRange = Application.Union(Range("$C$36"), Range("$C$39"), Range("$C$40"), Range("$C$41"), Range("$C$43"))   'and any other areas you want.
    If (Not Application.Intersect(Target, MyRange) Is Nothing) And Me.Range("$C$34").Value = "DWB" Then ' Code should be attached to proper worksheet 
        Application.EnableEvents = False
        WeldCalc_Click
        Application.EnableEvents = True
    End If
    Set MyRange = Nothing
End Sub

I get the same "Code execution has been interrupted" message box...

 

[CSmith]

I get the same "Code execution has been interrupted" message box...

Where is the code block placed?

If this is placed in the correct location and you've recreated this file from scratch it would seem something else is going on like a piece of code is missing elsewhere or in the wrong location.

 

[StructEng1]

Where is the code block placed?

If this is placed in the correct location and you've recreated this file from scratch it would seem something else is going on like a piece of code is missing elsewhere or in the wrong location.

This code is placed in the sheet object in which all the inputs, and calculations are done in the excel workbook. Every other macro in this workbook including UDF are put into each separate modules. Interesting thing is that when my UDF code is not there or not working, the Worksheet_Change code in the Sheet object works perfectly fine without and "Code execution has been interrupted" error message. When the Worksheet_Change code is not there, the UDF code runs perfectly fine without any "Code execution has been interrupted" error. If both of these codes are in the sheet and working properly, the "Code execution has been interrupted" error occurs at random moments when changing any of the cells listed in the Worksheet_Change code. I can input the same input in the same cell at different times and one time would be find and the other time would not be fine. I don't understand this