通过一题十解谈拓宽编程思路

好奇心 · 发表于 2013-4-6 08:48

俗话说：站在什么样的山头唱什么样的山歌。楼主既然是在此发表东东，就是基于VBA的资源而展示其对某一问题的多种解决方法。因此，只要编程的代码少，效率(代码的执行有所下降)还行，代码易于维护，就值得称赞。比如："字典"的方法，大家对其的运用已经熟能生巧，如果能用"字典"解决的问题(如一楼的问题或者不仅限于此问题)，您偏要用"数组"的方法来解决，就会编程的代码多，代码不易于维护。所以说：充分利用本地的资源，较好地解决问题，是非常简单而有效的方法。

mmwwdd · 发表于 2013-4-6 10:20

一味的追求编程技巧，就失去了编程目标

liuzhouaa11 · 发表于 2013-4-6 11:07

原来是这样呀

香川群子 · 发表于 2013-4-6 11:09

本帖最后由香川群子于 2013-4-6 11:18 编辑

office2008 发表于 2013-4-6 01:17
应该不算完美
以上数据只有第2和第5 是正确的.

取得A列最大值的方法，经过比较确认，结果大致如下：

A列单元格个数超过200时，直接用工作表函数Application.max([Range])的方法速度效率高。

仅当A列单元格个数<200时，数组For……Next遍历比较方法速度更快一些。
并且有个前提是原始数据已经读入数组了。
（这个条件一般是具备的，即代码本来就需要把原始数据预先读入VBA内存数组使用）

而这时候的时间差异其实是很小的。【就本例来说，数组循环快了 0.000028秒】

……
因此，不是一定要全部工作都必须通过数组搞定的。
我的代码方法kagawa2中的直接Max用法也是可以的。

Sub SpeedTest()
Dim ii&, nn%, i&, m&, n&
nn = 4 '指定循环比较次数=10^nn
m = 200 '直接指定A列计算行数(即A列单元格个数)
tms = Timer
For ii = 1 To 10 ^ nn
n = Application.max(Range("A1").Resize(m)) '用工作表函数Max直接获取最大值
Next
MsgBox "Max " & Format(Timer - tms, "0.000s ") & n
arr = Range("A1").Resize(m) '单元格内容读入数组，然后才可以比较
tms = Timer
For ii = 1 To 10 ^ nn
For i = 1 To m
If arr(i, 1) > n Then n = arr(i, 1) '数组循环遍历比较取得最大值
Next
Next
MsgBox "For " & Format(Timer - tms, "0.000s ") & n
End Sub

复制代码

liucqa · 发表于 2013-4-6 11:21

香川群子发表于 2013-4-6 11:09
取得A列最大值的方法，经过比较确认，结果大致如下：

A列单元格个数超过200时，直接用工作表函数App ...

你用vb指针测试一下数组的速度

junbo213 · 发表于 2013-4-6 13:10

太强大了

ベ.わそ德 · 发表于 2013-4-6 14:12

学习了.谢谢分享.

香川群子 · 发表于 2013-4-6 16:52

本帖最后由香川群子于 2013-4-6 17:07 编辑

liucqa 发表于 2013-4-6 11:21
你用vb指针测试一下数组的速度

呵呵，如果是用工作表函数Max求VBA内存数组的最大值，
速度显然没有VBA内存数组直接For……Next循环快。（数组循环速度最快）

但是，如果同样使用工作表函数Max，但【求和对象】为【工作表单元格区域】的引用时，
则速度会比VBA内存数组直接For……Next循环【更快】。

这可能不符合很多人的常识，但事实如此。

推测或许Excel在把数据写入工作表单元格内时，已经做了一些数值计算、统计的工作，方便引用。
因此速度加快。

Sub test()
Dim i&, j&, k&, m&, n&
m = 10000 '数据行数m
n = 100 '数据列数n
ReDim arr&(1 To m, 1 To n)
For i = 1 To m
For j = 1 To n
arr(i, j) = Rnd * 16777216
Next
Next
[a1].Resize(m, n) = arr '产生随机数并写入工作表【这很重要！】
'以下为速度比较：
Dim ii&, nn&, tms
nn = 1 '速度比较重复次数nn，可以调整
tms = Timer
For ii = 1 To nn
k = Application.Max(arr) '用工作表函数Max检查【VBA数组】的最大值
' k = WorksheetFunction.Max(arr)
Next
MsgBox "工作表Max求数组对象耗时：" & vbCr & Format(Timer - tms, "0.000s ") & k
tms = Timer
For ii = 1 To nn
k = Application.Large(arr, 1) '用工作表函数Large检查【VBA数组】的最大值
' k = WorksheetFunction.Large(arr, 1)
Next
MsgBox "工作表Large求数组对象耗时：" & vbCr & Format(Timer - tms, "0.000s ") & k
tms = Timer
For ii = 1 To nn
k = Application.Large(Range("A1").Resize(m, n), 1) '用工作表函数Large检查【区域】的最大值
Next
MsgBox "工作表Large求Range区域耗时：" & vbCr & Format(Timer - tms, "0.000s ") & k
tms = Timer
For ii = 1 To nn
k = Application.Max(Range("A1").Resize(m, n)) '用工作表函数Max检查【区域】的最大值
Next
MsgBox "工作表Max求Range区域耗时：" & vbCr & Format(Timer - tms, "0.000s ") & k
'因为可以直接利用工作表内容，所以速度提高！
'推测是数据写入工作表函数的过程中，Excel已经做了很多基础工作，包括求总和所以速度更快。
tms = Timer
For ii = 1 To nn
k = 0
For i = 1 To m
For j = 1 To n
If arr(i, j) > k Then k = arr(i, j) '数组循环比较查找最大值
Next
Next
Next
MsgBox "数组循环比较耗时：" & vbCr & Format(Timer - tms, "0.000s ") & k
End Sub

复制代码

测试时，可以按以下组合：
1. m=10000: n=1 即1万行1列时: 速度比较循环次数 nn=100
2. m=10000: n=10 即1万行10列时: 速度比较循环次数 nn=10
3. m=10000: n=100 即1万行100列时: 速度比较循环次数 nn=1

结果都是：工作表Max函数求Range区域对象的最大值时，速度比VBA内存数组的For……Next遍历比较还要快！

而且，已经dim arr()为长整型，更加方便VBA中数组的运算效率了。

香川群子 · 发表于 2013-4-6 17:13

但是，如果是用 Application.max(arr)的方式进行，确实是速度更慢，远远不如VBA内存数组直接进行比较了。

因此结论是：
1. 如果数据仅仅是存在于VBA过程中的内存数组中，没有也不需要写入工作表单元格中去时，应该用VBA内存数组直接循环比较求最大值。

2. 如果数据已经存在于工作表单元格区域中，则不必读入数组而直接用Application.max([Range])的方法求最大值更快。

liucqa · 发表于 2013-4-6 17:13

本帖最后由 liucqa 于 2013-4-6 17:15 编辑

香川群子发表于 2013-4-6 16:52
呵呵，如果是用工作表函数Max求VBA内存数组的最大值，
速度显然没有VBA内存数组直接For……Next循环快 ...

office本身是C/C++写的，使用的是C API，速度自然比VBA快多了，不是有人说C的速度比VB快10倍比VBA快100倍嘛。

谁要是没事可以用C++自己写代码，搞个XLL，看看计算最大值的速度。另外，算法用分而治之算法也许比较快，具体可以百度

		自动登录	找回密码
密码			免费注册

[原创] 通过一题十解谈拓宽编程思路

浏览过的版块