Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Discussion (Misc queries) > UDF returning #VALUE! why?
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
Adam Kroger
 
Posts: n/a
Default 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


Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.misc
Niek Otten
 
Posts: n/a
Default 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



Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.misc
JE McGimpsey
 
Posts: n/a
Default 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
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.misc
Chip Pearson
 
Posts: n/a
Default 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



Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.misc
Adam Kroger
 
Posts: n/a
Default 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



Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.misc
Niek Otten
 
Posts: n/a
Default 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





Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.misc
Adam Kroger
 
Posts: n/a
Default 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







Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.misc
Chip Pearson
 
Posts: n/a
Default 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









Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Returning Descending Values Jim Excel Worksheet Functions 14 September 27th 05 01:30 PM
LOOKUP returning value in cell above what I was searching for mwrfsu Excel Discussion (Misc queries) 2 September 10th 05 06:25 PM
Returning all values from a lookup - not just the first/last one Jim Burns Excel Worksheet Functions 2 June 20th 05 04:04 PM
MY VLOOKUP IS RETURNING N/A AND THEREFORE MY TOTAL SUM AT THE BOT. Martin Excel Discussion (Misc queries) 2 December 13th 04 11:21 AM
returning a text cell based on a number cell Josh7777777 Excel Worksheet Functions 2 November 2nd 04 07:42 PM


All times are GMT +1. The time now is 03:54 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement