Function, Sub, Method, Property Differences

This tutorial explains differences among Function, Sub, Method, Property Excel / Access VBA.

You may also want to read:

Difference between Dim and Set

Excel VBA Option Explicit to force declaration of variables

Difference between ByRef ByVal

Function, Sub, Method, Property Differences

In my blog, I have categorized different VBA topics by their properties. If you don’t understand Function, Sub, Method, Property Differences, you will find my tips confusing and difficult to use. Those terms are fundamentals in Excel VBA, read this article before you starting writing VBA!

Function (or Function Procedure)

Function is something you have already learned in high school or even elementary school. Consider the following formula

y = 2x+3 OR

y = f(x)

In the above formula, value of y depends on x. In mathematics we call y “dependent variable” and x as “independent variable”, because y depends on x.

We also call this formula “y is a function of x”, as formula 2x+3 or f(x) returns value y.

In Excel worksheet, you must have used Function before, such as SUM, COUNT, TEXT, VLOOKUP. The returned value depends on the Function parameter ( or argument).

Below is a summary of Function.

1) Function usually requires one or more parameters (arguments) from user

2) Function returns a value

3) Function can be used independently, which means the use of formula is not bounded to used only by specific Cells or Worksheets

Similar to Worksheet Function (those you use everyday in Excel), VBA also offers a lot of additional Functions but they come from a completely different library. You may still find the Excel VBA library quite similar to Worksheet library but they are different in terms of syntax and you fail to find many Worksheet Function in Excel VBA library.

Sub (or Sub Procedure)

Sub is a set of codes to run within key word Sub and End Sub. You can think of it as a list of instructions of what you want VBA to do in order.

Take an example, you want your son to do the followings in the morning: brush the teeth, change clothes, eat breakfast, go to school, then you write those down these instructions for your son to execute.

Note the below key differences between Sub and Function.

1) Sub can take parameters from user

2) Sub cannot return a value

Method

Method can only be used in VBA, but not in Excel worksheet. In terms of declaration, Method is same as Function except that Method only works for particular Object.

In Excel, “Object” mostly refers to Range, Worksheet, Workbook. Each Object has it own set of Methods, for example, Range has its exclusive Method called “Autofill” used to autofill formula in the cells below.

Set sourceRange = Worksheets("Sheet1").Range("A1:A2") 
Set fillRange = Worksheets("Sheet1").Range("A1:A20") 
sourceRange.AutoFill Destination:=fillRange

You should be very carefully that different Object may use the same Method name, but they can be entirely different things.

Below are some attributes of Method

1) Method usually requires one or more parameters (arguments) from user, but allow no argument

2) Method always return something, which can be an Object, a Number, a String

3) Method requires an Object to act on, it cannot be used independently

4) Method has its own library, do not mix up Function library with Method Library

Property

Property is the “attribute” or “characteristics” of an Object, can be used in VBA only. For example, Range has attributes such as color and border color.¬† You can set a Property to a Range, or you can retrieve the Property value from a Range.

Property has the ability to set and retrieve value because Property is declared as a pair, one declares using Let, one declares using Get, but there are Property that do not have the retrieval part declared.

Below is an example to set a formula in Range A1.

Range("A1").Formula = "=A4+A10"

Or you can retrieve a formula from A1

val = Range("A1").Formula

Outbound References

https://www.youtube.com/watch?v=cMMBZwkF124

 

Wyman W
Wyman is a Human Resources professional based in Hong Kong, specialized in business analysis, project management, data transformation with Access and Excel.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *