Excel 2019 Bible

Part VI: Automating Excel

See Chapter 42, “Introducing Visual Basic for Applications,” for an overview of the VBE.

Seeing a Simple Example

Creating custom functions is relatively easy after you understand VBA. Without further

ado, here’s an example of a VBA function procedure. This function is stored in a VBA mod-

ule, which is accessible from the VBE.

Creating a custom function

This example function, named NumSign, uses one argument. The function returns a text

string of Positive if its argument is greater than zero, Negative if the argument is less

than zero, and Zero if the argument is equal to zero. If the argument is nonnumeric, the

function returns an empty string. Figure 43.1 shows the NumSign function.

FIGURE 43.1

A simple custom worksheet function

You can, of course, accomplish the same effect with the following worksheet formula,

which uses nested IF functions:

=IF(ISNUMBER(A1),IF(A1=0,"Zero",IF(A1>0,"Positive","Negative")),"")

Many would agree that the custom function solution is easier to understand and to edit

than the worksheet formula.