#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]

Here is the updated macro code:

Private Sub Worksheet_Change(ByVal Target As Range)
    ' Detect cell change and trigger WeldCalc Macro if necessary
    ' Allocate variable for our trigger range
    Dim rngWatchedCells As Range
 
    ' Using named cells to make this more readable and dynamic for adjustments.
    Set rngWatchedCells = Union( _
          [ConnectionType] _
        , [AngleSize] _
        , [BoltsPerColumn] _
        , [VerticalPitch] _
        , [VerticalEdgeDistance] _
        )
   
    ' See if the sheet changed cell is in our target range and DWB is the connection type . . .
    If Not Intersect(Target, rngWatchedCells) Is Nothing _
    And [ConnectionType].Value = "DWB" Then
        Application.EnableEvents = False 'Don't interrupt our macro
        ' WeldCalc_Click  ' This will not currently work as you have quite a few broken references in this worksheet, but should in the original
        '                 ' You may need to bring in the other sheets instead of having broken links to them. 
        Debug.Print "Triggered by Cell:" & Target.Address  ' Let me know this triggered
    End If
 
    Application.EnableEvents = True ' Proceed with normal events
 
    'Cleanup memory objects
    Set rngWatchedCells = Nothing
End Sub

Only thing I did here is used named ranges to ease code readability. You should validate your other named ranges as most show as:

=#REF!#REF!

This is most likely the reason your other code is having issue running...

View attachment 91561

Copy of Sample SS.xlsm
	B	C	D
33	Connection Details
34	Type ?	DWB
35	Safety Connection ?	No
36	Angle Size ?	Test sample
37
38	Bolts:
39	Bolt Size?	3/4" (19 mm)
40	Bolts per Vertical Columns =	4
41	Vertical Pitch =	55	mm
42	Gauge =	-	mm
43	Vertical Edge Distance on Angle =	35	mm
44	Horizontal Edge Distance on Beam =	35	mm
45	Center Bolts on Beam ?	No
Angle Shear Connections

Your cell c119 seems to be causing the error also not the worksheet change event . . .

 

[CSmith]

Change the text color to match the background if you want to hide E119 . . .
This seems to keep the error from happening:

Copy of Sample SS.xlsm
	B	C	D	E
117	1. Shear Resistance
118	Vr/Bolt (kN/Bolt)	C of Bolt Group	# Angles
119	79.00	3.06	2	3.056111208
120	Vr =	N/A	kN	N/A
Angle Shear Connections

Cell Formulas
Range	Formula
B119	B119	=G68
D119	D119	=IF(OR(G63="SBB",G63="SWB"),1,2)
C119	C119	=IF(G63="DBB",G64*D119-IF(G72="Yes",1,0),E119)
C120	C120	=IF(OR(C34="SWB",C34="DWB"),"N/A",B119*C119)
E119	E119	=boltcoefficient(G64,1,G65,0,(G83+C83/2),DEGREES(ATAN(C64/C63)))
E120	E120	=IF(C120="N/A",C120,C63/C120)

Named Ranges
Name	Refers To	Cells
ConnectionType	='Angle Shear Connections'!$C$34	C120

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E120	Cell Value	="N/A"	text	NO
E120	Cell Value	<0.95	text	NO
E120	Cell Value	>=0.95	text	NO

 

[CSmith]

I am running into an #Name? error

This usually comes from a Named Range error/reference which I did find in your worksheet see above post and those are the most likely cause of your debug triggering during the worksheet change event too.

 

[Joe4]

Did you copy the VBA code containing the UDF to the new workbook?
If so, did you copy/paste this UDF VBA code to a new, general module, or did you place it in one of the sheet or "ThisWorkbook" module?
You want to put it in a new general module in your workbook, if you want it to be accessible from every sheet.
If you put it in the "Sheet1" module, for example, and try to use this UDF, you would get the #NAME? error.

 

[StructEng1]

Did you copy the VBA code containing the UDF to the new workbook?
If so, did you copy/paste this UDF VBA code to a new, general module, or did you place it in one of the sheet or "ThisWorkbook" module?
You want to put it in a new general module in your workbook, if you want it to be accessible from every sheet.
If you put it in the "Sheet1" module, for example, and try to use this UDF, you would get the #NAME? error.

Yes, I copied the VBA code containing the UDF to the new workbook in a new module. I didn't put the code in any of the workbook sheets of "ThisWorkbook" but put it in a separate module

 

[Joe4]

I cannot even compile your code, as you have it written.
I get an error on the "Type BoltInfo" line that looks like this:

Are you able to even compile your code?

 

[StructEng1]

I cannot even compile your code, as you have it written.
I get an error on the "Type BoltInfo" line that looks like this:

View attachment 90816

Are you able to even compile your code?

Really? Mine compiles no problem. Its weird because I just created another workbook and did the same thing and it works but it doesn't work for the first copy I am working on....but I did the same thing....

 

[Joe4]

Really? Mine compiles no problem. Its weird because I just created another workbook and did the same thing and it works but it doesn't work for the first copy I am working on....but I did the same thing....

Yep! You can see the error message! I didn't make that up!
I wonder if you had to select some additional libraries that I do not have selected.

So if it works on this new copy, then I trust that you are good now and can move forward?

 

[StructEng1]

Yep! You can see the error message! I didn't make that up!
I wonder if you had to select some additional libraries that I do not have selected.

So if it works on this new copy, then I trust that you are good now and can move forward?

Nope there are no additional libraries that are needed. And I deleted that test workbook that worked without saving it and tried to make another one and it doesn't work again . So back to square 1 haha. I don't understand why it doesn't work or sometimes work..