VBA程序設(shè)計范例

1、高考VBA程序設(shè)計解析一、累加和問題：1、基本問題問題：編程計算1+2+3+100；程序界面：源代碼:Private Sub Command3_Click()Dim s As IntegerDim i As Integers = 0For i = 1 To 100 Step 1 s = s + iNext iText1.SetFocusText1.Value = sEnd SubPrivate Sub Command4_Click()DoCmd.Close acForm, "VBA程序設(shè)計范例1"End Sub2、問題拓展拓展1：編程計算1+3+5+7+99；源代碼修改：F

2、or i = 1 To 99 Step 2 s = s + iNext i拓展2：編程計算2+4+6+8+100；源代碼修改：For i = 2 To 100 Step 2 s = s + iNext i拓展3：編程計算1+2+3+N；程序界面：源代碼：Private Sub Command10_Click()Dim i As IntegerDim s As IntegerText3.SetFocusIf Trim(Text3.Text) = Space(0) Then MsgBox "請輸入N的值！", vbOKOnly Exit SubElseIf Val(Text3.

3、Text) < 1 Then MsgBox "請輸入大于1的正整數(shù)！", vbOKOnly Exit SubEnd Ifs = 0For i = 1 To Val(Text3.Value) Step 1 s = s + iNext iText8.SetFocusText8.Value = sEnd Sub拓展4：編程計算1+(1+2)+(1+2+3)+(1+2+3+4)+(1+2+3+4+ +100)；程序界面：源代碼：Private Sub Command3_Click()Dim i As Integer, j As IntegerDim s1 As Integer

4、, s2 As Doubles1 = 0s2 = 0For i = 1 To 100 Step 1 s1 = 0 For j = 1 To i s1 = s1 + j Next j s2 = s2 + s1Next iText1.SetFocusText1.Text = s2End Sub拓展5：編程計算1+(1+3)+(1+3+5)+(1+3+5+7)+(1+3+5+7+ +99)；源代碼：Private Sub Command3_Click()Dim i As Integer, j As IntegerDim s1 As Integer, s2 As Doubles1 = 0s2 = 0F

5、or i = 1 To 50 Step 1 s1 = 0 For j = 1 To i*2-1 step 2 s1 = s1 + j Next j s2 = s2 + s1Next iText1.SetFocusText1.Text = s2End Sub3、算法相關(guān)問題編程計算1*2*3* *100；提示：內(nèi)存變量的數(shù)據(jù)類型的確定；二、排序問題1、基本問題問題：提示用戶輸入兩個數(shù)，比較其大??；源代碼：Private Sub Command4_Click()Dim a1 As StringDim a2 As StringDim b1 As IntegerDim b2 As IntegerTex

6、t0.SetFocusa1 = Trim(Text0.Text)Text2.SetFocusa2 = Trim(Text2.Text)If a1 = Space(0) Or a2 = Space(0) Then MsgBox "請輸入兩個待比較的數(shù)！", vbOKOnly Exit SubEnd Ifb1 = Val(a1)b2 = Val(a2)Text8.SetFocusIf b1 < b2 Then Text8.Text = "小于"ElseIf b1 = b2 Then Text8.Text = "等于"Else Tex

7、t8.Text = "大于"End IfEnd Sub2、問題拓展拓展1：有一組數(shù)：3,2,7,1,5,4,12,10,8,6， 請找出這組數(shù)的最小（大）值；程序界面：源代碼：Private Sub Command4_Click()Dim maxvalue As IntegerDim minvalue As IntegerDim arr(1 To 10) As Integerarr(1) = 3arr(2) = 2arr(3) = 7arr(4) = 1arr(5) = 5arr(6) = 4arr(7) = 12arr(8) = 10arr(9) = 8arr(10) =

8、 6maxvalue = arr(1)minvalue = arr(1)For i = 2 To 10 If arr(i) > maxvalue Then maxvalue = arr(i) End If If arr(i) < minvalue Then minvalue = arr(i) End IfNext iText2.SetFocusText2.Text = maxvalueText3.SetFocusText3.Text = minvalueEnd Sub拓展2：有一組數(shù)：3,2,7,1,5,4,12,10,8,6， 請按升序排序輸出這組數(shù)；程序界面：源代碼：Priva

