求助大侠优化下这段代码 数度很慢
情况是这样的 我想处理excel 里边的内容 excel 内容如下
如图片所示 连续十二行来看 给定一个表格行的数字 要求往上12行 符合条件就涂色 条件： 第一到第12行的竖着下来 单元格里边第一位数是“合”或者全部是“质” 那么全部标黄色。或者 假如第二个数全部是“合”或者全部是“质”也标黄色 ， 第3位也一样。。 反正竖着看 连续12行的就标一样颜色
所以我的编程思路是这样的 在给定具体哪一行后由text1控件得到 j 循环所有列数 读取给定行数的j上面11行 然后读取到数组 bsq()中去 然后用mid 把每个单元格里边的数据 分割成3个 数据 存储 到 数组中去bsh(1 to 12 ,1 to 3)中。。然后进行比较是否相同。。
如果相同的就把这个12个单元格涂色。。。 问题就来了 由于我要处理大量的表格 处理起来特别的慢 我想请问大家要如何优化下这个过程代码。。谢谢大家了
Dim i, j, k As Integer
Dim bsq(1 To 10) As String
Dim bsh(1 To 12, 1 To 3)
Dim xlApp As Excel.Application
Dim xlbook As Excel.Workbook
Dim xlsheet As Excel.Worksheet
Set xlApp = New Excel.Application
xlApp.Visible = False
Set xlbook = xlApp.Workbooks.Open(App.Path & "\" + Text1.Text + ".xls")
Set xlsheet = xlbook.Worksheets(i)
j = Text2.Text
For k = 1 To 150
bsq(10) = xlsheet.Cells(j, k)
bsq(9) = xlsheet.Cells(j - 1, k)
bsq(8) = xlsheet.Cells(j - 2, k)
bsq(7) = xlsheet.Cells(j - 3, k)
bsq(6) = xlsheet.Cells(j - 4, k)
bsq(5) = xlsheet.Cells(j - 5, k)
bsq(4) = xlsheet.Cells(j - 6, k)
bsq(3) = xlsheet.Cells(j - 7, k)
bsq(2) = xlsheet.Cells(j - 8, k)
bsq(1) = xlsheet.Cells(j - 9, k)
bsq(11) = xlsheet.Cells(j - 10, k)
bsq(12) = xlsheet.Cells(j - 11, k)
bsh(1, 1) = Mid$(bsq(1), 1, 1)
bsh(2, 1) = Mid$(bsq(2), 1, 1)
bsh(3, 1) = Mid$(bsq(3), 1, 1)
bsh(4, 1) = Mid$(bsq(4), 1, 1)
bsh(5, 1) = Mid$(bsq(5), 1, 1)
bsh(6, 1) = Mid$(bsq(6), 1, 1)
bsh(7, 1) = Mid$(bsq(7), 1, 1)
bsh(8, 1) = Mid$(bsq(8), 1, 1)
bsh(9, 1) = Mid$(bsq(9), 1, 1)
bsh(10, 1) = Mid$(bsq(10), 1, 1)
bsh(11, 1) = Mid$(bsq(11), 1, 1)
bsh(12, 1) = Mid$(bsq(12), 1, 1)
bsh(1, 2) = Mid$(bsq(1), 2, 1)
bsh(2, 2) = Mid$(bsq(2), 2, 1)
bsh(3, 2) = Mid$(bsq(3), 2, 1)
bsh(4, 2) = Mid$(bsq(4), 2, 1)
bsh(5, 2) = Mid$(bsq(5), 2, 1)
bsh(6, 2) = Mid$(bsq(6), 2, 1)
bsh(7, 2) = Mid$(bsq(7), 2, 1)
bsh(8, 2) = Mid$(bsq(8), 2, 1)
bsh(9, 2) = Mid$(bsq(9), 2, 1)
bsh(10, 2) = Mid$(bsq(10), 2, 1)
bsh(11, 2) = Mid$(bsq(11), 2, 1)
bsh(12, 2) = Mid$(bsq(12), 2, 1)
bsh(1, 3) = Mid$(bsq(1), 3, 1)
bsh(2, 3) = Mid$(bsq(2), 3, 1)
bsh(3, 3) = Mid$(bsq(3), 3, 1)
bsh(4, 3) = Mid$(bsq(4), 3, 1)
bsh(5, 3) = Mid$(bsq(5), 3, 1)
bsh(6, 3) = Mid$(bsq(6), 3, 1)
bsh(7, 3) = Mid$(bsq(7), 3, 1)
bsh(8, 3) = Mid$(bsq(8), 3, 1)
bsh(9, 3) = Mid$(bsq(9), 3, 1)
bsh(10, 3) = Mid$(bsq(10), 3, 1)
bsh(11, 3) = Mid$(bsq(11), 3, 1)
bsh(12, 3) = Mid$(bsq(12), 3, 1)
If bsh(1, 1) = bsh(10, 1) And bsh(2, 1) = bsh(10, 1) And bsh(3, 1) = bsh(10, 1) And bsh(4, 1) = bsh(10, 1) And bsh(5, 1) = bsh(10, 1) And bsh(6, 1) = bsh(10, 1) And bsh(7, 1) = bsh(10, 1) And bsh(8, 1) = bsh(10, 1) And bsh(9, 1) = bsh(10, 1) And bsh(11, 1) = bsh(10, 1) And bsh(12, 1) = bsh(10, 1) _
Or bsh(1, 2) = bsh(10, 2) And bsh(2, 2) = bsh(10, 2) And bsh(3, 2) = bsh(10, 2) And bsh(4, 2) = bsh(10, 2) And bsh(5, 2) = bsh(10, 2) And bsh(6, 2) = bsh(10, 2) And bsh(7, 2) = bsh(10, 2) And bsh(8, 2) = bsh(10, 2) And bsh(9, 2) = bsh(10, 2) And bsh(11, 2) = bsh(10, 2) And bsh(12, 2) = bsh(10, 2) _
Or bsh(1, 3) = bsh(10, 3) And bsh(2, 3) = bsh(10, 3) And bsh(3, 3) = bsh(10, 3) And bsh(4, 3) = bsh(10, 3) And bsh(5, 3) = bsh(10, 3) And bsh(6, 3) = bsh(10, 3) And bsh(7, 3) = bsh(10, 3) And bsh(8, 3) = bsh(10, 3) And bsh(9, 3) = bsh(10, 3) And bsh(11, 3) = bsh(10, 3) And bsh(12, 3) = bsh(10, 3) _
Then
xlsheet.Cells(j, k).Interior.ColorIndex = 6
xlsheet.Cells(j - 1, k).Interior.ColorIndex = 6
xlsheet.Cells(j - 2, k).Interior.ColorIndex = 6
xlsheet.Cells(j - 3, k).Interior.ColorIndex = 6
xlsheet.Cells(j - 4, k).Interior.ColorIndex = 6
xlsheet.Cells(j - 5, k).Interior.ColorIndex = 6
xlsheet.Cells(j - 6, k).Interior.ColorIndex = 6
xlsheet.Cells(j - 7, k).Interior.ColorIndex = 6
xlsheet.Cells(j - 8, k).Interior.ColorIndex = 6
xlsheet.Cells(j - 9, k).Interior.ColorIndex = 6
xlsheet.Cells(j - 10, k).Interior.ColorIndex = 6
xlsheet.Cells(j - 11, k).Interior.ColorIndex = 6
End If
Next
[解决办法]
关闭屏幕刷新，写个方法把列数替换成列号（A,B,C...)，替换颜色的时候用xlsheet.range("A1:A12")这样的替换
[解决办法]
xlsheet.Cells(j, k).Interior.ColorIndex = 6
xlsheet.Cells(j - 1, k).Interior.ColorIndex = 6
xlsheet.Cells(j - 2, k).Interior.ColorIndex = 6
xlsheet.Cells(j - 3, k).Interior.ColorIndex = 6
xlsheet.Cells(j - 4, k).Interior.ColorIndex = 6
xlsheet.Cells(j - 5, k).Interior.ColorIndex = 6
xlsheet.Cells(j - 6, k).Interior.ColorIndex = 6
xlsheet.Cells(j - 7, k).Interior.ColorIndex = 6
xlsheet.Cells(j - 8, k).Interior.ColorIndex = 6
xlsheet.Cells(j - 9, k).Interior.ColorIndex = 6
xlsheet.Cells(j - 10, k).Interior.ColorIndex = 6
xlsheet.Cells(j - 11, k).Interior.ColorIndex = 6
这样excel的处理很慢，改为xlsheet.range(xlsheet.Cells(j - 11, k),xlsheet.Cells(j, k)).Interior.ColorIndex = 6这样应该快点，你试试
