Prev: Creating but not sending a Lotus Notes email
Next: Windows hook and control hook
From: CY on 25 Jan 2010 09:14
On 25 Jan, 01:57, "FM" <s...(a)uce.gov> wrote:
> To rephrase this question further. Bear with me :) Im passing the number of
> dice to spin and which dice to spin. It could be one or seven. The Spinning
> (D parameter) below refers to the particular dice to spin. In the following
> code four dice will roll: D1 could be passed for example as 1, D2 as 3, D3
> as 5, D4 as 7, so the 1st, 3rd, 5th and 7th dice will spin. Each starts
> spinning sequentially like a mexican wave as explained in my earlier post
> using the ElseIf. It works perfectly well but as can be seen it gets a bit
> drawn out. Is there a more efficient way to do this:
>
> Select Case NumDiceToSpin

So using what we know from above...
Erase spinning()
Spinning(Max(Min(Int(CTR/50)+1,NumDiceToSpin),1) )
is what you want, then there is a free "for loop" to use for something
else *smile*

but I still dont get it.. .



Dim test As Integer
For test = 0 To 350 Step 50 ' as testin 222 is the same as 200 and 249
Debug.Print test, Max(Min(Int(test / 50) + 1, 7), 1)
Next test
Stop

something like this must be there somewhere too, oh where to put is I
dont know ;)

Function Max(ParamArray TheValues() As Variant) As Variant
Dim intLoop As Integer
Dim varCurrentMax As Variant
varCurrentMax = TheValues(LBound(TheValues))
For intLoop = LBound(TheValues) + 1 To UBound(TheValues)
If TheValues(intLoop) > varCurrentMax Then
varCurrentMax = TheValues(intLoop)
End If
Next intLoop
Max = varCurrentMax
End Function
Function Min(ParamArray TheValues() As Variant) As Variant
Dim intLoop As Integer
Dim varCurrentMin As Variant
varCurrentMin = TheValues(LBound(TheValues))
For intLoop = LBound(TheValues) + 1 To UBound(TheValues)
If TheValues(intLoop) < varCurrentMin Then
varCurrentMin = TheValues(intLoop)
End If
Next intLoop
Min = varCurrentMin
End Function

would get you

1 1
51 2
101 3
151 4
201 5
251 6
301 7

or if starting at 0

0 1
50 2
100 3
150 4
200 5
250 6
300 7
350 7

I could read what you wrote but no....
From: Rick Rothstein on 25 Jan 2010 17:21
It is unclear to me if you need to reset all the Spinning array elements to False before setting the "correct" one to True... if you do, then you will need to run this loop first...

For X = 1 To 7
Spinning(X) = False
Next

Next, take your D1, D2, D3 etc. variables and make them into an array named D (Dim'med from 1 to 7) so that whatever you assign to D1 would be assigned to D(1) and whatever you would assign to D2 would be assigned to D(2) and so on. Then, after you have done that, and if I did this right, you can replace **all** of the code you posted with this single line of code...

Spinning(D(WorksheetFunction(Min(NumDiceToSpin, Int((CTR + 49.99999999) / 50))))) = True

--
Rick (MVP - Excel)



"FM" <spam(a)uce.gov> wrote in message news:%23ZoiwlVnKHA.4628(a)TK2MSFTNGP06.phx.gbl...
> To rephrase this question further. Bear with me :) Im passing the number of
> dice to spin and which dice to spin. It could be one or seven. The Spinning
> (D parameter) below refers to the particular dice to spin. In the following
> code four dice will roll: D1 could be passed for example as 1, D2 as 3, D3
> as 5, D4 as 7, so the 1st, 3rd, 5th and 7th dice will spin. Each starts
> spinning sequentially like a mexican wave as explained in my earlier post
> using the ElseIf. It works perfectly well but as can be seen it gets a bit
> drawn out. Is there a more efficient way to do this:
>
> Select Case NumDiceToSpin
> Case 1 'Only roll one Dice
> Spinning(D1) = True 'D1 refers to the dice to roll between 1 and 7
> Case 2 'Only Roll two Dice
> If CTR <= 50 Then
> Spinning(D1) = True
> ElseIf CTR <= 100 Then
> Spinning(D2) = True
> End If
> Case 3 'Only roll three Dice
> If CTR <= 50 Then
> Spinning(D1) = True
> ElseIf CTR <= 100 Then
> Spinning(D2) = True
> ElseIf CTR <= 150 Then
> Spinning(D3) = True
> End If
> Case 4 'Only roll four dice
> If CTR <= 50 Then
> Spinning(D1) = True
> ElseIf CTR <= 100 Then
> Spinning(D2) = True
> ElseIf CTR <= 150 Then
> Spinning(D3) = True
> ElseIf CTR <= 200 Then
> Spinning(D4) = True
> End If
> 'and so on...through to case 7 if more dice are to spin
> End Select
> ~
> Regards
> FM
>
>
From: Rick Rothstein on 25 Jan 2010 23:01
Sorry, I forgot that I was not in an Excel newsgroup, so you do not have access to the WorksheetFunction's (even though my syntax for it was incorrect). Everything about my previous post applies here except for the "single line of code" I posted at the end of my message... it needs to be these two lines of code instead (Dim Index as Long and still use an array named D in place of your D1, D2, etc.)...

