VBA for Excel 2007 tutorial-Procedure


VBA Procedure

Procedure is is a block of code to do a specific thing. In VBA, procedure is classified into sub-procedure and function. A sub-procedure is declared with the Sub keyword followed by its name and the End sub keywords are used to close the sub-procedure. The sub-procedure doesn’t return a value. A function is declared with the Function keyword followed by its name and the End Function keywords are used to close the function. The function returns a value.

-The Sub-procedure Declaration:
          Sub procedureName( [parameters])
                   code….
          End sub
 
          Example:
 
          Sub sum
                   Dim x,y,s As Integer
                   x=10
                   y=20
                   s=x+y
                   Msgbox "Total=" & s
          End Sub
         

-The Function Declaration:
 
          Function functionname([parameters]) As Datatype
                   code…
                   functionname=value (return value)
          End Function
         
          Example:
          Function getName() As String
                   Dim fname As String
                   fname=InputBox("Enter your name:")
                   getName=fname
          End Function
  -Calling the sub-procedure: After a sub-procedure was declared you
 can call it by using the prototype:
          Call procedurename([values of parameters])
          Example: To call the sum sub-procedure you can write:
          call sum
  -Calling the function: A function can return a value. Therefore, you can let a variable to get the value. The function can be called by using the prototype:

 Dim Variable_name As Data Type (That is the same as the function data type)
 Variable_name=getName
         
   Example:
 
        Private Sub Command0_Click()
           Call welcome 'calling welcome sub-procedure
        End Sub
 
       Function getName() As String
          Dim fname As String
          fname = InputBox("Enter your name:")
          getName = fname
       End Function
 
      Sub welcome()
         Dim fullname As String
         fullname = getName 'calling getName function
         MsgBox "Welcome " & fullname 

  End Sub


Comments





This website intents to provide free and high quality tutorials, examples, exercises and solutions, questions and answers of programming and scripting languages:
C, C++, C#, Java, VB.NET, Python, VBA,PHP & Mysql, SQL, JSP, ASP.NET,HTML, CSS, JQuery, JavaScript and other applications such as MS Excel, MS Access, and MS Word. However, we don't guarantee all things of the web are accurate. If you find any error, please report it then we will take actions to correct it as soon as possible.