This tutorial follows on from the Create a custom function in Excel tutorial and refactors the code from that tutorial. It introduces constants and the concept of scope.

1. Refactor

Our starting point is the code from Create a custom function in Excel tutorial.

Option Explicit

Function VATTED(price)
    VATTED = price * 1.15
    VATTED = Application.Round(VATTED, 2)
End Function

Note specifically that it is not necessary to declare either VATTED or price.

Because the VAT rate will not — and must not — change while the program is running, it is a good idea to declare it as a Constant instead of a Variable.

Option Explicit
Public Const VATRATE As Integer = 15

Function VATTED(price)
    VATTED = Price + Price * VATRATE / 100
    VATTED = Application.Round(VATTED, 2)
End Function

2. Data types

Constants can be declared as one of the following data types: Boolean, Byte, Integer, Long, Currency, Single, Double, Date, String, or Variant. [1]

3. Scope

The constant is declared within a Procedure or at the top of the Module, in the Declarations section.

The Public keyword means that it can be accessed from anywhere in the Project. Setting the Constant to Private would mean that the Constant would be available only in the Module it was declared in. We refer to this as scope.

Constants are Private by default.

4. Refactor

An improvement on the above code might arguably be to use Double as the type for the VATRATE constant as it simplifies the calculation in line 5:

Option Explicit
Public Const VATRATE As Double = 0.15

Function VATTED(price)
    VATTED = Price + Price * VATRATE
    VATTED = Application.Round(VATTED, 2)
End Function

References:

  1. Microsoft Learn, o365devx. (no date) Declaring constants (VBA). Available at: https://learn.microsoft.com/en-us/office/vba/language/concepts/getting-started/declaring-constants (Accessed: 7 July 2024).

By MisterFoxOnline

Mister Fox AKA @MisterFoxOnline is an ICT, IT and CAT Teacher who has just finished training as a Young Engineers instructor. He has a passion for technology and loves to find solutions to problems using the skills he has learned in the course of his IT career.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.