Lesson 15: 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.
In table 15.1, when a salesman attain a sale volume of $6000, he will be paid $6000x15%=$720.00. A visual basic function to calculate the commissions can be written as follows:
15.2 Using Microsoft Excel Visual Basic Editor
To create VBA functions in MS Excel, you can click on tools,
select macro and then click on Visual Basic Editor as shown in Figure
Figure 15.1: Inserting MS_Excel Visual Basic Editor
Upon clicking the Visual Basic Editor, the VB Editor windows will appear as shown in figure 15.2. To create a function, type in the function as illustrated in section 15.1 above After typing, save the file and then return to the Excel windows.
Figure 15.2 : The VB Editor
In the Excel window, type in the titles Sales Volume and Commissions in any two cells. By referring to figure 15.3, key-in the Comm function at cell C4 and by referencing the value in cell B4, using the format Comm(B4). Any value appear in cell B4 will pass the value to the Comm function in cell C4. For the rest of the rows, just copy the formula by dragging the bottom right corner of cell C4 to the required cells, a nice and neat table that shows the commissions will automatically appear (as shown in figure 15.3). It can also be updated anytime
Figure 15.3: MS Excel Windows- Sales Volume
Please refer to VBA Tutorial section for a complete tutorial on VBA for Excel