jcfields
10-28-2008, 12:17 PM
Hello world :)
I've been lurking here for awhile, but this'll be my first post. I haven't been able to find another post with the exact same problem I seem to be having, so sorry if this has been answered elsewhere.
I'll preface this by stating that I'm very new to VBA, and this problem is probably something obvious for a pro.
Anyway, in the last statement of my code (right before "end function"), I can't get my UDF to output the 'ToothAngle' variable to the specified cell (or any cell anywhere in the workbook for that matter). Can anyone see what I might be doing wrong? Thanks in advance for the help.
BTW, "rad()" and "deg()" are private functions defined elsewhere in the same module.
Best,
Jeremy
Function ActualCutterAngle(NumberOfTeeth, ConeHalfAngle, CutterInclineAngle, CuttingStructure) As Double
Dim ToothAngle_ As Double, ConeHalfAngle_ As Double, AngleBetweenTeeth_ As Double, Ex As Double, Ey As Double, Ez As Double, E_x As Double, E_y As Double, E_z As Double, Fx As Double, Fy As Double, Fz As Double, E_times_dotF As Double, Length_ As Double, EffCutterAngle_ As Double, ActualCutterAngle_ As Double, IntActualCutterAngle_ As Double, Diff As Double, DesiredCutterAngle As Double, n As Single, delta As Single
'
ConeHalfAngle_ = Rad(ConeHalfAngle)
AngleBetweenTeeth_ = Rad(360 / NumberOfTeeth)
'
If CuttingStructure = 11 Then
ToothAngle = 42
ElseIf CuttingStructure = 12 Then
ToothAngle = 43
ElseIf CuttingStructure = 13 Then
ToothAngle = 44
ElseIf CuttingStructure = 14 Then
ToothAngle = 45
ElseIf CuttingStructure = 21 Then
ToothAngle = 46
ElseIf CuttingStructure = 22 Then
ToothAngle = 47
ElseIf CuttingStructure = 23 Then
ToothAngle = 48
ElseIf CuttingStructure = 24 Then
ToothAngle = 49
ElseIf CuttingStructure = 31 Then
ToothAngle = 50
ElseIf CuttingStructure = 32 Then
ToothAngle = 51
ElseIf CuttingStructure = 33 Then
ToothAngle = 52
Else
ToothAngle = 53
End If
'
ToothAngle_ = Rad(ToothAngle)
Ex = Sin(ToothAngle_ / 2) * Sin(ConeHalfAngle_)
Ey = Cos(ToothAngle_ / 2)
Ez = Sin(ToothAngle_ / 2) * Cos(ConeHalfAngle_)
E_x = Ex * 1
E_y = Ey * Cos(AngleBetweenTeeth_) - Ez
E_z = Ey + Ez * Cos(AngleBetweenTeeth_)
Fx = Ex * 1
Fy = -Ey
Fz = Ez * 1
E_times_dotF = Fx * E_x + Fy * E_y + Fz * E_z
EffCutterAngle_ = Deg(3.14159265358979 - Acosine(E_times_dotF))
ActualCutterAngle_ = Deg(2 * (3.14159265358979 / 2 - Atn(Sqr(Tan(Rad(CutterInclineAngle)) ^ 2 + 1 / (Cos(Rad(CutterInclineAngle)) * Tan(Rad(EffCutterAngle_ / 2))) ^ 2))))
IntActualCutterAngle_ = Int(ActualCutterAngle_)
Diff = ActualCutterAngle_ - IntActualCutterAngle_
If Diff < 0.125 Then Diff = 0
If Diff >= 0.125 And Diff < 0.375 Then Diff = 0.25
If Diff >= 0.375 And Diff < 0.625 Then Diff = 0.5
If Diff >= 0.625 And Diff < 0.875 Then Diff = 0.75
If Diff >= 0.875 Then Diff = 1
DesiredCutterAngle = IntActualCutterAngle_ + Diff
'
n = 0
delta = 0.001
Do
ToothAngle = ToothAngle + n * delta
ToothAngle_ = Rad(ToothAngle)
Ex = Sin(ToothAngle_ / 2) * Sin(ConeHalfAngle_)
Ey = Cos(ToothAngle_ / 2)
Ez = Sin(ToothAngle_ / 2) * Cos(ConeHalfAngle_)
E_x = Ex * 1
E_y = Ey * Cos(AngleBetweenTeeth_) - Ez
E_z = Ey + Ez * Cos(AngleBetweenTeeth_)
Fx = Ex * 1
Fy = -Ey
Fz = Ez * 1
E_times_dotF = Fx * E_x + Fy * E_y + Fz * E_z
EffCutterAngle_ = Deg(3.14159265358979 - Acosine(E_times_dotF))
ActualCutterAngle_ = Deg(2 * (3.14159265358979 / 2 - Atn(Sqr(Tan(Rad(CutterInclineAngle)) ^ 2 + 1 / (Cos(Rad(CutterInclineAngle)) * Tan(Rad(EffCutterAngle_ / 2))) ^ 2))))
If Abs(Round(ActualCutterAngle_, 4) - DesiredCutterAngle) < 0.0005 Then Exit Do
'
ToothAngle = ToothAngle - 2 * n * delta
ToothAngle_ = Rad(ToothAngle)
Ex = Sin(ToothAngle_ / 2) * Sin(ConeHalfAngle_)
Ey = Cos(ToothAngle_ / 2)
Ez = Sin(ToothAngle_ / 2) * Cos(ConeHalfAngle_)
E_x = Ex * 1
E_y = Ey * Cos(AngleBetweenTeeth_) - Ez
E_z = Ey + Ez * Cos(AngleBetweenTeeth_)
Fx = Ex * 1
Fy = -Ey
Fz = Ez * 1
E_times_dotF = Fx * E_x + Fy * E_y + Fz * E_z
EffCutterAngle_ = Deg(3.14159265358979 - Acosine(E_times_dotF))
ActualCutterAngle_ = Deg(2 * (3.14159265358979 / 2 - Atn(Sqr(Tan(Rad(CutterInclineAngle)) ^ 2 + 1 / (Cos(Rad(CutterInclineAngle)) * Tan(Rad(EffCutterAngle_ / 2))) ^ 2))))
If Abs(Round(ActualCutterAngle_, 4) - DesiredCutterAngle) < 0.0005 Then Exit Do
'
ToothAngle = ToothAngle + 2 * n * delta
n = n + 0.001
Loop
'
ActualCutterAngle = DesiredCutterAngle
ActiveSheet.Cells(Row, 11) = ToothAngle
End Function
I've been lurking here for awhile, but this'll be my first post. I haven't been able to find another post with the exact same problem I seem to be having, so sorry if this has been answered elsewhere.
I'll preface this by stating that I'm very new to VBA, and this problem is probably something obvious for a pro.
Anyway, in the last statement of my code (right before "end function"), I can't get my UDF to output the 'ToothAngle' variable to the specified cell (or any cell anywhere in the workbook for that matter). Can anyone see what I might be doing wrong? Thanks in advance for the help.
BTW, "rad()" and "deg()" are private functions defined elsewhere in the same module.
Best,
Jeremy
Function ActualCutterAngle(NumberOfTeeth, ConeHalfAngle, CutterInclineAngle, CuttingStructure) As Double
Dim ToothAngle_ As Double, ConeHalfAngle_ As Double, AngleBetweenTeeth_ As Double, Ex As Double, Ey As Double, Ez As Double, E_x As Double, E_y As Double, E_z As Double, Fx As Double, Fy As Double, Fz As Double, E_times_dotF As Double, Length_ As Double, EffCutterAngle_ As Double, ActualCutterAngle_ As Double, IntActualCutterAngle_ As Double, Diff As Double, DesiredCutterAngle As Double, n As Single, delta As Single
'
ConeHalfAngle_ = Rad(ConeHalfAngle)
AngleBetweenTeeth_ = Rad(360 / NumberOfTeeth)
'
If CuttingStructure = 11 Then
ToothAngle = 42
ElseIf CuttingStructure = 12 Then
ToothAngle = 43
ElseIf CuttingStructure = 13 Then
ToothAngle = 44
ElseIf CuttingStructure = 14 Then
ToothAngle = 45
ElseIf CuttingStructure = 21 Then
ToothAngle = 46
ElseIf CuttingStructure = 22 Then
ToothAngle = 47
ElseIf CuttingStructure = 23 Then
ToothAngle = 48
ElseIf CuttingStructure = 24 Then
ToothAngle = 49
ElseIf CuttingStructure = 31 Then
ToothAngle = 50
ElseIf CuttingStructure = 32 Then
ToothAngle = 51
ElseIf CuttingStructure = 33 Then
ToothAngle = 52
Else
ToothAngle = 53
End If
'
ToothAngle_ = Rad(ToothAngle)
Ex = Sin(ToothAngle_ / 2) * Sin(ConeHalfAngle_)
Ey = Cos(ToothAngle_ / 2)
Ez = Sin(ToothAngle_ / 2) * Cos(ConeHalfAngle_)
E_x = Ex * 1
E_y = Ey * Cos(AngleBetweenTeeth_) - Ez
E_z = Ey + Ez * Cos(AngleBetweenTeeth_)
Fx = Ex * 1
Fy = -Ey
Fz = Ez * 1
E_times_dotF = Fx * E_x + Fy * E_y + Fz * E_z
EffCutterAngle_ = Deg(3.14159265358979 - Acosine(E_times_dotF))
ActualCutterAngle_ = Deg(2 * (3.14159265358979 / 2 - Atn(Sqr(Tan(Rad(CutterInclineAngle)) ^ 2 + 1 / (Cos(Rad(CutterInclineAngle)) * Tan(Rad(EffCutterAngle_ / 2))) ^ 2))))
IntActualCutterAngle_ = Int(ActualCutterAngle_)
Diff = ActualCutterAngle_ - IntActualCutterAngle_
If Diff < 0.125 Then Diff = 0
If Diff >= 0.125 And Diff < 0.375 Then Diff = 0.25
If Diff >= 0.375 And Diff < 0.625 Then Diff = 0.5
If Diff >= 0.625 And Diff < 0.875 Then Diff = 0.75
If Diff >= 0.875 Then Diff = 1
DesiredCutterAngle = IntActualCutterAngle_ + Diff
'
n = 0
delta = 0.001
Do
ToothAngle = ToothAngle + n * delta
ToothAngle_ = Rad(ToothAngle)
Ex = Sin(ToothAngle_ / 2) * Sin(ConeHalfAngle_)
Ey = Cos(ToothAngle_ / 2)
Ez = Sin(ToothAngle_ / 2) * Cos(ConeHalfAngle_)
E_x = Ex * 1
E_y = Ey * Cos(AngleBetweenTeeth_) - Ez
E_z = Ey + Ez * Cos(AngleBetweenTeeth_)
Fx = Ex * 1
Fy = -Ey
Fz = Ez * 1
E_times_dotF = Fx * E_x + Fy * E_y + Fz * E_z
EffCutterAngle_ = Deg(3.14159265358979 - Acosine(E_times_dotF))
ActualCutterAngle_ = Deg(2 * (3.14159265358979 / 2 - Atn(Sqr(Tan(Rad(CutterInclineAngle)) ^ 2 + 1 / (Cos(Rad(CutterInclineAngle)) * Tan(Rad(EffCutterAngle_ / 2))) ^ 2))))
If Abs(Round(ActualCutterAngle_, 4) - DesiredCutterAngle) < 0.0005 Then Exit Do
'
ToothAngle = ToothAngle - 2 * n * delta
ToothAngle_ = Rad(ToothAngle)
Ex = Sin(ToothAngle_ / 2) * Sin(ConeHalfAngle_)
Ey = Cos(ToothAngle_ / 2)
Ez = Sin(ToothAngle_ / 2) * Cos(ConeHalfAngle_)
E_x = Ex * 1
E_y = Ey * Cos(AngleBetweenTeeth_) - Ez
E_z = Ey + Ez * Cos(AngleBetweenTeeth_)
Fx = Ex * 1
Fy = -Ey
Fz = Ez * 1
E_times_dotF = Fx * E_x + Fy * E_y + Fz * E_z
EffCutterAngle_ = Deg(3.14159265358979 - Acosine(E_times_dotF))
ActualCutterAngle_ = Deg(2 * (3.14159265358979 / 2 - Atn(Sqr(Tan(Rad(CutterInclineAngle)) ^ 2 + 1 / (Cos(Rad(CutterInclineAngle)) * Tan(Rad(EffCutterAngle_ / 2))) ^ 2))))
If Abs(Round(ActualCutterAngle_, 4) - DesiredCutterAngle) < 0.0005 Then Exit Do
'
ToothAngle = ToothAngle + 2 * n * delta
n = n + 0.001
Loop
'
ActualCutterAngle = DesiredCutterAngle
ActiveSheet.Cells(Row, 11) = ToothAngle
End Function