UDF returning #VALUE! why?
 

=con_check(0,2) == #VALUE!

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
Dim con_count As Integer

con_count = con_now - con_old
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) And (hit < 6) Then
targ = hit + 6
End If
If hit 5 Then
con_check = False
Exit Function
End If
If Application.RoundUp(Rnd() * 6, 0) + Application.RoundUp(Rnd() *
6, 0) targ Then
myCell.Value = True
Else: con_check = False
Exit Function
End If
Next i
Exit Function
End If
End Function

UDF returning #VALUE! why?
 

If you change myCell.Value to con_check it works

--
Kind regards,

Niek Otten

"Adam Kroger @hotmail.com" <adam_kroger<nospam wrote in message
...
=con_check(0,2) == #VALUE!

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
Dim con_count As Integer

con_count = con_now - con_old
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) And (hit < 6) Then
targ = hit + 6
End If
If hit 5 Then
con_check = False
Exit Function
End If
If Application.RoundUp(Rnd() * 6, 0) + Application.RoundUp(Rnd() *
6, 0) targ Then
myCell.Value = True
Else: con_check = False
Exit Function
End If
Next i
Exit Function
End If
End Function

UDF returning #VALUE! why?
 

Functions called from the worksheet can't change cell values. Instead of

myCell.Value = True

did you mean

con_check = True?


In article ,
"Adam Kroger" wrote:

=con_check(0,2) == #VALUE!

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
Dim con_count As Integer

con_count = con_now - con_old
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) And (hit < 6) Then
targ = hit + 6
End If
If hit 5 Then
con_check = False
Exit Function
End If
If Application.RoundUp(Rnd() * 6, 0) + Application.RoundUp(Rnd() *
6, 0) targ Then
myCell.Value = True
Else: con_check = False
Exit Function
End If
Next i
Exit Function
End If
End Function

UDF returning #VALUE! why?
 

The problem line is likely

myCell.Value = True

You haven't delared this variable (do yourself a huge favor and
declare your variables -- put Option Explicit as the very first
line in the code module, outside of and before any procedure).
Moreover, a UDF can NOT change the value of any cell. It can ONLY
return a value to the cell from which it was called. Delete the
above line of code and your code should work.


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






"Adam Kroger @hotmail.com" <adam_kroger<nospam wrote in message
...
=con_check(0,2) == #VALUE!

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
Dim con_count As Integer

con_count = con_now - con_old
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) And (hit < 6) Then
targ = hit + 6
End If
If hit 5 Then
con_check = False
Exit Function
End If
If Application.RoundUp(Rnd() * 6, 0) +
Application.RoundUp(Rnd() * 6, 0) targ Then
myCell.Value = True
Else: con_check = False
Exit Function
End If
Next i
Exit Function
End If
End Function

UDF returning #VALUE! why?
 

I think I have it figured out.... at least I seem to be getting valid
returns now.

woo hoo


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
Dim con_count As Integer

con_count = con_now - con_old
hit = con_old
If con_now 5 Then
con_check = "DEAD"
Exit Function
End If
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) And (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
con_check = "PASS"
Else: con_check = "FAIL"
Exit Function
End If
Next i
Exit Function
End If
End Function

"Adam Kroger @hotmail.com" <adam_kroger<nospam wrote in message
...
=con_check(0,2) == #VALUE!

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
Dim con_count As Integer

con_count = con_now - con_old
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) And (hit < 6) Then
targ = hit + 6
End If
If hit 5 Then
con_check = False
Exit Function
End If
If Application.RoundUp(Rnd() * 6, 0) + Application.RoundUp(Rnd() *
6, 0) targ Then
myCell.Value = True
Else: con_check = False
Exit Function
End If
Next i
Exit Function
End If
End Function

UDF returning #VALUE! why?
 

Always include Option Explicit in your modules.
If you once check ToolsOptionsEdit, Require Variable Declaration it will
be inserted for you automatically

