[竟赛题]十进制递增输入（已结贴）

戏子

<p><strong>Luckyguy2008的代码比较长，但也是速度最快的一位,也是本期的胜者：</strong><br/>Sub Start()</p><p>&nbsp;&nbsp;&nbsp; Dim i, j, k, row_max, row_temp, num_max, column_max, page, temp_array(), num() As Integer<br/>&nbsp;&nbsp;&nbsp; <br/>&nbsp;&nbsp;&nbsp; Dim number As Long</p><p>&nbsp;&nbsp;&nbsp; Dim temp_range As Range</p><p>&nbsp;&nbsp;&nbsp; Dim time_start As Double</p><p>&nbsp;&nbsp;&nbsp; time_start = Timer</p><p>&nbsp;&nbsp;&nbsp; Application.ScreenUpdating = False</p><p>&nbsp;&nbsp;&nbsp; row_max = 200</p><p>&nbsp;&nbsp;&nbsp; num_max = 999</p><p>&nbsp;&nbsp;&nbsp; page = Int((num_max + 1) / row_max) + IIf((num_max + 1) Mod row_max = 0, 0, 1)</p><p>&nbsp;&nbsp;&nbsp; For i = 1 To page - 1</p><p>&nbsp;&nbsp;&nbsp; Sheets.Add , Worksheets(Sheets.Count)</p><p>&nbsp;&nbsp;&nbsp; Next i</p><p>&nbsp;&nbsp;&nbsp; Sheets(1).Select</p><p>&nbsp;&nbsp;&nbsp; column_max = Len(num_max)</p><p>&nbsp;&nbsp;&nbsp; ReDim num(1 To column_max)</p><p>&nbsp;&nbsp;&nbsp; For i = 1 To column_max</p><p>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; num(i) = 10 ^ (column_max - i)</p><p>&nbsp;&nbsp;&nbsp; Next i</p><p>&nbsp;&nbsp;&nbsp; ReDim temp_array(1 To row_max, 1 To column_max)</p><p>&nbsp;&nbsp;&nbsp; number = 0</p><p>&nbsp;&nbsp;&nbsp; row_temp = row_max</p><p>&nbsp;&nbsp;&nbsp; For i = 1 To page</p><p>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; If i = page Then</p><p>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; row_temp = num_max Mod row_max + 1</p><p>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ReDim temp_array(1 To row_temp, 1 To column_max)</p><p>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; End If</p><p>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; For j = 1 To row_temp</p><p>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; For k = 1 To column_max</p><p>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; temp_array(j, k) = (number \ num(k)) Mod 10</p><p>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Next k</p><p>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; number = number + 1</p><p>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Next j<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Set temp_range = Sheets(i).Range("A1:" &amp; Chr(64 + column_max) &amp; row_temp)<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; temp_range.Value = temp_array</p><p>&nbsp;&nbsp;&nbsp; Next i</p><p>&nbsp;&nbsp;&nbsp; MsgBox 1000 * (Timer - time_start) &amp; "毫秒"</p><p>&nbsp;&nbsp;&nbsp; Application.ScreenUpdating = True<br/>&nbsp;&nbsp;&nbsp; <br/>End Sub<br/><strong>耗时15.47毫秒</strong></p>

[此贴子已经被作者于2007-5-1 10:22:58编辑过]

戏子

