By using expertatexcel.com you agree to our cookie policy, We and our partners operate globally and use cookies, for multiple purposes

expertatexcel.com

 


Home
What can Excel do?
Learning Excel Books
Basic concepts Immediate window Macros 101 Syntax Use existing code Hello world Math formulas Functions Modifying Recorded Macros
Selecting Moving data Formatting Clipboard Range names Filters Sorting Fill Combining Hyperlinks Comments Options Vlookup Conditional Formatting Data validation Pivot Tables Charts Charts
Debugging Strings Active This vba statements VBA functions Run stuff
Workbook Sheet Cells Rows Column Range Selection Methods Properties Events Collections Chart
Variables Data types variables/scope Conditionals Loops functions/sub Arrays testing Batch Software dev techniques Backup/Recovery Documentation Wrappers webservice Refactor
#Errors Problems Too Slow/Optimize Error handling Problem Solving Method Someone else's sheet complex formula
Date/times Code Generation Random Shortcuts Forms Special Chars Customize your training Work and learn
Reporting Sample projects Reconcliation Adjusting data Generate Slides Special Characters
Outlook Web Database Files PPT Word API Objects Script host Dos Bloomberg JIRA Input Spreadsheets or files
Stocks Bitcoin Sports Job Productivity Project Management Tracking Health Games Programmer

Variables

Variables are one of the most basic concepts of a programming language.

They store information that you can retrieve at a later time, sort of like short-term memory.

It's a way of adding a new word that VBA can understand. That word or variable has a value associated with it that can change over time. If you access the variable you'll get the latest value of it.

If you close Excel the value in a variable is lost.

Variable can store different types of data.

The most common are numbers and strings.

Variables have a name and naming standards.

New programmers should declare variables and use the Option Explicit command.

You should try to use names that have meaning, for example

sales_tax = price * tax_rate. Note that the * (Star or asterisk) mean multiply in this context.

You can declare variables via the Dim statement.

Dim i, j, sales_tax, price, tax_rate

By default the type is variant, which means it can be any type of data.

You can also declare what type the variable is

Dim i as integer, s as String, v as Variant.

You store data to a variable via the assignment statement.

i = 3
s = "some string"
v = 103.5

If you have the setting Option Explicit turned on (Recommended for New programmers) you are forced to declare all variables in the module, form, object or class.

In the VB editor go to Tools > Options...



Set Require Variable Declaration

This allows VBA to tell you when you've made a typing mistake.

Enter the following:


Sub test_Option_Explicit
dim sMisspelled_variable As String

sMisspelled_variable = "judgment"

debug.print "The word " & sMispelled & " is often misspelled"

End Sub

When you try the run the program you get



This allows you to find bugs at Compile time vs. Run time. A user might have your software and you could have easily found the bug.

Why do you recommend Option Explicit?

Variables can have a Scope
  • Global
  • Module
  • Local


    •