Vba Basics

The VBA Language

Overview

Overview

• The VBA Project and Components
• Data Types
• Objects
• Operators
• Essential Functions

The VBA Project

• Top‐level container for all application components
• Can be accessed and modified through code

• Requires a reference to Microsoft Visual Basic for Applications Extensibility
• Requires the ‘Trust access to the VBA project object model’ option to be set in the Trust Center

• Automating the project should never be used for applications

• Only developer tools and add‐ins

VBA Components

• Directly below the project in the Project Explorer
• Four Types of Components

• Documents (application‐specific)
• Modules (general‐purpose code containers)
• Class Modules (for creating non‐visual objects)
• UserForms (for creating custom dialogs)

• Interfaces

• Created as Class Modules
• Implemented in Class Modules or UserForms

Data Types

• Variant type
• String type
• Integer types
• Boolean type
• Floating‐point types
• Scaled types
• Date type
• Enumerations
• Object types
• User‐Defined types

Variant Data Type

• The default data type
• Special ‘container’ that can hold any type of data

• Including arrays or objects

• Automatically takes on the correct data type on assignment
• Often used in ‘For Each’ or other types of loops
• Carries some overhead as the price for flexibility
• Don’t use unless necessary
• Default value is Empty

String Data Type

• For holding character (non‐numeric) data
• Two forms of strings

• Variable length (~ 2B characters)
• Fixed length (1 to 64K characters)

• VBA code will not compile if you declare a string > 64K
• Default value for variable length is an empty string (“”)
• Default value for fixed length is the size of the string filled with spaces
• All strings are stored internally as Unicode
• Automatically converted to ANSI as needed

• Byte (1 byte)

□        0 to 255

• Integer (2 bytes)

□        ‐32,768 to 32,767

• Long (4 bytes)

□        ‐2,147,483,648 to 2,147,483,647

• Long Long (8 bytes – only on 64‐bit systems)

□        ‐9,223,372,036,854,775,808 to 9,223,372,036,854,775,807

• Long Ptr

• Same as Long (Dword) on 32‐bit systems
• Same as LongLong (Qword) on 64‐bit systems

• The default value for all Integer types is 0

• Used for storing True or False values
• Default value is False
• Stored as 16‐bit (2‐byte) integers, but can only be True or False
• When numerics are converted to Boolean, 0 = False, anything else = True (Not False)
• When Booleans are converted to numbers, False = 0, True = ‐1

• The most basic unit of information in computing is the bit
• Can have 2 values – 0 (cleared) and 1 (set)
• You can ‘flip’ a bit to toggle between 0 and 1
• A group of 4 bits is a nibble
• A group of 8 bits is a byte (2 nibbles) sometimes called an octet
• Groups of bytes are often referred to as words

• Word (2 bytes – 16 bits)
• Dword (4 bytes – 32 bits)
• Qword (8 bytes – 64 bits)

• Use string data type to store and display bit representations

• Values are based on their position
• Bits are usually ordered from right to left

• Least Significant Bit is on the right
• Most Significant Bit is on the left

• Bit numbering systems

• LSB 0 means the LSB is numbered as bit 0 (right to left) 76543210
• MSB 0 means the MSB is numbered as bit 0 (left to right) 01234567

• Bit values can be determined by power of 2 for their position

□        2^0 = 1

□        2^1 = 2

□        2^2 = 4

□        2^3 = 8, etc …

• Unsigned max value equals the sum of all the bit values
• Signed max is half the sum of all bit values

• Set MSB to indicate a negative value

Binary

Decimal

There are only 10 types of

people in the world:

...