UDF help please

  • Thread starter Thread starter Adam Kroger
  • Start date Start date
A

Adam Kroger

I need the following working UDF modified 2 ways

Function ROll2D6()
ROll2D6 = Application.RoundUp(Rnd() * 6, 0) + Application.RoundUp(Rnd()
* 6, 0)
End Function

Way 1:
accept 2 integer inputs
int1 will tell the number of instances of Application.RoundUp(Rnd() * 6, 0)
to sum
int2 will replace the 6 in Application.RoundUp(Rnd() * 6, 0)
an exit check for if my.cell ISNUMBER()=TRUE

Way 2:
accept 2 integer inputs
int1 will tell the number of instances of Application.RoundUp(Rnd() * 6, 0)
to sum
int2 will replace the 6 in Application.RoundUp(Rnd() * 6, 0)
an exit check if the cell to the right ="fail"

thanks
 
Adam said:
I need the following working UDF modified 2 ways

Function ROll2D6()
ROll2D6 = Application.RoundUp(Rnd() * 6, 0) + Application.RoundUp(Rnd()
* 6, 0)
End Function

Way 1:
accept 2 integer inputs
int1 will tell the number of instances of Application.RoundUp(Rnd() * 6, 0)
to sum
int2 will replace the 6 in Application.RoundUp(Rnd() * 6, 0)
an exit check for if my.cell ISNUMBER()=TRUE

Way 2:
accept 2 integer inputs
int1 will tell the number of instances of Application.RoundUp(Rnd() * 6, 0)
to sum
int2 will replace the 6 in Application.RoundUp(Rnd() * 6, 0)
an exit check if the cell to the right ="fail"

thanks
Click to expand...

Function ROll2D6(times As Long, mult As Long)
Dim i As Long
For i = 1 To times
ROll2D6 = ROll2D6 + _
Application.RoundUp(Rnd() * mult, 0)
Next i
End Function


Don't understand
 
Whant I mean by an "exit check" is that if the condition is true, then the
function would do nothing. for example

cell A1 has the formula =IF (B1="Y",RollDice(2,6),"")

Function RollDice(times As Long, mult As Long)
Dim i As Long
For i = 1 To times
ROll2D6 = ROll2D6 + _
Application.RoundUp(Rnd() * mult, 0)
Next i
End Function

what I want is some type of check whereby if tRollDice has already run once
in Cell A1 and generated a number, then it will not do it again the next
time the WorkBook refreashes the data. Using "pidgen language" programming
I would envision it looking something like this:

Function RollDice(times As Long, mult As Long)
Dim i As Long
IF ISNUMBER(my.cell)
GoTo End Function
For i = 1 To times
ROll2D6 = ROll2D6 + _
Application.RoundUp(Rnd() * mult, 0)
Next i
End Function

with my.cell referring to the cell that called the function.

I would also like know how to specify an OFFSET() from my.cell to perform
the same sort of "exit check"
Function Whatever()
IF OFFSET(my.cell, 1,-1)="fail"
GoTo End
<function code>
End Function

Does that make more sense? I guess my questions fall more into the "How do
you do this?" catagory.

I wish there was a "Programming UDFs for Dummies" on the web somewhere, that
would walk you through the basics of creating, and implementing a UDF. The
documentation that I have managed to find doesn't really explain the "How"
or "Why" just gives you some code that people have found useful. I do have
some basic (type not language, though I played around in that also, back
when dinosaurs roamed the earth) programming knowledge. I just don't really
know where to start, nor what the excel specific constants, and commands are
(how to declare variables, get user input, evaluate data, negotiate around
excel, etc).
 
usnig the function is a cell in this manner :
=IF(W12=TRUE,RollDice(2,6),"")

The function returns 0 every time.

When I modified it by frankensteining what you wronte into another sample
function I came up wiht this function:

Function RollDice(times As Long, mult As Long)
Dim i As Long
Dim myTemp As Integer
Randomize
myTemp = 0
For i = 1 To times
myTemp = myTemp + Application.RoundUp(Rnd() * mult, 0)
Next i
myCell.Value = myTemp
End Function

but it returns #VALUE!

any suggestions?
 
Back
Top