PDA

View Full Version : Solved: Help w/ simple 'For-Next' loop



jcfields
03-11-2009, 08:34 AM
I cannot for the life of me figure out why this won't work. I've written dozens of little snips like this, but I'm about ready to take it out on my monitor using the blunt end of my stapler :banghead: The function just keeps returning "#VALUE!", and I don't think it's even cycling through the loop. Can someone please help? Thanks in advance.


Function GetR(roller_dia, roller_num, roller_clearance) As Double
Dim k As Double
Dim j As Double
Dim f_x() As Double
Dim theta As Double
Dim i As Integer
k = roller_dia + roller_clearance / 2
j = roller_num - 2

f_x(0) = 0
For i = 1 To 100000000 Step 1
theta = i / 100000
f_x(i) = k * Sin(theta) - Sin(j * theta)
If f_x(i - 1) * f_x(i) < 0 Then GoTo exit_loop
Next i
exit_loop:
If Abs(f_x(i - 1)) <= Abs(f_x(i)) Then GetR = f_x(i - 1) Else GetR = f_x(i)
End Function

Kenneth Hobs
03-11-2009, 09:15 AM
For one thing, the variable "i" should be dimmed as Long. Dimming the array too large will cause an overflow. Maybe something like this would work?
Sub Test_GetR()
MsgBox GetR(20, 5, 10)
End Sub


Function GetR(roller_dia, roller_num, roller_clearance) As Double
Dim k As Double
Dim j As Double
Dim f_x(1) As Double
Dim theta As Double
Dim i As Long
k = roller_dia + roller_clearance / 2
j = roller_num - 2

f_x(0) = 0
For i = 1 To 100000000 Step 1
theta = i / 100000
f_x(1) = k * Sin(theta) - Sin(j * theta)
If f_x(0) * f_x(1) < 0 Then GoTo exit_loop
f_x(0) = f_x(1)
Next i
exit_loop:
If Abs(f_x(0)) <= Abs(f_x(1)) Then GetR = f_x(0) Else GetR = f_x(1)
End Function

jcfields
03-11-2009, 09:20 AM
Dim I as Long.

That didn't help - darn! There's something else wrong with it; I just don't see what...

Bob Phillips
03-11-2009, 09:38 AM
What does didn't help mean? Did it error, return an unexpected result?

jcfields
03-11-2009, 10:18 AM
For one thing, the variable "i" should be dimmed as Long. Dimming the array too large will cause an overflow. Maybe something like this would work?
(snip...)

Kenneth, your version works, but it just kicks out of the loop on the first pass through. So I guess that proves that the array is too large as I have it coded?:dunno I'm a little confused here.

jcfields
03-11-2009, 10:19 AM
What does didn't help mean? Did it error, return an unexpected result?

The function still returns "#VALUE!".

Bob Phillips
03-11-2009, 10:29 AM
You're confused!

Those two statements are contradictory, at least to me. It either works (#5) or it errors (#6), can't do both.

jcfields
03-11-2009, 10:52 AM
You're confused!

Those two statements are contradictory, at least to me. It either works (#5) or it errors (#6), can't do both.

Sorry; should've been more clear:
(#6) - Answering your original question. Dim'ing the variable "i" as "long" didn't change the returned output of the function. It still returned "#VALUE!".
(#5) - Replying to Kenneth. His version of the code just loops through the loop one time and then always reports the answer from that one trip through the loop.

The purpose of the function is to solve for the first instance of where two sinusoidal functions intersect (in a range from 0 to some positive integer - in this case "1000").

Anyway, in playing with my code a little more (by changing the size of the array and the parameters of the loop), I think I've confirmed that Kenneth is correct - namely, my array was too big. I'm going to toy with it some more, and I'll post my result(s) here. Thanks for all the help so far.

Kenneth Hobs
03-11-2009, 11:03 AM
Hmm, that is odd. The data that I used for testing took 314,159 loops to complete.

Bob Phillips
03-11-2009, 11:15 AM
Hmm, that is odd. The data that I used for testing took 314,159 loops to complete.

Same here.

jcfields
03-11-2009, 12:30 PM
Hmm, that is odd. The data that I used for testing took 314,159 loops to complete.

Let me look at it again. I probably don't understand how your revised version works (just thought I did). Sorry...

jcfields
03-11-2009, 12:42 PM
Okay... I see what you did now. You're not keeping every value generated by the loop, only the current and previous values; thus, reducing the size of the array to just two elements. Pretty slick :bow: . This is why I suck at coding - I would've never thought of that. I just glanced at it, saw the "f_x(0)" and "f_x(1)", and made a bad assumption. My apologies sir. You're a genious :friends: . Thanks again.