A G
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
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
...
by the way, you can write this as
Static RunNumber As Long
If RunNumber > 4 Then
RunNumber = 1
Else
RunNumber = RunNumber + 1
End If
>>>RunNumber = (RunNumber mod 5) + 1
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
? ThanksSub 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
Line 26-38 can be replaced with this...
see my first post...
see my first post...
Range("N1:Q1").Value = Array(Indices(RunNumber, 1), Indices(RunNumber, 2), Indices(RunNumber, 2), Indices(RunNumber, 4))
oops, it should be
RunNumber = (RunNumber Mod 5) + 1
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
the code looks good, any issue with that?
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.
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
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
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)
...
...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))
>>>
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
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)
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:
above code, increases the array size by one, and adds a new item...
ReDim Preserve indices(UBound(ReDim)+1)
indices(UBound(indices)) = Array(1, 2, 3, 4)
I am a bit lost...above code, increases the array size by one, and adds a new item...
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.
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)
...
so indices becomes
Is it always 5 values in the array?
Where are the no of datapoints set/found?
Where are the no of datapoints set/found?
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
and here, I put a parameter for
Max Number of Animation
Stop Button to stop animation
Animation interval, in seconds
29203229.xlsm
Max Number of Animation
Stop Button to stop animation
Animation interval, in seconds
29203229.xlsm
Open in new window