Programming Excel with VBA and .NET : Variables (part 1) - Names & Declarations

- How To Install Windows Server 2012 On VirtualBox
- How To Bypass Torrent Connection Blocking By Your ISP
- How To Install Actual Facebook App On Kindle Fire
11/15/2011 9:15:00 AM

Variables are names that your code uses to refer to pieces of information. I've already shown lots of variables in code and used the word many times—you can't say much about programming without doing that and fortunately variables aren't a difficult concept to grasp, but there's a lot of details to know about them.

The following sections tell you all you need to know (possibly all there is to know) about variables in Visual Basic.

1. Names

Anything that you name in Visual Basic (variables, procedures, classes, etc.) has to follow certain rules. For example, if you try to name module 1Off, you'll get an error (Figure 1).

Figure 1. Not all names are allowed in Visual Basic

To be valid in Visual Basic, a name must:

  • Start with a letter (A-Z)

  • Not include any of the restricted characters listed in Table 2-1

  • Not be one of the Visual Basic restricted words listed in Table 1

  • Be less than 256 characters long

  • Be unique within its scope (more on scope later)

Table 1. Characters you can't use in Visual Basic names

The last seven characters in Table 1 (in bold) are allowed if used as the last character in a name—in that case, they identify the data type of the variable. That is a holdover from older versions of Basic and it's not a good idea to use that practice in modern programs.

The words listed in Table 2 are restricted because Visual Basic couldn't determine the meaning of certain statements if they were allowed as variable or procedure names. In some cases, the word is no longer commonly used in Visual Basic programs (Rem, GoSub), but the restriction remains for compatibility with earlier versions.

Table 2. Words that can't be used as names in Visual Basic

2. Declarations

Visual Basic has automatic variables by default. That means a new variable is created the first time you use it. This makes life somewhat easier for beginning programmers, but it makes things harder when writing and maintaining complex programs. For that reason, most experts recommend that you require variable declarations by adding Option Explicit to the beginning of each class or module.

Option Explict turns off Visual Basic's automatic variables and thus requires that you declare each variable before you use it. To declare a variable, use the Dim statement:

    Dim x As Integer

The preceding code declares that the name x is a variable that can contain an integer. The 12 different types of variables in Visual Basic are listed in Table 3.

Table 3. Data types for variables in Visual Basic
TypeKind of dataSizeValues
BooleanTrue/false choices2 bytesTrue (0), False (-1)
ByteBinary data1 byte0-255
CurrencyMonetary values8 bytes−922,337,203,685,477.5808 to 922,337,203,685,477.5807
DateA date or time8 bytes1 January 100 to 31 December 9999
DoubleLarge decimal numbers8 bytes1.79769313486231E308 to −4.94065645841247E-324 for negative values and from 4.94065645841247E-324 to 1.79769313486232E308 for positive values
IntegerWhole numbers2 bytes−32,768 to 32,767
LongLarge whole numbers4 bytes−2,147,483,648 to 2,147,483,647
ObjectAn instance of a class4 bytesAddress of the object in memory
SingleDecimal values4 bytes3.402823E38 to −1.401298E-45 for negative values and from 1.401298E-45 to 3.402823E38 for positive values
StringText values4 bytes0 to approximately 2 billion (231) characters
String (fixed)Fixed-length text values1 byte per character1 to 10,000 characters
VariantData that might be any type4 bytesSame as numeric and String types

If you don't specify a type when declaring a variable, Visual Basic makes it a Variant by default.

You can use any of the types listed in Table 2-3 as part of a Dim statement. For example, the following line declares integer, single, and string variables:

    Dim i as Integer, s As Single, str As String

Most of the types in Table 3 are value types . Those types are stored as real values in an area of memory called the stack . The stack is a place that Visual Basic can access very quickly, but it has a limited size and can accommodate only variables that have fixed lengths. Some types, such as Object, String, and Variant don't have fixed lengths and so Visual Basic handles those as reference types . Reference types store a 4-byte number on the stack that resolves to the address where the data is actually stored.

String variables are kind of a special case because they can be value types or reference types depending on whether or not they have a fixed length. Most strings have a variable length—that is, they can grow or shrink as needed to fix the data they are assigned. However, you can define the length of a string if you like:

    Dim fs As String * 12

The preceding line declares a fixed-length string 12 characters long. Visual Basic stores fs as a value type on the stack, but it truncates any strings that are more than 12 characters:

    fs = "This is way too long for a 12-character string."

Becomes This is way. Fixed-length strings are mainly used in combination with advanced programming techniques such as reading binary files.

Modern computers come with lots of memory, and you're not likely to run out while programming in Excel. So why show size in Table 2-3? A few reasons:

  • The size of a variable helps you understand its limits. For example, Integers are 2 bytes (which is 16 bits) and so have 65,536 (2^16) possible values. When you divide that between negative and positive numbers, you get a range of -32,768 to 32,767. Numbers outside that range result in an overflow error if assigned to an Integer variable.

  • Size matters when converting from one data type to another. Larger types can cause overflow errors when converted to smaller types.

  • You need to know the size of data types when creating user-defined types, reading binary data, or performing bitwise operations.

  •  Windows Vista : Performing Local PC Administration (part 2) - Performing common workstation administration tasks
  •  Windows Vista : Performing Local PC Administration (part 1) - Working with workstation administration tools
  •  Filtering Out Evil with Firewalls (part 3) - Manually Configuring a Firewall's Ports
  •  Filtering Out Evil with Firewalls (part 2)
  •  Filtering Out Evil with Firewalls (part 1)
  •  Windows 7 : Windows Driver Foundation Architecture (part 4) - Tools for Development and Testing
  •  Windows 7 : Windows Driver Foundation Architecture (part 3) - Driver Frameworks
  •  Windows 7 : Windows Driver Foundation Architecture (part 2) - Integrated I/O Queuing and Cancellation
  •  Windows 7 : Windows Driver Foundation Architecture (part 1)
  •  Windows 7 : Using Advanced Security Options (part 2) - Configuring Windows Defender
    Top 10
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Finding containers and lists in Visio (part 2) - Wireframes,Legends
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Finding containers and lists in Visio (part 1) - Swimlanes
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Formatting and sizing lists
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Adding shapes to lists
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Sizing containers
    - Microsoft Access 2010 : Control Properties and Why to Use Them (part 3) - The Other Properties of a Control
    - Microsoft Access 2010 : Control Properties and Why to Use Them (part 2) - The Data Properties of a Control
    - Microsoft Access 2010 : Control Properties and Why to Use Them (part 1) - The Format Properties of a Control
    - Microsoft Access 2010 : Form Properties and Why Should You Use Them - Working with the Properties Window
    - Microsoft Visio 2013 : Using the Organization Chart Wizard with new data
    Video Sports
    programming4us programming4us