Creating VBA Functions For MS Excel

15.1 The Needs to Create VBA Functions in MS-Excel
You can create your own functions to supplement the built-in functions in Microsoft Excel spreadsheet, which are quite limited in some aspects. These user-defined functions are also called Visual Basic for Applications functions, or simply VBA functions. They are very useful and powerful if you know how to program them properly. One main reason we need to create user defined functions is to enable us to customize our spreadsheet environment for individual needs. For example, we might need a function that could calculate commissions payment based on the sales volume, which is quite difficult if not impossible by using the built-in functions alone. The code for VBA is illustrated on the right.

Table 15.1: Commissions Payment Table

Sales Volume($)	Commissons
<500	3%
<1000	6%
<2000	9%
<5000	12%
>5000	15%

Creating User-Defined Functions

14.1 Creating Your Own FunctionThe general format of a function is as follows:
Public Function functionName (Arg As dataType,..........) As dataType
or
Private Function functionName (Arg As dataType,..........) As dataType
* Public indicates that the function is applicable to the whole project and
Private indicates that the function is only applicable to a certain module or procedure. 

Example 14.1
In this example, a user can calculate the future value of a certain amount of money he has today based on the interest rate and the number of years from now, supposing he will invest this amount of money somewhere .The calculation is based on the compound interest rate.

 

Formatting Functions

Formatting output is a very important part of programming so that the data can be presented systematically and clearly to the users. Data in the previous lesson were presented fairly systematically through the use of commas and some of the functions like Int, Fix and Round. However, to have better control of the output format, we can use a number of formatting functions in Visual basic.

The three most common formatting functions in VB are Tab, Space, and Format 

(i) The Tab function

Tab (n); x

The item x will be displayed at a position that is n spaces from the left border of the output form. There must be a semicolon in between Tab and the items you intend to display (VB will actually do it for you automatically).

Example1

Private Sub Form_Activate

String Manipulation Functions

In this lesson, we will learn how to use some of the string manipulation function such as Len, Right, Left, Mid, Trim, Ltrim, Rtrim, Ucase, Lcase, Instr, Val, Str ,Chr and Asc.

(i)The Len Function 

The length function returns an integer value which is the length of a phrase or a sentence, including the empty spaces. The format is 

Len (“Phrase”) 

For example, 

Len (VisualBasic) = 11 and Len (welcome to VB tutorial) = 22 

The Len function can also return the number of digits or memory locations of a number that is stored in the computer. For example,