9、te Sub Command4_Click()Dim arr(1 To 10) As Integerarr(1) = 3arr(2) = 2arr(3) = 7arr(4) = 1arr(5) = 5arr(6) = 4arr(7) = 12arr(8) = 10arr(9) = 8arr(10) = 6For i = 1 To 9 For j = i + 1 To 10 If arr(i) > arr(j) Then temp = arr(i) arr(i) = arr(j) arr(j) = temp End If Next jNext iText2.SetFocusFor i =

10、1 To 10 Text2.Text = Text2.Text & arr(i) & ","Next iEnd Sub拓展3：輸入一組數(shù)， 請按升序排序輸出這組數(shù)；程序界面：源代碼:Private Sub Command6_Click()Dim arr(1 To 10) As IntegerDim i As IntegerDim j As IntegerDim temp As IntegerDim s1 As StringFor i = 1 To 10 arr(i) = InputBox("請輸入第" & i & &quo

11、t;個數(shù)：") s1 = s1 & arr(i) & ","Next iText1.SetFocusText1.Value = s1For i = 1 To 9 For j = i + 1 To 10 If arr(i) > arr(j) Then temp = arr(i) arr(i) = arr(j) arr(j) = temp End If Next jNext iText3.SetFocusFor i = 1 To 10 Text3.Text = Text3.Text & arr(i) & ","

12、Next iEnd Sub三、窗體設(shè)計1、基本問題源代碼：Private Sub Command11_Click()On Error GoTo Err_Command11_Click DoCmd.GoToRecord , , acFirstExit_Command11_Click: Exit SubErr_Command11_Click: MsgBox Err.Description Resume Exit_Command11_ClickEnd SubPrivate Sub Command12_Click()On Error GoTo Err_Command12_Click DoCmd.GoT

13、oRecord , , acPreviousExit_Command12_Click: Exit SubErr_Command12_Click: MsgBox Err.Description Resume Exit_Command12_ClickEnd SubPrivate Sub Command13_Click()On Error GoTo Err_Command13_Click DoCmd.GoToRecord , , acNextExit_Command13_Click: Exit SubErr_Command13_Click: MsgBox Err.Description Resume

14、 Exit_Command13_ClickEnd SubPrivate Sub Command14_Click()On Error GoTo Err_Command14_Click DoCmd.GoToRecord , , acLastExit_Command14_Click: Exit SubErr_Command14_Click: MsgBox Err.Description Resume Exit_Command14_ClickEnd Sub2、問題拓展拓展1：窗體查詢1源代碼：SQL查詢部分：SELECT * FROM 學(xué)生學(xué)籍 WHERE ;子窗體的源對象設(shè)置為查詢對象；查詢按鈕單擊

15、事件代碼：Private Sub Command6_Click()Dim studname As StringText2.SetFocusstudname = Trim(Text2.Text)If studname = Space(0) Then MsgBox "請輸入學(xué)生姓名！", vbOKOnlyElse End IfEnd Sub拓展2：窗體查詢2源代碼：SQL查詢部分：SELECT 學(xué)生學(xué)籍.班級編號, 學(xué)生學(xué)籍.學(xué)號, 學(xué)生學(xué)籍.姓名, 學(xué)生成績.學(xué)期, 學(xué)生成績.語文, 學(xué)生成績.數(shù)學(xué), 學(xué)生成績.英語, 學(xué)生成績.專業(yè)一, 學(xué)生成績.專業(yè)二, 學(xué)生成績.總分FROM 學(xué)生學(xué)籍 INNER JOIN 學(xué)生成績 ON (學(xué)生學(xué)籍.學(xué)號 = 學(xué)生成績.學(xué)號) AND (學(xué)生學(xué)籍.班級編號 = 學(xué)生成績.班級編號)WH