UDF returning #VALUE! why?

[Adam Kroger]

=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

 

[Niek Otten]

If you change myCell.Value to con_check it works

 

[JE McGimpsey]

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

myCell.Value = True

did you mean

con_check = True?

 

[Chip Pearson]

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]

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

 

[Niek Otten]

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

 

[Adam Kroger]

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

 

[Chip Pearson]

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


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