![visual basic editor excel 2016 compile error syntax visual basic editor excel 2016 compile error syntax](https://www.oreilly.com/library/view/writing-excel-macros/0596003595/httpatomoreillycomsourceoreillyimages45639.png)
Print Energy & " Joules" End SubĮven though we never defined the speed of light constant, c, in this module, our subroutine successfully calculates the energy as 3.78370639456939E-19 Joules. This module will allow you to calculate the energy of an object based on its mass.Ĭonst h As Double = 6.62607015 * 10 ^ ( - 34 ) 'J/Hz - Planck Constant Sub PlanckEinstein500 () 'v=c/lambda and E=hv 'E=h*c/lambda Dim lambda As Double 'nanometers, wavelength Dim Energy As Double lambda = 500 Energy = h * c / ( lambda * 10 ^ - 9 ) Debug. In the mMassEnergy module, you’ll declare a project level constant using the Public Const declaration. You can see both of our modules in this screenshot from our Project Explorer Pane. Let’s say you have a module named mMassEnergy and a module named mWavelengthEnergy. This is going to be a fun scientific example so stick with me. Project level constants can be accessed by any subroutine or function inside your workbook, even if they’re in different modules. Project Level Constants with VBA Public Const You don’t want to accidentally change the value of c when accessing it in different procedures! That’s the risk you’d run into if you had declared it as a variable. You can start to see the appeal of using constants with this example. We succesfully referenced the speed of light constant, c, in both subroutines. In this example, we used Einstein’s famous E=mc 2 special relativity formula to calculate the energy of an object with two different masses. Once you do that, you can access the constant from any procedure (subroutine or function) in that module. Declarations must be made at the top of your module and must not be inside a procedure. This is the key for module level constants. It’s declared outside of any subroutine or function. Notice the Const declaration was placed at the top of your module. Print Energy End Sub Sub SpecialRelativity50 () 'E=mc^2 Dim mass As Double Dim Energy As Double mass = 50 'kg Energy = mass * c ^ 2 Debug. Let’s walk through an example.Ĭonst c As Double = 299792458 'm/s Speed of Light in a Vacuum Sub SpecialRelativity100 () 'E=mc^2 Dim mass As Double Dim Energy As Double mass = 100 'kg Energy = mass * c ^ 2 Debug. The general syntax is Const Name as Type = Value where Name is the name of your constant, Type is the data type you want to declare and Value is the value you want to assign to the constant. Procedure level constants in VBA are declared inside your procedure using the Const keyword. Constants with this highest level of scope are called project level constants. The final method makes the constant available in all modules and procedures in your entire workbook. That’s why these constants are called module level constants. The second method lets you access the constant from any procedure within a single module. These are called procedure level constants. The first method only makes the constant available in the procedure (subroutine or function) where it’s declared. There are 3 ways to declare a constant in VBA.
VISUAL BASIC EDITOR EXCEL 2016 COMPILE ERROR SYNTAX HOW TO
This tutorial will show you how to declare constants and will explain the difference in scope between a constant declared using a Const statement and one declared using a Public Const statement. Unlike variables, you can’t change the value of a VBA constant once it’s declared. Recall that you typically declare variables using Dim statements, instead. To declare constants, you use the VBA Const and Public Const statements. I often find myself using VBA constants when working with scientific equations. You should declare constants in your VBA project when you need a value defined and you know the value won’t change.