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

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

 

[Joe4]

OK, that was really weird!
Even though I copied your code into a General Module, for some reason, it was also trying to put a copy in one of my sheet modules (and it did that on BOTH my home and work computers!).
I have never seen anything like that before!

In any event, I deleted the copy in the Sheet module, and then it compiled without an issue.
I then tried to use your formula in a cell, and I get the #VALUE! error, which is OK, because I don't have all your data. That just indicates a calculation error, which is to be expected with no data.

The #NAME? seems to suggest that it cannot find the UDF in your code.
Are you sure that you have VBA code enabled on these new workbooks?
Try running this simple test to make sure:

Sub Test()
    MsgBox "VBA code works!"
End Sub

 

[StructEng1]

OK, that was really weird!
Even though I copied your code into a General Module, for some reason, it was also trying to put a copy in one of my sheet modules (and it did that on BOTH my home and work computers!).
I have never seen anything like that before!

In any event, I deleted the copy in the Sheet module, and then it compiled without an issue.
I then tried to use your formula in a cell, and I get the #VALUE! error, which is OK, because I don't have all your data. That just indicates a calculation error, which is to be expected with no data.

The #NAME? seems to suggest that it cannot find the UDF in your code.
Are you sure that you have VBA code enabled on these new workbooks?
Try running this simple test to make sure:

Sub Test()
    MsgBox "VBA code works!"
End Sub

Oh yeah that is very strange! Weird..

Yes, I saved my workbooks as macro enabled workbook and all of my other macros work including the test

 

[StructEng1]

Oh yeah that is very strange! Weird..

Yes, I saved my workbooks as macro enabled workbook and all of my other macros work including the test

Also for the inputs for the function, you can just put any numbers and just have this part of the equation in your cell.

boltcoefficient(G64,1,G65,0,(G83+C83/2),DEGREES(ATAN(C64/C63)))