如下图1所示,以散点图中的各点为端点组成的正方形会自动变化,直至遍历完所有点。首先,选择一个单元格来设定动画的速度,示例中为单元格L8,输入值0.25,并将其命名为Pause,如下图2所示。然后,打开VBE,插入一个标准模块,输入下面的代码:Sub LoopTheSquares()
Dim iMin As Long
Dim iMax As Long
iMin = Worksheets("Sheet1").Range("ScrollMin").Value2
iMax = Worksheets("Sheet1").Range("ScrollMax").Value2
Dim tPause As Double
tPause = Worksheets("Sheet1").Range("Pause").Value2
Dim rNow As Range
Set rNow = Worksheets("Sheet1").Range("ScrollNow")
Dim iNow As Long
For iNow = iMin To iMax
rNow.Value2 = iNow
Dim t As Double
t = Timer
Worksheets("Sheet1").Calculate
DoEvents
Do
DoEvents
If Timer - t > tPause Then Exit
Do Loop
Next
rNow.Value2 = 0
End Sub
接着,在工作表中放置一个按钮控件,将其关联到上面的LoopTheSquares过程。这样,单击该按钮,散点图上的正方形就会如图1一样依次动起来。Sub LoopTheSquaresInRandomOrder()
Dim iMax As Long
iMax = Worksheets("Sheet1").Range("ScrollMax").Value2
Dim tPause As Double
tPause = Worksheets("Sheet1").Range("Pause").Value2
Dim rNow As Range
Set rNow = Worksheets("Sheet1").Range("ScrollNow")
rNow.Value2 = 0
Dim v As Variant, w As Variant, x As Variant
v = WorksheetFunction.Sequence(iMax)
w = WorksheetFunction.RandArray(iMax, , 0, 1, False)
x = WorksheetFunction.SortBy(v, w, 1)
Dim iNow As Long
For iNow = 1 To iMax
rNow.Value2 = x(iNow, 1)
Dim t As Double
t = Timer
wsDotsAndSquares.Calculate
DoEvents
Do
DoEvents
If Timer - t > tPause Then Exit
Do Loop
Next
rNow.Value2 = 0
End Sub
这里使用了一些新的动态数组函数。使用SEQUENCE函数生成从1到iMax的数字列表;使用RANDARRAY函数生成与此序列对应的随机数列表;使用SORTBY函数根据随机数对序列进行排序。正方形按照随机排序的顺序显示,如下图3所示。欢迎在下面留言,完善本文内容,让更多的人学到更完美的知识。
欢迎到知识星球:完美Excel社群,进行技术交流和提问,获取更多电子资料,并通过社群加入专门的微信讨论群,更方便交流。