土木工程专业毕业设计:关于excel“我爱你”vba的N种写法

来源:百度文库 编辑:我要文章网 时间:2019/11/20 14:49:51
关于excel“我爱你”vba的N种写法,欢迎续篇 

题目要求:用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