#Name? error when using user defined function

StructEng1

New Member
Joined
Apr 4, 2023
Messages
36
Office Version
  1. 365
Platform
  1. Windows
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:
VBA Code:
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
 
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.
 
Upvote 0

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
What happens in the offending workbook if you do CTRL+ALT+SHIFT+F9?
 
Upvote 0
I have no issues. I can post your code into a new workbook (general code module) and the equation you posted works.
 
Upvote 0
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?
 
Upvote 0
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:

VBA Code:
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.
 
Upvote 0
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:

VBA Code:
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?
VBA Code:
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:
VBA Code:
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:
VBA Code:
Sub ReEnableEvents()
    Application.EnableEvents = True
End Sub
 
Upvote 0
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.
 
Upvote 0
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).
 
Upvote 0
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:

VBA Code:
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
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top