UDF help please

[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

 

[Bob Phillips]

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

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

 

[Adam Kroger]

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).

 

[Adam Kroger]

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?