#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

 

[StructEng1]

What does this procedure do?

WeldCalc_Click

Note that procedures like "Worksheet_Change" and "Worksheet_Selection" change are automated procedures which run automatically when any cell is manually updated or selected. You need to be VERY careful with this procedures. If other lines in your code change or select cells, your code can cause these procedures to fire again. You can actually get caught in an infinite loop!!!

One thing that people often do is temporarily disable events when certain parts of the code is running. This prevents the code from calling itself and getting caught in a loop.
You could do that by adding lines to temporarily suspend automated events while certain procedures or steps are running, like this:

Application.EnableEvents = False
WeldCalc_Click
Application.EnableEvents = True

Note that if your code stops or errors out before getting to the "Application.EnableEvents = True" line, events will not be turned back on, and these automated procedures will not work.
If that ever happens, you can manually turn them back on by manually running a simple macro like this:

Sub ReEnableEvents()
    Application.EnableEvents = True
End Sub

This procedure basically calls an existing macro that clicks a button and performs calculations to update the values in some cells once the procedure detects a change in value in cells C36, C39, C40, C41, or C43.

I have not tried your suggestion yet but I will be trying it now. Before I made a new workbook, I tried to write several versions of the procedure you inquired about above because I kept getting the "Code execution has been interrupted" error. It would happen very inconsistently and often. I also tried in other versions of my code the Application.EnableEvents = True or False but I got the same issue. I literally wrote 10 different versions in copies of the original workbook and they all had the same problems. This Worksheet_Change is giving me lots of headache but I can't think of any other way to do what I have to do without using it. And when I made the new workbook without the UDF, it worked fine and then now that it is in there, I am getting this error but these two have nothing to do with each other....

 

[StructEng1]

That is the infamous VBA phantom breakpoint problem (google it, there has been a lot of discussions about various workarounds over the years).

I saw many feeds about that and I tried all of the solutions but none of it solved my problem.........

 

[CSmith]

I saw many feeds about that and I tried all of the solutions but none of it solved my problem.........

Simple mismatch error from the code break spot you listed. Try adding .Value to the if statement . . .

 

[StructEng1]

Simple mismatch error from the code break spot you listed. Try adding .Value to the if statement . . .

I tried that and unfortunately it still gives me the same error...

 

[CSmith]

I tried that and unfortunately it still gives me the same error...

Can you show the code block again? and the error?

 

[rlv01]

I saw many feeds about that and I tried all of the solutions but none of it solved my problem.........

That's because there is no definitive solution to the phantom breakpoint problem.

 

[StructEng1]

Can you show the code block again? and the error?

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

 

[StructEng1]

That's because there is no definitive solution to the phantom breakpoint problem.

That seems like a major issue in excel that needs to be fixed if this is the case because I think this has been happening to many people for many years....

 

[rlv01]

That seems like a major issue in excel that needs to be fixed if this is the case because I think this has been happening to many people for many years....

You are far from being the first person to make such a comment.

 

[StructEng1]

You are far from being the first person to make such a comment.

Do you know if they are working on this issue? It is very unfortunate that the Ctrl Break/Pause solution does not work for me but has worked for many people.... quite frustrating. 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.