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

 

[StructEng1]

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

I tried using your code but I get an error at the Set rngWatchedCells line. The error is a Run-time Error '424': Object required.

Also I am not sure I understand the link you attached that shows the name manager and how you got that... and I am unsure of what you mean when you showed the following:

Sorry, I am very new to VBA and don't know much it along with programming....

Thank you for your help and your patience. I really appreciate it

 

[StructEng1]

This is with the sample worksheet

 

[CSmith]

This is with the sample worksheet

Yes, you are looking at a named range just click Formulas > Name Manager to add/edit them

 

[CSmith]

Yes, you are looking at a named range just click Formulas > Name Manager to add/edit them

This is with the sample worksheet

Try this one: MrExcel-1236244.xlsm

 

[StructEng1]

Try this one: MrExcel-1236244.xlsm

Wow I don't understand this. The same code works in the spreadsheet that you gave me but the one that I am using, it will not work. I am not understanding what Excel is doing. On whatever version that I work with with the code you provided me, it gives me a run time error saying I that object is required and it highlights the entire code shown below.

But with that spreadsheet with the same code it works perfectly!

 

[CSmith]

Wow I don't understand this. The same code works in the spreadsheet that you gave me but the one that I am using, it will not work. I am not understanding what Excel is doing. On whatever version that I work with with the code you provided me, it gives me a run time error saying I that object is required and it highlights the entire code shown below.
View attachment 91658
But with that spreadsheet with the same code it works perfectly!

You must provide the named ranges . . .
Look under the Formulas > Name Manager and create them / fix yours ...

Or Static ranges Soooo . . . [ConnectionType] = Range("$C$34")

 

[CSmith]

Wow I don't understand this. The same code works in the spreadsheet that you gave me but the one that I am using, it will not work. I am not understanding what Excel is doing. On whatever version that I work with with the code you provided me, it gives me a run time error saying I that object is required and it highlights the entire code shown below.
View attachment 91658
But with that spreadsheet with the same code it works perfectly!

Check this as well . . .

Named Range.mp4

drive.google.com