You can find the Excel tips you're looking at Pro-business Excel VBA Programming by entering the keyword in the textbox below and clicking the "Google Search" button.

Wednesday, August 1, 2007

Creating a Procedure in Excel VBA

Situation:
You need to create module procedures to start your programming. You want two procedures to display the phrase “Hello World!” on the screen.

Solution:
There are three types of procedures in Excel VBA: Sub, Function and Property. A Sub procedure performs actions but doesn’t return a value; a Function procedure, however, returns a value. The Property procedure is a type of procedure used in Class module. I will only give example of Sub and Function procedures in here.


Source Code:

















Output:




Explanation:
The program consists of two procedures:
1.) Procedure1

“Procedure1” is an example of SUB procedure. The first line “Sub Procedure1()” is the declaration of the procedure. The line “Dim sString As String” is a variable declaration. To know more about variable declaration, please go to topic “Declaration of Variable”.
The next line is what make this procedure interesting, “sString = Procedure2”. The variable sString value is being changed to the value that Procedure2 provides. Below discusses how Procedure2 works. Whatever Procedure2 provides, the next line invoke the MsgBox command to display a message box with the sString value as the message.
2.) Procedure2
“Procedure2” is an example of Function procedure. The first line “Function Procedure2() As String” simply declares the procedure and states that the return value of it is a string value. Inside the procedure, the only line is:
Procedure2 = “Hello World!”
This means that the procedure will return the value “Hello World!” whenever Procedure2 is used within the program. Thus, the line “sString = Procedure2” in the first procedure also means:
sString = “Hello World!”



Click here to subscribe and receive Pro-business Excel VBA Programming tips.


If you like this post in Pro-business Excel VBA Programming, buy me a coffee.


0 comments: