Link to home
Start Free TrialLog in
Avatar of A G
A GFlag for United States of America

asked on

Changing a Select - Case loop to be Dynamic

Hi, I have the following code with 5 Cases.  I need it to dynamic as I have more data. So, instead of 5 cases I wanted it to have i (Depending on # of data points I have at a time) cases. I'll have the arrays set up the same way. So , they are always going to be (x,x+1,x+2,x+3)

Sub ChartAnimator(),x
Dim Indices As Variant
Static RunNumber As Long
If RunNumber > 4 Then
   RunNumber = 1
Else
   RunNumber = RunNumber + 1
End If
Select Case RunNumber
Case 1
     Indices = Array(5, 6, 7, 8)
Case 2
     Indices = Array(4, 5, 6, 7)
Case 3
     Indices = Array(3, 4, 5, 6)
Case 4
     Indices = Array(2, 3, 4, 5)
Case 5
     Indices = Array(1, 2, 3, 4)
End Select
Range("N1:Q1").Value = Indices

Dim color As Long
Dim cv, pv As Double

cv = Cells(14, RunNumber + 2).Value
pv = Cells(14, RunNumber + 1).Value

If cv = pv Then color = RGB(0, 0, 0)
If cv < pv Then color = RGB(192, 0, 0)
If cv > pv Then color = RGB(0, 192, 0)

Dim c As Chart
Set c = ActiveSheet.ChartObjects(1).Chart
c.FullSeriesCollection(1).Format.Line.ForeColor.RGB = color
    
If RunNumber < 5 Then
     Application.OnTime Now() + TimeSerial(0, 0, 2), "ChartAnimator"
End If
End Sub

Open in new window

Avatar of HainKurt
HainKurt
Flag of Canada image

maybe you can use this structure

Dim Indices(5, 4) As Integer

Indices(1, 1) = 1: Indices(1, 2) = 2: Indices(1, 3) = 3: Indices(1, 4) = 4
Indices(2, 1) = 2: Indices(2, 2) = 3: Indices(2, 3) = 4: Indices(2, 4) = 5
Indices(3, 1) = 3: Indices(3, 2) = 4: Indices(3, 3) = 5: Indices(3, 4) = 6
Indices(4, 1) = 4: Indices(4, 2) = 5: Indices(4, 3) = 6: Indices(4, 4) = 7
Indices(5, 1) = 5: Indices(5, 2) = 6: Indices(5, 3) = 7: Indices(5, 4) = 8

Static RunNumber As Long
If RunNumber > 4 Then
   RunNumber = 1
Else
   RunNumber = RunNumber + 1
End If

Range("N1:Q1").Value = Arry(Indices(RunNumber, 1), Indices(RunNumber, 2), Indices(RunNumber, 2), Indices(RunNumber, 4))

Open in new window

or a bit better maybe

Sub setArray(a, ix, v1, v2, v3, v4)
  a(ix, 1) = v1
  a(ix, 2) = v2
  a(ix, 3) = v3
  a(ix, 4) = v4
End Sub

Sub ChartAnimator()

Dim Indices(5, 4) As Integer

setArray Indices, 1, 5, 6, 7, 8
setArray Indices, 2, 4, 5, 6, 7
setArray Indices, 3, 3, 4, 5, 6
setArray Indices, 4, 2, 3, 4, 5
setArray Indices, 5, 1, 2, 3, 4

...

Open in new window


by the way, you can write this as

Static RunNumber As Long
If RunNumber > 4 Then
   RunNumber = 1
Else
   RunNumber = RunNumber + 1
End If

Open in new window

>>>
RunNumber = (RunNumber mod 5) + 1 

Open in new window


Avatar of A G

ASKER

Thank you very much but I don't entirely follow and it is certainly my fault. How are we exactly changing the St elect Case loop? I added the lines you suggested below but how will I remove the select case and where wouldl I put the 
RunNumber = (5 mod RunNumber) + 1 

Open in new window

? Thanks


Sub setArray(a, ix, v1, v2, v3, v4)
  a(ix, 1) = v1
  a(ix, 2) = v2
  a(ix, 3) = v3
  a(ix, 4) = v4
End Sub

Sub ChartAnimator(),x
Dim Indices As Variant

Dim Indices(5, 4) As Integer

setArray Indices, 1, 5, 6, 7, 8
setArray Indices, 2, 4, 5, 6, 7
setArray Indices, 3, 3, 4, 5, 6
setArray Indices, 4, 2, 3, 4, 5
setArray Indices, 5, 1, 2, 3, 4



Static RunNumber As Long


RunNumber = (5 mod RunNumber) + 1 

Select Case RunNumber
Case 1
     Indices = Array(5, 6, 7, 8)
Case 2
     Indices = Array(4, 5, 6, 7)
Case 3
     Indices = Array(3, 4, 5, 6)
Case 4
     Indices = Array(2, 3, 4, 5)
Case 5
     Indices = Array(1, 2, 3, 4)
End Select
Range("N1:Q1").Value = Indices

Dim color As Long
Dim cv, pv As Double

cv = Cells(14, RunNumber + 2).Value
pv = Cells(14, RunNumber + 1).Value

If cv = pv Then color = RGB(0, 0, 0)
If cv < pv Then color = RGB(192, 0, 0)
If cv > pv Then color = RGB(0, 192, 0)

Dim c As Chart
Set c = ActiveSheet.ChartObjects(1).Chart
c.FullSeriesCollection(1).Format.Line.ForeColor.RGB = color
    If RunNumber < 5 Then
     Application.OnTime Now() + TimeSerial(0, 0, 2), "ChartAnimator"
End If
End Sub

Open in new window


Line 26-38 can be replaced with this...
see my first post...

Range("N1:Q1").Value = Array(Indices(RunNumber, 1), Indices(RunNumber, 2), Indices(RunNumber, 2), Indices(RunNumber, 4)) 

Open in new window

oops, it should be

RunNumber = (RunNumber Mod 5) + 1

Open in new window

Avatar of A G

ASKER


Sub setArray(a, ix, v1, v2, v3, v4)
  a(ix, 1) = v1
  a(ix, 2) = v2
  a(ix, 3) = v3
  a(ix, 4) = v4
End Sub

Sub ChartAnimator(),x
Dim Indices As Variant

Dim Indices(5, 4) As Integer

setArray Indices, 1, 5, 6, 7, 8
setArray Indices, 2, 4, 5, 6, 7
setArray Indices, 3, 3, 4, 5, 6
setArray Indices, 4, 2, 3, 4, 5
setArray Indices, 5, 1, 2, 3, 4



Static RunNumber As Long


RunNumber = (RunNumber Mod 5) + 1


Range("N1:Q1").Value = Array(Indices(RunNumber, 1), Indices(RunNumber, 2), Indices(RunNumber, 2), Indices(RunNumber, 4)) 


Dim color As Long
Dim cv, pv As Double

cv = Cells(14, RunNumber + 2).Value
pv = Cells(14, RunNumber + 1).Value

If cv = pv Then color = RGB(0, 0, 0)
If cv < pv Then color = RGB(192, 0, 0)
If cv > pv Then color = RGB(0, 192, 0)

Dim c As Chart
Set c = ActiveSheet.ChartObjects(1).Chart
c.FullSeriesCollection(1).Format.Line.ForeColor.RGB = color
    If RunNumber < 5 Then
     Application.OnTime Now() + TimeSerial(0, 0, 2), "ChartAnimator"
End If
End Sub

Open in new window

the code looks good, any issue with that?
Avatar of A G

ASKER

Thanks again, I just checked and it works but not exactly how  I wanted it to work. So, perhaps I misworded my question.
Or, I may be doing something wro (for example, I am not setting up an "i" so it is only looping the ng. So, what if I have 30 dates of data which this code is going to loop. I think the current core only loops until data point 8. Again, I may be forgetting to set an "i" which will make it loop up until data point 30.
I found a better/shorter way

Sub ChartAnimator2()
Dim v(5) As Variant
Dim indices As Variant

v(1) = Array(5, 6, 7, 8)
v(2) = Array(4, 5, 6, 7)
v(3) = Array(3, 4, 5, 6)
v(4) = Array(2, 3, 4, 5)
v(5) = Array(1, 2, 3, 4)

indices = Array(v(1), v(2), v(3), v(4), v(5))

Static RunNumber As Long
RunNumber = (RunNumber Mod 5) + 1

Range("N1:Q1").Value = indices(RunNumber)
...

Open in new window

...
but I could not get what you mention above... what is i, what is 8/30?
I dont see any of them in your code
Line 11 above can be replaced with this, if we have lots of values

indices = Array(v(1), v(2), v(3), v(4), v(5)) 
>>>
ReDim indices(UBound(v))
For i = 1 To UBound(v)
  indices(i) = v(i)
Next

Open in new window

Avatar of A G

ASKER

Ok, so, on line 5 your array has 8 at the end. and on line 9 you 1 as the first  digit of the array. So, in total , the data points it will loop is from 1 to 9 when the animation runs. My question is, when I have 30 data points, how can I make it so it runs 1 to 30 instead of 1 to 9 which we have on the code.

So We have these 5 instances on the animation but what if I need 30 of these
v(1) = Array(5, 6, 7, 8) 
v(2) = Array(4, 5, 6, 7) 
v(3) = Array(3, 4, 5, 6) 
v(4) = Array(2, 3, 4, 5) 
v(5) = Array(1, 2, 3, 4) 

Is there a way to change the code in such a way that I dont need to run 30 of the above. For example if I want to add one more instance to the animation it would like the below but I would like to automate it that if I have 30 of these , i dont have to write each single one of the v(x)s. Again , I may be missing something about this. 

v(1) =  Array(6, 7, 8, 9) 
v(2) = Array(5, 6, 7, 8) 
v(3) =Array(4, 5, 6, 7) 
 
v(4) =Array(3, 4, 5, 6)  

v(5) =Array(2, 3, 4, 5) 
 Array(1, 2, 3, 4) 

adding new instance is:

ReDim Preserve indices(UBound(ReDim)+1) 
indices(UBound(indices)) = Array(1, 2, 3, 4) 

Open in new window

I am a bit lost...
above code, increases the array size by one, and adds a new item...
Avatar of A G

ASKER

Ok, l'll try to explain
So we have the code below
v(1) = Array(5, 6, 7, 8) 
v(2) = Array(4, 5, 6, 7) 
v(3) = Array(3, 4, 5, 6) 
v(4) = Array(2, 3, 4, 5) 
v(5) = Array(1, 2, 3, 4)  

I may need to have more v(i) where i is a number I'll designate depending on the number of data points at the time. So if I have 10 Data points, it will look like the code below. 

v(1) =  Array(7, 8, 9, 10) 
v(2) = Array(6, 7, 8, 9) 
v(3) =Array(5, 6, 7, 8) 
v(4) =Array(4, 5, 6, 7) 
 
v(5) =Array(3, 4, 5, 6)  
 v(6) =  Array(2, 3, 4, 5) 
v(7) = Array(1, 2, 3, 4)  

I want to automate this if I have more data points. You may have answered this question above, I am not 100% sure that's why I am asking. 



still I could not get, but maybe this
Dim ix As Integer
Dim v(30) As Variant
Dim indices As Variant

ix = UBound(v)

For i = 1 To ix
  v(i) = Array(ix - i + 1, ix - i + 2, ix - i + 3, ix - i + 4)
Next

ReDim indices(UBound(v))
For i = 1 To UBound(v)
  indices(i) = v(i)
Next

Static RunNumber As Long
RunNumber = (RunNumber Mod 5) + 1

Range("N1:Q1").Value = indices(RunNumber)
...

Open in new window

so indices becomes
User generated image

Avatar of Norie
Norie

Is it always 5 values in the array?

Where are the no of datapoints set/found?
Avatar of A G

ASKER

Skew Dec 2020.xlsm

I'm attaching file, maybe it will be easier to understand this way.
I replaced the latest code snippet you put. Now It goes from 30-25 but it needs to go from 30 to 1. Take a look at the spreadsheet and let me know if you have questions. 
ASKER CERTIFIED SOLUTION
Avatar of HainKurt
HainKurt
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
and here, I put a parameter for

Max Number of Animation
Stop Button to stop animation
Animation interval, in seconds
29203229.xlsm