StructEng1
New Member
- Joined
- Apr 4, 2023
- Messages
- 36
- Office Version
- 365
- Platform
- 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:
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
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 FunctionThe 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