Index = Int((CTR + 49.99999999) / 50)
Spinning(D(NumDiceToSpin, IIf(Index > 7, 7, Index))) = True

--
Rick (MVP - Excel)


> It is unclear to me if you need to reset all the Spinning array
> elements to False before setting the "correct" one to True...
> if you do, then you will need to run this loop first...
>
> For X = 1 To 7
> Spinning(X) = False
> Next

> Next, take your D1, D2, D3 etc. variables and make them into
> an array named D (Dim'med from 1 to 7) so that whatever you
> assign to D1 would be assigned to D(1) and whatever you
> would assign to D2 would be assigned to D(2) and so on.
> Then, after you have done that, and if I did this right, you can
> replace **all** of the code you posted with this single line of code...
>
> Spinning(D(WorksheetFunction(Min(NumDiceToSpin, Int((CTR + 49.99999999) / 50))))) = True

--
Rick (MVP - Excel)



"FM" <spam(a)uce.gov> wrote in message news:%23ZoiwlVnKHA.4628(a)TK2MSFTNGP06.phx.gbl...
> To rephrase this question further. Bear with me :) Im passing the number of
> dice to spin and which dice to spin. It could be one or seven. The Spinning
> (D parameter) below refers to the particular dice to spin. In the following
> code four dice will roll: D1 could be passed for example as 1, D2 as 3, D3
> as 5, D4 as 7, so the 1st, 3rd, 5th and 7th dice will spin. Each starts
> spinning sequentially like a mexican wave as explained in my earlier post
> using the ElseIf. It works perfectly well but as can be seen it gets a bit
> drawn out. Is there a more efficient way to do this:
>
> Select Case NumDiceToSpin
> Case 1 'Only roll one Dice
> Spinning(D1) = True 'D1 refers to the dice to roll between 1 and 7
> Case 2 'Only Roll two Dice
> If CTR <= 50 Then
> Spinning(D1) = True
> ElseIf CTR <= 100 Then
> Spinning(D2) = True
> End If
> Case 3 'Only roll three Dice
> If CTR <= 50 Then
> Spinning(D1) = True
> ElseIf CTR <= 100 Then
> Spinning(D2) = True
> ElseIf CTR <= 150 Then
> Spinning(D3) = True
> End If
> Case 4 'Only roll four dice
> If CTR <= 50 Then
> Spinning(D1) = True
> ElseIf CTR <= 100 Then
> Spinning(D2) = True
> ElseIf CTR <= 150 Then
> Spinning(D3) = True
> ElseIf CTR <= 200 Then
> Spinning(D4) = True
> End If
> 'and so on...through to case 7 if more dice are to spin
> End Select
> ~
> Regards
> FM
>
>
From: Rick Rothstein on 25 Jan 2010 23:12
Actually, we can still make it a one-liner. Instead of these two lines of
code...