<strong>djyjysxxs代码如下：</strong><br/>Sub a()<br/>t = Timer<br/>Application.ScreenUpdating = False<br/>nn = Sheets.Count<br/>If nn &lt; 5 Then<br/>&nbsp;&nbsp; ActiveWorkbook.Sheets.Add after:=Worksheets(Worksheets.Count), Count:=5 - nn<br/>End If<br/>n = 1<br/>For k1 = 0 To 1<br/>For k2 = 0 To 9<br/>For k3 = 0 To 9<br/>&nbsp; Sheets(1).Cells(n, 1).Value = k1<br/>&nbsp; Sheets(1).Cells(n, 2).Value = k2<br/>&nbsp; Sheets(1).Cells(n, 3).Value = k3<br/>&nbsp; Sheets(2).Cells(n, 1).Value = k1 + 2<br/>&nbsp; Sheets(2).Cells(n, 2).Value = k2<br/>&nbsp; Sheets(2).Cells(n, 3).Value = k3<br/>&nbsp; Sheets(3).Cells(n, 1).Value = k1 + 4<br/>&nbsp; Sheets(3).Cells(n, 2).Value = k2<br/>&nbsp; Sheets(3).Cells(n, 3).Value = k3<br/>&nbsp; Sheets(4).Cells(n, 1).Value = k1 + 6<br/>&nbsp; Sheets(4).Cells(n, 2).Value = k2<br/>&nbsp; Sheets(4).Cells(n, 3).Value = k3<br/>&nbsp; Sheets(5).Cells(n, 1).Value = k1 + 8<br/>&nbsp; Sheets(5).Cells(n, 2).Value = k2<br/>&nbsp; Sheets(5).Cells(n, 3).Value = k3<br/>&nbsp; n = n + 1<br/>Next k3<br/>Next k2<br/>Next k1<br/>Application.ScreenUpdating = True<br/>MsgBox (Timer - t) * 1000<br/>End Sub<br/><strong>耗时375毫秒</strong>

[此贴子已经被作者于2007-5-1 10:24:32编辑过]

戏子

<strong>mxqchina2代码：<br/></strong>Sub 矩形1_单击()<br/>Application.ScreenUpdating = False<br/>t = Timer<br/>Dim arr(200, 3) As Integer<br/>Dim i, j, k, l As Integer<br/>For i = 1 To 200<br/>&nbsp; For j = 1 To 3<br/>&nbsp;&nbsp;&nbsp; arr(i, j) = Mid(Format(i - 1, "000"), j, 1)<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Next j<br/>Next i<br/>[a1:c200] = arr<br/>For k = 1 To 4<br/>Worksheets.Add after:=Sheets(k)<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; For l = 1 To 200<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; arr(l, 1) = arr(l, 1) + 2<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Next l<br/>[a1:c200] = arr<br/>Next k<br/>Sheets("sheet1").Select<br/>Application.ScreenUpdating = True<br/>MsgBox (Timer - t) * 1000<br/>End Sub<br/><strong>耗时31.25毫秒</strong>

[此贴子已经被作者于2007-5-1 10:25:07编辑过]

戏子

<strong>当看到lpz001 MM用写入公式法来解决时，我才真正知道，这次竞赛算法上几乎没有差距，暴汗~~~</strong>[em04][em04][em04]<br/>rivate Sub CommandButton1_Click()<br/>&nbsp;&nbsp;&nbsp; <br/>&nbsp;&nbsp;&nbsp; Application.ScreenUpdating = False<br/>&nbsp;&nbsp;&nbsp; t = Timer<br/>&nbsp;&nbsp;&nbsp; <br/>&nbsp;&nbsp;&nbsp; For i = 1 To 5<br/>&nbsp;&nbsp;&nbsp; <br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; On Error Resume Next<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; X = ThisWorkbook.Worksheets("sheet" &amp; i).Name<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; If Not Err.Number = 0 Then<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ThisWorkbook.Sheets.Add(after:=Worksheets("Sheet" &amp; i - 1)).Name = "Sheet" &amp; i<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Else<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Worksheets(X).Activate<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; End If<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; On Error GoTo 0<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; With ActiveSheet<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Na = .Name<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; .Names.Add Name:=Na &amp; "!number", RefersTo:=(Right(Na, 1) - 1) * 200<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; .Range("A1:A200").Formula = "=mod(Int((number+row()-1)/100),10)"<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; .Range("B1:B200").Formula = "=mod(Int((number+row()-1)/10),10)"<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; .Range("C1:C200").Formula = "=mod(number+row()-1,10)"<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; End With<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <br/>&nbsp;&nbsp;&nbsp; Next i<br/>&nbsp;&nbsp;&nbsp; <br/>&nbsp;&nbsp;&nbsp; Application.ScreenUpdating = True<br/>&nbsp;&nbsp;&nbsp; MsgBox (Timer - t) * 1000<br/>End Sub<br/><strong>耗时46毫秒</strong>

[此贴子已经被作者于2007-5-1 10:23:41编辑过]

Luckyguy2008

