zhenning
Uploaded by
30 SLIDES
307 VUES
300LIKES

EXCEL VBA tutorial

DESCRIPTION

EXCEL VBA tutorial

1 / 30

Télécharger la présentation

EXCEL VBA tutorial

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Excel VBA Tutorial

  2. Private Sub CommandButton1_Click() • Dim YourName As String • Dim BirthDay As Date • Dim Income As Currency • YourName = "Alex" • BirthDay = "1 April 1980" • Income = 1000 • Range("A1") = YourName • Range("A2") = BirthDay • Range("A3") = Income • End Sub

  3. Private Sub CommandButton1_Click()MsgBox ("Welcome to VBA Programming")End Sub

  4. Private Sub CommandButton1_Click()Dim message As Integermessage = MsgBox("Click Yes to Proceed, No to stop", vbYesNoCancel, "Login")If message = 6 ThenRange("A1").Value = "You may proceed"ActiveWorkbook.ActivateElseIf message = 7 ThenActiveWorkbook.CloseEnd IfEnd Sub

  5. Private Sub CommandButton3_Click()Dim message As Integermessage = MsgBox("Click Yes to Proceed, No to stop", vbYesNo, "Login")If message = 6 ThenRange("A1").Value = "You may proceed"ActiveWorkbook.ActivateElseIf message = 7 ThenActiveWorkbook.CloseEnd IfEnd Sub

  6. Private Sub CommandButton1_Click()Dim i As IntegerFor i = 1 To 10Cells(i, 1).Value = iNextEnd Sub

  7. Private Sub CommandButton1_Click()Dim i, j As IntegerFor i = 1 To 10For j = 1 To 5Cells(i, j).Value = i + jNext jNext iEnd Sub

  8. Private Sub CommandButton1_Click()Dim counter As IntegerDocounter = counter + 1Cells(counter, 1) = counterLoop While counter < 10

  9. Private Sub CommandButton1_Click()Dim counter As IntegerDo Until counter = 10counter = counter + 1Cells(counter, 1) = 11 - counterLoopEnd Sub

  10. rivate Sub CommandButton1_Click()Dim counter , sum As Integer • 'To set the alignment to centerRange("A1:C11").SelectWith Selection.HorizontalAlignment = xlCenterEnd WithCells(1, 1) = "X"Cells(1, 2) = "Y"Cells(1, 3) = "X+Y"Do While counter < 10counter = counter + 1Cells(counter + 1, 1) = counterCells(counter + 1, 2) = counter * 2sum = Cells(counter + 1, 1) + Cells(counter + 1, 2)Cells(counter + 1, 3) = sumLoopEnd Sub

  11. Select Case   variable Case value 1      Statement Case value 2      Statement Case value 3      Statement . . . . Case Else End Select

  12. Private Sub CommandButton1_Click() • Dim mark As SingleDim grade As Stringmark = Cells(1, 1).Value • 'To set the alignment to centerRange("A1:B1").SelectWith Selection.HorizontalAlignment = xlCenterEnd WithSelect Case markCase 0 To 20grade = "F"Cells(1, 2) = gradeCase 20 To 29grade = "E"Cells(1, 2) = gradeCase 30 To 39grade = "D"Cells(1, 2) = gradeCase 40 To 59grade = "C"Cells(1, 2) = gradeCase 60 To 79grade = "B"Cells(1, 2) = gradeCase 80 To 100grade = "A"Cells(1, 2) = gradeCase Elsegrade = "Error!"Cells(1, 2) = gradeEnd SelectEnd Sub

  13. The format to set the font color is •                      cells(i,j).Font.Color=RGB(x,y,x), where x ,y , z can be any number between 1 and 255 • For example • cells(1,1).Font.Color=RGB(255,255,0) will change the font color to yellow • The format to set the cell's background color is • cells(i,j).Interior.Color=RGB(x,y,x), where x ,y , z can be any number between 1 and 255

  14. Private Sub CommandButton1_Click()Randomize TimerDim i, j, k As Integeri = Int(255 * Rnd) + 1j = Int(255 * Rnd) + 1k = Int(255 * Rnd) + 1Cells(1, 1).Font.Color = RGB(i, j, k)Cells(2, 1).Interior.Color = RGB(j, k, i)End Sub

  15. Private Sub CommandButton1_Click()Dim i, counter As IntegerFor i = 1 To 20If Cells(i, 2).Value > 50 Thencounter = counter + 1Cells(i, 2).Font.ColorIndex = 5Else'do nothingCells(i, 2).Font.ColorIndex = 3End IfNext iCells(21, 2).Value = counterCells(22, 2).Value = 20 - counterEnd Sub

  16. Private Sub cmdInstr_Click()Dim phrase As Stringphrase = Cells(1, 1).ValueCells(4, 1) = InStr(phrase, "ual")End SubPrivate Sub cmdLeft_Click()Dim phrase As Stringphrase = Cells(1, 1).ValueCells(2, 1) = Left(phrase, 4)End SubPrivate Sub cmdLen_Click()Dim phrase As Stringphrase = Cells(1, 1).ValueCells(6, 1) = Len(phrase)End SubPrivate Sub cmdMid_Click()Dim phrase As Stringphrase = Cells(1, 1).ValueCells(5, 1) = Mid(phrase, 8, 3)End SubPrivate Sub cmdRight_Click()Dim phrase As Stringphrase = Cells(1, 1).ValueCells(3, 1) = Right(phrase, 5)

  17. Private Sub CommandButton1_Click()Dim rng, cell As Range, selectedRng As StringselectedRng = InputBox("Enter your range")Set rng = Range(selectedRng)For Each cell In rngIf cell.Value >= 50 Thencell.Font.ColorIndex = 5Elsecell.Font.ColorIndex = 3End IfNext cellEnd Sub

  18. Creating a Quadratic Equation Solver • Private Sub CommandButton1_Click() • Dim a, b, c, det, root1, root2 As Singlea = Cells(2, 2)b = Cells(3, 2)c = Cells(4, 2)det = (b ^ 2) - (4 * a * c)If det > 0 Thenroot1 = (-b + Sqr(det)) / (2 * a)root2 = (-b - Sqr(det)) / (2 * a)Cells(5, 2) = Round(root1, 2)Cells(6, 2) = Round(root2, 2)ElseIfdet = 0 Thenroot1 = (-b) / 2 * aCells(5, 2) = root1Cells(6, 2) = root1ElseCells(5, 2) = "No root"End IfEnd Sub

  19. Creating a BMI Calculator • Private Sub CommandButton1_Click()Dim weight, height, bmi, x As Singleweight = Cells(2, 2)height = Cells(3, 2)bmi = (weight) / height ^ 2Cells(4, 2) = Round(bmi, 1)If bmi <= 15 ThenCells(5, 2) = "Under weight"ElseIfbmi > 15 And bmi <= 25 ThenCells(5, 2) = "Optimum weight"ElseCells(5, 2) = "Over weight"End IfEnd Sub

  20. Creating a Financial Calculator • Private Sub CommandButton1_Click()Dim N As IntegerDim p, pmt, rate, I, PVIFA As Doublep = Cells(2, 2)rate = Cells(3, 2)N = Cells(4, 2) * 12I = (rate / 100) / 12PVIFA = 1 / I - 1 / (I * (1 + I) ^ N)pmt = p / PVIFACells(5, 2) = Format(pmt, "$#,##0.00")End Sub

  21. End • http://excelvbatutor.com/vba_chp1.htm

More Related