--
Kind regards,

Niek Otten

"Adam Kroger @hotmail.com" <adam_kroger<nospam wrote in message
. ..
I think I have it figured out.... at least I seem to be getting valid
returns now.

woo hoo


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
Dim con_count As Integer

con_count = con_now - con_old
hit = con_old
If con_now 5 Then
con_check = "DEAD"
Exit Function
End If
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) And (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
con_check = "PASS"
Else: con_check = "FAIL"
Exit Function
End If
Next i
Exit Function
End If
End Function

"Adam Kroger @hotmail.com" <adam_kroger<nospam wrote in message
...
=con_check(0,2) == #VALUE!

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
Dim con_count As Integer

con_count = con_now - con_old
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) And (hit < 6) Then
targ = hit + 6
End If
If hit 5 Then
con_check = False
Exit Function
End If
If Application.RoundUp(Rnd() * 6, 0) + Application.RoundUp(Rnd() *
6, 0) targ Then
myCell.Value = True
Else: con_check = False
Exit Function
End If
Next i
Exit Function
End If
End Function

UDF returning #VALUE! why?
 

That checkbox is not in that screen. I am using '97 does that make a
difference?


"Niek Otten" wrote in message
...
Always include Option Explicit in your modules.
If you once check ToolsOptionsEdit, Require Variable Declaration it will
be inserted for you automatically

--
Kind regards,

Niek Otten

"Adam Kroger @hotmail.com" <adam_kroger<nospam wrote in message
. ..
I think I have it figured out.... at least I seem to be getting valid
returns now.

woo hoo


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
Dim con_count As Integer

con_count = con_now - con_old
hit = con_old
If con_now 5 Then
con_check = "DEAD"
Exit Function
End If
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) And (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
con_check = "PASS"
Else: con_check = "FAIL"
Exit Function
End If
Next i
Exit Function
End If
End Function

"Adam Kroger @hotmail.com" <adam_kroger<nospam wrote in message
...
=con_check(0,2) == #VALUE!

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
Dim con_count As Integer

con_count = con_now - con_old
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) And (hit < 6) Then
targ = hit + 6
End If
If hit 5 Then
con_check = False
Exit Function
End If
If Application.RoundUp(Rnd() * 6, 0) + Application.RoundUp(Rnd()
* 6, 0) targ Then
myCell.Value = True
Else: con_check = False
Exit Function
End If
Next i
Exit Function
End If
End Function

UDF returning #VALUE! why?
 

Go to the Tools menu in the VBA Editor, not Excel.


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


"Adam Kroger @hotmail.com" <adam_kroger<nospam wrote in message
. ..
That checkbox is not in that screen. I am using '97 does that
make a difference?


"Niek Otten" wrote in message
...
Always include Option Explicit in your modules.
If you once check ToolsOptionsEdit, Require Variable
Declaration it will be inserted for you automatically

--
Kind regards,

Niek Otten

"Adam Kroger @hotmail.com" <adam_kroger<nospam wrote in
message . ..
I think I have it figured out.... at least I seem to be
getting valid returns now.

woo hoo


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
Dim con_count As Integer

con_count = con_now - con_old
hit = con_old
If con_now 5 Then
con_check = "DEAD"
Exit Function
End If
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) And (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
con_check = "PASS"
Else: con_check = "FAIL"
Exit Function
End If
Next i
Exit Function
End If
End Function

"Adam Kroger @hotmail.com" <adam_kroger<nospam wrote in
message ...
=con_check(0,2) == #VALUE!

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
Dim con_count As Integer

con_count = con_now - con_old
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) And (hit < 6) Then
targ = hit + 6
End If
If hit 5 Then
con_check = False
Exit Function
End If
If Application.RoundUp(Rnd() * 6, 0) +
Application.RoundUp(Rnd() * 6, 0) targ Then
myCell.Value = True
Else: con_check = False
Exit Function
End If
Next i
Exit Function
End If
End Function