simple (i think) UDF debug help needed

This is my first time writing a new UDF

The error reported by debug is: "Block IF without End IF"
the highlight is on "next i"


Function con_check(con_old, con_now)
Dim i As Integer
Dim targ As Integer
Dim hit As Integer
Dim roll As Integer

If con_old < con_now Then
For i = 1 To con_count
hit = hit + 1
If hit < 3 Then
targ = 1 + hit
If 3 < hit < 6 Then
targ = hit + 6
If hit > 5 Then
myCell.Value = "DEAD"
End If
End If
roll = Application.RoundUp(Rnd() * 6, 0) + Application.RoundUp(Rnd()
* 6, 0)
If roll > targ Then
myCell.Value = "PASS"
Next i
Exit Function
Else: myCell.Value = "FAIL"
Exit Function
End If
End If
End Function

There are loads of problems here

- con_count is not defined
- what is mycell
- 3 < hit < 6 doesn't do what I think you think it does
- there is a Exit function after a Next (redundant)
- a missing End If

What is it trying to do (apart from the obvious bit about two randoms).

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Adam Kroger @hotmail.com>" <adam_kroger<nospam> wrote in message
news:[email protected]...
> This is my first time writing a new UDF
>
> The error reported by debug is: "Block IF without End IF"
> the highlight is on "next i"
>
>
> Function con_check(con_old, con_now)
> Dim i As Integer
> Dim targ As Integer
> Dim hit As Integer
> Dim roll As Integer
>
> If con_old < con_now Then
> For i = 1 To con_count
> hit = hit + 1
> If hit < 3 Then
> targ = 1 + hit
> If 3 < hit < 6 Then
> targ = hit + 6
> If hit > 5 Then
> myCell.Value = "DEAD"
> End If
> End If
> roll = Application.RoundUp(Rnd() * 6, 0) +
Application.RoundUp(Rnd()
> * 6, 0)
> If roll > targ Then
> myCell.Value = "PASS"
> Next i
> Exit Function
> Else: myCell.Value = "FAIL"
> Exit Function
> End If
> End If
> End Function
>
>
>

I figureed there were probably a few problems, I was hoping to avoid trying
to explain the logic, as it is hard to explain, but hopefully this analogy
will help..

Think of a boxer in a fight. con_now is the number of times a boxer has been
hit in the head during the fight, con_old was how many times the boxer was
hit in the head through the last round.

EXAMPLE:
fight is in round 4
round 1:
fighter was not hit in head
con_old = 0
con_now = 0
no check is needed
round 2
fighter was hit in the head once
con_old = 0
con_new = 1
con. check is needed with target at level 1
round 3
fighter hit in the head twice
con_old = 1
con_new = 3
con check needed at level 2
if that check succeeds
con check needed at level 3
round 4
fighter hit in the head 3 times
con_old = 3
con_new = 6
fighter damage = 6 so fighter dies, the check is not needed

RULES:
IF con_now is greater than con_old a "conciousness check" needs to be
performed for EACH new "hit"
IF a check fails, no further checks are needed
the target number depends on how many hits have bee taken
from 1 to 3 hits target is "hits+1"
from 4 - 5 hits target is "hits +6"
6 or more hits results in death

I think the decision tree logic of the routine is solid, but my programming
syntax knowledge is weak.

"Bob Phillips" <[email protected]> wrote in message
news:%[email protected]...
> There are loads of problems here
>
> - con_count is not defined
> - what is mycell
> - 3 < hit < 6 doesn't do what I think you think it does
> - there is a Exit function after a Next (redundant)
> - a missing End If
>
> What is it trying to do (apart from the obvious bit about two randoms).
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "Adam Kroger @hotmail.com>" <adam_kroger<nospam> wrote in message
> news:[email protected]...
>> This is my first time writing a new UDF
>>
>> The error reported by debug is: "Block IF without End IF"
>> the highlight is on "next i"
>>
>>
>> Function con_check(con_old, con_now)
>> Dim i As Integer
>> Dim targ As Integer
>> Dim hit As Integer
>> Dim roll As Integer
>>
>> If con_old < con_now Then
>> For i = 1 To con_count
>> hit = hit + 1
>> If hit < 3 Then
>> targ = 1 + hit
>> If 3 < hit < 6 Then
>> targ = hit + 6
>> If hit > 5 Then
>> myCell.Value = "DEAD"
>> End If
>> End If
>> roll = Application.RoundUp(Rnd() * 6, 0) +
> Application.RoundUp(Rnd()
>> * 6, 0)
>> If roll > targ Then
>> myCell.Value = "PASS"
>> Next i
>> Exit Function
>> Else: myCell.Value = "FAIL"
>> Exit Function
>> End If
>> End If
>> End Function
>>
>>
>>
>
>

UDF has been rewritten:

it now compiles, but returns 0
what is the correct syntax for "If 3 < hit < 6 Then"


Function con_check(con_old As Integer, con_now As Integer)
Dim i As Integer
Dim targ As Integer
Dim hit As Integer
Dim roll As Integer