<p>请问老师，下面这句为什么当i&gt;1时就会出错，好象只有当Sheet为当前Sheet时才能使用。</p><p>Set temp_range = Sheets(i).Range(Cells(1,1),Cells(200,3))</p>

lpz001

<p>祝贺<strong><font size="6"><font color="#ff0000">Luckyguy2008</font>、<font color="#ff0000">mxqchina2</font></font></strong></p><p><strong><font color="#ff0000" size="6">先学习ing</font></strong></p>

lpz001

<p>后感：</p><p>拿到本题的第一反应就是应该用数组来解决问题，但在用二维数组生成了000---999后，发现自已不知道怎么把它写入各个工作表,所以放弃了数组转而用公式来解决问题。在给每个工作表定义名称"number"时，又钻进了牛角尖，忘了直接取用变量i,一直在工作表名称上打转转，以至于写出了一串令人费解可笑的代码。我无权去修改，呵呵，挂在那丢人吧。这儿给个简洁的。</p><p>rivate Sub CommandButton1_Click11()<br/>&nbsp;&nbsp;&nbsp; <br/>&nbsp;&nbsp;&nbsp; Application.ScreenUpdating = False<br/>&nbsp;&nbsp;&nbsp; <br/>&nbsp;&nbsp;&nbsp; t = Timer<br/>&nbsp;&nbsp;&nbsp; <br/>&nbsp;&nbsp;&nbsp; For i = 1 To 5<br/>&nbsp;&nbsp;&nbsp; <br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Worksheets.Add after:=Sheets(i)</p><p>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; With ActiveSheet<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Na = .Name<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; .Names.Add Name:=Na &amp; "!number", RefersTo:=(i - 1) * 200&nbsp;&nbsp;&nbsp; '定义局部名称<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; .Range("A1:A200").Formula = "=Int((number+row()-1)/100)"<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; .Range("B1:B200").Formula = "=mod(Int((number+row()-1)/10),10)"<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; .Range("C1:C200").Formula = "=mod(number+row()-1,10)"<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; End With<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <br/>&nbsp;&nbsp;&nbsp; Next i<br/>&nbsp;&nbsp;&nbsp; <br/>&nbsp;&nbsp;&nbsp; Application.ScreenUpdating = True<br/>&nbsp;&nbsp;&nbsp; <br/>&nbsp;&nbsp;&nbsp; MsgBox (Timer - t) * 1000<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <br/>End Sub</p><p></p><p>近来一直忙于猫抓老鼠的游戏，疏于学习，汗……</p><p>继续学习数组！</p>

qee用

<p>戏兄已有结论:本题的速度主要体现在写入工作表上,我的测试验证亦如此.</p><p>就本题特点,测试速度有两点主要影响因素提起讨论:</p><p>1.在毫秒级的测试中,对一段代码做有限次测试,受系统后台影响,其差别亦会很大,机器配置越低,差别越大,在我这儿(DURON700),往往超过10倍.在单位P4 3G为2倍左右.个人观点,对多数有限次,毫秒级差别应视为无差别.</p><p>2.application.screenupdating的使用.因application.screenupdating=false仅在代码运行期生效,代码停止后,系统会自动检查恢复application.screenupdating=true,如省略,由于未刷新界面,固全部任务并未真正完成.个人观点,测试素度应自第一句实质代码起,至application.screenupdating=true后止.</p><p>基于以上,MXQCHINA/Luckyguy2008/lpz001应按速度相同论.</p>

飞雨飘

<p>看完用兄贴我就想说所有代码都需application.screenupdating的使用或不使，否则对内部代码时间上的测试很不公平。另外在10毫秒上下可视同等速，因为同一段代码在每一次测试，都是不同的，或者可考虑连续测试十次求平均数，呵呵麻烦死戏子了。</p>

戏子

<p>谢谢用兄的提点!</p><p>用兄提出的异议解释如下:</p><p>偶在测试时已经考虑进去了,最终测试时偶发现差距的写入工作表中</p><p>所以我在测试时所有答案全部关闭了application.screenupdating进行测试</p><p>测试结果也是在关闭application.screenupdating后的测试结果</p><p>代码贴出来只是不想改变大家原来的写得原创内容</p><p>如飘兄所言,测试结果为连续十次不关闭EXCEL的情况下是平均数</p>