560 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Variables and Constants in Excel Visual Basic
Part eight of an eight-part series of blogs
This blog explains the nuts and bolts of Excel VBA variables, including how to declare them, how to set their scope and all sorts of other tips and tricks. The blog also covers constants in Excel macros.
There are two differences between a constant and a variable:
You set a value for a constant when you first declare it; and
You can not subsequently change this value.
To continue the suitcase analogy from earlier: a constant is like a suitcase which, once locked, can never be re-opened.
Here are some examples of constants that you might create:
'the name of the current client
Public Const CompanyName As String = "Wise Owl"
'the folder containing model files
Public Const FilePath As String = "c:\model\"
'the current VAT rate (UK sales tax)
Public Const VatRate As Double = 0.2
Constants don't have to be public - you can declare them in a procedure as follows:
'the cell reference of hero name
Const HeroNameCell As String = "C4"
Const HeroRatingCell As String = "C6"
'the name of each superhero
Dim HeroName As String
'the rating assigned to them
Dim HeroRating As Long
'go to the votes sheet and get the value of the superhero, and their rating
HeroName = Range(HeroNameCell).Value
HeroRating = Range(HeroRatingCell).Value
Good things to hold in constants are file paths, worksheet names and workbook names.
If you want to create a truly global constant - one whose value will be available within all workbooks, all of the time - you need to create an add-in.
|Parts of this blog|
25 Aytoun Street