Create worksheet functions

With VBA you can create functions and use them in workbook. This can be really cool to do, especially to avoid huge formulas using Excel native functions. This is however rarely a good choice in terms of computing efficiency, so you really have to weigh the pros and cons.

With VBA you can create functions and use them in the worksheets. This can be really cool to do, especially to avoid huge formulas using Excel native functions. This is however rarely a good choice in terms of computing efficiency, so you really have to weigh the pros and cons.

Why to use VBA custom worksheet functions

You can do loads of things with Excel formulas, and they are optimized, so that it is nearly always more computationally efficient to use Excel formulas instead of VBA to do something. However complex tasks can lead you to horrendous formulas. Handling errors ( IFERROR ), using conditions (IF( Condition, Then do this , Or otherwise do this) ), lookup functions ( VLOOKUP, HLOOKUP , the very powerful INDEX( MATCH MATCH) combo, INDIRECT …) can create massive formulas. Then it makes sense to use VBA to create a custom function. Compared to a large Excel formula, the VBA custom function will be:

  • Clearer for the user, leaner,
  • Easier to debug and maintain thanks to VBA's debugging tools.

VBA sub procedure can create the same effect as a custom function, except the function will be much easier to use and adapt in a workbook. The worksheet template could change for example, the custom excel function can then be modified quickly (by changing the parameters), but the sub will be broken until you dig into the code to repair it. Compared to a sub procedure, the VBA custom function will be:

  • More flexible: parameters don't need to be hard-coded,
  • Clearer for the user: he can see the input, the functon and the output, while the Sub procedure is a blackbox

How to create a custom VBA worksheet function

The theory is very simple: go to your VBA programming window (ALT+F11), in the project window on the left, click on Sheet1 and simply write this:

Function MyFunction(YourName)
    MyFunction = “Hello World! And hello to you ” & YourName
End Function

You have created a VBA function!! Yeah!

Now you can use it in Sheet1 (only). If you want to be able to use it anywhere in the workbook, simply right click anywhere in the project window and select “Insert >> Module”. Copy the same code in a module and you are done!

Note that the value given to the variable MyFunction (the same name as the function) is the result of the function that will be displayed in Excel. This is actually how VBA knows what value to return once the function has ran. At the end of the execution of the function, the value of the result can be attributed to the function like it would to a variable: [Name of the function] = [Result of the function].

Now how to use it? Go in the workbook and in a cell, type =MyFunction(“Mat”), press enter and observe the result in the cell: “Hello World! And hello to you Mat”.

Some code sample on example

Let us take an example. We want to build a function that retrieves some data in a table depending on some conditions. Below is a very simple dataset on rocket classes around the world (with largely invented data, as I am not a spaceship expert):

Name

Producing company

Launch country

Can go to the Moon?

Cost MUSD

height (m )

Soyuz 2

OKB-1

Kazakstan

Yes

150

46

Soyuz 3

OKB-1

Kazakstan

Yes

120

44

Ariane 5

Airbus

French Guiana

No

160

50

Antares

Orbital ATK

United States

Yes

100

40

Minotaur 4

Orbital Sciences

United States

No

50

24

We want a function that will simply give us the cheapest rocket among those that fit some criteria on the launch country, producer and ability to reach the Moon.

A straight forward way to do it would be to loop through all the line and list those that match the criteria. And from those, choose the one with the smallest cost.

 

 If TableArea.Cells(1, 1).Offset(row, colConytry).Value = country And _
    TableArea.Cells(1, 1).Offset(row, colConstruct).Value = constructor And _
      TableArea.Cells(1, 1).Offset(row, colMoon).Value = moon Then
         If BestPriceYet > TableArea.Cells(1, 1).Offset(row, colPrice).Value Then
           'If all conditions are fulfiled, check the price
           'BestPriceYet tracks the lowest price recorded yet in the loop for a rocket matching the conditions,
           'If a new best price is found, the value returned by the function given on GetCheapestRocket_only
           'If is changed and the BestPriceYet variable is updated to the new lowest price
           BestPriceYet = TableArea.Cells(1, 1).Offset(row, colPrice).Value
           GetCheapestRocket_onlyIf = TableArea.Cells(1, 1).Offset(row, colName).Value
        End If 
End If

Tips and Tricks

Don’t forget that VBA code is less optimized than Excel native functions . It is sometime possible to replace a piece of code by an Excel function. Average, Min, Max, Sum can be used with great gains in computing efficiency, as they usually can replace loops on a range of cells.

Type “ Application.WorksheetFunction.[FunctionName] ” and you can use most Excel functions. VBA also provide lots of maths functions, access them typing “ VBA.[FunctionName] ”. However they are usually restricted to basic operations. Here is an example of useful functions:

Native Excel

Min

Max

Sum

Average

Match, vlookup, hlookup

Index

To access full code sample for this example, do this quiz. It will test your knowledge on Excel functions and VBA on custom functions.

Matthieu Liatard
Remember this!
Se souvenir de cela
Play!
Tester mes connaissances
Close

Bonjour :)
Avec Question-Player, apprenez de nouvelles choses et n'oubliez plus rien. Jamais. Et en plus c'est gratuit.

Pour nous aider, vous pouvez créer un compte:

Login / Créer un compte
Notez cet article :
1
2
3
4
5

Articles similaires