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

@StructEng1 Without a sample to investigate not sure you will get a fix for this. Something seems to be interfering with the Worksheet_Change Event not sure without more to go on

 

[StructEng1]

@StructEng1 Without a sample to investigate not sure you will get a fix for this. Something seems to be interfering with the Worksheet_Change Event not sure without more to go on

I will make a sample workbook with the codes and necessary inputs and upload it.

 

[CSmith]

Sounds good I will try helping once I have more info to look at . . .

 

[StructEng1]

Sorry for taking some time. I will try to get the sample ss as soon as I am able. Thank you for your patience

 

[CSmith]

No worries

 

[StructEng1]

Sorry for taking some time. I will try to get the sample ss as soon as I am able. Thank you for your patience

No worries

How can I attach my sample macro enabled spreadsheet on here?

 

[CSmith]

Can you post a link to it? From OneDrive or Google Drive maybe?

 

[StructEng1]

OneDrive

Sign in to your OneDrive cloud storage and Office Online.

onedrive.live.com

Please check this link and let me know if it works. This is the first time I've used it.

Cells C34, C40, C41, C43, and C44 are the inputs for this sample spreadsheet. When C34 is not selected as "DWB", I would like the spreadsheet to function normally without using macros. When cell C34 is selected as DWB, I want the macro to execute and a message box to appear indicating that the macro ran. After any changes to the inputs in cells C40, C41, C43, and C44, the same macro will be executed each time. In the original SS that I possess, I also have it respond to changes in cell C36.

 

[CSmith]

OneDrive

Sign in to your OneDrive cloud storage and Office Online.

onedrive.live.com

Please check this link and let me know if it works. This is the first time I've used it.

Cells C34, C40, C41, C43, and C44 are the inputs for this sample spreadsheet. When C34 is not selected as "DWB", I would like the spreadsheet to function normally without using macros. When cell C34 is selected as DWB, I want the macro to execute and a message box to appear indicating that the macro ran. After any changes to the inputs in cells C40, C41, C43, and C44, the same macro will be executed each time. In the original SS that I possess, I also have it respond to changes in cell C36.

Yes, I got the file fine I will look this over this evening and get back to you.

 

[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.  I would name the button and use btnName.Click event to trigger . . .
        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...

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