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.
In this tutorial:
Required knowledge:
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:
- 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).