#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

 

[Joe4]

OK, when I do that I get a value and no more error. So it seems to work for me.

Have you tried the following?
Instead of creating a new workbooks and copying everything over, how about just making a copy of your original working workbook, and just remove whatever needs to be removed.

 

[MARK858]

What happens in the offending workbook if you do CTRL+ALT+SHIFT+F9?

 

[rlv01]

I have no issues. I can post your code into a new workbook (general code module) and the equation you posted works.

 

[rlv01]

As as experiment, try making a copy of your UDF and rename it slightly, e.g. Function BoltCoefficient --> Function BoltCoefficient2 . Does it recognize it then?

 

[StructEng1]

I was able to get it to work by deleting that module and pasting it into a new module. It is so strange. However now, I am in a situation where it fixed one problem and now is giving me another problem (a problem that made me make a new workbook after long diagnostics trying to figure out if creating a new workbook would fix the problem). For some reason, now that I am able to get this problem to work, it is giving me a "Code execution has been interrupted error" when I make changes to certain values. If I hit "Continue" it continues to run with no problems. If I click "Debug" it shows me the code that it is having issues with:

Private Sub Worksheet_Change(ByVal Target As Range)

If Range("C34") = "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
        WeldCalc_Click
    End If
        
End If
   

End Sub

It is reacting with the very first line of code which makes no sense to me. "Private Sub Worksheet_Change(ByVal Target As Range).

I find it interesting because the UDF code has nothing to do with this specific code and from this code from running when excel detects a change in the certain cells that is listed in the code.

 

[StructEng1]

What happens in the offending workbook if you do CTRL+ALT+SHIFT+F9?

What does CTRL + ALT +SHIFT +F9 do??

 

[Joe4]

I was able to get it to work by deleting that module and pasting it into a new module. It is so strange. However now, I am in a situation where it fixed one problem and now is giving me another problem (a problem that made me make a new workbook after long diagnostics trying to figure out if creating a new workbook would fix the problem). For some reason, now that I am able to get this problem to work, it is giving me a "Code execution has been interrupted error" when I make changes to certain values. If I hit "Continue" it continues to run with no problems. If I click "Debug" it shows me the code that it is having issues with:

Private Sub Worksheet_Change(ByVal Target As Range)

If Range("C34") = "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
        WeldCalc_Click
    End If
       
End If
  

End Sub

It is reacting with the very first line of code which makes no sense to me. "Private Sub Worksheet_Change(ByVal Target As Range).

I find it interesting because the UDF code has nothing to do with this specific code and from this code from running when excel detects a change in the certain cells that is listed in the code.

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

 

[Joe4]

By the way, have you given any thought to this suggestion I made yesterday?

Have you tried the following?
Instead of creating a new workbooks and copying everything over, how about just making a copy of your original working workbook, and just remove whatever needs to be removed.

If you have a copy that works, I would use that as my starting block instead of trying to create new workbooks from scratch!
Maybe you are missing something or some setting in your new creations.

 

[rlv01]

it is giving me a "Code execution has been interrupted error" when I make changes to certain values. If I hit "Continue" it continues to run with no problems. If I click "Debug" it shows me the code that it is having issues with:

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

 

[CSmith]

I was able to get it to work by deleting that module and pasting it into a new module. It is so strange. However now, I am in a situation where it fixed one problem and now is giving me another problem (a problem that made me make a new workbook after long diagnostics trying to figure out if creating a new workbook would fix the problem). For some reason, now that I am able to get this problem to work, it is giving me a "Code execution has been interrupted error" when I make changes to certain values. If I hit "Continue" it continues to run with no problems. If I click "Debug" it shows me the code that it is having issues with:

Private Sub Worksheet_Change(ByVal Target As Range)

If Range("C34") = "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
        WeldCalc_Click
    End If
       
End If
  

End Sub

It is reacting with the very first line of code which makes no sense to me. "Private Sub Worksheet_Change(ByVal Target As Range).

I find it interesting because the UDF code has nothing to do with this specific code and from this code from running when excel detects a change in the certain cells that is listed in the code.

I think you want:

If Range("C34").Value = "DWB" Then