关于excel“我爱你”vba的N种写法
来源:百度文库 编辑:神马文学网 时间:2024/07/03 10:50:01
题目要求:用vba,在a1:a99写入,“我爱你”,循环
就是A1=我,a2=爱,a3=你,a4=我,a5=爱,a6=你,,,,这样循环
----------------------------------------
Sub 方法1()
T = Timer
For i = 1 To 99
y = i Mod 3
Select Case y
Case Is = 1
Range("A" & i) = "我"
Case Is = 2
Range("A" & i) = "爱"
Case Is = 0
Range("A" & i) = "你"
End Select
Next
Range("A100") = Timer - T '0.109375秒
End Sub
---------------------------------------------
Sub 方法2()
T = Timer
For i = 1 To 99 Step 3
Range("A" & i) = "我"
Range("A" & i + 1) = "爱"
Range("A" & i + 2) = "你"
Next
Range("A100") = Timer - T '0.125秒
End Sub
---------------------------------------
Sub 方法3()
T = Timer
HL = Array("我", "爱", "你")
For i = 1 To 99 Step 3
Range("A" & i).Resize(3, 1) = WorksheetFunction.Transpose(HL)
Next
Range("A100") = Timer - T '0.03125秒
End Sub
-------------------------------------------
Sub 方法4()
T = Timer
HL = WorksheetFunction.Rept("我爱你", 33)
For i = 1 To 99
Range("A" & i) = Mid(HL, i, 1)
Next
Range("A100") = Timer - T '0.109375秒
End Sub
-----------------------------------------------
Sub 方法5()
T = Timer
HL = WorksheetFunction.Rept("我,爱,你,", 1000)
xm = Split(HL, ",")
r = UBound(xm)
Range("a1:a" & r + 1) = WorksheetFunction.Transpose(xm)
Range("A100") = Timer - T '0.015625秒0.234375
End Sub
----------------------------------------
Sub 方法6()
'杰堂远程教室(327712892)提供
xx = Timer
For i = 1 To 99
Select Case i Mod 3
Case 1
ad1 = ad1 & ",a" & i
Case 2
ad2 = ad2 & ",a" & i
Case 0
ad3 = ad3 & ",a" & i
End Select
Next i
Sheet1.Range(Right(ad1, Len(ad1) - 1)) = "我"
Sheet1.Range(Right(ad2, Len(ad2) - 1)) = "爱"
Sheet1.Range(Right(ad3, Len(ad3) - 1)) = "你"
Sheet2.Range("b1") = Timer - xx '0.015625秒
End Sub
------------------------------------
Sub 方法7()
'深山老猫(38677446)
Dim Ts As Double
Ts = Timer
Dim myarrange(1 To 99) As String
For i = 1 To 99
myarrange(i) = Mid("你我爱", (i Mod 3) + 1, 1)
Next i
Range("A1:A99") = WorksheetFunction.Transpose(myarrange)
Range("B1") = Timer - Ts
End Sub
--------------------------------
飞翔(65921751) 20:11:38
最简单最快的写法
Sub 方法8()
Dim a(1 To 99, 1 To 1)
Dim b, n As Byte
Dim t As Double
t = Timer
b = Array("我", "爱", "你")
For i = 1 To 99
n = IIf(n > 2, 0, n)
a(i, 1) = b(n)
n = n + 1
Next
[a1:a99] = a
MsgBox Format(Timer - t, "0.00")'0.016秒
End Sub