Defining Constants


You'd like to declare some constants and need to know the correct VBA syntax.


VBA constants are declared using the syntax Const Name As DataType = Value, as illustrated in Example 2-6.

Example 2-6. Declaring constants

 Const MyConstant As Integer = 14

 Public MyConstant2 As Double = 1.025


Just as with variables, you can declare constants with local or global scope. Constants declared within procedures have local scope; that is, they are available only within the procedure. Constants declared outside of any procedures at the beginning of a code module have module-level scope and can be used by any procedure within the module. As with variables, you can use the Public keyword, as illustrated in Example 2-6, when declaring constants at the beginning of a code module to make the constant available to all procedures in the project.

A constant is just that, constant. Once a constant is declared with its value set, you cannot change its value. Constants are useful for defining descriptive names to commonly used values such as scientific or engineering coefficients. For example, you could define a constant to represent p and use the constant Pi throughout your calculations rather than write out the numerical value.

Excel includes a built-in worksheet function PI that returns the value of p. You can actually use that function in your VBA code by accessing the function through the WorksheetFunction object, as in WorksheetFunction.Pi. See Recipe 2.13 for more information on Excel Objects.

Using Excel

Getting Acquainted with Visual Basic for Applications

Collecting and Cleaning Up Data


Statistical Analysis

Time Series Analysis

Mathematical Functions

Curve Fitting and Regression

Solving Equations

Numerical Integration and Differentiation

Solving Ordinary Differential Equations

Solving Partial Differential Equations

Performing Optimization Analyses in Excel

Introduction to Financial Calculations


Excel Scientific and Engineering Cookbook
Excel Scientific and Engineering Cookbook (Cookbooks (OReilly))
ISBN: 0596008791
EAN: 2147483647
Year: N/A
Pages: 206
Authors: David M Bourg © 2008-2020.
If you may any questions please contact us: