Airman
Lv 4
Airman 發問時間： 電腦與網際網路軟體 · 9 年前

VBA~InputBox的進階語法。

Private Sub CommandButton1_Click()Dim startrang%, endrang%, q%, t%, e%, s%, a, tim!,arr(48), brr(), tx%, ty%, b, Tname\$, d, shcount% NUM =InputBox("請選擇公式的起迄序號") Nrange =InputBox("請輸入運算的起迄期數","輸入期數") tim =Timer [B2] ="" [F2] ="" Numx =NUMFor N = Left(Numx, 2) To Right(Numx, 2) NUM = N Sheets(2).Range("D" & NUM).Copy Sheets("DATA").[T7].Select ActiveSheet.Paste Application.ScreenUpdating = False '在背景下執行 Application.Calculation = xlCalculationManual '手動計算 K = 0 For I =1 To Len(Nrange) IfMid(Nrange, I, 1) = "-" Then K = 1 startrang = Left(Nrange, I - 1) endrang = Mid(Nrange, I + 1, 4) EndIf Next If K = 0Then startrang = Nrange endrang = Nrange End IfFor mthcount = startrang To endrang

︰︰︰NextNext[T1].Select[B2] = Numx & "=>"[F2] = startrang & "~" & endrang& "=" & Format((Timer - tim) / 24 / 60 / 60,"hh:mm:ss")End Sub

^^"

1 個解答

• 顯栓
Lv 7
9 年前
最佳解答

Dim In1rr(), In2rr() numx = numFor x = 2 To Len(numx) 　If Mid(num, x, 1) = "," Then 　　m = m + 1 　　ReDim Preserve In1rr(m - 1) 　　In1rr(m - 1) = --Mid(numx, sta + 1, 2) 　　sta = x 　　x = x + 2 　End If 　If Mid(num, x, 1) = "-" Then 　　For y = Mid(numx, sta + 1, 2) To Mid(numx, x + 1, 2) 　　　 m = m + 1 　　　ReDim Preserve In1rr(m - 1) 　　　In1rr(m - 1) = y 　　Next 　　sta = x + 3 　　x = x + 5 　End If 　If x = Len(numx) Then 　　m = m + 1 　　ReDim Preserve In1rr(m - 1) 　　In1rr(m - 1) = --Mid(numx, sta + 1, 2) 　End IfNext m1 = 0 sta = 0 For I = 2 To Len(Nrange) 　If Mid(Nrange, I, 1) = "," Then 　　m1 = m1 + 1 　　ReDim Preserve In2rr(m1 - 1) 　　In2rr(m1 - 1) = --Mid(Nrange, sta + 1, I - sta - 1) 　　sta = I 　End If 　If Mid(Nrange, I, 1) = "-" Then 　　For J = I + 1 To I + 5 　　　If J > Len(Nrange) Then Exit For 　　　If Mid(Nrange, J, 1) = "," Then 　　　　Exit For 　　　End If 　　Next 　For y = Mid(Nrange, sta + 1, I - sta - 1) To Mid(Nrange, I + 1, J - I - 1) 　　m1 = m1 + 1 　　ReDim Preserve In2rr(m1 - 1) 　　In2rr(m1 - 1) = y 　Next 　sta = J 　I = J 　End If 　If I = Len(Nrange) Then 　　m1 = m1 + 1 　　ReDim Preserve In2rr(m1 - 1) 　　In2rr(m1 - 1) = --Mid(Nrange, sta + 1, I - sta) 　End If NextFor n = 1 To m 　num = In1rr(n - 1) 　　　： 　For m2 = 1 To m1 mthcount = In2rr(m2 - 1) 　　　： 　Next Next