Index = Int((CTR + 49.99999999) / 50)
Spinning(D(NumDiceToSpin, IIf(Index > 7, 7, Index))) = True

use this single line of code in place of them...

Spinning(D(NumDiceToSpin, Int(IIf(CTR > 300, 301, CTR) + 49.99999999) / 50))
= True

--
Rick (MVP - Excel)


> Sorry, I forgot that I was not in an Excel newsgroup, so you
> do not have access to the WorksheetFunction's (even
> though my syntax for it was incorrect). Everything about
> my previous post applies here except for the "single line
> of code" I posted at the end of my message... it needs to
> be these two lines of code instead (Dim Index as Long
> and still use an array named D in place of your D1, D2, etc.)...
>
> Index = Int((CTR + 49.99999999) / 50)
> Spinning(D(NumDiceToSpin, IIf(Index > 7, 7, Index))) = True
>
> > It is unclear to me if you need to reset all the Spinning array
> > elements to False before setting the "correct" one to True...
> > if you do, then you will need to run this loop first...
> >
> > For X = 1 To 7
> > Spinning(X) = False
> > Next
>
> > > Next, take your D1, D2, D3 etc. variables and make them into
> > > an array named D (Dim'med from 1 to 7) so that whatever you
> > > assign to D1 would be assigned to D(1) and whatever you
> > > would assign to D2 would be assigned to D(2) and so on.
> > > Then, after you have done that, and if I did this right, you can
> > > replace **all** of the code you posted with this single line of
> > > code...
> > >
> > > Spinning(D(WorksheetFunction(Min(NumDiceToSpin, Int((CTR +
> > > 49.99999999) / 50))))) = True
> > >
> > > > To rephrase this question further. Bear with me :) Im passing the
> > > > number of
> > > > dice to spin and which dice to spin. It could be one or seven. The
> > > > Spinning
> > > > (D parameter) below refers to the particular dice to spin. In the
> > > > following
> > > > code four dice will roll: D1 could be passed for example as 1, D2 as
> > > > 3, D3
> > > > as 5, D4 as 7, so the 1st, 3rd, 5th and 7th dice will spin. Each
> > > > starts
> > > > spinning sequentially like a mexican wave as explained in my earlier
> > > > post
> > > > using the ElseIf. It works perfectly well but as can be seen it gets
> > > > a bit
> > > > drawn out. Is there a more efficient way to do this:
> > > >
> > > > Select Case NumDiceToSpin
> > > > Case 1 'Only roll one Dice
> > > > Spinning(D1) = True 'D1 refers to the dice to roll between 1 and 7
> > > > Case 2 'Only Roll two Dice
> > > > If CTR <= 50 Then
> > > > Spinning(D1) = True
> > > > ElseIf CTR <= 100 Then
> > > > Spinning(D2) = True
> > > > End If
> > > > Case 3 'Only roll three Dice
> > > > If CTR <= 50 Then
> > > > Spinning(D1) = True
> > > > ElseIf CTR <= 100 Then
> > > > Spinning(D2) = True
> > > > ElseIf CTR <= 150 Then
> > > > Spinning(D3) = True
> > > > End If
> > > > Case 4 'Only roll four dice
> > > > If CTR <= 50 Then
> > > > Spinning(D1) = True
> > > > ElseIf CTR <= 100 Then
> > > > Spinning(D2) = True
> > > > ElseIf CTR <= 150 Then
> > > > Spinning(D3) = True
>> > > ElseIf CTR <= 200 Then
> > > > Spinning(D4) = True
> > > > End If
> > > > 'and so on...through to case 7 if more dice are to spin
> > > > End Select

From: FM on 26 Jan 2010 20:51
Ive got this up and running perfectly with the total control and flexibility
I wanted. I couldnt get any of the formulas to work right so have decided to
stick with the ElseIf structure. It does the job and its only 70 odd lines
of code so I can live with that. As always appreciate your help and
assistance.

Regards
FM


First  |  Prev  |  Next  |  Last
Pages: 1 2 3 4
Prev: Creating but not sending a Lotus Notes email
Next: Windows hook and control hook