Cos & Sin WorksheetFunction

[gsandy]

I used the loop code below in Microsoft Visual Studio 8 and am trying to get it to work in Excel’s VBA. The Cos and Sin function seem to be the problem, is there any way to get it to work?

Thanks Sandy.

Sub TestInputs()

Dim Fun_1 As Double
Dim Fun_2 As Double
Dim Fun_3 As Double
Dim Rise As Double
Dim TempRad As Double
Dim InLength As Double
Dim RadiusNew As Double

     	Rise = Range("M231").Value
     	InLength = Range("M231").Value
    	AproxRad = Range("O232").Value
    	TempRad = AproxRad
     
Do
Fun_1 = 1 - (Rise / TempRad) - (WorksheetFunction.Cos(InLength / 2 / TempRad))
Fun_2 = (Rise / (TempRad ^ 2)) - ((WorksheetFunction.Sin(InLength / 2 / TempRad)) * InLength / 2 / TempRad ^ 2)
Fun_3 = TempRad - (Fun_1 / Fun_2)
    	 RadiusNew = Fun_3
     	TempRad = RadiusNew
     
Loop Until Fun_1 = 0
     
   	Range("O233") = RadiusNew
     
End Sub

My only thought was to some how get Fun_3 by writing the Fun_1 and Fun_2 formula into Excel during a Loop until Fun_1 = 0??

[shg]

Sin and Cos are native functions in VBA.

[gsandy]

Thanks for that, I must have other problems which I will try to sort.
Sandy

[shg]

You've got more variables than you need, and you initialize two of them from the same cell, which looks dubious.

Sub TestInputs()
    Dim Fun1        As Double
    Dim Fun2        As Double
    Dim Rise        As Double
    Dim Rad         As Double
    Dim InLength    As Double

    Rise = Range("M231").Value
    InLength = Range("M231").Value
    Rad = Range("O232").Value

    Do
        Fun1 = 1 - Rise / Rad - Cos(InLength / 2 / Rad)
        Fun2 = Rise / Rad ^ 2 - Sin(InLength / 2 / Rad) * InLength / 2 / Rad ^ 2
        Rad = Rad - Fun1 / Fun2
    Loop Until Fun1 = 0

    Range("O233").Value = Rad
End Sub

EDIT: Also, you should probably be looping until Fun1 is less than (perhaps in absolute value) some epsilon.

[gsandy]

Thanks SHD, now we are winning. My maths is a little rusty.
Sandy

[shg]

Sandy, you could do this instead with Solver. It would probably be faster.

You could also automate Solver to do it.