hit = con_old
If con_old < con_now Then
For i = 1 To con_count
hit = hit + 1
If hit < 3 Then
targ = 1 + hit
End If
If 3 < hit < 6 Then
targ = hit + 6
End If
If hit > 5 Then
con_check = "DEAD"
Exit Function
End If
If Application.RoundUp(Rnd() * 6, 0) + Application.RoundUp(Rnd() *
6, 0) > targ Then
myCell.Value = "PASS"
Else: con_check = "FAIL"
Exit Function
End If
Next i
Exit Function
End If
End Function


"Bob Phillips" <[email protected]> wrote in message
news:%[email protected]...
> There are loads of problems here
>
> - con_count is not defined
> - what is mycell
> - 3 < hit < 6 doesn't do what I think you think it does
> - there is a Exit function after a Next (redundant)
> - a missing End If
>
> What is it trying to do (apart from the obvious bit about two randoms).
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "Adam Kroger @hotmail.com>" <adam_kroger<nospam> wrote in message
> news:[email protected]...
>> This is my first time writing a new UDF
>>
>> The error reported by debug is: "Block IF without End IF"
>> the highlight is on "next i"
>>
>>
>> Function con_check(con_old, con_now)
>> Dim i As Integer
>> Dim targ As Integer
>> Dim hit As Integer
>> Dim roll As Integer
>>
>> If con_old < con_now Then
>> For i = 1 To con_count
>> hit = hit + 1
>> If hit < 3 Then
>> targ = 1 + hit
>> If 3 < hit < 6 Then
>> targ = hit + 6
>> If hit > 5 Then
>> myCell.Value = "DEAD"
>> End If
>> End If
>> roll = Application.RoundUp(Rnd() * 6, 0) +
> Application.RoundUp(Rnd()
>> * 6, 0)
>> If roll > targ Then
>> myCell.Value = "PASS"
>> Next i
>> Exit Function
>> Else: myCell.Value = "FAIL"
>> Exit Function
>> End If
>> End If
>> End Function
>>
>>
>>
>
>

> what is the correct syntax for "If 3 < hit < 6 Then"

If (3 < hit) And (hit < 6) Then


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"Adam Kroger @hotmail.com>" <adam_kroger<nospam> wrote in message
news:[email protected]...
> UDF has been rewritten:
>
> it now compiles, but returns 0
> what is the correct syntax for "If 3 < hit < 6 Then"
>
>
> Function con_check(con_old As Integer, con_now As Integer)
> Dim i As Integer
> Dim targ As Integer
> Dim hit As Integer
> Dim roll As Integer
>
> hit = con_old
> If con_old < con_now Then
> For i = 1 To con_count
> hit = hit + 1
> If hit < 3 Then
> targ = 1 + hit
> End If
> If 3 < hit < 6 Then
> targ = hit + 6
> End If
> If hit > 5 Then
> con_check = "DEAD"
> Exit Function
> End If
> If Application.RoundUp(Rnd() * 6, 0) +
> Application.RoundUp(Rnd() * 6, 0) > targ Then
> myCell.Value = "PASS"
> Else: con_check = "FAIL"
> Exit Function
> End If
> Next i
> Exit Function
> End If
> End Function
>
>
> "Bob Phillips" <[email protected]> wrote in
> message news:%[email protected]...
>> There are loads of problems here
>>
>> - con_count is not defined
>> - what is mycell
>> - 3 < hit < 6 doesn't do what I think you think it does
>> - there is a Exit function after a Next (redundant)
>> - a missing End If
>>
>> What is it trying to do (apart from the obvious bit about two
>> randoms).
>>
>> --
>> HTH
>>
>> Bob Phillips
>>
>> (remove nothere from email address if mailing direct)
>>
>> "Adam Kroger @hotmail.com>" <adam_kroger<nospam> wrote in
>> message
>> news:[email protected]...
>>> This is my first time writing a new UDF
>>>
>>> The error reported by debug is: "Block IF without End IF"
>>> the highlight is on "next i"
>>>
>>>
>>> Function con_check(con_old, con_now)
>>> Dim i As Integer
>>> Dim targ As Integer
>>> Dim hit As Integer
>>> Dim roll As Integer
>>>
>>> If con_old < con_now Then
>>> For i = 1 To con_count
>>> hit = hit + 1
>>> If hit < 3 Then
>>> targ = 1 + hit
>>> If 3 < hit < 6 Then
>>> targ = hit + 6
>>> If hit > 5 Then
>>> myCell.Value = "DEAD"
>>> End If
>>> End If
>>> roll = Application.RoundUp(Rnd() * 6, 0) +
>> Application.RoundUp(Rnd()
>>> * 6, 0)
>>> If roll > targ Then
>>> myCell.Value = "PASS"
>>> Next i
>>> Exit Function
>>> Else: myCell.Value = "FAIL"
>>> Exit Function
>>> End If
>>> End If
>>> End Function
>>>
>>>
>>>
>>
>>
>
>