Unable to output variable's content to cell

[jcfields]

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

[RichardSchollar]

Hi

Where are you using this function? If it is in a worksheet cell then you won't be able to modify the contents of any other cell by using this function (you would need to be running it from within a sub routine in code). Functions can only return a value to the cell in which they reside I'm afraid.

Richard

[mdmackillop]

A couple of things to help trim your code

Select Case instead of multiple IFs
Select Case CuttingStructure
Case 11
ToothAngle = 42
Case 12
ToothAngle = 43
Case 13
ToothAngle = 44
'etc.
End Select

Rounding using a formula

Diff = ActualCutterAngle_ - IntActualCutterAngle_
Diff = Int(Application.Round(Diff / 0.25, 0)) * 0.25



Set Pi as a variable


Dim Pi
Pi = 4 * Atn(1)
'e.g.
EffCutterAngle_ = Deg(Pi - Acosine(E_times_dotF))

[jcfields]

Hi

Where are you using this function? If it is in a worksheet cell then you won't be able to modify the contents of any other cell by using this function (you would need to be running it from within a sub routine in code). Functions can only return a value to the cell in which they reside I'm afraid.

Richard

Well that wasn't in the brochure! I didn't know you couldn't return a value to another cell other than the one in which the function resides. In this case, the value of the variable I'm trying to write is just as important as the output of the function. Any suggestions on how I can get this variable's value back to the spreadsheet (before the next call of that function overwrites it)?

Thanks for your help.

Cheers,
Jeremy

[jcfields]

A couple of things to help trim your code

Select Case instead of multiple IFs
Select Case CuttingStructure
Case 11
ToothAngle = 42
Case 12
ToothAngle = 43
Case 13
ToothAngle = 44
'etc.
End Select

Rounding using a formula

Diff = ActualCutterAngle_ - IntActualCutterAngle_
Diff = Int(Application.Round(Diff / 0.25, 0)) * 0.25



Set Pi as a variable


Dim Pi
Pi = 4 * Atn(1)
'e.g.
EffCutterAngle_ = Deg(Pi - Acosine(E_times_dotF))



Thanks for the suggestions. I figured there were ways I could shorten the code some, but since I'm lazy... ;)

Anyway, I'll be implementing your suggestions.

Cheers,
Jeremy

[mdmackillop]

Hi Jeremy,
I suspect your loop is not meeting the limits set. While possibly the result may be meaningless, add this at the end to force an exit after 100000 cycles.
x = x + 1
If x > 100000 Then
Exit Do
End If
Loop

[mdmackillop]

BTW, Delete this line.

ActiveSheet.Cells(Row, 11) = ToothAngle
It will cause your function to fail.

[holshy]

In this case, the value of the variable I'm trying to write is just as important as the output of the function. Any suggestions on how I can get this variable's value back to the spreadsheet (before the next call of that function overwrites it)?You could declare the function so that it returns an array "as Double()" and then return both values. This would require it to be an array formula in XL.

This workbook has a simple example of such a custom formula. I hope it helps.

[jcfields]

Thanks for all the suggestions - this forum is great. Holshy, thanks for the example formula; that helps.

This function is sort-of mimicking (or trying to anyway) Excel's "goal seek" function. Is there a cleaner way to just use the "goal seek" function within my UDF and output the two desired outputs as an array (as Holshy suggests)?

Sorry for all the newbness here with the elementary questions. This is my first attempt at a VBA macro, and it's definitely a learning experience.