How to use Excel VBA

Document Sample
How to use Excel VBA Powered By Docstoc
					Psyc 689 Spring 2008: Takashi Yamauchi                         1


How to use Excel VBA
     Open VBA

            Select Tools / Macro / Visual Basic Editor




            You type codes here. This is a property window.

            Worksheet1 is activated here.
Psyc 689 Spring 2008: Takashi Yamauchi        2

Writing a simple program
     Click Sheet 1 and type this
           Sub FirstProgram()
             Cells(1, 1).Value = "TY"
           End Sub
     Click ThisWorkbook and type this

           Sub FirstProgram2()
             Cells(1, 1).Value = "TY2"
           End Sub

     Insert Module and type this on Module1
            Sub FirstProgram3()
              Cells(1, 1).Value = "TY3"
            End Sub
Psyc 689 Spring 2008: Takashi Yamauchi                                   3




How to run a program

     Click on the program you want to run, and click the “run” button from
     VBE toolbar. (or select Run / Run Sub/UserForm)
Psyc 689 Spring 2008: Takashi Yamauchi                                       4



      Or insert a button on a spreadsheet, and run from there. (right click the
      menu bar on the spreadsheet and select “form”)




Try the following examples

Sub FirstProgram()
  Cells(1, 1).Value = "TY"

End Sub



'procedure
Sub testProcedure()
   Dim a As Integer
   Dim b As Integer
   Dim c As Integer

  Cells(1, 1).Value = "TY"
  Cells(1, 2).Value = 1 + 2 + 3

  a=1
  b=2
Psyc 689 Spring 2008: Takashi Yamauchi            5

  c=3
  Cells(1, 3).Value = (a + b) / c

  Cells(1, 4).Value = testFunction1(10)



End Sub

'function
Function testFunction1(x As Integer) As Integer

  testFunction1 = x * 10

End Function


'test data type
Sub testDataType()
   Dim a As Integer
   Dim b As Long
   Dim c As Double
   Dim d As String
   a = 10 / 3
   b = 10 / 3
   c = 10 / 3
   d = "done'"

  Cells(3, 1).Value = "integer"
  Cells(3, 2).Value = a
  Cells(4, 1).Value = "Long"
  Cells(4, 2).Value = b
  Cells(5, 1).Value = "Double"
  Cells(5, 2).Value = c
  Cells(6, 1).Value = d
End Sub

'array & variant data type
Sub testDataType2()
Dim a(4) As Variant
Psyc 689 Spring 2008: Takashi Yamauchi   6

  a(0) = Cells(1, 1)
  a(1) = Cells(1, 2)
  a(2) = Cells(1, 3)
  a(3) = Cells(1, 4)
  a(4) = Cells(1, 5)

  Cells(2, 1) = a(0)
  Cells(2, 2) = a(1)
  Cells(2, 3) = a(2)
  Cells(2, 4) = a(3)
  Cells(2, 5) = a(4)

End Sub

'if then
Sub testIfThen()

  Dim x As Variant

  x = Cells(7, 1)

  If x > 10 Then
     Cells(7, 2).Value = x + 10
  Else
     Cells(7, 2).Value = x - 10
  End If


End Sub

' for loop & while loop
Sub testForLoop()
   Dim i As Integer

  For i = 1 To 100
    Cells(9 + i, 1).Value = i
  Next

  i=1
Psyc 689 Spring 2008: Takashi Yamauchi   7

  Do While i <= 100
    Cells(9 + i, 3).Value = i
    i=i+1
  Loop
End Sub

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:16
posted:9/15/2012
language:English
pages:7